印刻数据库

外观

PostgreSQL 常用监控命令 ​

连接状态监控 ​

连接状态监控是DBA日常运维的基础，通过以下命令可以实时了解数据库的连接情况，包括当前连接数、活跃连接、等待中的连接等。

sql
-- 查看当前所有连接的详细信息，包括连接来源、执行的SQL、状态等
SELECT * FROM pg_stat_activity;

-- 统计当前数据库的总连接数
SELECT count(*) FROM pg_stat_activity;

-- 查看处于活跃状态的连接（正在执行SQL的连接）
SELECT * FROM pg_stat_activity WHERE state = 'active';

性能监控 ​

性能监控帮助DBA识别数据库的性能瓶颈，包括慢查询、索引使用情况、表访问频率等。

sql
-- 查看执行时间最长的前10条SQL语句，需要先启用pg_stat_statements扩展
SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- 查看用户表索引的使用情况，包括扫描次数、命中次数等
SELECT * FROM pg_stat_user_indexes;

-- 查看用户表的统计信息，包括插入、更新、删除次数等
SELECT * FROM pg_stat_user_tables;

锁监控 ​

锁监控用于识别数据库中的锁竞争和阻塞情况，是解决数据库性能问题的重要手段。

sql
-- 查看当前数据库中的所有锁信息
SELECT * FROM pg_locks;

-- 查看被阻塞的进程，帮助识别锁等待链
SELECT blocked_locks.*, blocked_activity.*
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
WHERE NOT blocked_locks.granted;

复制监控 ​

复制监控用于监控主从复制的状态，确保数据同步正常，包括复制延迟、同步状态等。

sql
-- 在主库上查看从库的复制状态，包括复制延迟、WAL位置等
SELECT * FROM pg_stat_replication;

-- 在从库上查看WAL接收进程的状态
SELECT * FROM pg_stat_wal_receiver;

常见问题（FAQ） ​

Q1: 如何查看慢查询？ ​

A1: 有两种常用方法：

  1. 配置postgresql.conf文件中的log_min_duration_statement参数，设置为具体毫秒值（如5000表示5秒），PostgreSQL会自动记录执行时间超过该值的SQL语句到日志中。
  2. 启用pg_stat_statements扩展，通过查询pg_stat_statements视图获取慢查询信息，如：
    sql
    SELECT query, total_exec_time, calls FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Q2: 如何监控复制延迟？ ​

A2: 可以通过以下方法监控复制延迟：

  1. 在主库上查询pg_stat_replication视图的replay_lag列，该列显示从库应用WAL的延迟时间。
  2. 在从库上执行SELECT now() - pg_last_xact_replay_timestamp();，计算当前时间与最后一次事务回放时间的差值，即为复制延迟。
  3. 使用pg_waldump工具分析WAL文件，计算主从WAL位置的差异。

Q3: 如何查看数据库的资源使用率？ ​

A3: 可以通过以下方式查看：

  1. 使用PostgreSQL内置的系统视图pg_stat_database查看数据库级别的资源使用情况。
  2. 结合操作系统命令（如top、vmstat、iostat）监控PostgreSQL进程的CPU、内存和I/O使用情况。
  3. 使用专门的监控工具，如Prometheus + Grafana、Zabbix等，实现可视化监控。

Q4: 如何识别长时间运行的查询？ ​

A4: 通过查询pg_stat_activity视图，过滤出执行时间较长的查询：

sql
SELECT pid, query, now() - query_start AS duration
FROM pg_stat_activity 
WHERE state = 'active' 
ORDER BY duration DESC;

可以根据实际情况设置duration的阈值，找出需要优化的长时间运行查询。