印刻数据库

外观

PostgreSQL 数据库空间监控 ​

数据库空间监控是数据库运维中的重要组成部分，用于实时了解数据库空间的使用情况，预测空间增长趋势，及时发现和解决空间相关问题。合理的空间监控可以避免因空间不足导致的服务中断，优化空间使用，降低运维成本。

空间监控概述 ​

空间监控定义 ​

空间监控是指对数据库系统中各种空间资源的使用情况进行监控和分析，包括数据库大小、表大小、索引大小、表空间大小、磁盘空间等。

空间监控的重要性 ​

  • 避免服务中断：及时发现空间不足问题，避免因空间不足导致的服务中断
  • 优化空间使用：识别空间使用不合理的对象，优化空间使用
  • 预测增长趋势：根据历史数据预测空间增长趋势，制定容量规划
  • 降低运维成本：合理规划空间，避免不必要的存储成本
  • 提高系统性能：合理的空间使用可以提高系统性能

空间监控目标 ​

  • 实时监控：实时监控数据库空间的使用情况
  • 预测趋势：预测空间增长趋势，提前规划扩容
  • 告警通知：当空间使用率超过阈值时，及时发送告警通知
  • 分析优化：分析空间使用情况，优化空间使用
  • 合规要求：满足数据保留和合规要求

空间监控指标 ​

数据库级空间指标 ​

  • 数据库总大小：包括数据文件、索引、WAL 等的总大小
  • 数据库增长率：数据库大小随时间的增长速率
  • 数据库空间使用率：数据库使用的空间占总分配空间的比例

表级空间指标 ​

  • 表大小：表数据文件的大小
  • 索引大小：表索引的大小
  • 表总大小：表数据和索引的总大小
  • 表增长率：表大小随时间的增长速率
  • 表空间使用率：表使用的空间占所在表空间的比例

索引级空间指标 ​

  • 索引大小：单个索引的大小
  • 索引使用率：索引的使用情况
  • 索引膨胀率：索引的膨胀程度

表空间级空间指标 ​

  • 表空间大小：表空间的总大小
  • 表空间使用率：表空间使用的空间占总分配空间的比例
  • 表空间增长率：表空间大小随时间的增长速率

磁盘级空间指标 ​

  • 磁盘总空间：磁盘的总容量
  • 磁盘已用空间：磁盘已使用的空间
  • 磁盘可用空间：磁盘剩余的可用空间
  • 磁盘使用率：磁盘已用空间占总空间的比例
  • 磁盘 I/O 性能：磁盘的读写速率、I/O 等待时间等

WAL 空间指标 ​

  • WAL 目录大小：WAL 文件占用的总空间
  • WAL 生成速率：单位时间内生成的 WAL 文件大小
  • WAL 归档状态：WAL 归档是否正常

空间监控方法 ​

SQL 查询方法 ​

PostgreSQL 提供了丰富的系统函数和视图，可以用于查询空间使用情况。

数据库空间查询 ​

sql
-- 查询所有数据库的大小
SELECT 
    datname, 
    pg_size_pretty(pg_database_size(datname)) AS database_size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- 查询指定数据库的大小
SELECT 
    pg_size_pretty(pg_database_size('postgres')) AS database_size;

表空间查询 ​

sql
-- 查询所有表空间的大小
SELECT 
    spcname AS tablespace_name, 
    pg_size_pretty(pg_tablespace_size(spcname)) AS tablespace_size
FROM pg_tablespace
ORDER BY pg_tablespace_size(spcname) DESC;

-- 查询指定表空间的大小
SELECT 
    pg_size_pretty(pg_tablespace_size('pg_default')) AS tablespace_size;

表和索引空间查询 ​

sql
-- 查询所有表的大小（包括索引）
SELECT 
    schemaname, 
    relname AS table_name, 
    pg_size_pretty(pg_table_size(relid)) AS table_size, 
    pg_size_pretty(pg_indexes_size(relid)) AS indexes_size, 
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- 查询指定表的大小
SELECT 
    pg_size_pretty(pg_table_size('users')) AS table_size, 
    pg_size_pretty(pg_indexes_size('users')) AS indexes_size, 
    pg_size_pretty(pg_total_relation_size('users')) AS total_size;

索引空间查询 ​

sql
-- 查询所有索引的大小
SELECT 
    schemaname, 
    tablename, 
    indexname, 
    pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS index_size
FROM pg_indexes
ORDER BY pg_indexes_size(schemaname || '.' || tablename) DESC;

-- 查询指定表的索引大小
SELECT 
    indexname, 
    pg_size_pretty(pg_relation_size(schemaname || '.' || indexname)) AS index_size
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'users'
ORDER BY pg_relation_size(schemaname || '.' || indexname) DESC;

系统命令方法 ​

可以使用系统命令监控数据库所在磁盘的空间使用情况。

