
Hello,

I've got some strange vacuum full behaviour. I've got a table: 

flows=# explain select * from data;
                            QUERY PLAN
-------------------------------------------------------------------
 Seq Scan on data  (cost=0.00..5045547.76 rows=236174576 width=55) (1 row)

That is sometimes loaded with bunch of data, and sometimes that data gets deleted. As I didn't delete data for a while, I had to reclaim space, that was allocated. So, the normal way to do it would be vacuum full. But it's leaks memory and exits with 'not enough memory'. So...

PostgreSQL 7.4.3
FreeBSD 4.7

vacuum_mem = 32768              # min 1024, size in KB
shared_buffers = 500            # min 16, at least max_connections*2, 8KB each (decreased for this operation)

All external queries stopped, so vacuum is the only operation on PG.

The initial process starts like:

  PID USERNAME PRI NICE  SIZE    RES STATE  C   TIME   WCPU    CPU COMMAND
11383 pgsql     29   0 45456K  7860K CPU1   0   1:57  7.91%  7.91% postgres

Later, rather fast (each line is taken in approximately two-five minute periods):

11383 pgsql     -2   0   140M   130M getblk 1   0:21  8.45%  8.45% postgres
11383 pgsql     -2   0   188M   180M getblk 1   0:29  7.57%  7.57% postgres
11383 pgsql     -2   0   188M   180M getblk 1   0:29  7.57%  7.57% postgres
11383 pgsql     -2   0   252M   240M getblk 0   0:40  8.20%  8.20% postgres
11383 pgsql     -2   0   276M   268M RUN    1   0:44 10.21% 10.21% postgres
11383 pgsql     -2   0   396M   382M getblk 0   1:04  7.47%  7.47% postgres
11383 pgsql     -2   0   468M   457M getblk 1   1:17  5.66%  5.66% postgres
11383 pgsql     -2   0   516M   345M getblk 0   1:27  6.79%  6.79% postgres

(you can see that at the list line the PG was swapped out, though, it didn't do too much of swap ins)

And eventualy:

INFO:  vacuuming "public.data"
ERROR:  out of memory
DETAIL:  Failed on request of size 192.

As PG was swapped out rather easily, I can expect there was some kind of memory leak. This table with indexes uses ~27G. After vacuum it should use around 15G.

If it goes this way I'll sure do simple copy-out/copy-in (downtime wouldn't cost too much). But the interesting way would be solving this trouble. 

Cheers,
Domas

Tue, 27 Jul 2004 11:15:00 +0300
"Domas Mituzas" <Domas.Mituzas@microlink.lt>
FW: 'vacuum full' memory leak?