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.