PostgreSQL pgcenter - 采样、统计、性能诊断、profile、cli小工具

postgres=# \dv pg_stat*  
                     List of relations  
   Schema   |            Name             | Type |  Owner     
------------+-----------------------------+------+----------  
 pg_catalog | pg_stat_activity            | view | postgres  
 pg_catalog | pg_stat_all_indexes         | view | postgres  
 pg_catalog | pg_stat_all_tables          | view | postgres  
 pg_catalog | pg_stat_archiver            | view | postgres  
 pg_catalog | pg_stat_bgwriter            | view | postgres  
 pg_catalog | pg_stat_database            | view | postgres  
 pg_catalog | pg_stat_database_conflicts  | view | postgres  
 pg_catalog | pg_stat_progress_vacuum     | view | postgres  
 pg_catalog | pg_stat_replication         | view | postgres  
 pg_catalog | pg_stat_ssl                 | view | postgres  
 pg_catalog | pg_stat_subscription        | view | postgres  
 pg_catalog | pg_stat_sys_indexes         | view | postgres  
 pg_catalog | pg_stat_sys_tables          | view | postgres  
 pg_catalog | pg_stat_user_functions      | view | postgres  
 pg_catalog | pg_stat_user_indexes        | view | postgres  
 pg_catalog | pg_stat_user_tables         | view | postgres  
 pg_catalog | pg_stat_wal_receiver        | view | postgres  
 pg_catalog | pg_stat_xact_all_tables     | view | postgres  
 pg_catalog | pg_stat_xact_sys_tables     | view | postgres  
 pg_catalog | pg_stat_xact_user_functions | view | postgres  
 pg_catalog | pg_stat_xact_user_tables    | view | postgres  
 pg_catalog | pg_statio_all_indexes       | view | postgres  
 pg_catalog | pg_statio_all_sequences     | view | postgres  
 pg_catalog | pg_statio_all_tables        | view | postgres  
 pg_catalog | pg_statio_sys_indexes       | view | postgres  
 pg_catalog | pg_statio_sys_sequences     | view | postgres  
 pg_catalog | pg_statio_sys_tables        | view | postgres  
 pg_catalog | pg_statio_user_indexes      | view | postgres  
 pg_catalog | pg_statio_user_sequences    | view | postgres  
 pg_catalog | pg_statio_user_tables       | view | postgres  
 pg_catalog | pg_stats                    | view | postgres  
 public     | pg_stat_statements          | view | postgres  
(32 rows)

要了解PG，有各自的手段。

pgcenter是本文主角：

digoal@pg11-test-> pgcenter --help  
pgCenter is a command line admin tool for PostgreSQL.  
  
Usage:  
  pgcenter [flags]  
  pgcenter [command] [command-flags] [args]  
  
Available commands:  
  config        configures Postgres to work with pgcenter  
  profile       wait events profiler  
  record        record stats to file  
  report        make report based on previously saved statistics  
  top           top-like stats viewer  
  
Flags:  
  -?, --help            show this help and exit  
      --version         show version information and exit  
  
Use "pgcenter [command] --help" for more information about a command.  
  
Report bugs to https://github.com/lesovsky/pgcenter/issues

它可以

1、观察LONG QUERY，或者指定有问题数据库BACKEND PID进程的profile。

2、给数据库的统计信息打快照，并根据不同维度生成报告。

  record        record stats to file  
  report        make report based on previously saved statistics  
  
digoal@pg11-test-> pgcenter report --help  
'pgcenter report' reads statistics from file and prints reports.  
  
Usage:  
  pgcenter report [OPTIONS]...  
  
Options:  
  -f, --file                    read stats from file (default: pgcenter.stat.tar)  
  -s, --start                   starting time of the report (format: [YYYYMMDD-]HHMMSS)  
  -e, --end                     ending time of the report (format: [YYYYMMDD-]HHMMSS)  
  -o, --order                   order values by column (default descending, use '+' sign before a column name for ascending order)  
  -g, --grep                    filter values in specfied column (format: colname:filtertext)  
  -l, --limit                   print only limited number of rows per sample (default: unlimited)  
  -t, --truncate                maximum string size to print (default: 32)  
  -i, --interval                delta interval (default: 1s)  
  
