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

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。

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=# select pg_backend_pid();
pg_backend_pid

(1 row)

postgres=# update t_hintbit set c1=1;
```

观察profile

```

% time seconds wait_event query: update t_b set info='test' ;