Re: autovacuum launcher process eating up 17G+ of ram?

Yes I was using top, but I am not adding up anything. I'm only ever
looking at the one process called "postgres: autovacuum launcher
process", not any of the other postgres processes.

Here is a vmstat 1 right after postgres has been started:

procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 55480 760172 1709424 3758492 0 12 4 294 23 26 3
2 94 0 0
0 0 55480 756080 1709460 3762308 0 0 8 6836 1139 442 10
8 79 3 0
0 0 55480 755088 1709492 3763328 0 0 0 0 1044 409 10
8 83 0 0
0 0 55480 754096 1709544 3763992 0 0 0 64 1023 405 10
7 83 0 0
1 0 55480 749696 1709692 3766816 0 0 2028 0 1141 539 11
9 74 7 0
0 0 55480 746544 1709764 3769900 0 0 2164 0 1128 527 11
7 83 0 0
1 0 55480 743332 1709812 3773040 0 0 2216 7440 1314 572 10
8 80 2 0
3 0 55480 739808 1709864 3776044 0 0 2128 0 1170 541 11
8 81 0 0
0 0 55480 737356 1709928 3779260 0 0 2216 0 1149 552 10
8 82 0 0

Here is another chunk after it's been running for about 18 hours:

procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 1812168 20332 59452 102172 0 12 5 302 8 19 3
3 93 0 0
0 0 1812168 19960 59456 102164 0 0 0 3260 1051 540 8
7 85 1 0
0 0 1812168 19932 59456 102324 0 0 0 0 1022 585 8
8 83 0 0
1 0 1812168 19056 59456 102620 0 0 0 0 1035 541 7
7 86 0 0
0 0 1812168 19808 59456 102128 0 0 0 0 1024 572 8
9 84 0 0
0 1 1812168 20304 59460 102124 0 0 0 28 1035 553 8
8 85 0 0
1 0 1812168 19428 59476 102312 0 0 0 3152 1031 535 8
6 84 2 0
0 0 1812168 19808 59476 102168 0 0 0 8 1041 573 8
9 83 0 0
1 0 1812168 18924 59476 102316 0 0 0 0 1016 526 9
8 84 0 0
0 0 1812168 18940 59476 102316 0 0 0 0 1042 535 7
8 85 0 0
0 0 1812168 19560 59476 102168 0 0 0 0 1021 439 8
8 85 0 0
0 0 1812168 19312 59488 102156 0 0 0 3136 1045 454 7
8 84 1 0
0 0 1812168 18692 59496 102168 0 0 0 3236 1221 505 9
8 82 2 0
1 0 1812168 21432 59496 102184 0 0 0 0 1044 498 8
8 85 0 0
0 0 1812168 22184 59496 102128 0 0 0 0 1019 511 7
6 86 0 0
0 0 1812168 21936 59496 102168 0 0 0 0 1039 582 8
9 84 0 0
0 0 1812168 21316 59504 102168 0 0 0 3284 1030 450 8
7 85 1 0
0 0 1812168 21440 59504 102168 0 0 0 0 1034 466 8
7 85 0 0
1 0 1812168 20812 59512 102168 0 0 0 16 1021 546 9
7 84 1 0
0 0 1812168 21316 59512 102168 0 0 0 0 1039 532 7
8 85 0 0

Here is a snapshot of all the postgres processes as listed in top
(29582 = postgres: autovacuum launcher process):

29582 pgsql 15 0 8739m 6.7g 1816 S 8.9 86.1 91:10.44 postgres
29583 pgsql 15 0 108m 7648 440 S 13.3 0.1 143:04.91 postgres
29578 pgsql 15 0 157m 3704 2992 S 0.3 0.0 1:46.29 postgres
29580 pgsql 15 0 157m 1472 760 S 0.0 0.0 0:00.78 postgres
29581 pgsql 15 0 157m 1204 492 S 0.0 0.0 0:00.12 postgres

As you can see, after a while the system is using a lot of swap. The
first time I noticed the problem, the swap was up to 12G (there is 8GB
of ram in this system). I believe postgres was running for several
days to achieve that much memory utilization.

Thanks for the other thoughts on tuning. I will investigate those
once I figure out what is up with this memory utilization problem.

On Thu, Oct 21, 2010 at 2:59 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Nick <t32(at)2thebatcave(dot)com> wrote:
>
>> I have a production server running postgres 8.3.11.  I did a dump
>> all and loaded up postgres 9.0.1 on another server.  On the new
>> server, the postgres autovacuum launcher process eats up an insane
>> amount of ram (I have seen 17G virt with 6.5G res).
>
> You're not looking at this in top and adding up the RAM per process,
> are you?  That is notoriously unreliable; in particular it tends to
> count the shared memory over and over.  What does free say about
> total usage?  How do things look with vmstat 1?
>
>> The only change I made in the postgresql.conf (on both versions)
>> is setting the maximum number of connections to 1000.
>
> Unless you have four or five hundred cores in the box, that's
> probably a bad idea.  You should seriously look at using a
> connection pool.  Search the archives for previous discussions on
> that topic.
>
>> So all other options are default to their particular postgres
>> versions.
>
> Since the defaults are designed to allow the server to come up on
> even a very small machine, you should probably be setting several
> more.  You might want to start here:
>
> http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
>
> -Kevin
>