Re: [HACKERS] [PERFORM] Memory question on win32 systems
On Thu, May 29, 2008 at 4:26 AM, Sabbiolina [EMAIL PROTECTED] wrote: On Thu, May 29, 2008 at 2:11 AM, Douglas McNaught [EMAIL PROTECTED] wrote: On Wed, May 28, 2008 at 7:05 PM, Sabbiolina [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PERFORM] Memory question on win32 systems
On Thu, May 29, 2008 at 01:05:22AM +0200, Sabbiolina wrote: 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? PG relies on the operating system to cache most disk accesses. Looking at the amount of memory a process directly uses isn't a good measure of this, a better method would be to watch what the IO subsystem is doing. If you've got lots of reads happening then the disk isn't being cached. Sam -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PERFORM] Memory question on win32 systems
Sabbiolina wrote: On Thu, May 29, 2008 at 2:11 AM, Douglas McNaught [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: On Wed, May 28, 2008 at 7:05 PM, Sabbiolina [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 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 multiplied all memory-related values by 10, still Postgres uses only less than 50 Mb of my RAM. How are you measuring this? Task Manager 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? Post the settings values you're using and people will be better able 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 = 1# 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' Need to Use Performance monitor and Process Explorer to track down the caching on windows machines and performance. 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.
Re: [HACKERS] [PERFORM] Memory question on win32 systems
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 [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] [PERFORM] Memory question on win32 systems
On Thu, May 29, 2008 at 10:19 AM, Justin [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PERFORM] Memory question on win32 systems
Merlin Moncure wrote: On Thu, May 29, 2008 at 10:19 AM, Justin [EMAIL PROTECTED] 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 This is not meant to be argumentative i'm trying to clearify what is going because documentation for different sources seem to be in conflict or confusing. --- From Postgresql Second Edition: Shared_Buffers: This cache is shared by all clients connected to a single cluster. DISK I?) (and cache I/O) is performed in 8KB chunks. The shared_buffers parameter determines how many 8KB will be created in the shared cache. from here explanation is different http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html it referrs to queries which means to me the SQL command and the result sets (but that is wrong) We have sort_mem aka work_mem --- From Postgresql Second Edition: where postgresql processes query it transforms the query from string form into an execution plan. An execution plan is a sequence of operations that must be performed in order satisfy the query. (This specific to the client connection and when the client is closed out this is cleared my thinking.) From postgresql Documentation work_mem (integer) Specifies the amount of memory to be used by internal sort operations and hash tables before switching to temporary disk files. The value is defaults to one megabyte (1MB). 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 temporary files. Also, several running sessions could be doing such operations concurrently. So the total memory used could be many times the value of work_mem; it is necessary to keep this fact in mind when choosing the value. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries - 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??? 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.
Re: [HACKERS] [PERFORM] Memory question on win32 systems
On Thu, May 29, 2008 at 4:45 PM, Justin [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PERFORM] Memory question on win32 systems
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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PERFORM] Memory question on win32 systems
On Thu, May 29, 2008 at 4:52 PM, Dave Page [EMAIL PROTECTED] wrote: On Thu, May 29, 2008 at 4:45 PM, Justin [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PERFORM] Memory question on win32 systems
On Thu, May 29, 2008 at 10:19 AM, Justin [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PERFORM] Memory question on win32 systems
On Wed, May 28, 2008 at 7:05 PM, Sabbiolina [EMAIL PROTECTED] 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 multiplied all memory-related values by 10, still Postgres uses only less than 50 Mb of my RAM. How are you measuring this? 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? Post the settings values you're using and people will be better able to help you. -Doug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
