印刻数据库

外观

KingBaseES 数据库膨胀分析 ​

数据库膨胀是指数据库表或索引占用的磁盘空间远大于实际存储数据所需的空间。膨胀会导致磁盘空间浪费、查询性能下降、备份时间延长等问题。深入理解和分析数据库膨胀是数据库运维的重要技能。

数据库膨胀的基本概念 ​

什么是数据库膨胀？ ​

数据库膨胀是指数据库表或索引中存在大量未使用的空间，这些空间通常是由于删除或更新操作产生的。在 KingBaseES 中，膨胀主要发生在表和索引上。

膨胀的类型 ​

  • 表膨胀：表中存在大量已删除但未回收的行
  • 索引膨胀：索引中存在大量无效的索引项
  • 系统表膨胀：系统表（如 pg_class、pg_index 等）中的膨胀

膨胀的影响 ​

  • 磁盘空间浪费：占用过多的磁盘空间
  • 查询性能下降：全表扫描和索引扫描需要读取更多的数据页
  • 备份时间延长：备份需要处理更多的数据
  • 恢复时间延长：恢复需要处理更多的数据
  • VACUUM 时间延长：VACUUM 需要处理更多的膨胀数据
  • 锁竞争加剧：VACUUM 操作需要获取锁，可能导致锁竞争

膨胀的原因 ​

1. MVCC 机制 ​

KingBaseES 使用 MVCC（多版本并发控制）机制，当删除或更新行时，旧版本的行不会立即被删除，而是被标记为无效。这些无效行需要通过 VACUUM 操作进行回收。

2. 频繁的删除和更新操作 ​

  • 大量删除操作：删除大量行后，表中会留下大量无效空间
  • 频繁更新操作：更新操作会创建新行并标记旧行为无效，导致表膨胀
  • 小批量频繁操作：频繁的小批量删除或更新操作可能导致膨胀加剧

3. 不合适的 VACUUM 配置 ​

  • VACUUM 不及时：autovacuum 配置不当，导致 VACUUM 不及时
  • VACUUM 配置不合理：如 autovacuum_vacuum_scale_factor、autovacuum_analyze_scale_factor 等参数设置不当
  • VACUUM 被中断：VACUUM 操作被中断，导致部分空间未被回收

4. 长事务 ​

长事务会持有旧的快照，导致 VACUUM 无法回收这些快照之前的无效行。

5. 索引设计不合理 ​

  • 过多的索引：每个索引都可能发生膨胀
  • 不合适的索引类型：某些索引类型更容易发生膨胀
  • 频繁更新索引列：频繁更新索引列会导致索引膨胀

膨胀监控 ​

动态性能视图 ​

查看表膨胀情况 ​

sql
-- 使用内置函数查看表膨胀情况
SELECT 
    schemaname,
    tablename,
    ROUND(pg_table_size(relid) / 1024 / 1024, 2) AS table_size_mb,
    ROUND(pg_indexes_size(relid) / 1024 / 1024, 2) AS indexes_size_mb,
    ROUND(pg_total_relation_size(relid) / 1024 / 1024, 2) AS total_size_mb,
    ROUND((pg_table_size(relid) - pg_relation_size(relid)) / 1024 / 1024, 2) AS toast_size_mb
FROM 
    pg_tables
WHERE 
    schemaname NOT IN ('sys_catalog', 'information_schema', 'kingbase')
ORDER BY 
    pg_total_relation_size(relid) DESC;

查看表膨胀率 ​

sql
-- 使用 pgstattuple 扩展查看表膨胀情况
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT 
    schemaname,
    tablename,
    ROUND(table_len / 1024 / 1024, 2) AS table_len_mb,
    ROUND(tuple_len / 1024 / 1024, 2) AS tuple_len_mb,
    tuple_count,
    dead_tuple_count,
    ROUND(dead_tuple_len / 1024 / 1024, 2) AS dead_tuple_len_mb,
    ROUND(dead_tuple_count::numeric / tuple_count::numeric * 100, 2) AS dead_tuple_percent,
    ROUND((table_len - tuple_len - dead_tuple_len) / 1024 / 1024, 2) AS free_space_mb,
    ROUND((table_len - tuple_len - dead_tuple_len)::numeric / table_len::numeric * 100, 2) AS free_space_percent
