AntDB 数据库集中式运维手册-数据库监控和维护5

SQL

长事务

监控原因：

• 锁定争用： 长事务可能会持有锁定资源，导致其他事务等待。
• 资源泄漏： 长事务可能会占用数据库资源，如内存和磁盘空间，而不释放它们。
• 难以维护： 长事务会增加数据库的复杂性，降低了维护和性能调整的可行性。

产生原因： 复杂查询和操作、事务未提交或回滚、事务内部循环、未释放的锁定、事务可能在等待锁定时保持活动状态，例如，它可能在等待其他事务释放的锁定资源上等待、长时间的数据库连接、异常处理不当、大事务的滞后提交

相关查看命令：

SELECT pid, backend_start, xact_start, query_start, wait_event, state, backend_xid FROM pg_stat_activity WHERE backend_xid is NOT NULL AND now() - xact_start > interval '1 min';

其中：
backend_start      -- 连接创建时间
xact_start         -- 事务begin时间
query_start        -- 事务中最近的查询开始时间，一个事务内有多个查询时，只记录最近的一个
state              -- 事务状态
backend_xid        -- 当前事务的id，即xid
query	             -- 具体执行的SQL

执行方式： 集中式数据库要在主库执行

长连接

监控原因： AntDB 中长连接会影响表中垃圾回收，会导致表的年龄增长无法 freeze，并且会导致表膨胀，并且会占用数据资源。

相关查看命令：

SELECT pid, backend_start, xact_start, query_start, wait_event, state, backend_xid FROM pg_stat_activity WHERE backend_xid is NOT NULL AND now() - xact_start > interval '1 min';

其中：
backend_start      -- 连接创建时间
xact_start         -- 事务begin时间
query_start        -- 事务中最近的查询开始时间，一个事务内有多个查询时，只记录最近的一个
state              -- 事务状态
backend_xid        -- 当前事务的id，即xid

慢查询

监控原因： 当 SQL 书写不规范，会导致笛卡尔积；当索引不生效，就会走全表扫描等等，遇到这种情况某些 SQL 查询就变得异常缓慢，并且耗费大量资源，需要对这些 SQL 进行改写、加索引等等优化措施。

相关查看命令：(前提创建了 pg_stat_statements 插件)

# 最耗IO SQL，单次调用最耗IO SQL TOP 5：
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (blk_read_time+blk_write_time)/calls DESC LIMIT 5;

# 总最耗IO SQL TOP 5：
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (blk_read_time+blk_write_time) DESC LIMIT 5;

# 最耗时 SQL，单次调用最耗时 SQL TOP 5：
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 5;

# 总最耗时 SQL TOP 5：
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

# 可以把这个条件加入到上面的查询中来查看每个sql对应的缓存命中率：
100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent