Re: VACUUM FULL out of memory - Mailing list pgsql-hackers

Just to conclude on the issue we had here.

As far as I can see, the ulimits are set up as they should; and on a
64-bit machine with 16GB RAM, I don't see there should be a problem with
allocating 2 GB maintenance work memory. In any case, I have serious
difficulty believing that the ulimits can be the problem since
PostgreSQL reacts the same even with maintenance work memory set very low.

Basically, all of the tests we've run over the past couple of weeks end
the same. VACUUM FULL on the tables runs out of memory and crashes.
Ordinary VACUUM runs fine (albeit slowly) - but recommends that one runs
VACUUM FULL.

Unfortunately, we will not have resources to run further test runs on
this for a while. If we get time next month, I may try to create a small
test case that demonstrates the problem (shouldn't be too hard to do - I
expect throwing in some gigabytes of objects should do the trick), if
anyone is interested.

Our solution to the issue will have to be simply to warn users/adopters
of our system to never run VACUUM FULL on the largeobject table.

Regards,

Michael Akinde
Database Architect, Met.no

---- Michael wrote: ----
Thanks for the explanation on the ulimits; I can see how that could turn
out a problem in some cases.

Following Tom's suggestion, here is the startup script I used:
#!/bin/sh
ulimit -a > $PGHOST/server.ulimit
pg_ctl start -l $PGHOST/server.log

The ulimits seem to be the same, though:
$> cat server.ulimit
core file size          (blocks, -c) 100000000
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

Regards,

Michael A.

Tom Lane wrote:
> Andrew Sullivan <ajs@crankycanuck.ca> writes:
>   
>> On Tue, Jan 08, 2008 at 05:27:16PM +0100, Michael Akinde wrote:
>>     
>>> Those are the ulimits of the db_admin account (i.e., the user that set 
>>> up and runs the DB processes). Is Postgres limited by other settings?
>>>       
>> On one system I used many years ago, /bin/sh wasn't what I thought it
was,
>> and so the ulimit that I got when logged in was not what the
postmaster was
>> starting under.  Took me many days to figure out what was up.
>>     
>
> The only thing I find convincing is to insert "ulimit -a >someplace"
> into the script that starts the postmaster, adjacent to where it does
> so, and then reboot.  There are too many systems on which daemons are
> launched under settings different from what interactive shells use
> (a policy that's often a good one, too).
>
>             regards, tom lane
>   


Content-Type: multipart/alternative;boundary="------------060809000001070209000406"


--------------060809000001070209000406
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Thanks for the explanation on the ulimits; I can see how that could turn 
out a problem in some cases.

Following Tom's suggestion, here is the startup script I used:
#!/bin/sh
ulimit -a > $PGHOST/server.ulimit
pg_ctl start -l $PGHOST/server.log

The ulimits seem to be the same, though:
$> cat server.ulimit
core file size          (blocks, -c) 100000000
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

Regards,

Michael A.

Tom Lane wrote:
> Andrew Sullivan <ajs@crankycanuck.ca> writes:
>   
>> On Tue, Jan 08, 2008 at 05:27:16PM +0100, Michael Akinde wrote:
>>     
>>> Those are the ulimits of the db_admin account (i.e., the user that set 
>>> up and runs the DB processes). Is Postgres limited by other settings?
>>>       
>> On one system I used many years ago, /bin/sh wasn't what I thought it was,
>> and so the ulimit that I got when logged in was not what the postmaster was
>> starting under.  Took me many days to figure out what was up.
>>     
>
> The only thing I find convincing is to insert "ulimit -a >someplace"
> into the script that starts the postmaster, adjacent to where it does
> so, and then reboot.  There are too many systems on which daemons are
> launched under settings different from what interactive shells use
> (a policy that's often a good one, too).
>
>             regards, tom lane
>   


--------------060809000001070209000406
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Thanks for the explanation on the ulimits; I can see how that could
turn out a problem in some cases.<br>
<br>
Following Tom's suggestion, here is the startup script I used:<br>
#!/bin/sh<br>
ulimit -a > $PGHOST/server.ulimit<br>
pg_ctl start -l $PGHOST/server.log<br>
<br>
The ulimits seem to be the same, though:<br>
$> cat server.ulimit<br>
core file size          (blocks, -c) 100000000<br>
data seg size           (kbytes, -d) unlimited<br>
max
nice                       
(-e)0<br>
 
file size               (blocks, -f)
unlimited<br>
pending signals                 (-i)
unlimited<br>
max locked memory       (kbytes, -l) unlimited<br>
max memory size         (kbytes, -m) unlimited<br>
open
files                     
(-n)1024<br>
 
pipe size            (512 bytes, -p) 8<br>
POSIX message queues     (bytes, -q) unlimited<br>
max rt priority                 (-r)
0<br>
stack size              (kbytes, -s) 8192<br>
cpu time               (seconds, -t)
unlimited<br>
max user processes              (-u) unlimited<br>
virtual memory          (kbytes, -v) unlimited<br>
file
locks                     
(-x)unlimited<br>
 
<br>
Regards,<br>
<br>
Michael A.<br>
<br>
Tom Lane wrote:
<blockquote cite="mid10838.1199813614@sss.pgh.pa.us" type="cite"> <pre wrap="">Andrew Sullivan <a
class="moz-txt-link-rfc2396E"href="mailto:ajs@crankycanuck.ca"><ajs@crankycanuck.ca></a> writes: </pre>
<blockquotetype="cite">   <pre wrap="">On Tue, Jan 08, 2008 at 05:27:16PM +0100, Michael Akinde wrote:   </pre>
<blockquotetype="cite">     <pre wrap="">Those are the ulimits of the db_admin account (i.e., the user that set 
 
up and runs the DB processes). Is Postgres limited by other settings?     </pre>   </blockquote> </blockquote> <pre
wrap=""><!----></pre><blockquote type="cite">   <pre wrap="">On one system I used many years ago, /bin/sh wasn't what I
thoughtit was,
 
and so the ulimit that I got when logged in was not what the postmaster was
starting under.  Took me many days to figure out what was up.   </pre> </blockquote> <pre wrap=""><!---->
The only thing I find convincing is to insert "ulimit -a >someplace"
into the script that starts the postmaster, adjacent to where it does
so, and then reboot.  There are too many systems on which daemons are
launched under settings different from what interactive shells use
(a policy that's often a good one, too).
        regards, tom lane </pre>
</blockquote>
<br>
</body>
</html>

--------------060809000001070209000406--