I have PostgreSQL configured mostly how pgtune recommended, and the settings are supposedly conservative. PostgreSQL is using more memory for a single connection than the settings would suggest is allowed, causing my Linux system to run out of RAM. Here are my settings for a system with 64GB of RAM:
# All set by pgtune... default_statistics_target = 50 maintenance_work_mem = 5GB # ... except I increased this one... constraint_exclusion = on checkpoint_completion_target = 0.9 effective_cache_size = 32GB # ... and decreased this one work_mem = 384MB wal_buffers = 8MB checkpoint_segments = 16 shared_buffers = 15GB max_connections = 80
Postgres is running a huge
UPDATE query right now using the process on top:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 21857 postgres 20 0 43.381g 0.042t 0.015t S 1.0 68.9 605:40.48 postgres 20623 postgres 20 0 15.475g 0.015t 0.015t S 0.0 24.3 64:25.95 postgres 20624 postgres 20 0 15.457g 7.021g 7.013g S 0.0 11.2 0:41.90 postgres
RES is ~42GB for that one. Combined with those other two processes, that makes ~64GB of RAM used, so something is probably page faulting. Shouldn't it be less than or equal to 32GB per process? I thought maybe the
work_mem was not included in
effective_cache_size (documentation doesn't explicitly state it), but that couldn't take 10GB of RAM by itself unless I had 26 joins at once, and I have at most 4. So I'd expect at the very most 34GB RES for one process. What am I missing here?