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 = 170000 # 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