Linux 系统 ​

bash
# 查看磁盘空间使用情况
df -h

# 查看指定目录的空间使用情况
du -sh /var/lib/pgsql/15/data/*

# 查看目录下各文件和子目录的大小
du -h --max-depth=1 /var/lib/pgsql/15/data/

Windows 系统 ​

cmd
:: 查看磁盘空间使用情况
dir

:: 查看指定目录的空间使用情况
dir C:\Program Files\PostgreSQL\15\data

:: 使用 PowerShell 查看磁盘空间
Get-PSDrive -PSProvider FileSystem

自动化脚本方法 ​

编写自动化脚本定期收集和分析空间使用数据。

空间监控脚本示例 ​

bash
#!/bin/bash

# PostgreSQL 空间监控脚本
# 适用于 PostgreSQL 12+

# 配置信息
PG_HOST="localhost"
PG_PORT="5432"
PG_USER="postgres"
PG_PASSWORD="your_password"
PG_DATABASE="postgres"
LOG_FILE="/var/log/pg_space_monitoring_$(date +%Y%m%d).log"

# 设置环境变量
export PGPASSWORD=$PG_PASSWORD

# 开始日志
echo "=== PostgreSQL 空间监控报告 $(date) ===" > $LOG_FILE

# 数据库大小
echo -e "\n数据库大小" >> $LOG_FILE
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DATABASE -c "
SELECT 
    datname, 
    pg_size_pretty(pg_database_size(datname)) AS database_size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
" >> $LOG_FILE

# 表空间大小
echo -e "\n表空间大小" >> $LOG_FILE
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DATABASE -c "
SELECT 
    spcname AS tablespace_name, 
    pg_size_pretty(pg_tablespace_size(spcname)) AS tablespace_size
FROM pg_tablespace
ORDER BY pg_tablespace_size(spcname) DESC;
" >> $LOG_FILE

# 前 10 大表
echo -e "\n前 10 大表" >> $LOG_FILE
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DATABASE -c "
SELECT 
    schemaname, 
    relname AS table_name, 
    pg_size_pretty(pg_table_size(relid)) AS table_size, 
    pg_size_pretty(pg_indexes_size(relid)) AS indexes_size, 
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;
" >> $LOG_FILE

# 磁盘空间使用情况
echo -e "\n磁盘空间使用情况" >> $LOG_FILE
df -h >> $LOG_FILE

# 数据目录空间使用情况
echo -e "\n数据目录空间使用情况" >> $LOG_FILE
du -h --max-depth=1 /var/lib/pgsql/15/data/ >> $LOG_FILE

# 结束日志
echo -e "\n=== 空间监控完成 $(date) ===" >> $LOG_FILE

# 发送邮件通知（可选）
# mail -s "PostgreSQL 空间监控报告 $(date +%Y%m%d)" dba@example.com < $LOG_FILE

空间监控工具 ​

PostgreSQL 内置工具 ​

psql 命令行工具 ​

bash
# 使用 psql 查看数据库大小
psql -h localhost -U postgres -d postgres -c "SELECT pg_size_pretty(pg_database_size('postgres'));"

# 使用 psql 查看表大小
psql -h localhost -U postgres -d postgres -c "\dt+"

# 使用 psql 查看索引大小
psql -h localhost -U postgres -d postgres -c "\di+"

pgstattuple 扩展 ​

sql
-- 安装扩展
CREATE EXTENSION pgstattuple;

-- 检查表的详细空间使用情况
SELECT 
    table_len, 
    tuple_len, 
    tuple_count, 
    tuple_percent, 
    dead_tuple_len, 
    dead_tuple_count, 
    dead_tuple_percent, 
    free_space, 
    free_percent
FROM pgstattuple('users');

-- 检查所有表的空间使用情况
SELECT 
    schemaname, 
    relname, 
    pg_size_pretty(table_len) AS table_size, 
    pg_size_pretty(dead_tuple_len) AS dead_tuple_size, 
    pg_size_pretty(free_space) AS free_space,
    dead_tuple_percent, 
    free_percent
FROM pg_stat_user_tables
JOIN LATERAL pgstattuple(schemaname || '.' || relname) ON true
ORDER BY dead_tuple_percent DESC;

pg_freespacemap 扩展 ​

sql
-- 安装扩展
CREATE EXTENSION pg_freespacemap;

-- 检查指定表的空闲空间
SELECT 
    blkno, 
    avail
FROM pg_freespace('users')
ORDER BY blkno;

-- 计算表的空闲空间总量
SELECT 
    sum(avail) AS total_free_space
FROM pg_freespace('users');

监控系统 ​

Prometheus + Grafana ​

  • 收集和存储时间序列数据
  • 提供丰富的可视化图表
  • 支持自定义告警规则
  • 可以预测空间增长趋势

Grafana 空间监控面板示例：

  • 数据库大小趋势图
  • 表空间使用率饼图
  • 前 10 大表排行榜
  • 空间使用率告警

Zabbix ​

  • 全面的监控功能
  • 支持自动发现数据库对象
  • 丰富的告警机制
  • 支持自定义监控项和触发器

Nagios ​

  • 成熟的监控系统
  • 支持 PostgreSQL 插件
  • 强大的告警功能
  • 支持自定义监控脚本

第三方工具 ​

pgAdmin ​

  • 图形化管理界面
  • 提供空间使用情况的可视化视图
  • 支持导出空间使用报告

PgHero ​

  • 实时监控和分析工具
  • 提供空间使用情况的可视化视图
  • 支持空间增长趋势预测
  • 提供空间优化建议

Datadog ​

  • 云原生监控平台
  • 支持 PostgreSQL 空间监控
  • 提供智能告警和预测功能
  • 支持多维度空间分析

空间监控流程 ​

监控计划制定 ​

制定详细的空间监控计划，包括：

  • 监控指标：确定需要监控的空间指标
  • 监控频率：根据业务需求和空间增长速率确定监控频率
  • 监控工具：选择合适的监控工具
  • 告警规则：制定空间告警规则
  • 责任分工：明确空间监控的责任人

监控实施 ​

按照监控计划实施空间监控：

  • 部署监控工具
  • 配置监控指标和告警规则
  • 定期收集和分析空间数据

告警处理 ​

当空间使用率超过阈值时，及时处理告警：

  • 确认告警真实性
  • 分析空间增长原因
  • 采取相应的处理措施
  • 记录处理过程和结果

优化调整 ​

根据空间监控结果，优化空间使用：

  • 清理无效数据
  • 优化表结构和索引
  • 调整表空间配置
  • 制定容量规划

持续改进 ​

持续改进空间监控流程：

  • 定期评估监控计划的有效性
  • 根据实际情况调整监控指标和告警规则
  • 优化监控工具和流程
  • 总结经验教训，持续改进

空间告警机制 ​

告警阈值设置 ​

根据业务需求和空间增长趋势，设置合理的告警阈值：

  • 警告阈值：空间使用率达到 70% 时，发送警告告警
  • 严重阈值：空间使用率达到 85% 时，发送严重告警
  • 紧急阈值：空间使用率达到 95% 时，发送紧急告警

告警方式 ​

选择合适的告警方式：

  • 邮件告警：发送邮件通知相关人员
  • 短信告警：发送短信通知相关人员
  • 即时通讯告警：通过 Slack、微信等即时通讯工具发送告警
  • 电话告警：对于紧急告警，通过电话通知相关人员

告警处理流程 ​

制定详细的告警处理流程：

  • 告警接收：相关人员接收告警通知
  • 告警确认：确认告警的真实性和严重程度
  • 问题分析：分析空间增长的原因
  • 采取措施：根据分析结果，采取相应的处理措施
  • 验证结果：验证处理措施的有效性
  • 记录归档：记录告警处理过程和结果

常见空间问题处理 ​

数据库空间不足 ​

症状：数据库无法写入数据，出现 "no space left on device" 错误

处理方法：

  • 紧急清理无效数据
  • 扩展磁盘空间
  • 迁移部分数据到其他存储
  • 优化空间使用

表空间空间不足 ​

症状：特定表空间无法写入数据

处理方法：

  • 清理表空间中的无效数据
  • 扩展表空间
  • 将部分表迁移到其他表空间
  • 优化表空间使用

WAL 目录空间不足 ​

症状：WAL 目录空间不足，导致数据库无法写入 WAL 日志

处理方法：

  • 检查 WAL 归档是否正常
  • 清理过期的 WAL 文件
  • 扩展 WAL 目录空间
  • 调整 WAL 相关参数

表空间使用率不均衡 ​

症状：部分表空间使用率过高，而其他表空间使用率较低

处理方法：

  • 将表从高使用率表空间迁移到低使用率表空间
  • 调整表空间配置
  • 优化表结构和索引

表和索引膨胀 ​

症状：表或索引的实际大小远大于数据大小

处理方法：

  • 执行 VACUUM FULL 或 REINDEX 操作
  • 调整 autovacuum 相关参数
  • 优化表结构和索引

版本差异注意事项 ​

版本差异说明
PostgreSQL 9.x支持基本的空间监控功能，但缺少一些高级特性
PostgreSQL 10+增强了表空间管理功能，支持更多的空间监控视图
PostgreSQL 11+引入了 pg_stat_wal 视图，支持 WAL 相关统计信息
PostgreSQL 12+增强了 pgstattuple 扩展，支持更多的空间统计信息
PostgreSQL 13+引入了 wal_compression 参数，影响 WAL 空间使用
PostgreSQL 14+增强了 pg_freespacemap 扩展，支持更高效的空闲空间管理
PostgreSQL 15+支持 CREATE TABLE ... LIKE ... INCLUDING ALL 语句，影响表空间使用

空间监控最佳实践 ​

制定合理的监控计划 ​

  • 根据业务需求和空间增长速率，确定监控频率
  • 选择合适的监控工具和指标
  • 制定详细的告警规则和处理流程

实时监控与定期分析相结合 ​

  • 实时监控空间使用情况，及时发现问题
  • 定期分析空间增长趋势，制定容量规划
  • 结合历史数据，预测未来空间需求

优化空间使用 ​

  • 定期清理无效数据
  • 优化表结构和索引
  • 合理使用表空间
  • 采用压缩技术减少空间使用

自动化监控和告警 ​

  • 实现空间监控的自动化
  • 设置合理的告警阈值和告警方式
  • 自动化处理一些常见的空间问题

文档化和规范化 ​

  • 详细记录空间监控的流程和结果
  • 制定空间管理的规范和标准
  • 定期培训相关人员，提高空间管理水平

容量规划 ​

  • 根据空间增长趋势，制定合理的容量规划
  • 提前规划扩容方案，避免紧急扩容
  • 考虑业务增长的季节性和突发性

空间监控示例 ​

表空间使用率监控 ​

需求：监控表空间使用率，当使用率超过 80% 时发送告警

实施：

  1. 创建监控脚本：

    bash
    #!/bin/bash

# 表空间使用率监控脚本
PG_HOST="localhost"
PG_PORT="5432"
PG_USER="postgres"
PG_PASSWORD="your_password"
PG_DATABASE="postgres"

export PGPASSWORD=$PG_PASSWORD

# 查询表空间使用率
TABLESPACE_USAGE=$(psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DATABASE -t -A -c "
SELECT 
    spcname,
    pg_size_pretty(pg_tablespace_size(spcname)) AS size,
    CASE 
        WHEN pg_tablespace_size(spcname) = 0 THEN 0
        ELSE pg_tablespace_size(spcname)::float / (SELECT pg_tablespace_size('pg_default') FROM pg_tablespace WHERE spcname = 'pg_default') * 100
    END AS usage_percent
FROM pg_tablespace
WHERE spcname NOT IN ('pg_global', 'pg_default');
")

# 检查使用率是否超过阈值
while IFS="|" read -r spcname size usage_percent; do
    if (( $(echo "$usage_percent > 80" | bc -l) )); then
        echo "WARNING: Tablespace $spcname usage is $usage_percent%, which exceeds the threshold of 80%"
        # 发送告警
        # mail -s "WARNING: Tablespace $spcname usage is high" dba@example.com <<< "Tablespace $spcname usage is $usage_percent%, which exceeds the threshold of 80%"
    fi
done <<< "$TABLESPACE_USAGE"

  2. 添加到 crontab 定期执行：

    bash
    # 每小时执行一次表空间使用率监控
0 * * * * /path/to/tablespace_usage_monitor.sh

数据库增长趋势分析 ​

需求：分析数据库增长趋势，预测未来 6 个月的数据库大小

实施：

  1. 收集历史数据：

    sql
    -- 创建空间监控历史表
CREATE TABLE pg_space_history (
    id SERIAL PRIMARY KEY,
    collection_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    database_name VARCHAR(255),
    database_size BIGINT,
    tablespace_name VARCHAR(255),
    tablespace_size BIGINT
);

-- 插入当前空间数据
INSERT INTO pg_space_history (database_name, database_size, tablespace_name, tablespace_size)
SELECT 
    datname, 
    pg_database_size(datname),
    spcname,
    pg_tablespace_size(spcname)
FROM pg_database, pg_tablespace
WHERE datname = 'postgres' AND spcname NOT IN ('pg_global');

  2. 使用线性回归预测未来增长：

    sql
    -- 使用线性回归预测未来 6 个月的数据库大小
-- 假设每月增长 10%
SELECT 
    database_name,
    pg_size_pretty(database_size) AS current_size,
    pg_size_pretty(database_size * 1.1^6) AS size_after_6_months
FROM pg_space_history
WHERE collection_time = (SELECT MAX(collection_time) FROM pg_space_history)
AND database_name = 'postgres';

总结 ​

空间监控是数据库运维中的重要组成部分，合理的空间监控可以避免因空间不足导致的服务中断，优化空间使用，降低运维成本。通过制定合理的监控计划、选择合适的监控工具、设置合理的告警规则和处理流程，可以有效地监控和管理 PostgreSQL 数据库空间，确保数据库系统的稳定运行。

DBA 团队应该重视空间监控工作，不断优化空间监控流程和方法，提高空间管理水平，为业务发展提供可靠的数据库支持。