“G”术时刻 | GBase数据库巡检指南，全方位护航数据安全

原创 GBASE数据库 2025-04-17

116

在数字经济时代，数据库作为管理企业数据资产的关键设施，其稳定性与安全性直接关系到企业信息系统的稳定与业务开展。随着数据量激增和业务场景复杂化，数据库系统往往面临高并发负载、资源瓶颈等挑战，而定期开展系统性数据库巡检能够有效识别性能隐患、优化资源配置、预防数据风险，为业务系统提供"全生命周期"的健康保障。

本文将以GBase数据库为例，介绍如何通过一系列巡检指标查询和SQL 查询，进行全面的健康检查。

巡检目标与用户权限

数据库巡检的目的是及时发现潜在问题，优化性能，确保数据的完整性和安全性。为了执行这些巡检任务，我们需要创建一个具备足够权限的数据库用户。以下是一个示例，创建一个名为 `db_inspector` 的用户，赋予其必要的权限：

CREATE USER db_inspector WITH SYSADMIN MONADMIN PASSWORD 'gbase;123';

巡检指标与 SQL 查询

1、表空间检查

表空间是 GBase8c中用于存储数据文件的逻辑区域。通过检查表空间的使用情况，可以提前发现存储空间不足的问题。

SELECT 
   to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "巡检时间",
   spcname AS "Name(名称)",
   pg_catalog.pg_get_userbyid(spcowner) AS "Owner(拥有者)",
   pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS "Size(表空间大小)"
FROM pg_catalog.pg_tablespace
ORDER BY 1;

需要关注的指标：表空间大小

查看表空间大小是否接近存储上限，需提前规划扩容。

2、数据库检查

检查各个数据库的大小，排除系统数据库（如 template0、template1和 postgres）。

SELECT datname, pg_size_pretty(pg_database_size(oid))
FROM pg_database
WHERE datname NOT IN ('template0', 'template1', 'postgres')
ORDER BY pg_database_size(oid) DESC;

需要关注的指标：数据库大小

查看数据库大小是否异常增长，需排查是否存在数据堆积或未清理的日志。

3、用户检查

检查数据库用户的权限、连接限制和资源配额。

SELECT usename, usecreatedb, usesuper, valbegin, valuntil, spacelimit, tempspacelimit, spillspacelimit FROM pg_user;

需要关注的指标：用户权限、资源配额

用户权限是否合理分配，是否存在高权限用户滥用资源。

4、数据库当前连接数

检查当前连接数与最大连接数的使用情况。

SELECT 
   to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "巡检时间",
   setting::int8 AS "max_conn(最大连接数)",
   (SELECT count(*) FROM pg_stat_activity) AS "now_conn(当前连接数)",
   setting::int8 - (SELECT count(*) FROM pg_stat_activity) AS "remain_conn(剩余连接数)"
FROM pg_settings
WHERE name = 'max_connections';

需要关注的指标：当前连接数、最大连接数

当前连接数是否超过最大连接数的 90%，需优化连接池或增加最大连接数。

5、当前处于空闲状态的会话数

检查空闲连接数，过多的空闲连接可能占用系统资源。

SELECT count(*) 
FROM pg_stat_activity 
WHERE state = 'idle';

6、长时间未提交事务会话

检查长时间未提交的事务，可能导致锁表或资源占用。

SELECT count(*) 
FROM pg_stat_activity 
WHERE state = 'idle in transaction' AND now() - state_change > INTERVAL '5 mins';

需要关注的指标：查看当前返回记录会话ID

若大量连接处于空闲状态且长时间未提交，需排查应用逻辑问题。

7、占用内存最多的会话 TOP 20

检查占用内存最多的会话，避免内存不足问题。

SELECT sessid, pg_size_pretty(sum(totalsize)), pg_size_pretty(sum(freesize))
FROM gs_session_memory_detail
GROUP BY sessid
ORDER BY sum(totalsize) DESC
LIMIT 20;

需要关注的指标：会话内存
若会话占用内存超过 1GB，需关注该会话的 SQL 查询，必要时进行优化。

8、数据库冲突事件检查

检查数据库冲突事件，可能导致数据不一致或性能下降。

SELECT * 
FROM pg_stat_database_conflicts 
WHERE datname NOT IN ('template0', 'template1');

9、缓冲区命中率查询

检查缓冲区命中率，评估内存使用效率。

SELECT sum(n_blocks_hit) / sum(n_blocks_fetched) AS cache_hit_rate FROM dbe_perf.statement;

需要关注的指标：缓冲区命中率

若缓冲区命中率低于 90%，可能需要增加内存或优化查询。

