Postgre Eating Up Too Much RAM
| От | Aaron Bono |
|---|---|
| Тема | Postgre Eating Up Too Much RAM |
| Дата | |
| Msg-id | CAHfMse1Z+HGnDJ+2may-7vQgOcogjzuGYASB7yPE2CjAmGyCaQ@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Postgre Eating Up Too Much RAM
(Craig Ringer <craig@2ndQuadrant.com>)
Re: Postgre Eating Up Too Much RAM (Christian Kratzer <ck-lists@cksoft.de>) Re: Postgre Eating Up Too Much RAM (Aaron Bono <aaron.bono@aranya.com>) |
| Список | pgsql-admin |
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.
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
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
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
В списке pgsql-admin по дате отправления: