Hi!
Some of our clients databases are performing less good after a while.
We are using autovacuum to vacuuming and analyzing the tables.
After some analyzes by my own it looks like that the tables or table
indexes are not analyzed or vacuumed fully or correctly.
A count(*) query takes multiple times longer on this databases as on a
fresh dumped/restored version of the same database on the same
machine.
During the query it looks like that postgres scans all the time the
harddisk and is reading a lot more data in then from the fresh
restored database. This showed me the output of the vmstat-commands
'bi'-attribute (blocks reading in) and the up to 99,9% wait-state of
the cpu.
My 1. question is,
if the known bugfixes for autovacuum after release 8.1.4 addressing my
depicted issues?
We are still using 8.1.4 because a database upgrade for us and our
product is a hefty step wich involves a lot of customer databases. But
if it could help we consider to upgrade to 8.1.11 or 8.3. What would u
suggest?
My 2. questions is,
if i possible configured something improper?
For this i will give a brief overview of our database.
The database stores mainly historical data for a reporting
application. This data will be consolidated per day at frequent
intervals. For this the data of one day will be removed from the
historical tables and will be newly calculated out of some tables with
raw data. Depending on the daytime and the amount of data it takes
normaly up to 2 minutes to summarize an compress the data of one day.
After one minute break it starts again.
We talk about up to 3000 records per day out of up to 3 million
records (and growing) in the whole historical table.
Can autovacuum handle that much changing data with this configuration?
From default configuration differing settings:
vacuum_cost_delay = 200
vacuum_cost_page_hit = 6
vacuum_cost_limit = 100
autovacuum_naptime = 60
Strange enough a manual analyze and vacuum makes the measured count(*)
query less performant? See attached vacuum log.
My 3. question is,
if it possible to read the vaccuming or analyzing state of a given
table manually? Are there any indicatores in statistic tables, wich
the autovacuum demaon is using too wich can show me the progress of a
running autovacuum?
We are using a customized debian Linux on Pentium 4 2,8 GHz
Thanks for any help!
regards, thomas