10、复制槽检查

检查复制槽的状态，确保数据同步正常。

SELECT * FROM pg_replication_slots;

需要关注的指标：复制槽状态

复制槽的active 列应为 t，restart_lsn 应持续向前推进。

11、节点内存使用情况

检查节点内存使用情况，避免内存不足。

SELECT * FROM pg_total_memory_detail;

需要关注的指标：节点内存

若dynamic_peak_memory 接近max_dynamic_memory，需考虑增加内存。

12、检查数据库重要配置

检查关键配置参数，确保符合生产环境要求。

SELECT 
   to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "巡检时间",
   name, setting
FROM pg_settings
WHERE name IN (
   'data_directory', 'port', 'client_encoding', 'config_file', 'hba_file', 
   'ident_file', 'archive_mode', 'logging_collector', 'log_directory', 
   'log_filename', 'log_truncate_on_rotation', 'log_statement', 
   'log_min_duration_statement', 'max_connections', 'listen_addresses'
)
ORDER BY name;

13、检查锁表

检查锁表情况，避免长时间锁表导致的性能问题。

SELECT 
   to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "巡检时间",
   relname AS "relname(表名)",
   b.nspname AS "shemaname(模式名)",
   c.rolname AS "user(用户名)",
   d.locktype AS "locktype(被锁对象类型)",
   d.mode AS "mode(锁类型)",
   d.pid AS "pid(进程id)",
   e.query AS "query(锁表sql)",
   current_timestamp - state_change AS "lock_duration(锁表时长)"
FROM pg_class a
INNER JOIN pg_namespace b ON a.relnamespace = b.oid
INNER JOIN pg_roles c ON a.relowner = c.oid
INNER JOIN pg_locks d ON a.oid = d.relation
LEFT JOIN pg_stat_activity e ON d.pid = e.pid
WHERE d.mode = 'AccessExclusiveLock'
ORDER BY "lock_duration(锁表时长)" DESC;

处理建议：若发现长时间锁表，可使用SELECT pg_terminate_backend(pid); 终止相关会话。

14、检查长事务 TOP5

检查长时间运行的事务，可能导致资源占用或锁表。

SELECT 
   to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "巡检时间",
   relname AS "relname(表名)",
   b.nspname AS "shemaname(模式名)",
   c.rolname AS "user(用户名)",
   d.locktype AS "locktype(被锁对象类型)",
   d.mode AS "mode(锁类型)",
   d.pid AS "pid(进程id)",
   e.query AS "query(锁表sql)",
   current_timestamp - state_change AS "lock_duration(锁表时长)"
FROM pg_class a
INNER JOIN pg_namespace b ON a.relnamespace = b.oid
INNER JOIN pg_roles c ON a.relowner = c.oid
INNER JOIN pg_locks d ON a.oid = d.relation
LEFT JOIN pg_stat_activity e ON d.pid = e.pid
WHERE d.mode = 'AccessExclusiveLock'
ORDER BY "lock_duration(锁表时长)" DESC;

15、检查表膨胀 TOP 5

检查表膨胀情况，可能导致性能下降。

SELECT 
   to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "巡检时间",
   current_database() AS current_database,
   relname AS "table_name(表名)",
   schemaname AS "schema_name(模式名)",
   pg_size_pretty(pg_relation_size('"' || schemaname || '"."' || relname || '"')) AS "table_size(表大小)",
   n_dead_tup AS "n_dead_tup(无效记录数)",
   n_live_tup AS "n_live_tup(有效记录数)",
   to_char(round(n_dead_tup * 1.0 / (n_live_tup + n_dead_tup) * 100, 2), 'fm990.00') AS "dead_rate(无效记录比例%)"
FROM pg_stat_all_tables
WHERE n_live_tup + n_dead_tup <> 0
ORDER BY "dead_rate(无效记录比例%)" DESC
LIMIT 5;

处理建议：对膨胀表执行 `VACUUM ANALYZE` 操作。

16、检查索引膨胀

检查索引膨胀情况，可能导致查询性能下降。

SELECT 
   to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "巡检时间",
   current_database() AS db,
   schemaname,
   tablename,
   bs,
   reltuples::bigint AS tups,
   relpages::bigint AS pages,
   otta,
   ROUND(CASE WHEN otta = 0 OR relpages = 0 OR relpages = otta THEN 0.0 ELSE relpages / otta::numeric END, 1) AS tbloat,
   CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
   CASE WHEN relpages < otta THEN 0 ELSE bs * (relpages - otta)::bigint END AS wastedbytes,
   CASE WHEN relpages < otta THEN '0 bytes' 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
   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
) AS sml;