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