Report options:  
  -A, --activity                show pg_stat_activity statistics  
  -S, --sizes                   show statistics about tables sizes  
  -D, --databases               show pg_stat_database statistics  
  -F, --functions               show pg_stat_user_functions statistics  
  -R, --replication             show pg_stat_replication statistics  
  -T, --tables                  show pg_stat_user_tables statistics  
  -I, --indexes                 show pg_stat_user_indexes and pg_statio_user_indexes statistics  
  -V, --vacuum                  show pg_stat_progress_vacuum statistics  
  -X, --statements [X]          show pg_stat_statements statistics, use additional selector to choose stats.  
                                'm' - timings; 'g' - general; 'i' - io; 't' - temp files io; 'l' - local files io.   
  
  -d, --describe                show statistics description, combined with one of the report options  
  
General options:  
  -?, --help            show this help and exit  
      --version         show version information and exit  
  
Report bugs to https://github.com/lesovsky/pgcenter/issues

digoal@pg11-test-> pgcenter report -A -d  
Activity statistics based on pg_stat_activity view:  
  
  column        origin                  description  
- pid           pid                     Process ID of this backend  
- cl_addr       client_addr             IP address of the client connected to this backend  
- cl_port       client_port             TCP port number that the client is using for communication with this backend  
- datname       datname                 Name of the database this backend is connected to  
- usename       usename                 Name of the user logged into this backend  
- appname       application_name        Name of the application that is connected to this backend  
- backend_type  backend_type            Type of current backend  
- wait_etype    wait_event_type         The type of event for which the backend is waiting, if any  
- wait_event    wait_event              Wait event name if backend is currently waiting  
- state         state                   Current overall state of this backend  
- xact_age*     xact_start              Current transaction's duration if active  
- query_age*    query_start             Current query's duration if active  
- change_age*   state_change            Age since last state has been changed  
- query         query                   Text of this backend's most recent query  
  
* - extended value, based on origin and calculated using additional functions.  
  
Details: https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW  
digoal@pg11-test-> pgcenter report -S -d  
Statistics about sizes of tables based on pg_*_size() functions:  
  
  column        origin  description  
- relation      -       Name of the table, including schema  
- total_size    -       Total size of the table, including its indexes, in kB  
- rel_size      -       Total size of the table, without its indexes, in kB  
- idx_size      -       Total size of tables' indexes, in kB  
- total_change  -       How does size of the table, including its indexes, is changing per second, in kB  
- rel_change    -       How does size of the table, without its indexes, is changing per second, in kB  
- idx_change    -       How does size of the tables' indexes is changing per second, in kB  
  
* - extended value, based on origin and calculated using additional functions.  
  
Details: https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT  
  
  
digoal@pg11-test-> pgcenter report -V -d  
Statistics about progress of vacuums based on pg_stat_progress_vacuum view:  
  
  column        origin                  description  
- pid           pid                     Process ID of this worker  
- xact_age*     xact_start              Current transaction's duration if active  
- datname       datname                 Name of the database this worker is connected to  
- relation      relid                   Name of the relation which is vacuumed by this worker  
- state         state                   Current overall state of this worker  
- phase         phase                   Current processing phase of vacuum  
- total*        heap_blks_total         Total size of the table, in kB  
- t_scanned*    heap_blks_scanned       Total amount of data scanned, in kB  
- t_vacuumed*   heap_blks_vacuumed      Total amount of data vacuumed, in kB  
- scanned       heap_blks_scanned       Amount of data scanned per second, in kB  
- vacuumed      heap_blks_vacuumed      Amount of data vacuumed per second, in kB  
- wait_etype    wait_event_type         The type of event for which the worker is waiting, if any  
- wait_event    wait_event              Wait event name if worker is currently waiting  
- query         query                   Text of this workers's "query"  
  
* - extended value, based on origin and calculated using additional functions.  
  
Details: https://www.postgresql.org/docs/current/progress-reporting.html#VACUUM-PROGRESS-REPORTING

