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