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