Re: Postgre Eating Up Too Much RAM - Mailing list pgsql-admin

From Aaron Bono
Subject Re: Postgre Eating Up Too Much RAM
Date
Msg-id CAHfMse0pNFN51yXRkkcR1_zgmscqZAmrGL5Dnx8LM2_kveskJQ@mail.gmail.com
Whole thread Raw
In response to Postgre Eating Up Too Much RAM  (Aaron Bono <aaron.bono@aranya.com>)
List pgsql-admin
Just putting a follow up on this issue as it is still unresolved.

I worked with a PostgreSQL sys admin and they could not find anything amiss with the server or configuration.

Then I talked to the hosting company (Liquid Web) and they said the parent (it is on a Bare Metal Storm server) had a hardware problem.  Last week I moved to a new server (did a clone) and it went to a new parent as well as new hardware.

And today the server went down twice - it was up for a full week no problem and then on a low usage day it went down two times in about 8 hours.

The attached is what was on the terminal when the server went down and before we rebooted it the second time.

Inline image 1

I have the ISP working on helping out but if anyone has any thoughts I am happy to hear them.

Thanks!
Aaron

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
==================================================================


On Tue, Nov 13, 2012 at 4:12 PM, Aaron Bono <aaron.bono@aranya.com> wrote:
I have been struggling with an issue on our database server lately with Postgres crashing our server by taking up too much RAM.  To alleviate this problem, I just upgraded from a 6 GB RAM server to a new 32 GB RAM server.  The new server is running Ubuntu 10 with nothing but PostgreSQL 8.4.14 installed.

Today, after being in use for only 24 hours, it hung the server again.  Now, when I run a check on memory usage, I get a quickly growing amount of RAM being used:

free -mt

             total       used       free     shared    buffers     cached
Mem:         30503      20626       9876          0        143      15897
-/+ buffers/cache:       4586      25917
Swap:         1913          0       1913
Total:       32417      20626      11790

Additionally, I see using ps that Postgres is the only process using over 0.1 % of the RAM.

Here is a sample of the PS command for some of the Postgres processes (there are currently a little over 200 active connections to the database):

ps axuf

....
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?  This seems excessive.  (Note I am not a system admin exactly so please correct me if I am reading this wrong.)

My postgresql.conf looks like this (I have only included the non-commented lines):

data_directory = '/var/lib/postgresql/8.4/main'
hba_file = '/etc/postgresql/8.4/main/pg_hba.conf'
ident_file = '/etc/postgresql/8.4/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/8.4-main.pid'
listen_addresses = 'localhost,192.168.1.200'
port = 5432
max_connections = 1000
unix_socket_directory = '/var/run/postgresql'
ssl = true
shared_buffers = 256MB
vacuum_cost_delay = 20ms
default_statistics_target = 100
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0MB
log_connections = on
log_disconnections = on
log_line_prefix = '<%t %u %h>'
track_activities = on
track_counts = on
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'

I have read quite a bit over the last couple days and must be missing something as I cannot see why each connection is using so much memory.

Thanks for any help you can provide!

-Aaron

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
==================================================================

Attachment

pgsql-admin by date:

Previous
From: "Kevin Grittner"
Date:
Subject: Re: Autovacuum issues with truncate and create index ...
Next
From: ynux
Date:
Subject: SQL to monitor postgres with prtg /