pg_wait_sampling

os: centos 7.4
db: postgresql 10.11

pg_wait_sampling 是 postgresql 的一个 extension，用于采集 sql 的等待事件。

由 postgrespro 公司开源。

版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# 
# 
# yum list installed |grep -i postgresql
postgresql10.x86_64                10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-contrib.x86_64        10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-debuginfo.x86_64      10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-devel.x86_64          10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-docs.x86_64           10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-libs.x86_64           10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-odbc.x86_64           12.00.0000-1PGDG.rhel7              @pgdg10  
postgresql10-plperl.x86_64         10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-plpython.x86_64       10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-pltcl.x86_64          10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-server.x86_64         10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-tcl.x86_64            2.4.0-1.rhel7                       @pgdg10  
postgresql10-tcl-debuginfo.x86_64  2.3.1-1.rhel7                       @pgdg10  
postgresql10-test.x86_64           10.11-2PGDG.rhel7                   @pgdg10 

# su - postgres
Last login: Wed Jan 15 18:34:12 CST 2020 on pts/0
$
$
$ psql -c "select version();"
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 10.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

下载安装

# su - postgres
$ git clone https://github.com/postgrespro/pg_wait_sampling.git
$ cd pg_wait_sampling
$ make USE_PGXS=1
$ sudo make USE_PGXS=1 install
$ make USE_PGXS=1 installcheck

修改配置文件 shared_preload_libraries

# vi /var/lib/pgsql/10/data/postgresql.conf

shared_preload_libraries = 'pg_stat_statements, pg_wait_sampling'

# systemctl restart postgresql-10.service 

初步使用

创建 extension pg_wait_sampling pg_stat_statements

# su - postgres
$ psql

postgres=# create extension pg_stat_statements;
postgres=# create extension pg_wait_sampling;
postgres=# 
postgres=# \dx
                                         List of installed extensions
        Name        | Version |   Schema   |                            Description                            
--------------------+---------+------------+-------------------------------------------------------------------
 pg_stat_statements | 1.6     | public     | track execution statistics of all SQL statements executed
 pg_wait_sampling   | 1.1     | public     | sampling based statistics of wait events
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=# \dx+ pg_wait_sampling

    Objects in extension "pg_wait_sampling"
               Object description               
------------------------------------------------
 function pg_wait_sampling_get_current(integer)
 function pg_wait_sampling_get_history()
 function pg_wait_sampling_get_profile()
 function pg_wait_sampling_reset_profile()
 view pg_wait_sampling_current
 view pg_wait_sampling_history
 view pg_wait_sampling_profile
(7 rows)

postgres=# select name,setting from pg_settings where name like 'pg_wait_sampling%';
               name               | setting 
----------------------------------+---------
 pg_wait_sampling.history_period  | 10
 pg_wait_sampling.history_size    | 5000
 pg_wait_sampling.profile_period  | 10
 pg_wait_sampling.profile_pid     | on
 pg_wait_sampling.profile_queries | on
(5 rows)

开个 session 做 pgbench 压测

# su - postgres
$ pgbench -r -j 2 -c 4 -T 60 -P 1 -h localhost -p 5432 pgbenchdb

查看状态

postgres=# \timing

postgres=# select * from pg_wait_sampling_current;
 pid  | event_type |        event        | queryid 
------+------------+---------------------+---------
 8207 | LWLock     | WALWriteLock        |       0
 8202 | Lock       | transactionid       |    7304
 8009 | Activity   | AutoVacuumMain      |       0
 8012 | Activity   | LogicalLauncherMain |       0
 8011 | Extension  | Extension           |       0
 8006 | Activity   | CheckpointerMain    |       0
 8007 | Activity   | BgWriterMain        |       0
 8008 | Activity   | WalWriterMain       |       0
(8 rows)

Time: 0.485 ms

参考：
https://github.com/postgrespro/pg_wait_sampling/