how do you manage postgres 9's postgres process's memory
| От | Keith Gabryelski |
|---|---|
| Тема | how do you manage postgres 9's postgres process's memory |
| Дата | |
| Msg-id | AANLkTi=c69joHpUj=rHm6irgy-UfCF6Nmeb4nBSWNp-N@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: how do you manage postgres 9's postgres process's
memory
("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
|
| Список | pgsql-admin |
I've noticed my postgres processes have been increasing their memory usage.
this seems to happen because my clients applications are using connection pooling and
until one of the clients forces a connection reset the postgres process does not
release its memory.
I'd love to understand how to manage (constrain) postgres process's memory usage.
as further information:
the clients, in question, are doing exactly one insert statement (with rows
that are around 200 bytes each). there are about 100 inserts (across many clients)
happening per second.
the server is a 16GB ram, 4-processor x64 bit centos machine -- memory grows
(in the worse case) 1G every four hours as long as the connections are kept
open -- it can be as little as 1G every 10 hours.
there can be up to (about) 750 connections to the machine -- and even though
the postgres processes seem to have an upper limit of 500mb (although it's
tough to tell)
here is an example of one process's growth over time --
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 20533 0.0 0.3 647388 52216 ? Ss 17:54 0:01 postgres: postgres mydb 10.252.11.16(39174) idle --> Feb 7 18:28
postgres 20533 0.0 0.4 663532 71028 ? Ss 17:54 0:01 postgres: postgres mydb 10.252.11.16(39174) idle --> Feb 7 18:58
postgres 20533 0.0 0.4 663532 77084 ? Ss 17:54 0:02 postgres: postgres mydb 10.252.11.16(39174) idle --> Feb 7 19:44
postgres 20533 0.0 0.5 663532 89636 ? Ss 17:55 0:03 postgres: postgres mydb 10.252.11.16(39174) idle --> Feb 7 21:14
postgres 20533 0.0 0.6 663532 99728 ? Ss 17:55 0:04 postgres: postgres mydb 10.252.11.16(39174) idle --> Feb 7 21:35
postgres 20533 0.0 0.7 663532 113876 ? Ss 17:55 0:06 postgres: postgres mydb 10.252.11.16(39174) idle in transaction --> Feb 7 22:27
postgres 20533 0.0 0.8 663532 129856 ? Ss 17:55 0:08 postgres: postgres mydb 10.252.11.16(39174) idle --> Feb 7 23:12
postgres 20533 0.0 0.8 663532 138704 ? Ss 17:55 0:09 postgres: postgres mydb 10.252.11.16(39174) idle in transaction --> Feb 7 23:49
postgres 20533 0.0 0.9 663532 143232 ? Ss Feb07 0:10 postgres: postgres mydb 10.252.11.16(39174) idle --> Feb 8 01:36
postgres 20533 0.0 0.9 663532 143232 ? Ss Feb07 0:10 postgres: postgres mydb 10.252.11.16(39174) idle --> Feb 8 03:46
postgres 20533 0.0 0.9 663532 143232 ? Ss Feb07 0:10 postgres: postgres mydb 10.252.11.16(39174) idle --> Feb 8 04:03
postgres 20533 0.0 0.9 663532 143260 ? Ss Feb07 0:10 postgres: postgres mydb 10.252.11.16(39174) idle --> Feb 8 04:11
postgres 20533 0.0 0.9 663532 143260 ? Ss Feb07 0:10 postgres: postgres mydb 10.252.11.16(39174) idle --> Feb 8 04:21
postgres 20533 0.0 0.9 663532 143260 ? Ss Feb07 0:10 postgres: postgres mydb 10.252.11.16(39174) idle --> Feb 8 04:39
postgres 20533 0.0 0.9 663532 143260 ? Ss Feb07 0:10 postgres: postgres mydb 10.252.11.16(39174) idle --> Feb 8 04:48
postgres 20533 0.0 0.9 663532 143364 ? Ss Feb07 0:10 postgres: postgres mydb 10.252.11.16(39174) idle --> Feb 8 10:18
postgres 20533 0.0 0.9 663532 144164 ? Ss Feb07 0:10 postgres: postgres mydb 10.252.11.16(39174) idle --> Feb 8 11:08
postgres 20533 0.0 0.9 663532 144328 ? Ss Feb07 0:10 postgres: postgres mydb 10.252.11.16(39174) idle --> Feb 8 11:56
as you can see -- even 100 connections is going to top out this machines memory if memory stopped at this point (it doesn't -- it continues to grow)
how can I control postgres 9's use of memory. thank you.
listen_addresses = '*'max_connections = 1000shared_buffers = 512MBwork_mem = 256MBmaintenance_work_mem = 1024MBmax_stack_depth = 9MBeffective_io_concurrency = 4synchronous_commit = offfull_page_writes = oncommit_delay = 10commit_siblings = 2checkpoint_segments = 128checkpoint_timeout = 10mincheckpoint_completion_target = 0.9checkpoint_warning = 30smax_wal_senders = 0wal_keep_segments = 128effective_cache_size = 1024MBlog_destination = 'stderr'logging_collector = onlog_directory = 'pg_log'log_filename = 'postgresql-%a.log'log_truncate_on_rotation = onlog_rotation_age = 1dlog_rotation_size = 0datestyle = 'iso, mdy'lc_messages = 'C'lc_monetary = 'C'lc_numeric = 'C'lc_time = 'C'default_text_search_config = 'pg_catalog.english'
В списке pgsql-admin по дате отправления:
Следующее
От: "Kevin Grittner"Дата:
Сообщение: Re: how do you manage postgres 9's postgres process's memory