作者：Digital Observer（施嘉伟）
Oracle  ACE Pro: Database
PostgreSQL  ACE Partner
11年数据库行业经验，现主要从事数据库服务工作
拥有Oracle  OCM、DB2 10.1 Fundamentals、MySQL 8.0 OCP、WebLogic 12c OCA、KCP、PCTP、PCSD、 PGCM、OCI、PolarDB技术专家、达梦师资认证、数据安全咨询高级等认证
ITPUB认证专家、PolarDB开源社区技术顾问、HaloDB技术顾问、TiDB社区技术布道师、青学会MOP技术社区专家顾问、国内某高校企业实践指导教师
公众号：Digital Observer；CSDN：施嘉伟；ITPUB：sjw1933；墨天轮：Digital Observer；PGFans：施嘉伟。

引言

随着数据库的日常运维和管理任务日益复杂，确保高效、稳定的数据库运行成为技术团队的重要目标。为了解决数据库运行中的常见故障并进行有效诊断，本文提供了一套 Kingbase 故障诊断标准化方案，涵盖SQL诊断、系统监控、表膨胀处理、统计信息管理等方面的标准操作流程与SQL语句。

本方案的目标是帮助数据库管理员（DBA）在日常运维过程中，通过标准化的诊断手段及时发现并解决潜在的问题，从而提高数据库的可靠性与性能。本文内容适用于Kingbase数据库的运维人员，并能够帮助团队在遇到数据库故障时快速定位问题并进行修复。

SQL 诊断指标

1.1 会话执行状态

SQL：

SELECT * FROM pg_stat_activity;

解释与依据：
pg_stat_activity 视图提供了当前数据库中所有活动会话的信息，返回当前连接的进程信息，包括会话的状态、执行的查询、启动时间等。用于诊断系统是否有活跃的会话，特别是用于识别阻塞、超时或长时间运行的查询。

1.2 Top SQL 分析

SQL（平均耗时前 5 的 SQL）：

SELECT userid::regrole, dbid, query FROM sys_stat_statements 
ORDER BY mean_exec_time DESC LIMIT 5;

解释与依据：
sys_stat_statements 视图记录了每个 SQL 语句的统计信息，包括平均执行时间（ mean_exec_time）。这个查询可以帮助识别系统中执行时间最长的 SQL 语句，特别适用于找出性能瓶颈。

SQL（总耗时前 5 的 SQL）：

SELECT userid::regrole, dbid, query FROM sys_stat_statements 
ORDER BY total_exec_time DESC LIMIT 5;

解释与依据：
查询  sys_stat_statements 视图中的总执行时间  total_exec_time，用于识别占用资源最多的 SQL 语句，帮助发现执行频繁且消耗大量系统资源的语句。

1.3 日志定位

解释与依据：
通过检查  pg_log 目录中的日志文件，可以找到系统发生故障的详细记录。此目录下的 CSV 格式日志记录了各种系统级的错误、警告、查询和连接等信息，是排查问题的重要依据。

系统与实例监控指标

2.1 数据库进程状态

SQL：

ps aux | grep kingbase

解释与依据：
使用  ps aux 命令可以查看操作系统中与 Kingbase 数据库相关的所有进程。 grep kingbase 用于筛选出与 Kingbase 相关的进程，帮助检查数据库服务是否正常运行。

2.2 数据库实例状态

SQL：

ksql -p 54321 -U system -d test -c "SELECT 1;"

解释与依据：
ksql 是 Kingbase 提供的命令行工具，用于执行 SQL 查询。此命令用于测试数据库实例的连通性，确保数据库实例在正常响应查询。

2.3 主备库角色判断

SQL：

SELECT pg_is_in_recovery(); -- f 表示主库，t 表示备库

解释与依据：
pg_is_in_recovery() 函数用于检查当前数据库是否处于恢复模式（即备库）。当数据库处于主库时，该函数返回  false（f），当处于备库时返回  true（t）。

2.4 主库检查备库同步情况

SQL：

SELECT client_addr, 
       pg_wal_lsn_diff(sent_lsn, write_lsn) AS sent_lag,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS apply_lag,
       state, sync_state 
FROM pg_stat_replication;

解释与依据：
pg_stat_replication 视图提供了主库和备库之间的同步状态。通过检查  sent_lsn（已发送的日志序列号）和  replay_lsn（已重放的日志序列号），可以诊断主备库之间的延迟。

2.5 磁盘容量监控

SQL：

df -h

解释与依据：
df -h 命令用于查看文件系统的磁盘使用情况。它显示各个挂载点的磁盘总量、已用量和剩余量，用于监控磁盘容量是否正常。

2.6 当前连接数

SQL：

