Well I adjusted work_mem, ran pg_ctl reload, verified that the setting
change took place by running "show work_mem", but I am noticing zero
difference. I am noticing no performance difference in the clustering,
and the out of memory errors still occur. First I halved work_mem,
reducing it to 100000. I tested that, noticed no difference, then I
reduced work_mem all the way down to 1024. Tested again - same results.
However halving maintenance_work_mem did make a difference. Normally,
when I'm watching top before the big bang (oom), I see one process
running at RES 1.6g...Then the other increases and increases till it
hits about 1g, then kaboom. After halving maintenance_work_mem (262144
now), I see one process running at RES 1.1g instead of the usual 1.6g.
The other process increases to about 900m, and lo and behold, nothing
blows up! I have tested several times now.
But now performance is taking a hit. My boss is eager to slap more RAM
into the server, and I can't blame him. So we will likely add a few
gigs of RAM, revert maintenance_work_mem, and tune work_mem a bit just
to get it at least a reasonable, non-arbitrary value.
Guess I'm about ready to wrap up this thread, but I was just wondering
if Alvaro might have confused work_mem with maintenance_work_mem. The
docs say that work_mem is used for internal sort operations, but they
also say maintenance_work_mem is used for create index. My tests seem
to show that clustering does not use work_mem at all (or my clusters
don't use very much of it for whatever reason). Can anyone confirm
this?
Thank you all for your help,
Mark