FROM 
    (
        SELECT 
            schemaname,
            tablename,
            (pgstattuple(schemaname || '.' || tablename)).*
        FROM 
            pg_tables
        WHERE 
            schemaname NOT IN ('sys_catalog', 'information_schema', 'kingbase')
    ) AS t
ORDER BY 
    dead_tuple_percent DESC;

查看索引膨胀情况 ​

sql
-- 使用 pgstatindex 扩展查看索引膨胀情况
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT 
    schemaname,
    tablename,
    indexname,
    ROUND(stat_size / 1024 / 1024, 2) AS stat_size_mb,
    ROUND(index_rel_size / 1024 / 1024, 2) AS index_rel_size_mb,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    ROUND((stat_size - index_rel_size)::numeric / stat_size::numeric * 100, 2) AS bloat_percent
FROM 
    (
        SELECT 
            n.nspname AS schemaname,
            c.relname AS tablename,
            i.relname AS indexname,
            (pgstatindex(i.oid)).*,
            pg_index_size(i.oid) AS index_rel_size,
            idx_scan,
            idx_tup_read,
            idx_tup_fetch
        FROM 
            pg_index idx
        JOIN 
            pg_class c ON idx.indrelid = c.oid
        JOIN 
            pg_class i ON idx.indexrelid = i.oid
        JOIN 
            pg_namespace n ON c.relnamespace = n.oid
        JOIN 
            pg_stat_user_indexes psi ON i.oid = psi.indexrelid
        WHERE 
            n.nspname NOT IN ('sys_catalog', 'information_schema', 'kingbase')
    ) AS t
ORDER BY 
    bloat_percent DESC;

系统命令 ​

bash
# 查看数据库目录大小
du -sh /path/to/kingbase/data

# 查看表空间大小
du -sh /path/to/tablespace

# 查看具体表的大小
du -sh /path/to/kingbase/data/base/16384/16385

膨胀分析工具 ​

1. pgstattuple 扩展 ​

pgstattuple 扩展用于查看表和索引的详细统计信息，包括死元组数量、死元组大小、空闲空间等。

sql
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- 查看表的统计信息
SELECT * FROM pgstattuple('table_name');

-- 查看索引的统计信息
SELECT * FROM pgstatindex('index_name');

2. pg_freespacemap 扩展 ​

pg_freespacemap 扩展用于查看表的空闲空间分布。

sql
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_freespacemap;

-- 查看表的空闲空间分布
SELECT * FROM pg_freespace('table_name');

3. pg_bloat_check 工具 ​

pg_bloat_check 是一个第三方工具，用于检查表和索引的膨胀情况。

bash
# 下载并安装 pg_bloat_check
git clone https://github.com/keithf4/pg_bloat_check.git
cd pg_bloat_check

# 运行 pg_bloat_check
./pg_bloat_check -d database_name -h localhost -p 5432 -U username

4. KingBaseES 内置工具 ​

kbfree ​

kbfree 是 KingBaseES 内置的工具，用于查看表和索引的空闲空间。

bash
# 使用 kbfree 查看表的空闲空间
kbfree -d database_name -t table_name

# 使用 kbfree 查看索引的空闲空间
kbfree -d database_name -i index_name

kbvacuum ​

kbvacuum 是 KingBaseES 内置的工具，用于执行 VACUUM 操作。

bash
# 执行 VACUUM 操作
kbvacuum -d database_name -t table_name

# 执行 VACUUM FULL 操作
kbvacuum -d database_name -t table_name -f

膨胀优化策略 ​

1. 执行 VACUUM 操作 ​

VACUUM 是回收膨胀空间的主要方法，包括普通 VACUUM 和 VACUUM FULL。

普通 VACUUM ​

普通 VACUUM 用于回收无效行，但不压缩表。它可以在不阻塞写操作的情况下运行。

sql
-- 对单个表执行 VACUUM
VACUUM table_name;

-- 对单个表执行 VACUUM ANALYZE
VACUUM ANALYZE table_name;

-- 对所有表执行 VACUUM
VACUUM;

-- 对所有表执行 VACUUM ANALYZE
VACUUM ANALYZE;

VACUUM FULL ​

