Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TYTFu-00035n-SR for pgsql-admin@postgresql.org; Wed, 14 Nov 2012 03:03:10 +0000 Received: from outmail149075.authsmtp.net ([62.13.149.75]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TYTFq-0002Wt-I2 for pgsql-admin@postgresql.org; Wed, 14 Nov 2012 03:03:10 +0000 Received: from mail-c232.authsmtp.com (mail-c232.authsmtp.com [62.13.128.232]) by punt12.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id qAE332YS061253; Wed, 14 Nov 2012 03:03:02 GMT Received: from ayaki.localdomain (124-148-90-153.dyn.iinet.net.au [124.148.90.153]) (authenticated bits=0) by mail.authsmtp.com (8.14.2/8.14.2/) with ESMTP id qAE32qYW016794 (version=TLSv1/SSLv3 cipher=DHE-RSA-CAMELLIA256-SHA bits=256 verify=NO); Wed, 14 Nov 2012 03:02:55 GMT Message-ID: <50A309DA.2040702@2ndQuadrant.com> Date: Wed, 14 Nov 2012 11:02:50 +0800 From: Craig Ringer Organization: 2nd Quadrant User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:16.0) Gecko/20121016 Thunderbird/16.0.1 MIME-Version: 1.0 To: Aaron Bono CC: Postgres Subject: Re: Postgre Eating Up Too Much RAM References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------080105060703090807020406" X-Server-Quench: ca04d1da-2e07-11e2-b10b-0025903375e2 X-AuthReport-Spam: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse X-AuthRoute: OCdxZQATClZeVg0b BQteCiN5VAwpPBRK HVkIKg5MJUcNSQVJ NksadBtFaQFbY1ZH HGQLWlZEUF57WWF/ bgwfbAdZfEtKVxtj UFZIR1FNFgBqBAMA SF9vJxpwNmwheH92 Yk9mEHJeXE14OxUs QkoAFDlXeGU1aGcC UUENdR4GcQMYYx9F aFV2VHAOZmBSZDQC El17DBkdGBR5HWxt RRoRIkgfCWsCAj86 SFg/BX0TGkpNYjg4 L1QcA3t0 X-Authentic-SMTP: 61633235383639.1019:706 X-AuthFastPath: 0 (Was 255) X-AuthSMTP-Origin: 124.148.90.153/2525 X-AuthVirus-Status: No virus detected - but ensure you scan with your own anti-virus system. X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201211/68 X-Sequence-Number: 40313 This is a multi-part message in MIME format. --------------080105060703090807020406 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit On 11/14/2012 06:12 AM, Aaron Bono wrote: > > USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND > .... > postgres 3523 0.5 1.0 426076 313156 ? Ss 08:44 2:42 \_ > postgres: myuser my_db 192.168.1.2(39786) idle > > postgres 3820 0.4 0.9 418988 302036 ? Ss 09:04 2:11 \_ > postgres: myuser my_db 192.168.1.2(52110) idle > > postgres 3821 0.1 0.5 391452 178972 ? Ss 09:04 0:44 \_ > postgres: myuser my_db 192.168.1.2(52111) idle > > postgres 3822 0.0 0.0 369572 9928 ? Ss 09:04 0:00 \_ > postgres: myuser my_db 192.168.1.2(52112) idle > > postgres 3823 0.2 0.6 383368 202312 ? Ss 09:04 1:12 \_ > postgres: myuser my_db 192.168.1.2(52114) idle > > postgres 3824 0.0 0.0 369320 8820 ? Ss 09:04 0:00 \_ > postgres: myuser my_db 192.168.1.2(52115) idle > > postgres 3825 0.4 0.8 413964 257040 ? Ss 09:04 1:54 \_ > postgres: myuser my_db 192.168.1.2(52116) idle > > > Am I reading this right? Are there individual connections using over > 300 MB or RAM by themselves? If I recall correctly, RSS is charged against a PostgreSQL back-end when it touches `shared_buffers`. So that doesn't necessarily mean that the back-end is using the full amount of memory listed as RSS. Yes, measuring how much memory Pg uses is seriously frustrating because OS accounting for shared memory is so bad. See http://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/ -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services --------------080105060703090807020406 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 11/14/2012 06:12 AM, Aaron Bono wrote:


USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
....
postgres  3523  0.5  1.0 426076 313156 ?       Ss   08:44   2:42  \_ postgres: myuser my_db 192.168.1.2(39786) idle                                                           
postgres  3820  0.4  0.9 418988 302036 ?       Ss   09:04   2:11  \_ postgres: myuser my_db 192.168.1.2(52110) idle                                                           
postgres  3821  0.1  0.5 391452 178972 ?       Ss   09:04   0:44  \_ postgres: myuser my_db 192.168.1.2(52111) idle                                                           
postgres  3822  0.0  0.0 369572  9928 ?        Ss   09:04   0:00  \_ postgres: myuser my_db 192.168.1.2(52112) idle                                                           
postgres  3823  0.2  0.6 383368 202312 ?       Ss   09:04   1:12  \_ postgres: myuser my_db 192.168.1.2(52114) idle                                                           
postgres  3824  0.0  0.0 369320  8820 ?        Ss   09:04   0:00  \_ postgres: myuser my_db 192.168.1.2(52115) idle                                                           
postgres  3825  0.4  0.8 413964 257040 ?       Ss   09:04   1:54  \_ postgres: myuser my_db 192.168.1.2(52116) idle                                                          


Am I reading this right?  Are there individual connections using over 300 MB or RAM by themselves?
If I recall correctly, RSS is charged against a PostgreSQL back-end when it touches `shared_buffers`. So that doesn't necessarily mean that the back-end is using the full amount of memory listed as RSS.

Yes, measuring how much memory Pg uses is seriously frustrating because OS accounting for shared memory is so bad.

See http://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/

-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
--------------080105060703090807020406--