Re: Strange performance response for high load times - Mailing list pgsql-performance

From Kenneth Marshall
Subject Re: Strange performance response for high load times
Date
Msg-id 20090618183052.GC23785@it.is.rice.edu
Whole thread Raw
In response to Strange performance response for high load times  (Peter Alban <peter.alban2@gmail.com>)
Responses Re: Strange performance response for high load times  (Peter Alban <peter.alban2@gmail.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Peter Alban
Date:
Subject: Strange performance response for high load times
Next
From: "Kevin Grittner"
Date:
Subject: Re: performance with query