Re: Tuning Help - What did I do wrong? - Mailing list pgsql-performance

On 10/4/07, Josh Trutwin <josh@trutwins.homeip.net> wrote:
> On Thu, 4 Oct 2007 11:19:22 -0500
> "Scott Marlowe" <scott.marlowe@gmail.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.