VACUUM FULL 用于回收无效行并压缩表，它会锁定表，阻塞所有写操作。

sql
-- 对单个表执行 VACUUM FULL
VACUUM FULL table_name;

-- 对单个表执行 VACUUM FULL ANALYZE
VACUUM FULL ANALYZE table_name;

VACUUM FREEZE ​

VACUUM FREEZE 用于冻结表中的元组，防止事务 ID 回卷。

sql
-- 对单个表执行 VACUUM FREEZE
VACUUM FREEZE table_name;

2. 重建表和索引 ​

对于严重膨胀的表和索引，可以通过重建来回收膨胀空间。

重建表 ​

sql
-- 使用 CREATE TABLE AS 重建表
CREATE TABLE new_table AS SELECT * FROM old_table;
DROP TABLE old_table;
ALTER TABLE new_table RENAME TO old_table;

-- 使用 REINDEX 重建表的所有索引
REINDEX TABLE old_table;

-- 使用 CLUSTER 重建表
CLUSTER old_table USING index_name;

重建索引 ​

sql
-- 重建单个索引
REINDEX INDEX index_name;

-- 重建表的所有索引
REINDEX TABLE table_name;

-- 重建数据库的所有索引
REINDEX DATABASE database_name;

3. 调整 autovacuum 参数 ​

通过调整 autovacuum 参数，可以优化 VACUUM 操作，减少膨胀的发生。

sql
-- 调整全局 autovacuum 参数
SET GLOBAL autovacuum = ON;
SET GLOBAL autovacuum_vacuum_scale_factor = 0.05;
SET GLOBAL autovacuum_analyze_scale_factor = 0.02;
SET GLOBAL autovacuum_max_workers = 4;
SET GLOBAL autovacuum_naptime = '1min';

-- 调整表级 autovacuum 参数
ALTER TABLE table_name SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE table_name SET (autovacuum_analyze_scale_factor = 0.02);
ALTER TABLE table_name SET (autovacuum_vacuum_threshold = 50);
ALTER TABLE table_name SET (autovacuum_analyze_threshold = 50);

4. 优化删除和更新操作 ​

  • 使用批量删除：批量删除比频繁的小批量删除更高效
  • 使用 TRUNCATE 替代 DELETE：TRUNCATE 可以快速删除表中的所有行，不会产生膨胀
  • 优化更新操作：尽量减少更新操作，或使用更高效的更新方式

5. 限制长事务 ​

长事务会持有旧的快照，导致 VACUUM 无法回收这些快照之前的无效行。因此，应该尽量避免长事务。

sql
-- 查看长事务
SELECT pid, usename, application_name, client_addr, backend_start, xact_start, query_start, state, query
FROM sys_stat_activity
WHERE state = 'active' AND xact_start < NOW() - INTERVAL '1 hour';

-- 终止长事务
SELECT pg_terminate_backend(pid);

膨胀预防措施 ​

1. 合理设计表结构 ​

  • 选择合适的数据类型：使用合适的数据类型可以减少数据占用的空间
  • 合理设计索引：避免过多的索引，只创建必要的索引
  • 使用分区表：对大表使用分区表，减少单表的膨胀

2. 优化 SQL 语句 ​

  • 避免全表扫描：全表扫描会导致更多的 I/O 操作
  • 优化查询条件：使用合适的查询条件，减少扫描的数据量
  • 使用 LIMIT：限制返回的行数，减少数据传输

3. 定期监控和维护 ​

  • 定期监控膨胀情况：使用动态性能视图和工具定期监控膨胀情况
  • 定期执行 VACUUM：定期执行 VACUUM 操作，回收膨胀空间
  • 定期重建表和索引：对于严重膨胀的表和索引，定期重建

4. 合理配置 autovacuum ​

  • 启用 autovacuum：确保 autovacuum 处于启用状态
  • 调整 autovacuum 参数：根据业务需求调整 autovacuum 参数
  • 监控 autovacuum 日志：定期查看 autovacuum 日志，了解 autovacuum 的运行情况

版本差异 (V8 R6 vs V8 R7) ​

V8 R6 ​

  • 膨胀监控主要依赖动态性能视图和扩展
  • autovacuum 配置相对简单
  • 缺少高级的膨胀分析工具
  • VACUUM FULL 操作需要持有表级排他锁，影响并发性能

