Re: postgresql + apache under heavy load

On Wednesday 21 January 2004 19:40, Alex Madon wrote:
Alex - I'm copying scott in on this since he asked for details on top too.
>
> Richard Huxton wrote:
> >Are you saying you had more than 8 connections open simultaneously?
>
> Well, I don't know how to find that out.
> What I did is issuing several tomes a ps aux and I never saw more than
> 6-7 postmaster SELECT.

Good enough for me - that's what I'd have done.

> >Are you
> >sure you restarted PG so that it noticed the new values? You can check
> > config settings with "show all;" from psql, or "show <setting>".
>
> Yes I restart it. The show command outputs the correct value (8).

Good - I don't think PG is using up your system memory, then (but we'll see).

> ---------------------------top-----------------------------
> 114 processes: 99 sleeping, 12 running, 3 zombie, 0 stopped
> CPU states: 91.8% user 8.1% system 0.0% nice 0.0% iowait 0.0% idle
> Mem: 384580k av, 316328k used, 68252k free, 0k shrd, 25424k
> buff
> 253976k actv, 36916k in_d, 4704k in_c
> Swap: 265064k av, 64788k used, 200276k free 71132k
> cached

If I'm decoding this through the email line wrapping you've got 71MB of disk
cached in RAM and 25MB used as i/o buffers (which is a good thing). I'm a
little puzzled as to why your swap is so high, but as long as it isn't
growing rapidly there shouldn't be a problem.

> PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
> 4914 apache 16 0 9016 8416 2552 S 6.7 2.1 0:00 0 httpd
> 4832 apache 16 0 9016 8416 2552 S 6.3 2.1 0:01 0 httpd

OK - so each httpd process is using about 8500KB with 2500KB shared = 6MB
each.

> 4947 postgres 25 0 2540 2392 1688 S 0.7 0.6 0:00 0
> postmaster
> 4952 postgres 25 0 2812 2664 1872 R 0.7 0.6 0:00 0
> postmaster
> 4954 postgres 24 0 1916 1768 1244 R 0.1 0.4 0:00 0 postmaster
> 4959 postgres 25 0 1596 1448 940 S 0.1 0.3 0:00 0 postmaster
> 4961 postgres 25 0 984 824 640 R 0.1 0.2 0:00 0 postmaster

Here we've got what appears to be about 1MB per PG backend. Since you've only
got 8, that should be fine.

[I noticed you were running X and mozilla (on top of gnome?). If you don't
need them, close them - that can free up to 64MB, depending on what you're
running]

I don't think you've got a RAM shortage, I think your settings are too small
and you're running out of I/O bandwidth. If you run "vmstat 5", what does it
show - you're interested in the i/o section: blocks in (bi) and out (bo) as
well as memory.
Save your readings, then follow the advice in that GeneralBits link I posted
earlier and see if that makes a difference.

--
Richard Huxton
Archonet Ltd