[GENERAL] autovacuum
Hello, I have a table (accounts) with 600,000,000 rows. A heavy high-concurrent workload that makes mostly updates on this table generates a lot of dead tuples in its run, which is expected due to MVCC. The problem is that even though autovacuum is enabled, the autovacuum worker does not vacuum this table (I entered custom autovacuum settings for this table in pg_autovacuum to try to force a situation). Autovacuum is working for other smaller tables but not for accounts. <2008-10-29 11:09:03.453 PDT>DEBUG: 0: accounts: vac: 16697969 (threshold 650), anl: 16697969 (threshold 12048) <2008-10-29 11:09:05.610 PDT>DEBUG: 0: accounts: vac: 16699578 (threshold 650), anl: 16699578 (threshold 12048) <2008-10-29 11:10:03.563 PDT>DEBUG: 0: accounts: vac: 16735906 (threshold 650), anl: 16735906 (threshold 12048) please check the first log message: the vacuum threshold is 6,000,050 rows and the number of dead tuples is 16,697,969. Even though the number of dead_tuples is greater than the threshold the autovacuum is not being triggered for this table. So, besides this condition (dead_tuples > threshold) what else is taken into account by autovacuum? Thank you, -Noah
Re: [GENERAL] autovacuum
On Thu, Oct 30, 2008 at 8:53 PM, Matthew T. O'Connor <[EMAIL PROTECTED]>wrote: > Noah Freire wrote: > >> On Wed, Oct 29, 2008 at 4:46 PM, Matthew T. O'Connor <[EMAIL >> PROTECTED]> [EMAIL PROTECTED]>> wrote: >>Is the table being excluded? (see the pg_autovacuum system table >>settings) >> there's an entry for this table on pg_autovacuum, and it's enabled. >> >> Are you sure that it's not getting processed? Perhaps one worker >>is / has been churning on this table for a *LONG* time (that is a >>fairly big table). >> Right. I was wrong :-) the table is being processed by autovacuum (I >> checked via pg_stat_activity). However, as you pinpointed, it's already >> running for hours (the test workload ended hours ago, basically it is just >> this autovacuum worker running on the system). Is there a way to make a >> more aggressive autovacuum setting for this table? it does not matter if it >> will affect performance, my concern is that it finishes as soon as possible. >> I wonder if a manual vacuum wouldn't be faster. >> > Yes, in the pg_autovacuum table, you can set per-relation vacuum cost > delay settings etc... > Right. cost-delay for this table is already zeroed. Perhaps autovacuum could have an entry for cpu and/or i/o usage threshold, in a way that when one of this resources had an activity below a pre-defined threshold, autovacuum could run more aggressively (using more i/o and/or more cpu).
Re: [GENERAL] autovacuum
On Wed, Oct 29, 2008 at 4:46 PM, Matthew T. O'Connor <[EMAIL PROTECTED]>wrote: > Noah Freire wrote: > >> <2008-10-29 11:09:03.453 PDT>DEBUG: 0: accounts: vac: 16697969 >> (threshold 650), anl: 16697969 (threshold 12048) >> <2008-10-29 11:09:05.610 PDT>DEBUG: 0: accounts: vac: 16699578 >> (threshold 650), anl: 16699578 (threshold 12048) >> <2008-10-29 11:10:03.563 PDT>DEBUG: 0: accounts: vac: 16735906 >> (threshold 650), anl: 16735906 (threshold 12048) >> >> please check the first log message: the vacuum threshold is 6,000,050 rows >> and the number of dead tuples is 16,697,969. Even though the number of >> dead_tuples is greater than the threshold the autovacuum is not being >> triggered for this table. So, besides this condition (dead_tuples > >> threshold) what else is taken into account by autovacuum? >> > > What version of PostgreSQL? 8.3 > Is the table being excluded? (see the pg_autovacuum system table settings) there's an entry for this table on pg_autovacuum, and it's enabled. > Are you sure that it's not getting processed? Perhaps one worker is / has > been churning on this table for a *LONG* time (that is a fairly big > table). Right. I was wrong :-) the table is being processed by autovacuum (I checked via pg_stat_activity). However, as you pinpointed, it's already running for hours (the test workload ended hours ago, basically it is just this autovacuum worker running on the system). Is there a way to make a more aggressive autovacuum setting for this table? it does not matter if it will affect performance, my concern is that it finishes as soon as possible. I wonder if a manual vacuum wouldn't be faster. Thanks, -Noah
