PostgreSQL自动化VACUUM FULL处理过大的表问题

Question

我们的产品使用PostgreSQL数据库服务器，在数百个客户端部署。其中一些客户端在多年内积累了数十GB的数据。因此，在下一个版本中，我们将引入自动清理程序，逐渐归档并删除旧记录，并在夜间批处理作业期间执行。

如果我理解正确，autovacuum将启动并分析和重新组织元组，因此性能将与存在较少记录时相同。

如果我理解正确，实际磁盘空间不会被释放，因为只有通过VACUUM FULL才会发生这种情况，而autovacuum不会触发VACUUM FULL。

因此，我考虑了一个自动化过程来完成这个任务。

我在http://wiki.postgresql.org/wiki/Show_database_bloat找到了由nagios check_postgres使用的膨胀视图。

这个视图好用吗？如果tbloat>2，需要使用VACUUM FULL吗？如果ibloat太高，需要使用REINDEX吗？

对于作为每日批处理作业运行的以下工作有什么评论吗？

- vacuumdb -Z mydatabase #仅分析性地进行vacuum - select tablename from bloatview order by tbloat desc limit 1 - vacuumdb -f -t tablename mydatabase - select tablename, iname from bloatview order by ibloat desc limit 1 - reindexdb -t tablename -i iname mydatabase 当然，我仍然需要在crontab中包装一个漂亮的perl脚本（我们正在使用ubuntu 12），或者postgresql是否有某种调度程序可以使用？

还是这完全过度了，有更简单的程序吗？

- GeertPt

---

vacuumdb -Z 可能并不必要，因为自动清理似乎已经很好地保持了分析数据的最新状态。 - GeertPt

Answer 1

你可能不需要这样做。建议在第一次归档作业后执行此操作以释放磁盘空间，但之后每日的归档作业和自动清理将防止死元组膨胀。

而且，与其执行vacuum full，通常更好的方法是运行cluster table_name using index_name; analyze table_name。这将根据索引重新排序行。这样，相关的表行可以在磁盘上物理接近保存，从而可以限制磁盘寻道（在传统磁盘驱动器上很重要，在固态硬盘上则基本无关紧要），并减少您典型查询的读取次数。

请记住，vacuum full和cluster都会在运行时使您的表无法使用。

- Tometzky

---

谢谢你的提示。我们引入的清理过程将被安排在晚上运行几个小时，但在某些客户那里需要几个晚上才能完成。我同意最好等到清理完全完成，然后在计划的停机时间内只进行一次完整的vacuum或cluster操作。由于我们有大约200个安装需要安排，所以我们希望对此进行一些自动化处理。感谢您提供关于CLUSTER的提示。 - GeertPt

从Postgresql 9.0及以上版本开始，对VACUUM FULL的优化使其成为正确的执行方式。(参见：https://wiki.postgresql.org/wiki/VACUUM_FULL#CLUSTER) - Andrea Salicetti

有人会认为缓存比闪存更快。所以Postgres中没有顺序预取吗？ - mckenzm

Answer 2

好的，我已经将其翻译完成。

我对视图进行了简化/重组，分为以下两个部分：

CREATE OR REPLACE VIEW
    bloat_datawidth AS
SELECT
    ns.nspname AS schemaname,
    tbl.oid   AS relid,
    tbl.relname,
    CASE
        WHEN every(avg_width IS NOT NULL)
        THEN SUM((1-null_frac)*avg_width) + MAX(null_frac) * 24
        ELSE NULL
    END AS datawidth
FROM
    pg_attribute att
JOIN
    pg_class tbl
ON
    att.attrelid = tbl.oid
JOIN
    pg_namespace ns
ON
    ns.oid = tbl.relnamespace
LEFT JOIN
    pg_stats s
ON
    s.schemaname=ns.nspname
AND s.tablename = tbl.relname
AND s.inherited=false
AND s.attname=att.attname
WHERE
    att.attnum > 0
AND tbl.relkind='r'
GROUP BY
    1,2,3;

并且

CREATE OR REPLACE VIEW
    bloat_tables AS
SELECT
    bdw.schemaname,
    bdw.relname,
    bdw.datawidth,
    cc.reltuples::bigint,
    cc.relpages::bigint,
    ceil(cc.reltuples*bdw.datawidth/current_setting('block_size')::NUMERIC)::bigint AS expectedpages,
    100 - (cc.reltuples*100*bdw.datawidth)/(current_setting('block_size')::NUMERIC*cc.relpages) AS bloatpct
FROM
    bloat_datawidth bdw
JOIN
    pg_class cc
ON
    cc.oid = bdw.relid
AND cc.relpages > 1
AND bdw.datawidth IS NOT NULL;

还有定时任务：

#!/bin/bash

MIN_BLOAT=65
MIN_WASTED_PAGES=100
LOG_FILE=/var/log/postgresql/bloat.log
DATABASE=unity-stationmaster
SCHEMA=public

if [[ "$(id -un)" != "postgres" ]]
then
echo "You need to be user postgres to run this script."
exit 1
fi

TABLENAME=`psql $DATABASE -t -A -c "select relname from bloat_tables where bloatpct > $MIN_BLOAT and relpages-expectedpages > $MIN_WASTED_PAGES and schemaname ='$SCHEMA' order by wastedpages desc limit 1"`

if [[ -z "$TABLENAME" ]]
then
echo "No bloated tables." >> $LOG_FILE
exit 0
fi

vacuumdb -v -f -t $TABLENAME $DATABASE >> $LOG_FILE

- GeertPt