Re: autovacuum launcher process eating up 17G+ of ram? - Mailing list pgsql-admin

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.Grittner@wicourts.gov> wrote:
> Nick <t32@2thebatcave.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
>