Re: One source of constant annoyance identified

Okay, now it's even more annoying...

We just upgraded the server from 1GB to 2GB of RAM because it kept
swapping out about 300MB.

I updated postgresql.conf accordingly, setting

max_connections = 190 (this is the minimum we need to satisfy webservers
and backend-jobs)
shared_buffers = 60000 (that's 468,75MB; I took this value assuming a
recommended value of 25% of RAM)
sort_mem = 80000 (that's 78,125MB, recommended value is 4% of RAM,
equalling 82MB).

Now top-output sorted by memory usage turns out to be:

2:43pm up 1:34, 3 users, load average: 3.29, 2.25, 2.08
123 processes: 118 sleeping, 5 running, 0 zombie, 0 stopped
CPU0 states: 60.5% user, 3.1% system, 0.0% nice, 35.4% idle
CPU1 states: 47.3% user, 15.3% system, 0.0% nice, 36.4% idle
CPU2 states: 51.0% user, 17.1% system, 0.0% nice, 31.3% idle
CPU3 states: 75.4% user, 4.0% system, 0.0% nice, 20.0% idle
Mem: 2061560K av, 2054268K used, 7292K free, 0K shrd, 13924K
buff
Swap: 2097136K av, 7788K used, 2089348K free 1825104K
cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
1652 postgres 9 0 469M 469M 467M S 0.0 23.3 2:21 postmaster
2041 postgres 9 0 431M 431M 429M S 1.2 21.4 1:32 postmaster
1588 postgres 9 0 411M 411M 410M S 15.6 20.4 1:25 postmaster
1597 postgres 9 0 289M 289M 287M S 0.1 14.3 0:31 postmaster
1849 postgres 9 0 259M 259M 258M S 0.0 12.9 0:14 postmaster
2046 postgres 9 0 239M 239M 238M S 0.0 11.9 0:25 postmaster
1973 postgres 9 0 172M 172M 171M S 0.0 8.5 0:21 postmaster
2142 postgres 9 0 128M 128M 127M S 0.0 6.3 0:04 postmaster
2156 postgres 9 0 116M 116M 114M S 0.0 5.7 0:02 postmaster
1598 postgres 9 0 86548 84M 84752 S 1.7 4.1 2:36 postmaster
1608 postgres 9 0 60932 59M 59356 S 0.0 2.9 2:09 postmaster
1582 postgres 9 0 57624 56M 55444 S 0.0 2.7 1:58 postmaster
1609 postgres 9 0 56408 55M 55164 S 8.3 2.7 2:05 postmaster
1766 postgres 9 0 45248 44M 43824 S 0.0 2.1 1:09 postmaster
2139 postgres 9 0 45276 44M 43892 S 0.0 2.1 0:01 postmaster
2045 postgres 9 0 41500 40M 39820 S 1.7 2.0 2:43 postmaster
1610 postgres 9 0 41336 40M 40172 S 0.0 2.0 0:53 postmaster
2044 postgres 14 0 38328 37M 37056 R 25.0 1.8 0:33 postmaster
1881 postgres 9 0 34936 34M 33676 S 17.2 1.6 0:29 postmaster
2042 postgres 9 0 33144 32M 31920 S 3.7 1.6 1:13 postmaster
1679 postgres 9 0 32516 31M 31288 S 0.0 1.5 0:05 postmaster
1678 postgres 9 0 31996 31M 30812 S 0.0 1.5 0:19 postmaster
1653 postgres 9 0 29424 28M 28180 S 0.0 1.4 0:05 postmaster
2048 postgres 9 0 27772 27M 26556 S 0.0 1.3 0:09 postmaster
1802 postgres 9 0 26676 26M 25504 S 0.0 1.2 0:06 postmaster
2211 postgres 9 0 25940 25M 24592 S 0.0 1.2 0:02 postmaster
2047 postgres 14 0 25588 24M 23532 R 8.0 1.2 0:55 postmaster
2065 postgres 11 0 25584 24M 24404 S 3.9 1.2 0:04 postmaster
1980 postgres 11 0 24584 24M 22864 S 3.5 1.1 0:07 postmaster
1872 postgres 9 0 23908 23M 22800 S 0.0 1.1 0:04 postmaster
2068 postgres 9 0 21352 20M 20188 S 0.0 1.0 0:01 postmaster
2138 postgres 9 0 20928 20M 19644 S 0.0 1.0 0:06 postmaster
1983 postgres 9 0 20544 20M 19344 S 0.0 0.9 0:04 postmaster
2342 postgres 9 0 20352 19M 13772 S 0.0 0.9 0:23 postmaster
2357 postgres 15 0 20260 19M 18532 R 93.1 0.9 1:34 postmaster
2204 postgres 9 0 19816 19M 18244 S 0.0 0.9 0:01 postmaster
2199 postgres 9 0 16840 16M 15452 S 0.0 0.8 0:00 postmaster
2207 postgres 9 0 16784 16M 15512 S 0.0 0.8 0:00 postmaster
2050 postgres 9 0 15880 15M 14136 S 0.0 0.7 0:06 postmaster
2200 postgres 9 0 15568 15M 14080 S 0.0 0.7 0:00 postmaster
2301 postgres 9 0 15076 14M 13940 S 0.0 0.7 0:00 postmaster
2236 postgres 9 0 14132 13M 12824 S 0.0 0.6 0:00 postmaster
2346 postgres 9 0 14080 13M 12972 S 0.0 0.6 0:09 postmaster
2347 postgres 9 0 14064 13M 12960 S 0.0 0.6 0:09 postmaster
2205 postgres 9 0 13904 13M 12412 S 0.0 0.6 0:00 postmaster
2339 postgres 11 0 12660 12M 11448 S 36.1 0.6 0:27 postmaster

