Re: postgresql + apache under heavy load

On Wed, 21 Jan 2004, Alex Madon wrote:

> >You'll want to use the "top" command to show the amount of memory each process
> >
>
> A typical output (in a concurrency of 20, no cache) is:
>  ps aux | grep postgres
> postgres  2332  0.0  0.0  8804  328 ?        R    18:54   0:01
> /usr/bin/postmaster -p 5432 -d2
> postgres  2334  0.0  0.0  9792   68 ?        S    18:54   0:00 postgres:
> stats buffer process
> postgres  2335  0.0  0.0  8828  200 ?        S    18:54   0:00 postgres:
> stats collector process
> postgres  4386  0.0  0.2  4312  956 pts/3    S    19:22   0:00 -bash
> postgres  4871  0.0  0.5  9480 2304 ?        S    20:36   0:00 postgres:
> user db [local] SELECT
> postgres  4873  0.0  0.2  8816 1032 ?        R    20:36   0:00 postgres:
> user db [local] startup
> myuser    4877  0.0  0.1  3572  624 pts/4    S    20:36   0:00 grep postgres
> postgres  4878  0.0  0.5  9220 2228 ?        R    20:36   0:00 postgres:
> user db [local] SELECT
> postgres  4879  0.0  0.5  9204 2016 ?        R    20:36   0:00 postgres:
> user db [local] SELECT
> ---------------------------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
>
>   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
>  4915 apache    16   0  9016 8416  2552 S     5.9  2.1   0:00   0 httpd
>  4917 apache    16   0  9016 8416  2552 S     5.9  2.1   0:00   0 httpd
>  4919 apache    16   0  9020 8420  2536 S     5.9  2.1   0:00   0 httpd
>  4774 apache    16   0  9016 8416  2552 S     5.7  2.1   0:02   0 httpd
>  4896 apache    16   0  9060 8460  2568 S     5.7  2.1   0:00   0 httpd
>  4908 apache    15   0  9016 8416  2552 S     5.7  2.1   0:00   0 httpd
>  4909 apache    16   0  9016 8416  2552 S     5.7  2.1   0:00   0 httpd
>  4658 apache    16   0  9136 8536  2568 S     5.5  2.2   0:04   0 httpd
>  4921 apache    16   0  9016 8416  2552 S     5.5  2.1   0:00   0 httpd
>  2581 root      16   0 14492 4544  1252 R     5.3  1.1   2:26   0 X
>  4795 apache    16   0  9104 8504  2568 S     5.3  2.2   0:02   0 httpd
>  4796 apache    16   0  9080 8480  2568 S     5.3  2.2   0:01   0 httpd
>  4782 apache    16   0  8924 8324  2568 R     3.5  2.1   0:02   0 httpd
>  2612 madona    15   0  4524 4136  2380 S     1.5  1.0   0:18   0 metacity
>  4656 apache    15   0  9084 8484  2568 S     1.3  2.2   0:03   0 httpd
>  4950 postgres  25   0     0    0     0 Z     1.1  0.0   0:00   0
> postmaster <defunct>
>  3812 madona    15   0 44728  42M 17460 S     0.7 11.2   3:21   0
> mozilla-bin
>  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
>  4610 madona    15   0  7460 7460  2152 R     0.5  1.9   0:00   0 xterm
>  4904 madona    15   0  1108 1108   856 R     0.3  0.2   0:00   0 top
>  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
>     1 root      15   0    88   60    40 S     0.0  0.0   0:04   0 init

OK, the memory usage is NOT your problem.  You have crashing postgresql
backends, see the <defunct> and the listing of 3 zombie processes in top?
That's bad.  You very likely have bad memory in your box, or possibly
other hardware problems.  It could be software, if you've got a really odd
configuration (i.e. compiling apache against one ldap sdk, and php against
another can make apache start failing, something similar may be happening
in postgresql, but I doubt it.)

You should go to www.memtest86.com and download their free tester and see
if your machine has any bad ram.

The reason the machine will get really slow when this happens is that the
postgresql database has to reset all the cache on all backends when one
crashes to prevent corruption.

The fact that X goes unresponsive is simply a load issue.  Have a little
more patience.  Even with bad memory under it, linux / unix will usually
come back from the brink once the load goes away.

You need to make sure logging is enabled on postgresql and then you can
look at the logs for clues as to why the backends are crashing.