Skip site navigation (1) Skip section navigation (2)

Strange performance response for high load times

From: Peter Alban <peter(dot)alban2(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Strange performance response for high load times
Date: 2009-06-18 18:27:02
Message-ID: 477dfcc10906181127t4b4150dei1cbd84f5177bc358@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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
#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

Responses

pgsql-performance by date

Next:From: Kenneth MarshallDate: 2009-06-18 18:30:52
Subject: Re: Strange performance response for high load times
Previous:From: Tom LaneDate: 2009-06-18 17:48:03
Subject: Re: very slow selects on a small table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group