Alvaro Herrera wrote:
> Darcy Buskermolen wrote:
> > On Thursday 13 September 2007 09:16:52 Alvaro Herrera wrote:
> > > Hi,
> > >
> > > Darcy Buskermolen noticed that when one has many databases, the autovac
> > > launcher starts eating too much CPU.
> >
> > Don't forget the memory leak as well. after 3 or 4 days of running I end
> > up
> > with a 2GB+ AVL..
>
> Huh, sorry for not letting you know, I already fixed that :-) (Please
> grab the latest CVS HEAD and confirm.)
Darcy, please also apply the following patch and see if it reduces the
CPU consumption to a reasonable level.
What this patch does is keep the pgstats data for 1 second in the
autovac launcher. The idea is to avoid reading the data too frequently.
I coded it so that it doesn't affect the worker, because it would make
the table recheck code less effective.
--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"MySQL is a toy compared to PostgreSQL." (Randal L. Schwartz)
(http://archives.postgresql.org/pgsql-general/2005-07/msg00517.php)
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 22:13:29 -0000
*************** int autovacuum_vac_cost_limit;
*** 116,121 ****
--- 116,124 ----
int Log_autovacuum = -1;
+ /* how long to keep pgstat data in the launcher, in milliseconds */
+ #define AUTOVAC_STATS_CACHE 1000
+
/* Flags to tell if we are in an autovacuum process */
static bool am_autovacuum_launcher = false;
*************** static void avl_sighup_handler(SIGNAL_AR
*** 291,296 ****
--- 294,300 ----
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 */
--- 492,501 ----
DatabaseListCxt = NULL;
DatabaseList = NULL;
! /*
! * Make sure pgstat also considers our stat data as gone. Note: we
! * mustn'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",
--- 843,849 ----
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();
--- 1070,1076 ----
oldcxt = MemoryContextSwitchTo(tmpcxt);
/* use fresh stats */
! autovac_refresh_stats();
/* Get a list of databases */
dblist = get_database_list();
*************** do_start_worker(void)
*** 1106,1114 ****
avw_dbase *tmp = lfirst(cell);
Dlelem *elem;
- /* Find pgstat entry if any */
- tmp->adw_entry = pgstat_fetch_stat_dbentry(tmp->adw_datid);
-
/* Check to see if this one is at risk of wraparound */
if (TransactionIdPrecedes(tmp->adw_frozenxid, xidForceLimit))
{
--- 1113,1118 ----
*************** do_start_worker(void)
*** 1121,1129 ****
else if (for_xid_wrap)
continue; /* ignore not-at-risk DBs */
/*
! * Otherwise, skip a database with no pgstat entry; it means it
! * hasn't seen any activity.
*/
if (!tmp->adw_entry)
continue;
--- 1125,1136 ----
else if (for_xid_wrap)
continue; /* ignore not-at-risk DBs */
+ /* Find pgstat entry if any */
+ tmp->adw_entry = pgstat_fetch_stat_dbentry(tmp->adw_datid);
+
/*
! * Skip a database with no pgstat entry; it means it hasn't seen any
! * activity.
*/
if (!tmp->adw_entry)
continue;
*************** 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);
--- 2265,2271 ----
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 ****
--- 2741,2769 ----
else
Assert(found);
}
+
+ /*
+ * Refresh pgstats data in the autovacuum launcher process, at most every 500
+ * ms. This is to avoid rereading the pgstats files too many times in quick
+ * succession. Note: we don't do this in the worker, as it would be
+ * counterproductive.
+ */
+ static void
+ autovac_refresh_stats(void)
+ {
+ if (IsAutoVacuumLauncherProcess())
+ {
+ static TimestampTz last_read = 0;
+ TimestampTz current_time;
+
+ current_time = GetCurrentTimestamp();
+
+ if (!TimestampDifferenceExceeds(last_read, current_time,
+ AUTOVAC_STATS_CACHE))
+ return;
+
+ last_read = current_time;
+ }
+
+ pgstat_clear_snapshot();
+ }
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
