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
Attachment:
vacuum.log
Description: Binary data
---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
