Alvaro Herrera wrote:
> Darcy Buskermolen noticed that when one has many databases, the autovac
> launcher starts eating too much CPU.
>
> I tried it here with 200 databases and indeed it does seem to eat its
> share. Even with the default naptime, which I wouldn't have thought
> that was too high (it does make the launcher wake up about three times a
> second though).
This patch does not solve the whole problem but it alleviates it a bit
by throttling pgstat reads. One problem with it is that the interval
for this increases:
/*
* Check whether pgstat data still says we need to vacuum this table.
* It could have changed if something else processed the table while we
* weren't looking.
*
* FIXME we ignore the possibility that the table was finished being
* vacuumed in the last 500ms (PGSTAT_STAT_INTERVAL). This is a bug.
*/
MemoryContextSwitchTo(AutovacMemCxt);
tab = table_recheck_autovac(relid);
which could be a problem in itself, by causing unnecessary vacuums.
Opinions?
--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"In Europe they call me Niklaus Wirth; in the US they call me Nickel's worth.
That's because in Europe they call me by name, and in the US by value!"
Index: src/backend/postmaster/autovacuum.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.58
diff -c -p -r1.58 autovacuum.c
*** src/backend/postmaster/autovacuum.c 12 Sep 2007 22:14:59 -0000 1.58
--- src/backend/postmaster/autovacuum.c 13 Sep 2007 16:57:59 -0000
*************** static void avl_sighup_handler(SIGNAL_AR
*** 291,296 ****
--- 291,297 ----
static void avl_sigusr1_handler(SIGNAL_ARGS);
static void avl_sigterm_handler(SIGNAL_ARGS);
static void avl_quickdie(SIGNAL_ARGS);
+ static void autovac_refresh_stats(void);
*************** AutoVacLauncherMain(int argc, char *argv
*** 488,494 ****
DatabaseListCxt = NULL;
DatabaseList = NULL;
! /* Make sure pgstat also considers our stat data as gone */
pgstat_clear_snapshot();
/* Now we can allow interrupts again */
--- 489,498 ----
DatabaseListCxt = NULL;
DatabaseList = NULL;
! /*
! * Make sure pgstat also considers our stat data as gone. Note: we
! * musn't use autovac_refresh_stats here.
! */
pgstat_clear_snapshot();
/* Now we can allow interrupts again */
*************** rebuild_database_list(Oid newdb)
*** 836,842 ****
HTAB *dbhash;
/* use fresh stats */
! pgstat_clear_snapshot();
newcxt = AllocSetContextCreate(AutovacMemCxt,
"AV dblist",
--- 840,846 ----
HTAB *dbhash;
/* use fresh stats */
! autovac_refresh_stats();
newcxt = AllocSetContextCreate(AutovacMemCxt,
"AV dblist",
*************** do_start_worker(void)
*** 1063,1069 ****
oldcxt = MemoryContextSwitchTo(tmpcxt);
/* use fresh stats */
! pgstat_clear_snapshot();
/* Get a list of databases */
dblist = get_database_list();
--- 1067,1073 ----
oldcxt = MemoryContextSwitchTo(tmpcxt);
/* use fresh stats */
! autovac_refresh_stats();
/* Get a list of databases */
dblist = get_database_list();
*************** table_recheck_autovac(Oid relid)
*** 2258,2264 ****
PgStat_StatDBEntry *dbentry;
/* use fresh stats */
! pgstat_clear_snapshot();
shared = pgstat_fetch_stat_dbentry(InvalidOid);
dbentry = pgstat_fetch_stat_dbentry(MyDatabaseId);
--- 2262,2268 ----
PgStat_StatDBEntry *dbentry;
/* use fresh stats */
! autovac_refresh_stats();
shared = pgstat_fetch_stat_dbentry(InvalidOid);
dbentry = pgstat_fetch_stat_dbentry(MyDatabaseId);
*************** AutoVacuumShmemInit(void)
*** 2734,2736 ****
--- 2738,2759 ----
else
Assert(found);
}
+
+ /*
+ * Refresh pgstats data in an autovacuum process, at most every 500 ms. This
+ * is to avoid rereading the pgstats files too many times in quick succession.
+ */
+ static void
+ autovac_refresh_stats(void)
+ {
+ static TimestampTz last_read = 0;
+ TimestampTz current_time;
+
+ current_time = GetCurrentTimestamp();
+
+ if (!TimestampDifferenceExceeds(last_read, current_time, 500))
+ return;
+
+ pgstat_clear_snapshot();
+ last_read = current_time;
+ }
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
