Thread: 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 = 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

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 <ktm@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 = 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
> >

Peter Alban <peter.alban2@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