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=236174576width=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,
Ihad to reclaim space, that was allocated. So, the normal way to do it would be vacuum full. But it's leaks memory and
exitswith '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
wouldbe solving this trouble.
Cheers,
Domas