PostgreSQL 监控指标

原文地址： PostgreSQL监控指标

PostgreSQL 版本： 8.2.15

数据库状态信息

1. 目前客户端的连接数
   

   SELECT COUNT(*) FROM pg_stat_activity WHERE NOT procpid = pg_backend_pid()
   

2. 连接状态
   

   SELECT procpid, waiting, current_timestamp - LEAST(query_start, xact_start) AS runtime, SUBSTR(current_query, 1, 25) AS current_query FROM pg_stat_activity WHERE NOT procpid = pg_backend_pid()
   

   可以查看每个连接的 procpid，是否发生等待，根据事物或查询统计已执行时间，查询语句等。有多少个连接查询就会有多少条记录。
   
3. 数据库占用空间
   

   SELECT pg_size_pretty(pg_database_size('postgres'))
   

   一个数据库数据文件对应存储在以这个数据库 oid (pid) 命令的文件中，通过统计所有以 oid(pid) 命名文件的总大小，也可以得出这个数据库占用大空间。oid(pid) 可通过 SQL SELECT oid, datname FROM pg_database;获得。
   表占用的空间使用 pg_relation_size() 查询，对应的系统中的文件名和 pg_class 中的 filename 相同，一个热点的表最好一天一个统计大小，获得表的一个增长状况。根据需求提前准备空间应付数据库的增长。
   
4. 等待事件
   

   SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS blocking_pid, ka.usename AS blocking_user, a.current_query AS blocked_statement 
   FROM pg_locks bl 
   JOIN pg_stat_activity a ON a.procpid = bl.pid 
   JOIN pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid 
   JOIN pg_stat_activity ka ON ka.procpid = kl.pid WHERE NOT bl.granted;
   

   根据阻塞的语句的会话 PID 做相应处理。
   

数据库统计信息

1. 统计 SQL, 需安装扩展 pg_stat_statements-1.1.sql，调整 postgres.conf: shared_preload_libraries=‘pg_stat_statements’。
   

   SELECT calls, total_time, rows, 100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent,substr(query,1,25)
   FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
   

   上述查询是按照查询到执行时间排序，可以定位到执行比较慢的 sql， 也可以用来查常用 SQL，以及 SQL 共享内存的命中率等信息。
   
2. 表的共享内存使用情况，需安装扩展 pg_buffercache-1.0.sql
   

   SELECT c.relname, count(*) AS buffers 
   FROM pg_buffercache b INNER JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) 
   AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database())) GROUP BY c.relname ORDER BY 2 DESC LIMIT 5;
   

   表在共享内存中占用的块数，用来查看表是不是在内存中，buffers 的单位是数据块，默认 8K， 如果计算大小等于表的大小，说明全表的数据都在缓存在，这时的查询速度很快。
   
3. 表执行操作的统计
   统计对一个表操作的偏重， INSERT, UPDATE, DELETE 的比率
   

   SELECT relname,cast(n_tup_ins AS numeric) / (n_tup_ins + n_tup_upd + n_tup_del) AS ins_pct,
   cast(n_tup_upd AS numeric) / (n_tup_ins + n_tup_upd + n_tup_del) AS upd_pct, 
   cast(n_tup_del AS numeric) / (n_tup_ins + n_tup_upd + n_tup_del) AS del_pct 
   FROM pg_stat_user_tables where relname='products';
   

4. 表 IO 和索引 IO
   表 IO 主要涉及查询的逻辑块和物理块，归到底也是命中率的问题。一个表存在在内存中的内容越多，相应的查询速度越快。相关视图：pg_stat_user_tables。
   相对于表的大小来说，索引占用的空间要小很多，所以常用的表，可以让其索引一直存在内存中，很多时候保持索引的一个高命中率是非常必要的。相关视图pg_stat_user_indexes。
   

   SELECT indexrelname,cast(idx_blks_hit as numeric) / (idx_blks_hit + idx_blks_read) AS hit_pct,
   idx_blks_hit,idx_blks_read 
   FROM pg_statio_user_indexes WHERE (idx_blks_hit + idx_blks_read)>0 ORDER BY hit_pct;
   

系统监控信息

1. 数据库基本状态信息
   

   $ ps -ef | grep postgres
   $ netstat -altunp | grep 5432
   $ pg_controdata 
   

2. 
   

   $ top -u gpadmin
   $ free
   $ iotop -u gpadmin
   

   用 top 可以分析出系统当前整体的负载状况。当总体负载率很低，在 IO 等待率高的时候可以用 iotop 来定位 IO 具体的进程， top 中的 VIRT RES 表示进程希望获取的内存和占用系统内存的数量。可以据此来判断系统内存的分配情况。内存的值也关联到一些参数的设定，如 postgres 在发生 checkpoint 之前， checkpointer process 进程会消耗比较大的物理内存，直到检查点发生后，占用的内存会被释放掉，所以在设置 checkpoint 时间的时候也要将内存的占用考虑进去。