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

From Josh Trutwin
Subject Re: Tuning Help - What did I do wrong?
Date
Msg-id 20071004120027.0c1b0bd4@joplin.trutwins.homeip.net
Whole thread Raw
In response to Re: Tuning Help - What did I do wrong?  ("Scott Marlowe" <scott.marlowe@gmail.com>)
Responses Re: Tuning Help - What did I do wrong?  ("Scott Marlowe" <scott.marlowe@gmail.com>)
List pgsql-performance
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.

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

sorry about the wrapping...

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.

 12:01pm  up 104 days, 12:05,  2 users,  load average: 9.75, 9.30,
7.70
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

> 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:
I believe itI believe it
# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/md0               66G   50G   16G  76% /
/dev/sda1              15M  6.6M  8.5M  44% /boot

# cat /proc/mdstat
Personalities : [raid0] [raid1]
read_ahead 1024 sectors
md0 : active raid1 sdb3[0] sdc3[1]
      70573440 blocks [2/2] [UU]

unused devices: <none>

Thanks for your help, I'm more of a developer guy so let me know what
else is useful.

Josh

pgsql-performance by date:

Previous
From: "Scott Marlowe"
Date:
Subject: Re: Tuning Help - What did I do wrong?
Next
From: "Scott Marlowe"
Date:
Subject: Re: Tuning Help - What did I do wrong?