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

Re: Strange performance response for high load times

From: Peter Alban <peter(dot)alban2(at)gmail(dot)com>
To: Kenneth Marshall <ktm(at)rice(dot)edu>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Strange performance response for high load times
Date: 2009-06-18 20:49:55
Message-ID: 477dfcc10906181349y72097cffvccedac73ac3821cf@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
What's  still badgering me , is the performance when, there is no load or
significantly lower than peek times ?

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 !

cheers,
Peter


On Thu, Jun 18, 2009 at 10:01 PM, Kenneth Marshall <ktm(at)rice(dot)edu> wrote:

> 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(at)rice(dot)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
> > >
>

In response to

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2009-06-18 21:01:17
Subject: Re: Strange performance response for high load times
Previous:From: Kenneth MarshallDate: 2009-06-18 20:01:02
Subject: Re: Strange performance response for high load times

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