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