mogdb理解表膨胀和死行

由迪 2024-01-29

228

原作者：陈坤

概念描述
测试验证
- 检查相关参数配置：
- 创建测试表
- 1，测试死行和膨胀率的关系
- 2，测试vacuum清理后的空间再利用
- 3，测试vacuum full回收空间
- 参数说明
- 测试用的sql:
  - 表膨胀检查sql
  - 死行检查sql：

概念描述

在做delete操作时，数据集(也叫做元组 (tuples))是没有立即从数据文件中移除的，仅仅是通过在行头部设置xmax做一个删除标记。而update操作则可以看作是先delete再insert。

这是Postgresql MVCC（Multi-Version Concurrency Control 多版本并发控制）的基本思想之一，因为它允许在不同进程之间只进行最小的锁定就可以实现更大的并发性。这个MVCC实现的缺点是它会留下被标记删除的元组（dead tuples），即使在这些版本的所有事务完成之后。

所以如果不清理掉对任何事务都是不可见的dead tuples，它们将会永远留在数据文件中，浪费磁盘空间，对于表来说，有过多的删除和更新，dead tuples很容易占绝大部分磁盘空间。而且dead tuples也会在索引中存在，更加加重磁盘空间的浪费。这就是所谓的膨胀（bloat）。

表膨胀后每次查询时需要处理的数据增多，会导致查询的速度变慢。

测试验证

检查相关参数配置：

  show autovacuum;

如果autovacuum是打开的，先暂时关闭

  alter system set autovacuum=off;

检查两次自动清理操作的时间间隔。

  show autovacuum_naptime;

检查autovacuum_mode

  show autovacuum_mode;

analyze表示只做autoanalyze。
vacuum表示只做autovacuum。
mix表示autoanalyze和autovacuum都做。
none表示二者都不做。
建议使用mix

创建测试表

  CREATE TABLE COMPANY(
ID INT PRIMARY KEY     NOT NULL,
NAME           TEXT    NOT NULL,
AGE            INT     NOT NULL,
ADDRESS        TEXT,
SALARY         INT,
datetime      varchar ( 40 )  DEFAULT  (now())
);

插入10万行数据

  begin
for i in 1..100000 LOOP
insert into COMPANY values(i,md5(random()::text), i1.1, md5(random()::text), i10);
end LOOP;
end;
/

1，测试死行和膨胀率的关系

