Re: VACUUM ANALYZE out of memory

Stefan Kaltenbrunner wrote:
> Michael Akinde wrote:
>> Incidentally, in the first error of the two I posted, the shared
>> memory setting was significantly lower (24 MB, I believe). I'll try
>> with 128 MB before I leave in the evening, though (assuming the other
>> tests I'm running complete by then).
>
> this is most likely not at all related to your shared memory settings
> but to your setting of maintenance_work_mem which is the amount of
> memory a single backend(!) can use for maintainance operations (which
> VACUUM is for example).
> notice that your first error refers to an allocation of about 500MB
> which your ulimit/kernel process limit simply might not be able to
> give a single process.

Yes - in the first case, the maintenance_work_mem was at default (so I
wasn't surprised to see it fail to allocate half a gigabyte). In the
second case, though, maintenance_work_mem was set at 1024 MB (where it
then has the slighly odd error "Failed on request of size 32").

The server has 4 GB RAM available, so even if it was trying to use 1.2
GB shared memory + 1 GB for maintenance_mem all at once, it still seems
odd that the process would fail. As far as I can tell (running ulimit -a
), the limits look pretty OK to me.

core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
max nice (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) unlimited
max locked memory (kbytes, -l) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) unlimited
max rt priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) unlimited
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

Being unable to run VACUUM FULL isn't a problem for the current
configuration of our application (as it will mostly be large amounts of
static data), but we're likely to have an application working with the
database next year where we'd move around 100 GB through the database on
a daily basis. At least based on the documentation of the various
commands, I would expect that one would want to perform VACUUM FULL
every once in a while.

Again, thanks for the feedback.

Regards,

Michael Akinde
Database Architect, met.no