2

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?

  • 1
    effective_cache_size is just a planner hints. It affects the planner cost calculation, but not the actual memory usage. – J-16 SDiZ Jan 8 '16 at 6:52
3

If table being updated by your huge UPDATE statement has foreign-key constraints on it, then it will queue up every row updated so that it can validate the constraint at the end of the UPDATE statement. To maintain this queue, it will use as much memory as it needs to, regardless of the settings of work_mem or anything else. That is probably where your 43GB is going. If you don't have enough memory to deal with this, you may need to break up your update into smaller transactions, or drop the constraint and re-add it later.

You are probably over-counting the amount of memory actually used. The "RES" column reported by top will report the amount shared memory (roughly, shared_buffers, with a little more for locks and other overhead) once under each process which has touched that shared_memory.

  • What you said about UPDATE is unfortunate. I think/hope the problem is actually what you said about the shared memory. – sudo Jan 8 '16 at 1:36
1

effective_cache_size have no effect on memory usage.

To quote http://www.postgresql.org/docs/9.5/static/runtime-config-query.html :

This parameter has no effect on the size of shared memory allocated by PostgreSQL, nor does it reserve kernel disk cache; it is used only for estimation purposes.

  • I remember reading that earlier, but it says "shared memory." I think it does affect memory usage. I saw all my postgres processes with much higher RES after I increased the parameter. – sudo Jan 8 '16 at 18:35
  • The key phase is "it is used only for estimation purposes" – J-16 SDiZ Jan 9 '16 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.