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

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

本文将以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;

建议：索引膨胀可能导致查询性能下降，需定期重建或优化索引。

巡检总结

通过以上巡检指标和 SQL 查询，我们可以全面了解 GBase8c数据库的健康状态和性能表现。定期执行这些巡检任务，可以帮助我们及时发现潜在问题，优化资源配置，确保数据库的稳定运行。建议将这些查询封装为脚本，并通过定时任务定期执行，将结果记录到日志文件中，便于后续分析和回顾。