On Tue, 8 May 2007, Heikki Linnakangas wrote:
Pomarede Nicolas wrote:
On Tue, 8 May 2007, Heikki Linnakangas wrote:
Pomarede Nicolas wrote:
But for the data (dead rows), even running a vacuum analyze every day is
not enough, and doesn't truncate some empty pages at the end, so the data
size remains in the order of 200-300 MB, when only a few effective rows
are there.
For a table like that you should run VACUUM much more often than once a
day. Turn on autovacuum, or set up a cron script etc. to run it every 15
minutes or so.
Yes, I already do this on another spool table ; I run a vacuum after
processing it, but I wondered if there was another way to keep the disk
size low for this table.
How much concurrent activity is there in the database? Running a vacuum right
after processing it would not remove the deleted tuples if there's another
transaction running at the same time. Running the vacuum a few minutes later
might help with that. You should run VACUUM VERBOSE to see how many
non-removable dead tuples there is.
There's not too much simultaneous transaction on the database, most of the
time it shouldn't exceed one minute (worst case). Except, as I need to run
a vacuum analyze on the whole database every day, it now takes 8 hours to
do the vacuum (I changed vacuum values to be a little slower instead of
taking too much i/o and making the base unusable, because with
default vacuum values it takes 3-4 hours of high i/o usage (total base
is 20 GB) ).
So, at this time, the complete vacuum is running, and vacuuming only the
spool table gives all dead rows are currently not removable (which is
normal).
I will run it again later when the complete vacuum is over, to see how
pages are affected.
Nicolas
