[PERFORM] Strange performance response for high load times
Hi All, We are having a reasonably powerful machine for supporting about 20 databases but in total they're not more then 4GB in size. The machine is 2 processor 8 core and 8 Gig or ram so I would expect that PG should cache the whole db into memory. Well actually it doesn't. What is more strange that a query that under zero load is running under 100ms during high load times it can take up to 15 seconds !! What on earth can make such difference ? here are the key config options that I set up : # - Memory - shared_buffers = 17 # min 16 or max_connections*2, 8KB each temp_buffers = 21000# min 100, 8KB each #max_prepared_transactions = 5 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem = 1048576 # min 64, size in KB maintenance_work_mem = 1048576 # min 1024, size in KB #max_stack_depth = 2048 # min 100, size in KB # - Free Space Map - max_fsm_pages = 524298 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 32768 # min 100, ~70 bytes each # - Kernel Resource Usage - max_files_per_process = 4000# min 25 #preload_libraries = '' any ideas ? cheers, Peter
Re: [PERFORM] Strange performance response for high load times
On Thu, Jun 18, 2009 at 08:27:02PM +0200, Peter Alban wrote: Hi All, We are having a reasonably powerful machine for supporting about 20 databases but in total they're not more then 4GB in size. The machine is 2 processor 8 core and 8 Gig or ram so I would expect that PG should cache the whole db into memory. Well actually it doesn't. What is more strange that a query that under zero load is running under 100ms during high load times it can take up to 15 seconds !! What on earth can make such difference ? here are the key config options that I set up : # - Memory - shared_buffers = 17 # min 16 or max_connections*2, 8KB each temp_buffers = 21000# min 100, 8KB each #max_prepared_transactions = 5 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem = 1048576 # min 64, size in KB maintenance_work_mem = 1048576 # min 1024, size in KB 1GB of work_mem is very high if you have more than a couple of queries that use it. Ken #max_stack_depth = 2048 # min 100, size in KB # - Free Space Map - max_fsm_pages = 524298 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 32768 # min 100, ~70 bytes each # - Kernel Resource Usage - max_files_per_process = 4000# min 25 #preload_libraries = '' any ideas ? cheers, Peter -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Strange performance response for high load times
So Ken , What do you reckon it should be ? What is the rule of thumb here ? cheers, Peter On Thu, Jun 18, 2009 at 8:30 PM, Kenneth Marshall k...@rice.edu wrote: On Thu, Jun 18, 2009 at 08:27:02PM +0200, Peter Alban wrote: Hi All, We are having a reasonably powerful machine for supporting about 20 databases but in total they're not more then 4GB in size. The machine is 2 processor 8 core and 8 Gig or ram so I would expect that PG should cache the whole db into memory. Well actually it doesn't. What is more strange that a query that under zero load is running under 100ms during high load times it can take up to 15 seconds !! What on earth can make such difference ? here are the key config options that I set up : # - Memory - shared_buffers = 17 # min 16 or max_connections*2, 8KB each temp_buffers = 21000# min 100, 8KB each #max_prepared_transactions = 5 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem = 1048576 # min 64, size in KB maintenance_work_mem = 1048576 # min 1024, size in KB 1GB of work_mem is very high if you have more than a couple of queries that use it. Ken #max_stack_depth = 2048 # min 100, size in KB # - Free Space Map - max_fsm_pages = 524298 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 32768 # min 100, ~70 bytes each # - Kernel Resource Usage - max_files_per_process = 4000# min 25 #preload_libraries = '' any ideas ? cheers, Peter
Re: [PERFORM] Strange performance response for high load times
On Thu, Jun 18, 2009 at 09:42:47PM +0200, Peter Alban wrote: So Ken , What do you reckon it should be ? What is the rule of thumb here ? cheers, Peter It really depends on your query mix. The key to remember is that multiples (possibly many) of the work_mem value can be allocated in an individual query. You can set it on a per query basis to help manage it use, i.e. up it for only the query that needs it. With our systems, which run smaller number of queries we do use 256MB. I hope that this helps. Regards, Ken On Thu, Jun 18, 2009 at 8:30 PM, Kenneth Marshall k...@rice.edu wrote: On Thu, Jun 18, 2009 at 08:27:02PM +0200, Peter Alban wrote: Hi All, We are having a reasonably powerful machine for supporting about 20 databases but in total they're not more then 4GB in size. The machine is 2 processor 8 core and 8 Gig or ram so I would expect that PG should cache the whole db into memory. Well actually it doesn't. What is more strange that a query that under zero load is running under 100ms during high load times it can take up to 15 seconds !! What on earth can make such difference ? here are the key config options that I set up : # - Memory - shared_buffers = 17 # min 16 or max_connections*2, 8KB each temp_buffers = 21000# min 100, 8KB each #max_prepared_transactions = 5 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem = 1048576 # min 64, size in KB maintenance_work_mem = 1048576 # min 1024, size in KB 1GB of work_mem is very high if you have more than a couple of queries that use it. Ken #max_stack_depth = 2048 # min 100, size in KB # - Free Space Map - max_fsm_pages = 524298 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 32768 # min 100, ~70 bytes each # - Kernel Resource Usage - max_files_per_process = 4000# min 25 #preload_libraries = '' any ideas ? cheers, Peter -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Strange performance response for high load times
What's still badgering me , is the performance when, there is no load or significantly lower than peek times ? Why is there such a big difference ? i.e. off peek times a simple select with where (on indexed column) and limit taks* 40 ms* during peek times it took *2 seconds* - 50 times slower ! cheers, Peter On Thu, Jun 18, 2009 at 10:01 PM, Kenneth Marshall k...@rice.edu wrote: On Thu, Jun 18, 2009 at 09:42:47PM +0200, Peter Alban wrote: So Ken , What do you reckon it should be ? What is the rule of thumb here ? cheers, Peter It really depends on your query mix. The key to remember is that multiples (possibly many) of the work_mem value can be allocated in an individual query. You can set it on a per query basis to help manage it use, i.e. up it for only the query that needs it. With our systems, which run smaller number of queries we do use 256MB. I hope that this helps. Regards, Ken On Thu, Jun 18, 2009 at 8:30 PM, Kenneth Marshall k...@rice.edu wrote: On Thu, Jun 18, 2009 at 08:27:02PM +0200, Peter Alban wrote: Hi All, We are having a reasonably powerful machine for supporting about 20 databases but in total they're not more then 4GB in size. The machine is 2 processor 8 core and 8 Gig or ram so I would expect that PG should cache the whole db into memory. Well actually it doesn't. What is more strange that a query that under zero load is running under 100ms during high load times it can take up to 15 seconds !! What on earth can make such difference ? here are the key config options that I set up : # - Memory - shared_buffers = 17 # min 16 or max_connections*2, 8KB each temp_buffers = 21000# min 100, 8KB each #max_prepared_transactions = 5 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem = 1048576 # min 64, size in KB maintenance_work_mem = 1048576 # min 1024, size in KB 1GB of work_mem is very high if you have more than a couple of queries that use it. Ken #max_stack_depth = 2048 # min 100, size in KB # - Free Space Map - max_fsm_pages = 524298 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 32768 # min 100, ~70 bytes each # - Kernel Resource Usage - max_files_per_process = 4000# min 25 #preload_libraries = '' any ideas ? cheers, Peter
Re: [PERFORM] Strange performance response for high load times
Peter Alban peter.alb...@gmail.com wrote: Why is there such a big difference ? i.e. off peek times a simple select with where (on indexed column) and limit taks* 40 ms* during peek times it took *2 seconds* - 50 times slower ! If your high work_mem setting you may have been causing the OS to discard cached data, causing disk reads where you normally get cache hits, or even triggered swapping. Either of those can easily cause a difference of that magnitude, or more. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
