Re: Tuning Help - What did I do wrong?

On 10/4/07, Josh Trutwin <josh(at)trutwins(dot)homeip(dot)net> wrote:
> On Thu, 4 Oct 2007 11:19:22 -0500
> "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> wrote:
>
> > We need to see examples of what's slow, including explain analyze
> > output for slow queries. Also a brief explanation of the type of
> > load your database server is seeing. I.e. is it a lot of little
> > transactions, mostly read, batch processing, lots of users, one
> > user, etc... Right now we don't have enough info to really help
> > you.
>
> Sorry, this server is for a few (100+?) websites so it's running
> along site apache, php. All connections to postgresql (except for
> the occaional psql console login) are done from php requests, using
> the same user (basically there are two users, the one php uses and
> postgres). The bulk of the activity would be reads, but
> certainly inesrts/updates/deletes would be interspersed in there.
> Most of the activity is done via auto-commits, not many long
> transactions.

So, are there certain queries that are much slower than the others?
Run them from psql with explain analyze in front of them and post the
query and the output here.

> From your followup email:
>
> > ... you should use tools like vmstat, iostat and top to get an idea
> > of what your server is doing.
>
> # vmstat
> procs memory swap io
> system cpu
> r b w swpd free buff cache si so bi bo in cs
> us sy id
> 3 1 0 268 68332 39016 2201436 0 0 3 3 4
> 2 3 4 2

vmstat needs to be run for a while to give you useful numbers. try:

vmstat 5

and let it run for a few minutes. The first line won't count so much,
but after that you'll get more reasonable numbers.

> iostat is not found - will see if I can download it. top typically
> shows postmaster as the top process with 10-15% of the CPU, followed
> by apache threads.

What OS are you on?

> 12:01pm up 104 days, 12:05, 2 users, load average: 9.75, 9.30,
> 7.70

That's pretty heavy load. I notice there's no wait % listed for CPU,
so I assume it's not a late model Linux kernel or anything.

> 215 processes: 214 sleeping, 1 running, 0 zombie, 0 stopped
> CPU states: 0.1% user, 0.0% system, 0.0% nice, 0.4% idle
> Mem: 3617400K av, 3552784K used, 64616K free, 0K shrd, 37456K
> buff
> Swap: 2457928K av, 264K used, 2457664K free
> 2273664K cached
>
> PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME
> COMMAND
> 31797 postgres 17 0 28836 28M 1784 S 0 8.5 0.7 10:15
> postmaster

Are the postmasters using most of the CPU? OR the other processes?

> > What kind of drive subsystem do you have? What kind of raid
> > controller? etc...
>
> Gathering more information on this - Raid is a software
> RAID-1. Some information:

OK, given that it's read mostly, it's likely not a problem that a
faster RAID controller would help. Possibly more drives in a RAID 10
would help a little, but let's look at optimizing your query and
postmaster first.

Do you have the postmaster configured to log long running queries?
That's a good starting point. also google pg_fouine (I think I spelt
it right) for analyzing your logs.

It's quite likely the issue here is one long running query that
chewing all your I/O or CPU and making everything else slow. Once we
find that query things should get better and we can worry about
performance tuning in a more leisurely manner.