参数autovacuum_naptime

这个参数是一个整型，其含义在官方文档中的描述如下：

Specifies the minimum delay between autovacuum runs on any given database. In each round the daemon examines the database and issues VACUUM and ANALYZE commands as needed for tables in that database. If this value is specified without units, it is taken as seconds. The default is one minute (1min). This parameter can only be set in the postgresql.conf file or on the server command line.

我们知道关于autovacuum有两种类型的进程，一个是autovacuum launcher，它是一个管理进程，数据库实例启动后它就会启动，我们简称它为AVL。另外一种进程是autovacuum worker进程，它是具体干活的，我们简称之为AVW进程。

AVL进程的主程序就是一个无限循环，直到整个数据库实例被关闭时才停止。每次循环，它都要休眠一段时间，这个时间就是autovacuum_naptime这个参数规定的。你可以通过把这个参数变小，来提高AVL的活动频度，对于一些需要经常vacuum操作的情景比较有利。下面是代码细节：

	/* loop until shutdown request */
	while (!ShutdownRequestPending)
	{
		struct timeval nap;

		/*
		 * This loop is a bit different from the normal use of WaitLatch,
		 * because we'd like to sleep before the first launch of a child
		 * process.  So it's WaitLatch, then ResetLatch, then check for
		 * wakening conditions.
		 */

		launcher_determine_sleep(!dlist_is_empty(&AutoVacuumShmem->av_freeWorkers),
								 false, &nap);

		/*
		 * Wait until naptime expires or we get some type of signal (all the
		 * signal handlers will wake us by calling SetLatch).
		 */
		(void) WaitLatch(MyLatch,
						 WL_LATCH_SET | WL_TIMEOUT | WL_EXIT_ON_PM_DEATH,
						 (nap.tv_sec * 1000L) + (nap.tv_usec / 1000L),
						 WAIT_EVENT_AUTOVACUUM_MAIN);

		ResetLatch(MyLatch);
        }

上述代码就是循环的主体部分。其中有一个时间变量nap，它的值由launcher_determin_sleep函数来计算出来，然后通过WaitLatch来进行休眠。下面我们看看launcher_determin_sleep()函数的代码：

/*
 * Determine the time to sleep, based on the database list.
 *
 * The "canlaunch" parameter indicates whether we can start a worker right now,
 * for example due to the workers being all busy.  If this is false, we will
 * cause a long sleep, which will be interrupted when a worker exits.
 */
static void
launcher_determine_sleep(bool canlaunch, bool recursing, struct timeval *nap)
{
	/*
	 * We sleep until the next scheduled vacuum.  We trust that when the
	 * database list was built, care was taken so that no entries have times
	 * in the past; if the first entry has too close a next_worker value, or a
	 * time in the past, we will sleep a small nominal time.
	 */
	if (!canlaunch)
	{
		nap->tv_sec = autovacuum_naptime;
		nap->tv_usec = 0;
	}
	else if (!dlist_is_empty(&DatabaseList))
	{
		TimestampTz current_time = GetCurrentTimestamp();
		TimestampTz next_wakeup;
		avl_dbase  *avdb;
		long		secs;
		int			usecs;

		avdb = dlist_tail_element(avl_dbase, adl_node, &DatabaseList);

		next_wakeup = avdb->adl_next_worker;
		TimestampDifference(current_time, next_wakeup, &secs, &usecs);

		nap->tv_sec = secs;
		nap->tv_usec = usecs;
	}
	else
	{
		/* list is empty, sleep for whole autovacuum_naptime seconds  */
		nap->tv_sec = autovacuum_naptime;
		nap->tv_usec = 0;
	}

	/*
	 * If the result is exactly zero, it means a database had an entry with
	 * time in the past.  Rebuild the list so that the databases are evenly
	 * distributed again, and recalculate the time to sleep.  This can happen
	 * if there are more tables needing vacuum than workers, and they all take
	 * longer to vacuum than autovacuum_naptime.
	 *
	 * We only recurse once.  rebuild_database_list should always return times
	 * in the future, but it seems best not to trust too much on that.
	 */
	if (nap->tv_sec == 0 && nap->tv_usec == 0 && !recursing)
	{
		rebuild_database_list(InvalidOid);
		launcher_determine_sleep(canlaunch, true, nap);
		return;
	}

	/* The smallest time we'll allow the launcher to sleep. */
	if (nap->tv_sec <= 0 && nap->tv_usec <= MIN_AUTOVAC_SLEEPTIME * 1000)
	{
		nap->tv_sec = 0;
		nap->tv_usec = MIN_AUTOVAC_SLEEPTIME * 1000;
	}

	/*
	 * If the sleep time is too large, clamp it to an arbitrary maximum (plus
	 * any fractional seconds, for simplicity).  This avoids an essentially
	 * infinite sleep in strange cases like the system clock going backwards a
	 * few years.
	 */
	if (nap->tv_sec > MAX_AUTOVAC_SLEEPTIME)
		nap->tv_sec = MAX_AUTOVAC_SLEEPTIME;
}

