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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
