Re: Still problems with memory swapping and server load

On Wed, 26 Jun 2002, Markus Wollny wrote:

> the same machine and database: 1GB RAM, 4xPIII550Xeon, dumpall.sql is
> ~300MB (see "[GENERAL] Urgent: Tuning strategies?"). It all starts with
> a humble 8MB swap being used (I expect that's just the empty swap with
> nothing in it but some system overhead). Then after a short time, memory
> usage climbs slow but continuously until it hits physical RAM ceiling
> and starts using swap - with not very nice results for the database.
> Swap sometimes amounts to 200MB or more.

Also use "vmstat", "systat vmstat" or whatever your system's
equivalant is to see just how much swapping you're doing. I wouldn't
be surprised to see some unused programs being pushed out to swap
as you do a lot of I/O, but if you're pushing stuff out to swap
and bringing it back in on a regular basis, you've still got
problems.

Also, remember, your OS may consider reading a program binary when
you run a program to be "page in" activity, so don't get to worried
about that, unless you also see page out activity.

> max_connections = 128
> shared_buffers = 32768
> sort_mem = 8192 (16384 or 32768 didn't help either)
> wal_files = 32
> wal_buffers = 32
> fsync = false

That looks good. Nothing should be using terribly much memory now.

> Mem: 1029400K av, 1023264K used, 6136K free, 0K shrd, 7176K buff

Ok, with only 7176K allocated to buffers, you've definitely got
some programs eating up your RAM, I'd say. Looking at your postmasters
below, sorted by size:

> PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
> 6837 postgres 9 0 251M 251M 250M S 9.8 25.0 0:37 postmaster
> 6894 postgres 9 0 247M 247M 246M S 2.1 24.6 0:27 postmaster
> 6848 postgres 16 0 247M 247M 246M R 93.6 24.6 4:06 postmaster
> 6852 postgres 9 0 227M 227M 226M R 7.8 22.6 0:12 postmaster
> 6903 postgres 12 0 204M 204M 203M R 10.2 20.3 0:27 postmaster
> 6911 postgres 9 0 66840 65M 65728 S 19.4 6.4 0:01 postmaster
> 6845 postgres 9 0 52344 51M 50916 S 3.6 5.0 0:09 postmaster
> 6874 postgres 9 0 49408 48M 43168 S 19.8 4.7 3:57 postmaster
> 6875 postgres 11 0 41564 40M 35324 R 18.7 4.0 3:31 postmaster
> 6834 postgres 9 0 25456 24M 24356 S 3.0 2.4 0:26 postmaster
> 6889 postgres 9 0 24844 24M 23632 S 15.8 2.4 0:17 postmaster
> 6893 postgres 9 0 18396 17M 17332 S 0.1 1.7 0:07 postmaster
> 6838 postgres 9 0 18364 17M 17304 S 5.6 1.7 0:04 postmaster
> 6904 postgres 9 0 16604 16M 15528 S 1.0 1.6 0:13 postmaster
> 6907 postgres 9 0 16020 15M 14992 S 1.8 1.5 0:03 postmaster
> 6897 postgres 9 0 14988 14M 13948 S 6.0 1.4 0:01 postmaster
> 6926 postgres 9 0 14572 14M 13756 S 23.8 1.4 0:13 postmaster
> 6920 postgres 10 0 14296 13M 13476 R 21.1 1.3 0:13 postmaster
> 6927 postgres 10 0 14148 13M 13328 R 17.4 1.3 0:12 postmaster
> 6928 postgres 9 0 13836 13M 13016 S 25.8 1.3 0:13 postmaster
> 6917 postgres 9 0 9108 9104 8204 R 19.4 0.8 0:13 postmaster
> 6916 postgres 9 0 8940 8936 8020 S 0.1 0.8 0:08 postmaster
> 4799 root 9 0 1820 1444 1300 S 0.1 0.1 0:07 sshd
> 6934 root 16 0 976 976 732 R 8.0 0.0 0:07 top
> 5929 postgres 15 0 940 884 668 R 8.9 0.0 8:23 top

Some of your backends are getting pretty darn big. I wonder what
they're doing? It can't be sort memory at this point. But as you
can see, those five 200-250MB backends are killing you.

If you can figure out what they're doing, and put a stop to that
memory usage, that would help you. Alternatively, perhaps just
dropping another 1-2 GB of RAM in the machine would fix your problem.

Also, for this kind of thing, it's better to provide a "ps aux" or
"ps -ef" than a top, unless you're sure that that display above is
all of the processes.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC