SAP BTP中的PostgreSQL数据库膨胀应对方法

前言

对PostgreSQL而言，什么是数据库膨胀？

在PostgreSQL数据库中，当对表或索引进行CUD操作时，会产生膨胀。导致膨胀的操作是事务性DML语句，如插入、更新和删除。虽然已删除/以前版本的行(元组)使用的磁盘空间可以进行重用，但大多数情况下不会立即回收，因此会导致空间上的膨胀。频繁的UPDATE和DELETE操作可能会在磁盘上的表或底层索引关系文件中留下大量未使用的空间。随着时间的推移，这个空间会不断增加，并导致表的性能显著下降，影响索引性能、搜索，在最坏的情况下，由于危险的低可用磁盘空间而导致停机。

分析

1、膨胀如何产生？

PostgreSQL数据库引擎实现了MVCC (Multiversion Concurrency Control)。PostgreSQL的MVCC实现基于元组的版本控制。

当在数据库上进行UPDATE或DELETE操作时，它不会从磁盘上物理删除该元组。对于UPDATE，它将受影响的行标记为“不可见”，并将这些行的新版本插入到相应的表中。这同样适用于delete，其中已删除的行被标记为不可见。这些“不可见”的行也被称为死元组。

随着时间的推移，这些死元组可能会大量累积，并且可能超过表中实际可见的行，从而使表在查询性能方面实际上无法使用。当然，这会导致相当大的性能下降。在更糟糕的情况下，死元组可能会耗尽大部分持久磁盘空间，并由于危险的磁盘满场景而导致正常的数据库连接被迫断开。

2、如何检测膨胀？

这里推荐两种方式：

2.1、膨胀检测

表和索引中的膨胀量可以使用下面的查询来估计:

SELECT
  current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
  ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::float/otta END)::numeric,1) AS tbloat,
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
  iname, /*ituples::bigint, ipages::bigint, iotta,*/
  ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::float/iotta END)::numeric,1) AS ibloat,
  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
  SELECT
    schemaname, tablename, cc.reltuples, cc.relpages, bs,
    CEIL((cc.reltuples*((datahdr+ma-
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
  FROM (
    SELECT
      ma,bs,schemaname,tablename,
      (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
      (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
    FROM (
      SELECT
        schemaname, tablename, hdr, ma, bs,
        SUM((1-null_frac)*avg_width) AS datawidth,
        MAX(null_frac) AS maxfracsum,
        hdr+(
          SELECT 1+count(*)/8
          FROM pg_stats s2
          WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
        ) AS nullhdr
      FROM pg_stats s, (
        SELECT
          (SELECT current_setting('block_size')::numeric) AS bs,
          CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
          CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
        FROM (SELECT version() AS v) AS foo
      ) AS constants
      GROUP BY 1,2,3,4,5
    ) AS foo
  ) AS rs
  JOIN pg_class cc ON cc.relname = rs.tablename
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
  LEFT JOIN pg_index i ON indrelid = cc.oid
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC

看一个简单的例子，看看上边结果如何：

postgres=# create table t(id int primary key, col2 text);
CREATE TABLE
postgres=# insert into t select n, 'test' || n from generate_series(1,500000) as n;
INSERT 0 500000
postgres=# delete from t where id between 100000 and 300000;
DELETE 200001

如果执行上边的检测语句，结果为：

 current_database | schemaname |    tablename     | tbloat | wastedbytes |                     iname                     | ibloat | wastedibytes 
------------------+------------+------------------+--------+-------------+-----------------------------------------------+--------+--------------
 postgres         | public     | t                |    1.7 |     8904704 | t_pkey                                        |    1.4 |      3432448

注意:这可能是估计膨胀最有效的方法，因为可以使用服务绑定和服务键中返回的标准数据库用户执行查询。
但是，查询只提供表和索引增长活动的粗略估计，不应将其视为数据库对象空间消耗的完全准确表示。
这里： 服务绑定和服务键中返回的标准数据库用户， 是指云环境下边绑定后生成的相关连接信息。不需要额外的用户权限。

2.2、使用pgstattuple进行准确的膨胀检测

Pgstattuple是一个扩展模块，可用于获得表大小的清晰图像。有两种方法可以在你的Hyperscaler PostgreSQL数据库上启用这个扩展(你应该只使用其中一种机制):

• 你可以遵循PostgreSQL hyperscaler选项扩展API的指南，并使用HTTP API创建扩展。注意，在此场景中，你需要单独授予对扩展对象的访问权限。

• 运行关于为数据库获取类超级用户的角色，并使用该数据库用户执行CREATE EXTENSION pgstattuple。获取这个角色，一样要用到相关的HTTP API。

这里假定你已经有了pgstattuple扩展。

SELECT relname, (pgstattuple(oid)).* FROM pg_class 
        WHERE relkind = 'r' order by dead_tuple_percent desc;

这里需要注意：

解释一下这项指标没有与dynatrace直接集成的原因： 
注意:我们没有为数据库膨胀提供Dynatrace自定义指标，因为膨胀是按表估计的。虽然理论上我们可以将带有表名的维度附加到这样的自定义度量，但这会给度量收集过程增加巨大的开销，特别是在具有大量模式和表的数据库中。此外，膨胀指标通常以很高的精度检测(例如每1天或者5天一次)—它们不适合通常与Dynatrace相关的高精度指标(例如每2分钟收集一次CPU利用率)。

3、如何去除膨胀

有很多方法(以及数据库内部的内置机制)可以从表中删除膨胀。从数据库中去除膨胀被称为抽真空。

3.1、Autovacuum

在大多数情况下，让autovacuum守护进程执行真空(回收空间）就足够了，autovacuum守护进程是PostgreSQL数据库引擎的一个子进程，它自动执行VACUUM和ANALYZE命令。自动真空检查具有大量插入、更新或删除元组的表。除非显式地关闭每个表(使用ALTER table SET (autovacuum_enabled = false))，否则默认情况下，所有数据库实例上都启用autovacuum守护进程。

• VACUUM：该命令处理表并删除表和索引中的死元组版本，并标记可供将来重用的空间。但是，它不会将空间返回给操作系统，除非在特殊情况下，表末尾的一个或多个页面完全空闲，并且可以很容易地获得排他表锁。VACUUM可以与表上的常规操作(SELECT, INSERT, UPDATE, DELETE)并行工作，但是表结构的更改(如ALTER table)在VACUUM期间被阻塞。

• VACUUM FULL：这是VACUUM的一个变体，它通过重新写入没有死空间的表文件的完整新版本来主动压缩表。这样可以最小化表的大小，但可能会花费很长时间。它还需要额外的磁盘空间用于表的新副本，直到操作完成，并且需要对表执行ACCESS EXCLUSIVE锁。因此，它不能与表的其他操作同时进行。

同样，要注意：

注意：autovacuum守护进程运行VACUUM，但如上所述，VACUUM不能删除所有死元组。auto vacuum对所有表按照特定的时间表运行，并受运行时因素的控制。调优这些参数可能会给你带来一些好处，但必须谨慎，因为不正确的调优设置可能会严重影响数据库性能。建议将这些设置应用于单个表，并且可以使用服务绑定用户来完成。例如:

ALTER TABLE employees SET (autovacuum_vacuum_scale_factor = 0.02);
ALTER TABLE employees SET (autovacuum_vacuum_threshold = 10000);

注意: ANALYZE是一个SQL命令，它收集有关数据库的统计信息(即数据库实体的内容，如表)，并将结果存储在pg_statistic系统编目中。查询规划器随后使用这些信息来确定查询的最有效执行计划。虽然Autovacuum Daemon会按计划自动执行ANALYZE，但通常建议定期对已真空的表执行ANALYZE，以定期更新统计数据编目。ANALYZE需要表上的读锁，并且可以像往常一样与其他活动并行运行。更多关于ANALYZE的细节,  建议读读官方文档：https://www.postgresql.org/docs/current/sql-analyze.html

3.2、使用pg_repack

pg_repack是PostgreSQL的扩展，它允许你从表和索引中删除膨胀，并可选择恢复集群索引的物理顺序。与VACUUM FULL不同，它是在线工作的，在处理过程中不会对已处理的表持有排他锁。

Pg_repack要求在表上启用和执行类似超级用户的特权。此外，在数据库上执行pg_repack需要安装一个CLI实用程序，并将其用于目标数据库，这在很大程度上类似于psql客户机实用程序。

在获取完对应的权限以后，你基本上就可以正常使用pg_repack了。

• 如上所述，pg_repack实用程序是一个客户端应用程序，它需要连接到数据库来执行清除膨胀的过程。如果你已经准备好了，你可以按照下面的指导原则，在你选择的客户机上自己编译和下载这个实用程序。当然，在执行该实用程序之前，你需要打开通往实例的Cloud Foundry SSH隧道。

警告: 如果你的表非常大，repack可能需要一段时间，并且由于本地网络故障或Cloud Foundry应用程序重新启动，隧道可能会中断。这可能会使数据库处于不一致的状态。这些因素，在生产环境当中也是需要考虑进去的。

总结

推荐的相关做法：

• 临时表因导致无法访问的膨胀空间而臭名昭著。如果您的应用程序创建了临时表，请确保您的应用程序在不再需要这些临时表时删除它们，因为autovacuum守护进程无法定位临时表。当不再需要临时表时，可以使用TRUNCATE清空表，然后清空pg_attribute和pg_depend系统目录表，以摆脱悬空引用。

  避免这个问题的另一个方法是删除临时表上事务提交时的所有行。这可以使用如下语法来实现:CREATE TEMP TABLE IF NOT EXISTS temp_table(column_descriptions) ON COMMIT DELETE ROWS;

• 当对表中索引定义的列进行更改时，会生成与索引相关的膨胀。索引更新会产生死元组，由autovacuum守护进程清理。但是，清理可能会花费大量的时间和CPU/IO资源。手动清理索引相关的膨胀可以在表上强制调用，使用:

  VACUUM (INDEX CLEANUP ON) table;
  
  

在实际应用当中，有可能有的表永远不会执行vacuum。因为它达不到触发autovacuum的必要条件。为了便于age的同步更新和处理，这个时候可以定期手动进行vaccum操作。

我是【Sean】,  欢迎大家长按关注公众号：数据库杂记。