PG的统计信息(二)
1.2.4 pg_statio_user_tables 指标含义
通过对pg_statio_user_tables的查询，如果heap_blks_read，idx_blks_read很高说明shared_buffer较小，存在频繁需要从磁盘或者page cache读取到shared_buffer中。
db1=# select * from pg_statio_user_tables where relname='t1'; -[ RECORD 1 ]---+------- relid | 17087 //相关表oid schemaname | public //schema模式 relname | t1 //表名 heap_blks_read | 54 //指从page cache或者磁盘中读入表的块数 heap_blks_hit | 13620 //指在shared_buffer中命中表的块数 idx_blks_read | 16 //指从page cache或者磁盘中读入索引的块数 idx_blks_hit | 19536 //在shared_buffer中命中的索引的块数 toast_blks_read | 0 //从page cache或者磁盘中读入toast表的块数 toast_blks_hit | 0 //指在shared_buffer中命中toast表的块数 tidx_blks_read | 0 //从page cache或者磁盘中读入toast表索引的块数 tidx_blks_hit | 0 //指在shared_buffer中命中toast表索引的块数
1.2.5 pg_stat_bgwriter 指标含义
db1=# select * from pg_stat_bgwriter; -[ RECORD 1 ]---------+------------------------------ checkpoints_timed | 9208 //指超过checkpoint_timeout的时间后触发的检查点 checkpoints_req | 18 //指手动触发的检查点或者因为wal文件数量到达max_wal_size大小时也会增加，如果这个值大于checkpoints_timed，说明checkpoint_timeout设置的不合理。 checkpoint_write_time | 1404403 //指从shared_buffer中write到page cache花费的时间 checkpoint_sync_time | 980 //指checkpoint调用fsync将脏数据同步到磁盘花费的时间，如果这个时间很长容易造成IO的抖动，这时候需要增加checkpoint_timeout或者增加checkpoint_completion_target。 buffers_checkpoint | 19861 //checkpoint写入的脏块的数量 buffers_clean | 1868 //通过bgwriter写入的块的数量 maxwritten_clean | 0 //指bgwriter超过bgwriter_lru_maxpages时停止的次数，如果这个值很高说明需要增加bgwriter_lru_maxpages的大小 buffers_backend | 404509 //通过backend写入的块数量 buffers_backend_fsync | 0 //指backend需要fsync的次数 buffers_alloc | 54296 //被分配的缓冲区数量 stats_reset | 2020-09-23 15:14:57.052247+08
1.2.6 pg_stat_replication 指标含义
pg_stat_replication仅仅在主从架构下才会显示相关数据。根据对pg_stat_replication表的查询可以查看当前复制的模式、复制配置信息、复制位点信息等。
postgres=# select * from pg_stat_replication ; -[ RECORD 1 ]----+------------------------------ pid | 15040 //负责流复制进程的pid usesysid | 16384 //用户ID usename | repl //复制用户 application_name | walreceiver //这是同步复制的通常设置，它可以通过连接字符串传递到master。 client_addr | 192.168.1.171 //客户端地址 client_hostname | //客户端主机名称，可在postgres.conf中对log_hostname参数设置，启动DNS反向查找 client_port | 58690 //客户端用来和WALsender进行通信使用的TPC端口号，如果不本地UNIX套接字被使用了将显示-1。 backend_start | 2020-09-05 13:42:30.108815+08 //流复制开始时间 backend_xmin | state | streaming //复制模式 sent_lsn | 0/3000148 //发送到连接的最后的位点 write_lsn | 0/3000148 //standby数据库落盘的位点 flush_lsn | 0/3000148 //standby数据库flush的位点 replay_lsn | 0/3000148 //standby数据库重放的位点 write_lag | //写延迟间隙 flush_lag | //flush延迟间隙 replay_lag | //重放延迟间隙 sync_priority | 0 //复制优先级权重 sync_state | async //同步模式，同步or异步 reply_time | 2020-09-05 13:49:41.269624+08 //
1.2.7 pg_stat_statement 指标含义
pg_stat_statements模块提供一种跟踪执行统计服务器执行的所有SQL语句的手段。该模块默认是不开启的，如果需要开启需要我们手动对其进进行编译安装，修改配置文件并重启数据库，并在使用前手动载入该模块。
1、手动安装加载pg_stat_statement模块
- 进入pgsql软件的contrib目录下，查看是否有pg_stat_statements相关模块 # pwd /usr/local/postgresql-12.2/contrib # ls | grep pg_stat_statements pg_stat_statements -- 进入pg_stat_statement模块目录下进行编译安装 # make && make install -- 检查pg_stat_statement是否安装成功 # cd /usr/local/pgsql/share/extension/ # ll | grep pg_stat_statements -rw-r--r-- 1 root root 1246 10月 25 15:46 pg_stat_statements--1.0--1.1.sql -rw-r--r-- 1 root root 1336 10月 25 15:46 pg_stat_statements--1.1--1.2.sql -rw-r--r-- 1 root root 1454 10月 25 15:46 pg_stat_statements--1.2--1.3.sql -rw-r--r-- 1 root root 345 10月 25 15:46 pg_stat_statements--1.3--1.4.sql -rw-r--r-- 1 root root 305 10月 25 15:46 pg_stat_statements--1.4--1.5.sql -rw-r--r-- 1 root root 1427 10月 25 15:46 pg_stat_statements--1.4.sql -rw-r--r-- 1 root root 376 10月 25 15:46 pg_stat_statements--1.5--1.6.sql -rw-r--r-- 1 root root 806 10月 25 15:46 pg_stat_statements--1.6--1.7.sql -rw-r--r-- 1 root root 191 10月 25 15:46 pg_stat_statements.control -rw-r--r-- 1 root root 449 10月 25 15:46 pg_stat_statements--unpackaged--1.0.sql -- 修改配置文件 # vi postgres.conf shared_preload_libraries='pg_stat_statements,pg_pathman' pg_stat_statements.max = 10000 pg_stat_statements.track = all -- 重启数据库 # su - postgres $ pg_ctl -D /data/pgsql12/data restart -- 登录数据库，载入pg_stat_statement模块 postgres=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements'; //查看可用模块 -[ RECORD 1 ]-----+---------------------------------------------------------- name | pg_stat_statements default_version | 1.7 installed_version | comment | track execution statistics of all SQL statements executed postgres=# create extension pg_stat_statements; //载入模块，载入后pg_stat_statements表可正常使用 CREATE EXTENSION
2、pg_stat_statements 指标含义
postgres=# select * from pg_stat_statements limit 1; -[ RECORD 1 ]-------+------------------------------------------------------------------------ userid | 10 //用户id dbid | 13547 //数据库oid queryid | 1194713979 //查询id query | SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' //查询SQL calls | 1 //调用次数 total_time | 53.363875 //SQL总共执行时间 min_time | 53.363875 //SQL最小执行时间 max_time | 53.363875 //SQL最大执行时间 mean_time | 53.363875 //SQL平均执行时间 stddev_time | 0 //SQL花费时间的表中偏差 rows | 1 //SQL返回或者影响的行数 shared_blks_hit | 1 //SQL在在shared_buffer中命中的块数 shared_blks_read | 0 //SQL从page cache或者磁盘中读取的块数 shared_blks_dirtied | 0 //SQL语句弄脏的shared_buffer的块数 shared_blks_written | 0 //SQL语句写入的块数 local_blks_hit | 0 //临时表中命中的块数 local_blks_read | 0 //临时表需要读的块数 local_blks_dirtied | 0 //临时表弄脏的块数 local_blks_written | 0 //临时表写入的块数 temp_blks_read | 0 //从临时文件读取的块数 temp_blks_written | 0 //从临时文件写入的数据块数 blk_read_time | 0 //从磁盘或者读取花费的时间 blk_write_time | 0 //从磁盘写入花费的时间