检查当前的表膨胀率和死行数量(sql在最后）

死行：

 relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup | last_vacuum | last_analyze
---------+-----------+-----------+-----------+------------+------------+-------------+--------------
 company |    100000 |         0 |         0 |     100000 |          0 |             |

膨胀率：
    db    | schemaname | tablename | 优化器估算表活的行数 | pages | 估算有效数据需要page | 表膨胀倍数 | wastedpages | wastedbytes |  wastedsize
----------+------------+-----------+----------------------+-------+----------------------+------------+-------------+-------------+--------------
 postgres | public     | company   |               100000 |  1819 |                 1714 |        1.1 |         105 |      860160 | 860160 bytes

(1 row)

然后更新10万行数据

  UPDATE COMPANY SET AGE = AGE-1 WHERE ID <= 100000;

检查死行数量，结果会显示死行有10万，就是刚才更新10万条数据产生的。

 relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup | last_vacuum |         last_analyze
---------+-----------+-----------+-----------+------------+------------+-------------+-------------------------------
 company |    100000 |    100000 |         0 |     100000 |     100000 |             | 2023-08-22 08:51:08.081892+08

检查表膨胀率。发现没有变化，因为关闭了autovacuum，所以此时没有触发更新统计信息，估算结果没有变化。

    db    | schemaname | tablename | 优化器估算表活的行数 | pages | 估算有效数据需要page | 表膨胀倍数 | wastedpages | wastedbytes |  wastedsize
----------+------------+-----------+----------------------+-------+----------------------+------------+-------------+-------------+--------------
 postgres | public     | company   |               100000 |  1819 |                 1714 |        1.1 |         105 |      860160 | 860160 bytes

手动调用

  analyze company

再检查表膨胀率，结果为2.1倍左右，符合预期。

    db    | schemaname | tablename | 优化器估算表活的行数 | pages | 估算有效数据需要page | 表膨胀倍数 | wastedpages | wastedbytes |   wastedsize
----------+------------+-----------+----------------------+-------+----------------------+------------+-------------+-------------+----------------
 postgres | public     | company   |               100000 |  3637 |                 1714 |        2.1 |        1923 |    15753216 | 15753216 bytes
(1 row)

手动vacuum 或打开autovacuum清理死行：

  vacuum analyze company;

或

  alter system set autovacuum=on;

等待自动清理

清理完成后查询死行数量，可以看到n_dead_tup为0说明已经清理干净，并且可以观察 last_vacuum 的时间。

 relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup |          last_vacuum          |         last_analyze
---------+-----------+-----------+-----------+------------+------------+-------------------------------+------------------------------
 company |    100000 |    100000 |         0 |     100000 |          1 | 2023-08-22 08:56:08.477993+08 | 2023-08-22 08:56:08.63536+08
(1 row)

再检查表的膨胀率，发现是不变的。因为有效数据没有变化，使用普通 vacuum清理死行后的空间，仍然被数据库占用，并没有还给操作系统。

2，测试vacuum清理后的空间再利用

再插入10万条数据

  begin
for i in 100001..200000 LOOP
insert into COMPANY values(i,md5(random()::text), i1.1, md5(random()::text), i10);
end LOOP;
end;
/

再检查一次

  select relname,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_dead_tup,last_vacuum,last_analyze  from pg_catalog.pg_stat_all_tables where relname='company';

可以从观察 n_tup_ins 插入行数为20万，n_live_tup 活跃行数也是20万，n_dead_tup为0

 relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup |          last_vacuum          |         last_analyze
---------+-----------+-----------+-----------+------------+------------+-------------------------------+------------------------------
 company |    200000 |    100000 |         0 |     200000 |          0 | 2023-08-22 09:01:18.773938+08 | 2023-08-22 08:56:08.63536+08
(1 row)

检查膨胀率可以发现膨胀率从2倍左右降到了1倍左右，说明刚才update后膨胀的空间被重新利用了。

    db    | schemaname | tablename | 优化器估算表活的行数 | 估算的总page数 | 估算有效数据需要page | 表膨胀倍数 | wastedpages | wastedbytes |
wastedsize
----------+------------+-----------+----------------------+----------------+----------------------+------------+-------------+-------------+--
-------------
 postgres | public     | company   |               200000 |           3637 |                 3427 |        1.1 |         210 |     1720320 | 1
720320 bytes
(1 row)

3，测试vacuum full回收空间

删除第二次插入的10万行数据，

  DELETE FROM COMPANY WHERE ID > 100000;

检查死行数，结果为10万，即刚删除的10万行

 relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup |          last_vacuum          |         last_analyze
---------+-----------+-----------+-----------+------------+------------+-------------------------------+-------------------------------
 company |    200000 |    100000 |    100000 |     100000 |     100000 | 2023-08-22 10:03:57.744568+08 | 2023-08-22 09:07:07.720917+08

手动vacuum或等待autovacuum后

再检查死行数和表的膨胀率

发现死行数为0，膨胀率为2左右，表示死行的空间被清理，但数据库依然占用。

 relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup |          last_vacuum          |         last_analyze
---------+-----------+-----------+-----------+------------+------------+-------------------------------+-------------------------------
 company |    200000 |    100000 |    100000 |     100000 |          0 | 2023-08-22 10:16:35.916702+08 | 2023-08-22 10:16:15.598456+08
(1 row)
    db    | schemaname | tablename | 优化器估算表活的行数 | 估算的总page数 | 估算有效数据需要page | 表膨胀倍数 | wastedpages | wastedbytes | wastedsize
----------+------------+-----------+----------------------+----------------+----------------------+------------+-------------+-------------+----------------
 postgres | public     | company   |               100000 |           3637 |                1714 |        2.1 |        1923 |    15753216 | 15753216 bytes
(1 row)

手动执行 vacuum full命令

  vacuum full COMPANY;

检查表膨胀率，发现回到了1倍左右。表示空间已被释放回操作系统。

    db    | schemaname | tablename | 优化器估算表活的行数 | 估算的总page数 | 估算有效数据需要page | 表膨胀倍数 | wastedpages | wastedbytes |wastedsize
----------+------------+-----------+----------------------+----------------+----------------------+------------+-------------+-------------+--------------
 postgres | public     | company   |               100000 |           1819 |                 1714 |        1.1 |         105 |      860160 | 860160 bytes
(1 row)

实验结束后要将autovacuum重新打开。

  alter system set autovacuum=on;

参数说明

官方文档中对autovacuum和VACUUM FULL的描述：

autovacuum
回收死元组占用空间的最直接方法是手动运行VACUUM命令。此维护命令将扫描表并从表和索引中删除死元组。它通常不会将磁盘空间返回到操作系统，但它将使其可用于新行。

vacuum full
会回收空间并将其返回给操作系统，但是有许多缺点。首先，它将获取表上的独占锁，阻止所有操作(包括SELECT)。其次，它实际上创建了一个表的副本(复制了一个表)，使所需的磁盘空间加倍，同时复制表非常慢。

建议
所以非必要一般不建议执行VACUUM FULL命令，如果要执行，表无法对外提供服务，并且根据要清理的数据大小，可能持续一段时间，需要极其谨慎执行。

测试用的sql:

表膨胀检查sql

SELECT
  current_database() AS db, schemaname, tablename, reltuples::bigint AS "优化器估算表活的行数", relpages::bigint AS "估算的总page数", otta as "估算有效数据需要page",
  ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS "表膨胀倍数",
  CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
  CASE WHEN relpages < otta THEN $$0 bytes$$::text ELSE (bs*(relpages-otta))::bigint || $$ bytes$$ END AS wastedsize
FROM (
  SELECT
    nn.nspname AS schemaname,
    cc.relname AS tablename,
    COALESCE(cc.reltuples,0) AS reltuples,
    COALESCE(cc.relpages,0) AS relpages,
    COALESCE(bs,0) AS bs,
    COALESCE(CEIL((cc.reltuples*((datahdr+ma-
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) 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
     pg_class cc
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> $$information_schema$$
  LEFT JOIN
  (
    SELECT
      ma,bs,foo.nspname,foo.relname,
      (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
        ns.nspname, tbl.relname, hdr, ma, bs,
        SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
        MAX(coalesce(null_frac,0)) AS maxfracsum,
        hdr+(
          SELECT 1+count(*)/8
          FROM pg_stats s2
          WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
        ) AS nullhdr
      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,
      (
        SELECT
          (SELECT current_setting($$block_size$$)::numeric) AS bs,
            CASE WHEN SUBSTRING(SPLIT_PART(v, $$ $$, 2) FROM $$#"[0-9]+.[0-9]+#"%$$ for $$#$$)
              IN ($$8.0$$,$$8.1$$,$$8.2$$) THEN 27 ELSE 23 END AS hdr,
          CASE WHEN v ~ $$mingw32$$ OR v ~ $$64-bit$$ THEN 8 ELSE 4 END AS ma
        FROM (SELECT version() AS v) AS foo
      ) AS constants
      WHERE att.attnum > 0 AND tbl.relkind=$$r$$
      GROUP BY 1,2,3,4,5
    ) AS foo
  ) AS rs
  ON cc.relname = rs.relname AND nn.nspname = rs.nspname
  LEFT JOIN pg_index i ON indrelid = cc.oid
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml  where tablename='company' order by wastedbytes;

死行检查sql：

  select relname,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_dead_tup,last_vacuum,last_analyze  from pg_catalog.pg_stat_all_tables where relname='company';

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者