Обсуждение: [PERFORM] Memory question on win32 systems
On Thu, May 29, 2008 at 2:11 AM, Douglas McNaught <doug@mcnaught.org> wrote:
On Wed, May 28, 2008 at 7:05 PM, Sabbiolina <sabbiolina@gmail.com> wrote:How are you measuring this?
> Hello, in my particular case I need to configure Postgres to handle only a
> few concurrent connections, but I need it to be blazingly fast, so I need it
> to cache everything possible. I've changed the config file and multiplied
> all memory-related values by 10, still Postgres uses only less than 50 Mb of
> my RAM.
Task Manager
Post the settings values you're using and people will be better able
> I have 4 Gigs of RAM, how do I force Postgres to use a higher part of such
> memory in order to cache more indexes, queries and so on?
to help you.
-Doug
config:
port = 5432 # (change requires restart)
max_connections = 50 # (change requires restart)
shared_buffers = 320MB # min 128kB or max_connections*16kB
temp_buffers = 80MB # min 800kB
max_prepared_transactions = 10 # can be 0 or more
work_mem = 10MB # min 64kB
maintenance_work_mem = 160MB # min 1MB
Max_stack_depth = 20MB # min 100kB
max_fsm_pages = 2048000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 10000 # min 100, ~70 bytes each
shared_preload_libraries = '$libdir/plugins/plugin
_debugger.dll' # (change requires restart)
wal_buffers = 640kB # min 32kB
log_destination = 'stderr' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_line_prefix = '%t ' # special values:
datestyle = 'iso, dmy'
lc_messages = 'Italian_Italy.1252' # locale for system error message
lc_monetary = 'Italian_Italy.1252' # locale for monetary formatting
lc_numeric = 'Italian_Italy.1252' # locale for number formatting
lc_time = 'Italian_Italy.1252' # locale for time formatting
default_text_search_config = 'pg_catalog.italian'
wal_buffers = 640kB # min 32kB
log_destination = 'stderr' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_line_prefix = '%t ' # special values:
datestyle = 'iso, dmy'
lc_messages = 'Italian_Italy.1252' # locale for system error message
lc_monetary = 'Italian_Italy.1252' # locale for monetary formatting
lc_numeric = 'Italian_Italy.1252' # locale for number formatting
lc_time = 'Italian_Italy.1252' # locale for time formatting
default_text_search_config = 'pg_catalog.italian'
On Thu, May 29, 2008 at 4:26 AM, Sabbiolina <sabbiolina@gmail.com> wrote: > On Thu, May 29, 2008 at 2:11 AM, Douglas McNaught <doug@mcnaught.org> wrote: >> On Wed, May 28, 2008 at 7:05 PM, Sabbiolina <sabbiolina@gmail.com> wrote: >> > Hello, in my particular case I need to configure Postgres to handle only >> > a >> > few concurrent connections, but I need it to be blazingly fast, so I >> > need it >> > to cache everything possible. I've changed the config file and [this should really be on -perform] This question gets asked a lot...by tweaking memory settings in postgresql.conf you can only reserve memory for one thing at the expense of the other. Shared buffers, for example will reserve space for the postgresql built in buffer cache instead of letting the operating system use it's own cache. Even if your shared buffers were set to the minimum setting, you would still not be going to disk very much if your active working set of files fit in RAM. work_mem and other settings _can_ have a drastic impact on performance, because they can force the server to use query plans depending on how much memory it has for things like sorting. You didn't give much detail...maybe if you gave examples of queries you don't think are going fast enough (with explain analyze)? merlin
Sabbiolina wrote:
Need to Use Performance monitor and Process Explorer to track down the caching on windows machines and performance.On Thu, May 29, 2008 at 2:11 AM, Douglas McNaught <doug@mcnaught.org> wrote:On Wed, May 28, 2008 at 7:05 PM, Sabbiolina <sabbiolina@gmail.com> wrote:How are you measuring this?
> Hello, in my particular case I need to configure Postgres to handle only a
> few concurrent connections, but I need it to be blazingly fast, so I need it
> to cache everything possible. I've changed the config file and multiplied
> all memory-related values by 10, still Postgres uses only less than 50 Mb of
> my RAM.
Task ManagerPost the settings values you're using and people will be better able
> I have 4 Gigs of RAM, how do I force Postgres to use a higher part of such
> memory in order to cache more indexes, queries and so on?
to help you.
-Doug
config:
port = 5432 # (change requires restart)
max_connections = 50 # (change requires restart)
shared_buffers = 320MB # min 128kB or max_connections*16kB
temp_buffers = 80MB # min 800kB
max_prepared_transactions = 10 # can be 0 or more
work_mem = 10MB # min 64kB
maintenance_work_mem = 160MB # min 1MB
Max_stack_depth = 20MB # min 100kB
max_fsm_pages = 2048000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 10000 # min 100, ~70 bytes each
shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll' # (change requires restart)
wal_buffers = 640kB # min 32kB
log_destination = 'stderr' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_line_prefix = '%t ' # special values:
datestyle = 'iso, dmy'
lc_messages = 'Italian_Italy.1252' # locale for system error message
lc_monetary = 'Italian_Italy.1252' # locale for monetary formatting
lc_numeric = 'Italian_Italy.1252' # locale for number formatting
lc_time = 'Italian_Italy.1252' # locale for time formatting
default_text_search_config = 'pg_catalog.italian'
Postgresql does not work like MS SQL where when it start ups grabs a large chuck memory that it uses to keep last X number of queries along with the indexes and tables in memory then its pushed out only when new queries that completely different need the memory.
To my understanding Postgresql only caches queries and results in memory for that specific connection. So when that connection is closed those cached results are cleared out. So cached indexs and queries are for that connection only. I hope my understanding is correct.
On Thu, May 29, 2008 at 10:19:46AM -0400, Justin wrote: > To my understanding Postgresql only caches queries and results in memory > for that specific connection. So when that connection is closed those > cached results are cleared out. So cached indexs and queries are for > that connection only. I hope my understanding is correct. Incorrect, the shared buffer cache is shared between all running postgresql processes and between connections. Also the OS cache is shared between all programs on the system. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
On Thu, May 29, 2008 at 10:19 AM, Justin <justin@emproshunts.com> wrote: > To my understanding Postgresql only caches queries and results in memory for > that specific connection. So when that connection is closed those cached > results are cleared out. So cached indexs and queries are for that > connection only. I hope my understanding is correct. completely wrong. They are called 'shared' buffers for a reason. Also you are missing the point of the o/s file cache which lies under that. If you have a computer with 4gb ram that is addressable, all its memory is used for caching at all times, period, less what operating system needs or what is used by running programs or temporary demands (sorting, etc). Also, postgresql doesn't as a rule cache 'results and queries'. shared buffers setting reserves memory for postgresql's internal cache (plus some housekeeping things like locks)...which can be faster than the o/s cache because it is more tightly integrated with the backend. However a page fault to disk is much more interesting in performance terms than the performance differences between shared buffers and o/s cache. merlin
<br /><br /> Merlin Moncure wrote: <blockquote cite="mid:b42b73150805290733y2d10b74ena234b3f276d19752@mail.gmail.com" type="cite"><prewrap="">On Thu, May 29, 2008 at 10:19 AM, Justin <a class="moz-txt-link-rfc2396E" href="mailto:justin@emproshunts.com"><justin@emproshunts.com></a>wrote: </pre><blockquote type="cite"><pre wrap="">Tomy understanding Postgresql only caches queries and results in memory for that specific connection. So when that connection is closed those cached results are cleared out. So cached indexs and queries are for that connection only. I hope my understanding is correct. </pre></blockquote><pre wrap=""> completely wrong. They are called 'shared' buffers for a reason. Also you are missing the point of the o/s file cache which lies under that. If you have a computer with 4gb ram that is addressable, all its memory is used for caching at all times, period, less what operating system needs or what is used by running programs or temporary demands (sorting, etc). Also, postgresql doesn't as a rule cache 'results and queries'. shared buffers setting reserves memory for postgresql's internal cache (plus some housekeeping things like locks)...which can be faster than the o/s cache because it is more tightly integrated with the backend. However a page fault to disk is much more interesting in performance terms than the performance differences between shared buffers and o/s cache. merlin </pre></blockquote> This is not meant to be argumentative i'm trying to clearify what is going because documentation fordifferent sources seem to be in conflict or confusing. <br /> ---------------------------------------------------<br/> From Postgresql Second Edition:<br /> Shared_Buffers: This cacheis shared by all clients connected to a single cluster. DISK I?) (and cache I/O) is performed in 8KB chunks. The shared_buffersparameter determines how many 8KB will be created in the shared cache. <br /><br /> from here explanation isdifferent <br /><a class="moz-txt-link-freetext" href="http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html">http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html</a> itreferrs to queries which means to me the SQL command and the result sets (but that is wrong)<br /><br /> We have sort_memaka work_mem<br /> ---------------------------------------------------<br /> From Postgresql Second Edition:<br /> where postgresql processes query it transforms the query from string form into an execution plan. An execution planis a sequence of operations that must be performed in order satisfy the query. <br /><br /> (This specific to theclient connection and when the client is closed out this is cleared my thinking.)<br /> ----------------------------------------------------<br/> From postgresql Documentation<br /><dl><dt><tt class="VARNAME">work_mem</tt>(<tt class="TYPE">integer</tt>)<dd><p>Specifies the amount of memory to be used by internalsort operations and hash tables before switching to temporary disk files. The value is defaults to one megabyte (<ttclass="LITERAL">1MB</tt>). Note that for a complex query, several sort or hash operations might be running in parallel;each one will be allowed to use as much memory as this value specifies before it starts to put data into temporaryfiles. Also, several running sessions could be doing such operations concurrently. So the total memory used couldbe many times the value of <tt class="VARNAME">work_mem</tt>; it is necessary to keep this fact in mind when choosingthe value. Sort operations are used for <tt class="LITERAL">ORDER BY</tt>, <tt class="LITERAL">DISTINCT</tt>, andmerge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of <tt class="LITERAL">IN</tt>subqueries<dt>---------------------------------------------------------<br /></dl> Quoting You "Also,postgresql doesn't as a rule cache 'results and queries'.<br /><br /> Then what is the purpose of shared buffers ifnothing is being reused is it only used to keep track locks, changes and what is to being spooled to the kernel???<br/><br /> I'm confussed trying to figure out how caches are being use and being moving through postgresql backend. As many have stated that small database can fit completely in the caches How does that really work. <br /><br /><br/><br /><br /><br /><br /><br /><br />
On Thu, May 29, 2008 at 4:45 PM, Justin <justin@emproshunts.com> wrote: > > Then what is the purpose of shared buffers if nothing is being reused is it > only used to keep track locks, changes and what is to being spooled to the > kernel??? It caches disk pages (and holds other data structures), not query results. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Thu, 29 May 2008, Justin wrote: > I'm confussed trying to figure out how caches are being use and being > moving through postgresql backend. The shared_buffers cache holds blocks from the database files. That's it. If you want some more information about how that actually works head to http://www.westnet.com/~gsmith/content/postgresql/ and read "Inside the PostgreSQL Buffer Cache". The work memory allocated for sorting is separate from that, and it doesn't cache anything. It just provides working room for a query that's being executed right now. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Thu, May 29, 2008 at 4:52 PM, Dave Page <dpage@pgadmin.org> wrote: > On Thu, May 29, 2008 at 4:45 PM, Justin <justin@emproshunts.com> wrote: >> >> Then what is the purpose of shared buffers if nothing is being reused is it >> only used to keep track locks, changes and what is to being spooled to the >> kernel??? > > It caches disk pages (and holds other data structures), not query results. Oops, misread that. Shared *buffers* is disk pages. Shared memory holds the buffers and other stuff as well. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
> On Thu, May 29, 2008 at 10:19 AM, Justin <justin@emproshunts.com> wrote: > Quoting You "Also, postgresql doesn't as a rule cache 'results and queries'. > > Then what is the purpose of shared buffers if nothing is being reused is it > only used to keep track locks, changes and what is to being spooled to the > kernel??? not much. PostgreSQL provides an alternative file cache to the o/s buffer cache (rather, it layers on top of it). The performance difference between these caches is an interesting topic. In my experience, it isn't very much, so I tend to keep shared buffers on the low side. > I'm confussed trying to figure out how caches are being use and being moving > through postgresql backend. As many have stated that small database can > fit completely in the caches How does that really work. If your database is smaller than working memory, it will be completely cached all the time, unless you have some big transient memory demand (a big sort, or result set). It's just a matter of which cache it sits in...o/s, or postgres cache, or both. This is actually the normal state of affairs in many databases. memory .conf optimization is pretty marginal in effect until your database is 'big' in terms of relative size to memory, does a lot of sorting (work_mem becomes important), or receives a lot of writing (vacuum, sync, checkpoints, and bgwriter become important). There are a few things you have to pay attention to, especially in older versions. For example effective cache size...but mainly because it affects query plans chosen, not cache policy. For smallish databases, your best investment is writing efficient queries and good index strategy. merlin