开发者学堂课程【PostgreSQL快速入门：PostgreSQL监控1统计进程和统计信息的解读】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/16/detail/79
PostgreSQL 监控1统计进程和统计信息的解读
监控冲突
本课程主要讲解 postgre 监控第三方插件，Postgre 数据库监控，包括数据库的统计信息产生的监控，例如表级别，索引级别，数据库级别，留级别等活动信息统计。
在超像层面，例如磁盘的使用，CPU 使用率，内存的使用率，网络的使用率等等，此外还包括数据库级别，活动会话级别的统计信息。首先查看数据库统计信息如何收集，通过以下进程进行收集统计信息：
stats collector process "src/backend/pdstmaster/pgstat.c"
数据库启动之后会看到如下进程：
代码对应如下：
src/backend/pdstmaster/pgstat.c
收集完成的统计信息，存放在shared buffer当中，数据库启动之后会向 shared buffer 当中写入内容，如果是数据库第一次启动，当中不存在内容。初始化指的是 initdb 此时产生的数据库集群。s Shared buffer 当中会填充0。如果有统计信息就会调用如下函数：
Pgstat_ read_ statsfiles
将上一次数据库关闭之后，写入pgdata当中的文件读出。历史的统计信息在数据库重启时就会将其加载。数据库启动之后，除了会将统计信息写入内存，还会将其写入到以下临时目录当中：
stats_ temp_ _directory
该目录在以下目录当中配置：
postgresql.oonf
是一个相对路径。假设数据库正在运行，统计信息在内存当中存放，也会在临时目录当中存放。每个数据库都有一个同名信息文件，还有数据库集群的全局统计。例如执行如下语句：
select cid.database from pg.database；
16384对应的是 database，是数据库的统计信息，global是全局的。Postgre 由于没有加载，所以没有统计信息。在数据库正常关闭时，就会将 Tmp 目录当中的内容拷贝到其中，以便下次启动时个数据库正常关闭的时候，内容不会丢失。因为会调用函数写入，函数都存在于文件当中，例如执行如下语句：
Select from pg.stat all tables where Relname=’ test’;
执行结果如下：
Digoal=# select * from pg_stat_all_tables where relnane='test’;
-[ RECORD 1 ]—----------------------------------
Relid：16406
schenanane: public
re name: test
seq scan:3
seq_tup_read:0
idx_scan:7
idx_tup_fetch:551
n_tup_ins: 300090
n_tup_upd:0
n_tup_del:548
n_tup hot_upd:0
n_live_tup:99851
n_dead_tup:0
last__vacuum:
last _ autovacuum：2014-03-0108:48:27.221004+08
last _analyze：
last_autoana lyze:2014-03-0108:48=27.284759+08
vacuum_count：0
autouacuumn_count:2
analyze_count：0
autoanalyze_count：3
例如存在表test，test当中有如上统计信息。将数据库关闭，由于数据库对应的ID是16384，数据库在关闭时需要将进程退出。数据库关闭之后，临时文件目录就不存在，因为将其拷贝到 pg.stat 当中，此时删除pg.stat<对数据库的使用没有任何影响，只是统计信息不存在。例如，执行如下语句:
Select *.new() from pg_stat_all_tables where relname=’test’;
结果如下：
此时test为0，插入行数与插入列数都不存在。文件删除对数据库启动没有任何影响，以上内容作为演示，在使用当中不必删除。查看代码文件当中如何运行：首先打开文件，数据库将已经存在的文件读入到哈希表当中，并且初始化数据库的哈希表。如果读取不到就会将其初始化为0。关闭数据库时，调用如下函数。将其写入到文件当中：
Pgstat_ write_ statsfiles(true, true);
true 表示需要：
Save the final stats to reuse at next startup.
将其加入到之前指定的目录，下次使用时就可以使用。如果是false，就表示不允许，不会调用该函数，也就不会写入。
统计信息放在内存文件和临时目录，数据库关闭之后会将其拷贝到持久化目录当中，临时文件也存在于持久化磁盘文件当中，所以不会丢失。就算将其删除，文件依然存在，不会丢失。只是没有将其拷贝到pg stat目录当中。
统计信息收集的内容，通过哪些参数可以控制该维度。例如 check activities 参数，参数在 postgre.config 当中配置，该参数用于控制是否收集 SQL 的执行开始以及 SQL 语句的内容。
例如，执行如下代码：
Select query.query.start from pg_stat_Activity;
在当中可以查看 SQL 语句的内容以及创建时间。如果将其关闭，就无法查看到该内容。说明已经不跟踪该会话，但另外一个对话仍然在跟踪。如果将其全局关闭不跟踪，就可以在参数文件当中直接关闭。默认是开启，尽量不关闭，如果关闭就无法看到当前数据库正在运行的 SQL 语句，在排错时进行较为困难，因为如果不存在 SQL 语句，用户无法得知数据库正在操作的内容。所以强烈建议打开。另一参数是控制 SQL 语句的长度。该内容是数据库启动时就调用的内存区域，数据库在记录 SQL 长度时是通过该参数进行判断，默认是1024，如果 SQL 语句超过该长度就会将其截断。最小值是100，查看截断效果如下:
设置成100之后，需要重启数据库。
执行如下代码：
Insert into test values<#.’aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa>;
在执行如下代码:
Select query.query.start from pg_stat_Activity;
效果如下：
Insert into test
values<#.’aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
该语句已经不完整。可以通过以下函数查看长度：
Select Length<query>.query.start from pg_stat_Activity;
设置为100，但显示结果是99，是因为存在指示符，占一个字节。最好不更改，1024即可，如需要更大长度，可以将其改为更大。例如日常需要的 SQL 语句较长，通常需要排错，所以可以将其设置为更大长度。演示完成之后，更改回1024，并重启。
Track counts 是一个计数器，该计数器据库活动信息，例如表新增的行数，删除的行数，通过 Track counts 进行记录。如果将其关闭就无法使用 autoVacuum。
因为 autovacuum 需要得知表被删除的行数和插入的行数，如果无法得知插入行数和被删除的行数，就无法得知表中有多少垃圾数据，那么就无法判断是否对其进行 autovacuum，也就是垃圾回收，所以 Trackcounts，一定要打开。如果不打开计数器无法使用，也就无法得知表中的状态。例如，执行如下代码：
/d Pg_ stat all tables;
结果如下:
relid
Schemaname
Relname
Seq_Scan
seq_ tup_read
idx_scan
idx_ tup_ fetch
n_ tup_ ins
n- tup_ upd
n_ tup_del
n_ tup_ huw_upd
n_ live_tup
n_ dead_tup
last_ vacuu
last_ autovacuum
last _ analyze
last_autoanalyze
vacuun Count
autovacuum_count
analyze_count
autoanalyze_Count
当中记录了插入行数，更新行数及正在运行和已经死去的行数。例如查看test表。由于数据库已经将统计信息文件删除，所以会将统计信息初始化。
执行如下代码：
analyze test；
分析该表，统计信息返回。结果如下：
如果 track counts 关闭，执行如下代码：
Delete from test；
结果如下：
由于将 track counts 关闭，仍然存在相同数量的正在运行。如果再次将 track counts 打开，将其再次删除。
查看最后结果如下：
执行如下代码：
insert into test select generate_series<1.1000>,’test’;
结果如下：
Live 行后加了1000，对于已经删除的行数，由于没有计数，此时数据不准确，其实内容只有1000行。做数据分析之后，得出数据有1000行，分析之后发现 dead tup 也正常。所以如果将计数器关闭，据库当中的统计信息就不正常或直接不存在，所以该参数不能关闭，如果关闭 auto vacuum 就无法正常执行，无法通过信息判断是否做垃圾回收。垃圾回收是通过参数进行控制，何时进行垃圾回收与以下参数有关：
首先是 Auto vacuum，必须打开，打开之后才会自动进行垃圾回收，由 Vacuum threshold 和analyze threshold 参数控制，分别是垃圾回收和自动表分析，只有在满足以上条件之后才能正常进行垃圾回收。在auto vacuum 代码当中，搜索relation needs vacanelyce，该函数当中说明到达阈值之后，就会开始触发vacuum。
首先是 base Threshold，Base The 的参数是指50条记录，其次 skill factor， 0.2是系数，包括0.1也是系数，其次是reltmpples，，reltmpples 来自于 pg.calss，该表当中，reltmpples 是1000，表示有1000条记录。
假设 Scale factor 是0.2，就表示0.2×1000=200，加上 threshold =50。最终结果为250，表示当该表有1000条记录，如果有250条记录发生了 update 或 Delete 就会触发 Auto vacuum。表当中有1000条记录，当前统计信息如下：
最后一次 Autovacuum 如下：
2014-03-01 09:12:54.795819+08
此时进行 delete，Analyze 先发生，Vacuum 后发生。当触发 analyze 之后，real tubes 就会开始变化，如果在变化之后达到了 auto vacuum 的阈值，就会自动触发垃圾回收。例如此处如果要触发垃圾回收，此处 factor 是0.1，
0.1*1000=100,100+50=150，执行如下代码：
Delete from test where ID<100;
稍等片刻。由于 napTime 是一分钟，可以将其设置为更小。
此时运行结果，表示没有发生垃圾回收，是因为没有达到预值。记录是150条，没有发生分析，是因为没有达到预值。只删除了49行内容，deadtup 只有149，此时的 threshold 是150，
所以执行如下代码：
Delete from test where ID<150;
在有151条记录之后就自动触发，也就是当 deadtup 超过指定的阈值之后，就会发生自动分析。
Doanalyze=<anliuplee> anlthreash>;
doVacuum=force Vacuum ::< Vactupples>vact horsh>;
也就是记录有151条时，刚好达到150，触发了自动分析。
要达到自动 vacuum，需要查看当前的 reltups，当前是849。如果需要发生自动 vacuum，需要219.8。如果有221条记录，就会触发 Autovacuum，还需要删除70条记录。执行如下代码
delete from test where ID<-221；
执行完成之后，last Autovacuum 产生变化，变成当前时间。以上是 vacuum 进程的触发。
还存在一个维度 track io timing，收集 IO 操作的时间开销，例如数据库统计信息，Block read time 以及blockwrite time 是指数据库对数据块，读的时间消费了多少，写的时间消费了多少。如果安装了 extension，会存在如下视图：
该视图当中也记录了，Block read time 以及 blockwrite time，Query 表示记录 SQL 语句，该 SQL 语句总共发生多少数据块的读的时间以及写的时间。通过打开 io timing 进行记录，io timing 对于使用有一定开销。
由于需要记录每一次 io 操作，也就是调用系统函数，会产生额外的探测开销，可以通过 pg test timing 函数进行测试，该函数是 postgre 提供。例如在每一次获取操作系统时间时，需要耗费多少额外的时间，例如每一次循环是70.9纳秒，属于浪费的时间。假设测试十秒，用于查看平均每个曲时间的循环需要浪费多少纳秒，之前默认测试三秒，浪费的时间为70.98纳秒，测试十秒，浪费70.35纳秒。
在 io timing 当中，并不存在太多运行时间，主要浪费的时间。如果将其打开，会影响数据库性能，一般情况下不需要将其打开。如果将其打开会有一定影响，尤其是百分比较小的情况下。usec 是指小于1us，越少越好，集中在小于一us最佳，个别 CPU 能够达到97%以上，是志强的 CPU。志强的2.0G的15504。
Track function 指的是跟踪函数的调用次数和时间开销，如果配置为 pl，就仅跟踪 plpgsql 函数，如果是 all，表示跟踪数据库当中的 SQL 函数以及 plpg SQL 函数默认是关闭假设数据库使用的函数较多，需要跟踪每次函数的调用时间，可以将其打开。
Updates_process_title，用于查看进程的cmd状态的更新，如果将其关闭，在 PS 当中就无法看到信息。例如local idlo，指的是当前连接的信息当前没有做查询，如果是查询就是select，如果将其关闭，就看不到该进程要做什么。默认是打开的。
Log_statement_ste=ats，默认关闭。如果打开，就包括后面三项所有的统计信息。统计信息类似于 UNIX 的GETRUSH 函数输出，数据结构当中会接收如下数据，例如跟踪了一条 SQL 语句。在该 SQL 语句当中，包括用户使用时间，系统时间，使用最大共享内存，非共享数据段和非共享战数据，交换分区的使用情况，数据快读入读出情况，消息发送和接收情况，信号接收情况及上下文切换统计信息。如果将其打开，就会统计以上内容 SQL 解析阶段，计划阶段和执行阶段的统计开销，如果关闭可以分别打开以上三项。
如果只想查看执行阶段，可以单独将其打开。默认情况下是关闭。查看其输出：
通过如下代码将其打开：
Set log_statement_stats=on;
可以看到如下输出:
DETAIL: ? systen usage stats:
0.0000?4 e lapsed 0. 000000 user 0. 000000 systen sec
[0.020996 user 0.008998 sys total ]
0/0 [0/328] f ilesysten blocks in/out
0/0 [0/3067] page f aults/rec la ins
[0] swaps
0 [0] signals rcvd, 0/0 [0/0 ]
messages rcvd/sent
0/0 [25/0] vo lun t ary/ invo luntary context switches
输入如下代码：
Select count from pg.class；
会输出该相关语句的统计信息，包括用户时间，系统时间，系统调用时间，数据文件块的读入读出时间，信号的接收，上下文的切换信息等等。如果需要查看解释，可以回滚源代码。当中打印了如下内容：
< long) <e lapse_ t.tu_ sec一Save_ t.tu_ sec)，
< long) <e lapse_ t.tu_ usec 一Save_ t.tu usec) ，
< long) (r.ru_ utins.tu sec一Save _ r.ru utime.tu sec)，
< long) (r.ru_ utine.tu usec -Save r- PU_ utine . tu_ usec )。
< long) (r.ru_ stine.tu_ sec一Save_r.ru_stine.tu_sec).
< long) (r.ru_ stine.tu usec 一Save_ r.rU_ stine .tu_ usec)) ;
统计信息的视图分为state和io。所有的表格存在于如下网址当中：
http:// www .postgresql.org/ docs/ 9.3/ static/ monitoring-stats.html
例如 standard state sphere，是标准统计信息视图。Activity是当前活动统计信息，bgwriter 是指进程统计信息。对于数据库来说，是 database。All tables 是指当前连接的用户可以看到所有的表。Sys Tables 是指系统表。
User tables是指用户创建的表。Xat是指当前事务的统计信息。在事物当中对该表做了多少插入和更新，可以对其进行查看。State io 是指关于io的统计信息，同样包含 State 、io 、users、序列等，User function 是指函数统计信息。如果打开了 track function，可以查看 user function 表。Replication 是指流复制统计信息，database conflicts 是指数据库发生数据库级别冲突时的统计信息。统计信息存放在临时文件和内存区域当中。如果数据库关，会将其放到 states 当中。
此时在 tmp 当中，每个数据库对应一个统计信息文件，如果要清除统计文件，可以使用函数对其进行清除。
查看统计信息的函数有，包括pg_backend_pid，是指当前连接的进程对应的 backend，通过如下代码查看当前进程所对应的进程 ID：
Selectpg_backend_pid（）；
此时的进程对应的 ID 是24823, pg stat, .get. activity (integer)是查看视图使用到的函数，也就是查看该视图。在试图当中也可以查看调用的函数就是 pg stat, .get. activity (integer)，如果在之前加入null，输入如下代码：
Select * from pg_stats get activity（24823）；
表示只查看指定进程的统计信息。
clear snapshot，表示丢弃当前状态。例如在事务当中查询统计信息文件，输入如下代码：
Select * from pg stat all tables where relname=’test’;
在事务当中一旦进行查询就不会变化，如果在另外会话当中进行删除操作，在此处也不会变化。因为查看到的是snapshot。只有当丢弃 snapshot 时，才会变化。此时在该会话当中，查询到的是全新的统计。在另一会话当中进行查询，结果表示已经删除。但此处并未体现出，因为此处是事务，事务启动之后查询不会变化，只有丢弃之后，重新获取镜像，才能查看出结果，之前看到的是 snapshot。Pg_stst_resest，表示重置连接的数据库，需要特殊权限，超级用户才能进行操作。
例如将当前连接的数据库统计信息全部重置，再次进行查询就已经重置，但查询之前需要丢弃，查询时就会全为零，在另外一个节点进行查询也是为零，因为已经进行 reset。当前数据库的所有统计都为零，相当于是初始化。如果需要把 Sharedbuffer 当中的统计信息清零，例如 pg_writer 统计信息，是 Sharedbuffer 当中的统计信息。首先需要进行清除，当中指定需要 reset 的区域。结果如下：
还可以清除单个表或单个函数的计数，参数是该表的 update ID。输入如下代码：
insert into test select generate_Series(1,100),’Test’；
此时插入100行记录之后，计数已经存在，如果需要把该表计数清零，输入如下代码：
Select *.new() from pg_stat_tables where relname=’test’;
清除完成之后，再次进行查询就已经清理。此时只将该表统计信息清除。部分函数操作需要超级用户。在事物当中获取统计信息时，只获取一次，除非使用 pg_stat_clear_Napshot()，丢弃该进程。查看或重置统计信息的函数，重置就是reset；查看会话或服务端进程级别的统计信息函数，根据进程ID查询统计信息；以上内容均讲述。指定一个进程，需要获取其 activity，需要传入 integer，进程 ID，返回的是text类型，返回的是 query，是分开查询，但都来自于一个视图。通过一个函数进行调用所有信息。方法只是将其全部拆开，分别返回，例如需要返回该进程是否在等待，返回类型是布尔类型。
语句级别的资源开销统计也已讲述，当中的变量通过哪些值进行传入，在源代码当中已经行讲解。信息和 rusage 数据结构当中的信息相同。查看当前活动会话信息，包括会话的启动时间，事务的启动时间，当前正在执行的 SQL 等，活动会话信息是指当前连接在数据库当中的。查看指定视图，并且 state 不等于 ide，也就是当前不是空闲的。
查看数据库 SQL 统计信息需要使用pg_state_statement插件, 该插件在数据库管理过程当中较为有效。该插件需要配置，除了创建之外，还要进入到 postgre 当中进行配置:
digoal= # create extension pg. _stat_ _statements;
CREATE EXTENSION
digoal=# \q
pg93@db-172-16-3-150-> cd $PGDATA
pg93@db-172-16-3-150-> vi postgresql conf
shared_ preload_ libraries = 'pg_ gstat_ _statements'
pg_ stat _statements. max = 1000
pg. stat_ statements.track = all
pg93@db-172-16-3- 150-> pg_ ctl restart -n fast
1000是指最多跟踪1000个 sql 语句，all是指跟踪所有 SQL 语句，此时重启数据库，需要提前编译好，如果没有编译，需要到源代码目录当中进行编译。编译了如下内容：
drwxrwxr-x 4 pg93 pg93 4.0K Jan 4 16:17 londiste3
drwxrwxr-x 2 pg93 pg93 4.0K Dec 29 1?:59 pgbak
rW-r--r-- 1 pg93 pg93 38K Dec 29 18:14 Pg_ class . bak
lruxrwxrwx 1 root root
21 Feb 2? 14:21 pgsql -> /hone/pg93/pgsq19.3.3
drwxr-xr-x 6 root root 4.0K Oct 13 09:00 pgsq19.3 .1
drwxr-xr-x 6 root root 4.0K Feb 2? 14:21 pgsq19.3.3
rW PW-r- -1 pg93 pg93
30K Dec 9 12:23 test . dmp
rwX----- 1 pg93 pg9343 Jan 4 15:06 test.sq1
-rw-rw-r-- 1 pg93 pg93 11K Dec 29 18:26 toc
由于已经编译过，所以重启之后可以进行使用。Extension 可以在任意数据库当中创建，由于是全局信息，只要在一个数据库当中创建即可。
首先将信息进行重置，通过调用函数，可以将信息进行重置，再次进行查询当中就不存在信息，只有当前查询的，此时进行测试：输入如下代码：
Insert into test values（1,‘test’）；
Create table （Test ID info text）；
创建表之后，准备八个连接，两个线程，测试时间为十秒，向其中插入十秒数据，再连接到数据库，Extension 创建在postgre数据库当中，此时能够查看部分信息。例如需要按照 SQL 总耗时倒序输出信息。
输入如下代码：
select *from pg_ stat_ _statements order by total_ time desc limit 1 offset 0；
其中存在 total time，也就是所有 CPU 耗时。执行 insert 语句 totaltime 是8387毫秒，也就是八秒钟，调用次数是77万次，总耗时是8秒钟，输出了整个系统当中跟踪到最耗费时间的 SQL 语句。例如业务系统运行当中，某一个 SQL语句在业务系统当中需要经常调用，该 SQL 语句总调用了1000万次，总耗时在所有SQL语句调用排在最前，只需要将该 SQL 语句优化，整个数据库就会轻松许多。在 SQL 语句优化时，可以根据 total time 进行倒序排行，然后根据最费时间的 SQL 语句进行优化，不是单句语句最费时，指的是总共运行次数耗时加起来是最多的 SQL 语句，将该 SQL语句优化，数据库就能得到缓解，或者按照调用次数进行倒序排列，就是order by calls，如果是单个 SQL 语句执行顺序排列，就是 total time calls，如果按照未命中块倒序排列，就是 shared blks read，shared blks read 指的是没有在 shared buffe r当中命中，不排除在操作系统层面的 catch，如果在操作系统层面有 catch，此时也会统计到Share buff中。只要得知是否在数据库的shared buffer当中命中，并不知道是否在操作系统的 catch 当中命中，所以只要没有在数据库的 shared buffer 当中命中，都会将其归类为 shared blks ，read指未在 shared buffer 当中命中，如果命中就是 hint。
通过以上输出，就能得知哪些SQL语句最费时，哪些 SQL 语句单词调用最费时，哪些SQL语句调用次数最多，哪些SQL 语句未命中读最多，为命中读最多，说明该 SQL 语句扫描数据块太多，因为需要的数据块数量有限，或者数据太冷，只是偶尔查询，未命中概率也会较高。例如偶尔查询，查询完成，就会被排挤，下次再进行查询就已经不在了。例如每天以邮件的形式发送 CPU 的 Time，也就是 total time 数据，将其发送给需要了解的用户，假设用户需要了解数据库最费时的 SQL 语句，可以通过 shell 脚本进行发送，例如每天8点将信息统计完成，然后将统计信息重置，如果不重置，当中就是历史数据，历史数据不具备参考价值，所以此时是统计每天的数据，因为已经清除，下一次统计时就是第二天，所以此时统计的就是一天的统计信息，较为准确。