which is not at all good...

And still the major problem in finding the cause of all this is to
identify the query which causes these huge backends. At the moment I
tend to suspect that there certainly are several queries which need a
bit of RAM, not that much however, and the backend fails to free up the
memory used by processed queries. What puzzles me here is that all these
400MB+ backend-processes are marked as idle (ps-output) respectively
sleeping, so they don't actually do much. But how come a sleeping
process grabs 20% of 2GB of RAM and 15% of processing time?

Another fact that hints at backends just eating memory without reason is
that when I stop the database, processes keep lingering - kill just
doesn't help, I have to kill -9 the last of the lot to get red of them
before restarting the database:

dunkles:/var/lib/pgsql/data/base # /etc/init.d/postgresql stop
Shutting down PostgreSQL
done
dunkles:/var/lib/pgsql/data/base # ps ax|grep post
863 pts/1 S 0:00 login -- postgres
885 pts/2 S 0:00 login -- postgres
1552 pts/0 S 0:01 /opt/pgsql/bin/postmaster -i
-D/var/lib/pgsql/data/base
1553 pts/0 S 0:00 postgres: stats buffer process

1554 pts/0 S 0:01 postgres: stats collector process

1650 pts/0 S 0:00 postgres: postgres template1 212.123.109.25
idle
1681 pts/0 S 0:00 postgres: postgres abo 212.123.109.25 idle

1682 pts/0 S 0:00 postgres: postgres bluebox 212.123.109.25
idle
1683 pts/0 S 0:00 postgres: postgres kidszone 212.123.109.25
idle
1684 pts/0 S 0:00 postgres: postgres mcv 212.123.109.25 idle

1685 pts/0 S 0:00 postgres: postgres mpo 212.123.109.25 idle

1686 pts/0 S 0:00 postgres: postgres nzone 212.123.109.25 idle

1687 pts/0 S 0:00 postgres: postgres pcaction 212.123.109.25
idle
1688 pts/0 S 0:00 postgres: postgres pcgames 212.123.109.25
idle
1689 pts/0 S 0:00 postgres: postgres phppgadmin 212.123.109.25
idle
1690 pts/0 S 0:00 postgres: postgres pszone 212.123.109.25 idle

1691 pts/0 S 0:00 postgres: postgres saturn 212.123.109.25 idle

1693 pts/0 S 0:00 postgres: postgres template1 212.123.109.25
idle
1780 pts/0 S 0:00 postgres: postgres template1 212.123.108.149
idle
1781 pts/0 S 0:00 postgres: postgres template1 212.123.108.149
idle
1784 pts/0 S 0:00 postgres: postgres pcgames 212.123.108.149
idle

This just has some sort of Windows-look&feel to it - processes not
responding any more.

Now I set back the original values before upgrading RAM: shared_buffers
= 32768 (256MB) and sort_mem = 51200 (50MB). Limit for backend size not
seems to be some 265 odd MB (about four to six of those around at any
given time, as always) and top reports:

3:11pm up 2:03, 3 users, load average: 3.24, 1.65, 1.81
163 processes: 156 sleeping, 7 running, 0 zombie, 0 stopped
CPU0 states: 81.3% user, 15.5% system, 0.0% nice, 2.2% idle
CPU1 states: 67.1% user, 26.0% system, 0.0% nice, 6.3% idle
CPU2 states: 69.2% user, 21.0% system, 0.0% nice, 9.3% idle
CPU3 states: 65.4% user, 20.2% system, 0.0% nice, 13.3% idle
Mem: 2061560K av, 2025392K used, 36168K free, 0K shrd, 13108K
buff
Swap: 2097136K av, 7732K used, 2089404K free 1750556K
cached

Which isn't quite as bad anymore - although it cannot be the optimum
performance for this machine, I can't imagine.

Now what on earth can I do to get this DB running, and when I say
"running" I don't mean "sort of crawling uphills"? Are there any
OS-patches or environment-settings I need to consider? Are there known
memory-leaks? I just doesn't seem to matter whichever settings I take in
postgresql.conf, the database just eats up any available memory in any
case. I cannot use it for production purposes this way. I am quite
dispaired right now - and I am definitely running out of time. Which
bits of linux (glibc, kernel, whatnot) does postgresql 7.2.1 need in
which version to run smoothly? As I mentioned before, we don't use any
"fancy" features of the database like foreign keys, triggers (except
one) or whatever, it's just basic functionality that seems to fall down
on us...

I am willing to try almost anything - but I need to squeeze more
performance out of this thing and I need some hints on which tools to
use to identify the problem. And unfortunately I need all this as soon
as possible... Hints and help are very, very much appreciated. Thank
you!

Regards,

Markus