Re: Postgre Eating Up Too Much RAM

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
==================================================================