生活在别处: 九月 2016

原文地址： PostgreSQL监控指标

PostgreSQL 版本： 8.2.15

数据库状态信息

目前客户端的连接数

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

连接状态

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，是否发生等待，根据事物或查询统计已执行时间，查询语句等。有多少个连接查询就会有多少条记录。

数据库占用空间
```
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 相同，一个热点的表最好一天一个统计大小，获得表的一个增长状况。根据需求提前准备空间应付数据库的增长。

等待事件

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 做相应处理。

数据库统计信息

统计 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 共享内存的命中率等信息。
表的共享内存使用情况，需安装扩展 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，如果计算大小等于表的大小，说明全表的数据都在缓存在，这时的查询速度很快。

表执行操作的统计
统计对一个表操作的偏重， 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';

表 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;
```

系统监控信息

数据库基本状态信息

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

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

查看当前服务器的连接情况
```
SELECT * FROM pg_stat_activity;
```
返回信息包括如下:
- datid
- datname 数据库名
- procpid 进程 ID
- sess_id 会话 ID
- usesysid
- usename 提交 SQL 的用户名
- current_query 当前 SQL 执行
- waiting 当前是否等待
- query_start SQL 提交时间
- backend_start 后台执行时间
- client_add SQL 提交的服务器
- application_name SQL 提交的终端
- xact_start SQL 执行开始时间
- waiting_reason 等待原因，与 waiting 字段结合使用
该查询结果也可在 PgAdmin 的 ”服务器状态“ 里查看到。

杀死 IDLE 进程

SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE current_query=''

生活在别处

2016年9月19日星期一

PostgreSQL 监控指标

数据库状态信息

数据库统计信息

系统监控信息

2016年9月18日星期日

获取 *nix 文件的绝对路径

2016年9月13日星期二

PostgreSQL 管理进程

2016年9月8日星期四

PostgreSQL psql 客户端命令