SELECT datname, usename, COUNT(*) AS connection_countFROM pg_stat_activity 
WHERE usename IS NOT NULL GROUP BY datname, usename;

解释与依据：
此查询返回当前数据库连接的统计信息。通过查看不同数据库和用户的连接数，管理员可以识别数据库连接是否过多或是否有异常连接。

2.7 数据库归档状态

SQL：

SELECT * FROM pg_stat_archiver;

解释与依据：
pg_stat_archiver 视图提供了数据库归档的相关信息，如归档是否成功、归档日志是否被写入等。通过检查归档状态，可以确保数据库日志的完整性和归档的正常进行。

2.8 长事务检测

SQL：

SELECT * 
FROM pg_stat_activity 
WHERE xact_start IS NOT NULL 
  AND xact_start + INTERVAL '1 hour' < NOW();

解释与依据：
此查询用于检查运行超过 1 小时的事务。长时间未提交的事务可能会导致事务锁定或资源浪费，及时发现有助于优化事务管理。

2.9 SQL 锁等待分析

SQL：

SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.query AS blocked_statement,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.query AS blocking_statementFROM pg_catalog.pg_locks blocked_locksJOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pidJOIN pg_catalog.pg_locks blocking_locks 
  ON <同类型匹配条件>JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pidWHERE NOT blocked_locks.granted;

解释与依据：
pg_locks 和  pg_stat_activity 视图帮助诊断锁等待问题。查询锁的相关信息，找出哪些事务处于阻塞状态并且被哪个事务阻塞。通过分析锁竞争情况，能够找到性能瓶颈。

2.10 数据库老化检查

SQL：

SELECT datname, age(datfrozenxid) AS age 
FROM pg_database 
WHERE datname NOT IN ('template0','template1') 
ORDER BY age DESC;

解释与依据：
此查询用于检查数据库的年龄。 datfrozenxid 表示数据库的事务 ID 冻结时间。长时间没有进行 VACUUM 或清理的数据库可能会遭遇性能下降，检查其老化情况以便及时维护。

2.11 表级别老化检查

SQL：

SELECT c.oid::regclass AS relname,       GREATEST(age(c.relfrozenxid), age(t.relfrozenxid)) AS ageFROM pg_class cLEFT JOIN pg_class t ON c.reltoastrelid = t.oidWHERE c.relkind IN ('r', 'm')  AND GREATEST(age(c.relfrozenxid), age(t.relfrozenxid)) > 500000ORDER BY age DESC;

解释与依据：
该查询用于检查表的老化情况。 relfrozenxid 表示表的事务 ID 冻结时间。如果表或 TOAST 表长时间没有进行 VACUUM 操作，表的年龄将不断增大，可能影响性能。

表膨胀诊断与处理

3.1 表膨胀率计算

SQL：

SELECT relname,       COALESCE(ROUND(n_dead_tup * 100.0 / 
               NULLIF(n_live_tup + n_dead_tup, 0), 2), 0.00) AS ratioFROM pg_stat_all_tablesWHERE n_dead_tup >= 10000 
  AND COALESCE(ROUND(n_dead_tup * 100.0 / 
               NULLIF(n_live_tup + n_dead_tup, 0), 2), 0.00) > 50ORDER BY ratio DESC;

解释与依据：
此查询用于检测表膨胀率。通过比较已删除的元组（ n_dead_tup）与总元组数的比例，判断表是否有过多的死元组，可能导致表膨胀，影响性能。

3.2 手动清理

SQL：

VACUUM FULL 表名;

解释与依据：
VACUUM FULL 用于回收数据库中删除的数据并释放磁盘空间。它会重建表并清理死元组，适用于表膨胀较严重的情况，但会锁定表，影响并发性。

统计信息管理

4.1 手动统计信息收集

SQL：

ANALYZE 表名;

解释与依据：
ANALYZE 命令用于收集表的统计信息，供查询优化器使用。定期收集统计信息可以帮助优化器生成更优的查询执行计划，提高查询性能。

附录：运维建议与优化措施

• 定期巡检指标建议：

  • 每日检查： 连接数、锁等待、慢查询、磁盘使用率
  • 每周检查： 表膨胀、事务老化、备库延迟
  • 每月检查： 表统计信息完整性、数据库老化情况、主备同步异常

• 建议工具和技术：

  • 监控工具： Prometheus + Grafana + alertmanager 集成用于监控、告警和可视化
  • 优化建议： 自动化故障诊断、容错设计、合理的索引策略

结语

本方案提供了一套针对Kingbase数据库的故障诊断流程，帮助运维团队及时发现和解决常见问题。通过标准化的操作和工具使用，能够有效提高数据库的可靠性、稳定性和性能。我们鼓励运维团队定期执行这些诊断操作，并根据实际情况不断优化操作流程。