V8 R7 ​

  • 增强了动态性能视图，提供更详细的膨胀信息
  • 优化了 autovacuum 机制，提高了 VACUUM 效率
  • 新增了 kbfree 等内置工具，用于查看膨胀情况
  • 支持并行 VACUUM，提高了 VACUUM 性能
  • 优化了 VACUUM FULL 操作，减少了锁持有时间

最佳实践 ​

  1. 定期监控膨胀情况：每周或每月监控一次膨胀情况，及时发现并处理膨胀问题
  2. 合理配置 autovacuum：根据业务需求调整 autovacuum 参数，确保 VACUUM 及时执行
  3. 避免长事务：尽量避免长事务，或限制长事务的运行时间
  4. 使用批量操作：对于大量删除或更新操作，使用批量操作
  5. 定期执行 VACUUM ANALYZE：定期执行 VACUUM ANALYZE，回收膨胀空间并更新统计信息
  6. 重建严重膨胀的表和索引：对于膨胀率超过 50% 的表和索引，考虑重建
  7. 使用分区表：对大表使用分区表，减少单表的膨胀
  8. 监控 autovacuum 日志：定期查看 autovacuum 日志，了解 autovacuum 的运行情况
  9. 合理设计索引：避免过多的索引，只创建必要的索引
  10. 定期进行性能测试：定期进行性能测试，评估膨胀对性能的影响

常见问题 (FAQ) ​

Q1: 如何判断表是否膨胀？ ​

A: 可以通过以下方法判断表是否膨胀：

  • 使用 pgstattuple 扩展查看表的死元组百分比，死元组百分比超过 20% 通常认为存在膨胀
  • 比较表的实际数据大小和磁盘占用大小，磁盘占用大小远大于实际数据大小则存在膨胀
  • 查看表的扫描时间，扫描时间明显变长可能是由于膨胀导致的

Q2: VACUUM 和 VACUUM FULL 有什么区别？ ​

A: VACUUM 和 VACUUM FULL 的主要区别：

  • VACUUM：回收死元组但不压缩表，不会阻塞写操作
  • VACUUM FULL：回收死元组并压缩表，会阻塞写操作

Q3: 什么时候使用 VACUUM FULL？ ​

A: 建议在以下情况下使用 VACUUM FULL：

  • 表的膨胀率超过 50%
  • 磁盘空间不足
  • 表的性能明显下降
  • 业务低峰期

Q4: 如何优化 autovacuum 配置？ ​

A: 优化 autovacuum 配置的方法：

  • 调整 autovacuum_vacuum_scale_factor 和 autovacuum_analyze_scale_factor 参数，减少触发 VACUUM 的阈值
  • 增加 autovacuum_max_workers 参数，增加 VACUUM 工作线程数
  • 调整 autovacuum_naptime 参数，减少 VACUUM 的间隔时间
  • 为频繁更新的表设置更激进的 autovacuum 参数

Q5: 长事务对膨胀有什么影响？ ​

A: 长事务会持有旧的快照，导致 VACUUM 无法回收这些快照之前的死元组，从而导致膨胀加剧。

Q6: 如何监控 autovacuum 的运行情况？ ​

A: 可以通过以下方法监控 autovacuum 的运行情况：

  • 查看 autovacuum 日志
  • 使用 sys_stat_activity 视图查看 autovacuum 进程
  • 使用 sys_autovacuum 视图查看 autovacuum 的统计信息

Q7: 重建表和索引会影响业务吗？ ​

A: 重建表和索引会影响业务，因为：

  • 重建表需要锁表，阻塞写操作
  • 重建索引会导致索引不可用，直到重建完成 建议在业务低峰期执行重建操作。

总结 ​

数据库膨胀是数据库运维中常见的问题，它会导致磁盘空间浪费、查询性能下降等问题。通过定期监控膨胀情况、合理配置 autovacuum、优化 SQL 语句、定期执行 VACUUM 和重建操作，可以有效地减少和预防膨胀。

DBA 应该将膨胀分析作为日常运维工作的重要组成部分，持续关注和优化数据库膨胀情况，确保数据库的高效、稳定运行。