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

Re: Queries becoming slow under heavy load

From: Andy Colson <andy(at)squeakycode(dot)net>
To: Anne Rosset <arosset(at)collab(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Queries becoming slow under heavy load
Date: 2011-01-25 22:12:45
Message-ID: 4D3F4ADD.6040304@squeakycode.net (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-performance
On 1/25/2011 3:37 PM, Anne Rosset wrote:
> Hi,
>
> We are running some performances tests. With a lot of concurrent access,
> queries get very slow. When there is no load, those queries run fast.
>
> We kind of see a trend about these queries: it seems like the ones that
> become very slow have an ORDER BY or MAX in them.
>
> Here are our config settings:
>

<SNIP>

> It seems to me that we should try increasing shared_buffers. But do you
> have any other suggestions? Or do you see anything wrong in our config?
>
> Thanks,
>
> Anne
>

While I applaud your attempt to get us lots of information, 
unfortunately the the one property you ask about (shared_buffers), I 
can't seem to find.

So, maybe you could post a bit more:

1) how many concurrent clients?
2) can we see an explain analyze for a query when its fast, and then 
again when its slow?
3) Is this box dedicated to PG or are there other services running?
4) Looks like you have 8 Gig of ram, so I assume this is a 64 bit OS, 
can you tell us what you have for:

shared_buffers
effective_cahce_size
work_mem


5) Once many clients start hitting the db, it might not all fit into ram 
and start hitting the HD, can you tell us what sort of IO you have 
(sata, scsi, raid, # of disks, etc).

The stats from /proc/meminfo:
SwapTotal:     2097112 kB
SwapFree:      2096612 kB

Was this run when the system was busy?  Looks like you are not using any 
swap, so thats good at least.  Oh, wait, there are two cat 
/proc/meminfo's.  Is one when its fast and one when its slow?

Looks to me, in both cases, you are not using much memory at all.  (if 
you happen to have 'free', its output is a little more readable, if you 
wouldn't mind posting it (only really need it for when the box is slow)

-Andy

In response to

Responses

pgsql-performance by date

Next:From: Ing. Marcos Ortiz ValmasedaDate: 2011-01-25 22:27:01
Subject: Re: Queries becoming slow under heavy load
Previous:From: Kevin GrittnerDate: 2011-01-25 22:12:24
Subject: Re: Queries becoming slow under heavy load

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