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 = 1000 shared_buffers = 512MB work_mem = 256MB maintenance_work_mem = 1024MB max_stack_depth = 9MB effective_io_concurrency = 4 synchronous_commit = off full_page_writes = on commit_delay = 10 commit_siblings = 2 checkpoint_segments = 128 checkpoint_timeout = 10min checkpoint_completion_target = 0.9 checkpoint_warning = 30s max_wal_senders = 0 wal_keep_segments = 128 effective_cache_size = 1024MB log_destination = 'stderr' logging_collector = on log_directory = 'pg_log' log_filename = 'postgresql-%a.log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 0 datestyle = 'iso, mdy' lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' default_text_search_config = 'pg_catalog.english'