如果入口参数canlaunch为false，则整个代码非常简单，就是nap的值被设置为autovacuum_naptime规定的值，在函数最后，检查一下这个值，防止它过大，最大值不能超过MAX_AUTOVAC_SLEEPTIME，其定义如下：

/* the minimum allowed time between two awakenings of the launcher */
#define MIN_AUTOVAC_SLEEPTIME 100.0 /* milliseconds */
#define MAX_AUTOVAC_SLEEPTIME 300	/* seconds */

由此可知，你能控制的autovacuum_naptime的取值范围是100毫秒到300秒之间，缺省值是1分钟。大家可以参考Robert Haas的一篇优秀的blog：

https://rhaas.blogspot.com/2019/02/tuning-autovacuumnaptime.html

在这个blog中，他提到了一个很好的案例，你可以在面试时吹嘘一下这是你的功劳：

Since the previous paragraph suggests that increase autovacuum_naptime is a really bad idea, you might reasonably wonder whether decreasing autovacuum_naptime is a really good idea.  I have run across one case where I recommended such a settings change and it helped a lot.  In that case, the customer had a very small table which was used as a queue, with items added and removed constantly at very high speed.  It bloated very rapidly with the default settings, but changing autovacuum_naptime to 15 seconds fixed the problem.

结论：

我们可以调整autovacuum_naptime的值来控制auto vacuum活动的频度。对于数据修改非常激烈的数据库，这个值应该越小越好。

参数autovacuum_work_mem和maintenance_work_mem的关系

这两个参数在PG17以后，进行了巨大的改变。在PG17之前，这两个参数最大不能超过1GB，但是17取消了这个限制。

具体可以参考这个文档：

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html

当你决定对一张表进行vacuum时，你需要考虑maintenance_work_mem的值的大小。你可以使用SET SESSION命令来设置。VACUUM会分配一个大数组，里面每个成员都是死亡记录的位置信息，每个成员占据6个字节，这个数组被称为“死亡记录数组”。假设表中有10条死亡记录，则就需要60个字节。所以maintenance_work_mem设置为多大呢，取决于表中有多少死亡记录。你可以通过下面的SQL来查询一个表中的死亡记录：

SELECT relname AS table_name, n_dead_tup AS dead_records 
FROM pg_stat_all_tables 
WHERE relname='state';

你把n_deap_tup的数值乘以6，就是死亡数组的体积，就是你应该设置的maintenance_work_mem的值的大小。

autovacuum的参数log_autovacuum_min_duration

这个参数的官方解释如下：

Causes each action executed by autovacuum to be logged if it ran for at least the specified amount of time. Setting this to zero logs all autovacuum actions. -1 disables logging autovacuum actions. If this value is specified without units, it is taken as milliseconds. For example, if you set this to 250ms then all automatic vacuums and analyzes that run 250ms or longer will be logged. In addition, when this parameter is set to any value other than -1, a message will be logged if an autovacuum action is skipped due to a conflicting lock or a concurrently dropped relation. The default is 10min. Enabling this parameter can be helpful in tracking autovacuum activity. This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overridden for individual tables by changing table storage parameters.

其含义是：

这是一个时间参数，缺省值是10分钟，缺省单位是毫秒(millisecond)，即千分之一秒。当一次autovacuum的持续时间等于或者超过这个参数规定的时间后，PG将在日志中记录本次autovacuum的信息。

int			Log_autovacuum_min_duration = 600000; /// 缺省值是10分钟

上述定义是在autovacuum.c中给出的。