3、查看数据库实时TOP 情况

  top           top-like stats viewer

pgcenter 用法

centos 7 x64为例

源码安装

yum install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm  
  
yum install -y golang  
  
git clone https://github.com/lesovsky/pgcenter  
  
cd pgcenter  
  
digoal@pg11-test-> which go  
/bin/go  
  
digoal@pg11-test-> which pg_config  
~/pgsql11.1/bin/pg_config  
  
USE_PGXS=1 make  
USE_PGXS=1 make install

rpm安装

https://github.com/lesovsky/pgcenter/releases  
  
wget https://github.com/lesovsky/pgcenter/releases/download/v0.6.1/pgcenter_0.6.1_Linux_x86_64.rpm  
  
rpm -ivh pgcenter_0.6.1_Linux_x86_64.rpm   
  
[root@pg11-test ~]# rpm -ql pgcenter  
/usr/bin/pgcenter

举例: 使用pgcenter观察问题PID或者当前某个慢SQL的等待事件

使用帮助

https://github.com/lesovsky/pgcenter/blob/master/doc/examples.md

1、找到当前慢SQL，以及对应的PID。

postgres=# select pid, now()-query_start during, query, wait_event_type, wait_event   
           from pg_stat_activity   
           where wait_event is not null order by query_start limit 1;  
  pid  |     during      |         query         | wait_event_type | wait_event   
-------+-----------------+-----------------------+-----------------+------------  
 21207 | 00:00:28.975778 | select pg_sleep(100); | Timeout         | PgSleep  
(1 row)

2、使用pgcenter profile跟踪这个PID。

pgcenter跟踪PID时，需要给出一个采样频率（每秒采样多少次），输出的是该PID下面每条执行完后对这个QUERY的等待时间占比统计。

digoal@pg11-test-> pgcenter profile --help  
'pgcenter profile' profiles wait events of running queries  
  
Usage:  
  pgcenter profile [OPTIONS]... [DBNAME [USERNAME]]  
  
Options:  
  -d, --dbname DBNAME           database name to connect to  
  -h, --host HOSTNAME           database server host or socket directory.  
  -p, --port PORT               database server port (default 5432)  
  -U, --username USERNAME       database user name  
  
  -P, --pid PID                 backend PID to profile to  
  -F, --freq FREQ               profile at this frequency (min 1, max 1000)  
  -s, --strsize SIZE            limit length of print query strings to STRSIZE chars (default 128)  
  
General options:  
  -?, --help            show this help and exit  
      --version         show version information and exit  
  
Report bugs to https://github.com/lesovsky/pgcenter/issues

跟踪，例如每秒采样10次等待事件，每次间隔100毫秒。

pgcenter profile -h 127.0.0.1 -p 8001 -U postgres -d postgres -P 42616 -F 10  
  
LOG: Profiling process 42616 with 100ms sampling

3、制造LONG QUERY

postgres=# \d t_hintbit  
                             Table "public.t_hintbit"  
 Column |   Type   | Collation | Nullable |                Default                  
--------+----------+-----------+----------+---------------------------------------  
 id     | bigint   |           | not null | nextval('t_hintbit_id_seq'::regclass)  
 c1     | smallint |           |          |   
Indexes:  
    "t_hintbit_pkey" PRIMARY KEY, btree (id)  
  
postgres=# select pg_backend_pid();  
 pg_backend_pid   
----------------  
          42616  
(1 row)  
  
postgres=# update t_hintbit set c1=1;

观察profile

------ ------------ -----------------------------  
% time      seconds wait_event                     query: update t_b set info='test' ;  
------ ------------ -----------------------------  
97.90     47.239459 Running  
1.47       0.707298 IO.DataFileExtend  
0.63       0.304460 IO.DataFileRead  
------ ------------ -----------------------------  
100.00    48.251217  
------ ------------ -----------------------------  
% time      seconds wait_event                     query: update t_b set info='test' ;  
------ ------------ -----------------------------  
87.35     25.146099 Running  
9.47       2.727026 IO.DataFileExtend  
3.16       0.909462 LWLock.WALWriteLock  
------ ------------ -----------------------------  
99.98     28.782587