Re: Strange VACUUM behaviour

Jim C. Nasby wrote:
>>We started a VACUUM (not a VACUUM FULL) on one of your postgres 7.4.9
>>databases a few days ago. It's still running yet, and says the
>>folloing about once per second:
>>
>>INFO: index "pg_toast_2144146_index" now contains 1971674 row versions
>>in 10018 pages
>>DETAIL: 4 index row versions were removed.
>>2489 index pages have been deleted, 0 are currently reusable.
>>
>>The number of row versions decreases by 4 each time the message is
logged.
>>
>>The file belonging to pg_toast_2144146_index has about 80MB,
>>for pg_toast_2144146 there are 6 files, five of them are
>>1GB, the last one is about 5MB in size. The "original" relation
>>(the one that references pg_toast_2144146 in it's reltoastrelid field)
>>has one datafile of 11MB.
> One issue is that pg_toast tables can't vacuum rows until their
> respective rows have been deleted by vacuuming the base table. But it's
> still odd that the count decreases by 4 each time you run it.
>
> As for the length of time, that could be due to heavily loaded hardware.
> You might do better if you increase vacuum_memory (or whatever the
> setting was called in 7.4...)
>
> That index does have about 20% bloat though; so a reindex would probably
> be a good idea.

Hi

Just for the archives - I finally solved the problem - and of course
it was me being a dumbass. I _wanted to set vacuum mem to 256mb, but
overlooked that the setting is in kb, not in bytes - so the value
I set was actually 256GB - which seems to have overflowed to some
awefully small value, and caused the vacuum to run in really small steps...

Might be worth an overflow check and/or some notice in the docs, though

greetings, Florian Pflug