本文简单介绍了PostgreSQL的插件：pgmetries。严格上来说，pgmetries不能成为插件，是一个用于统计pg数据库的一个工具。 **安装** 安装很简单，在[github](https://github.com/rapidloop/pgmetrics/releases)上下载相应的binary文件，解压即可使用。 ``` [pg12@localhost software]$ cd pgmetrics_1.7.1_linux_amd64 [pg12@localhost pgmetrics_1.7.1_linux_amd64]$ ls LICENSE pgmetrics README.md [pg12@localhost pgmetrics_1.7.1_linux_amd64]$ [pg12@localhost pgmetrics_1.7.1_linux_amd64]$ ./pgmetrics --help pgmetrics collects PostgreSQL information and metrics. Usage: pgmetrics [OPTION]... [DBNAME] General options: -t, --timeout=SECS individual query timeout in seconds (default: 5) -i, --input=FILE don't connect to db, instead read and display this previously saved JSON file -V, --version output version information, then exit -?, --help[=options] show this help, then exit --help=variables list environment variables, then exit Collection options: -S, --no-sizes don't collect tablespace and relation sizes -c, --schema=REGEXP collect only from schema(s) matching POSIX regexp -C, --exclude-schema=REGEXP do NOT collect from schema(s) matching POSIX regexp -a, --table=REGEXP collect only from table(s) matching POSIX regexp -A, --exclude-table=REGEXP do NOT collect from table(s) matching POSIX regexp --omit=WHAT do NOT collect the items specified as a comma-separated list of: "tables", "indexes", "sequences", "functions", "extensions", "triggers", "statements" --sql-length=LIMIT collect only first LIMIT characters of all SQL queries (default: 500) --statements-limit=LIMIT collect only utmost LIMIT number of row from pg_stat_statements (default: 100) --only-listed collect info only about the databases listed as command-line args (use with Heroku) Output options: -f, --format=FORMAT output format; "human", "json" or "csv" (default: "human") -l, --toolong=SECS for human output, transactions running longer than this are considered too long (default: 60) -o, --output=FILE write output to the specified file --no-pager do not invoke the pager for tty output Connection options: -h, --host=HOSTNAME database server host or socket directory (default: "/data/run/pg12") -p, --port=PORT database server port (default: 5120) -U, --username=USERNAME database user name (default: "pg12") -w, --no-password never prompt for password For more information, visit <https://pgmetrics.io>. ``` **体验** 执行pgmetries，输入password ``` [pg12@localhost pgmetrics_1.7.1_linux_amd64]$ ./pgmetrics -h localhost db1 Password: [pg12@localhost pgmetrics_1.7.1_linux_amd64]$ ./pgmetrics -h localhost -U pg12 db1 Password: pgmetrics run at: 19 Dec 2019 5:24:20 PM (1 second ago) ``` 这是数据库Cluster的概要信息 ``` PostgreSQL Cluster: Name: Server Version: 12.1 -->版本 Server Started: 19 Dec 2019 5:03:01 PM (21 minutes ago) -->启动时间 System Identifier: 6761668844610171990 -->数据库ID Timeline: 1 -->时间线 Last Checkpoint: 19 Dec 2019 5:18:00 PM (6 minutes ago) -->最后一次checkpoint时间 REDO LSN: 1A/8CF526B8 -->LSN Checkpoint LSN: 1A/906502D0 (55 MiB since REDO) -->checkpoint的LSN Transaction IDs: 479 to 117837591 (diff = 117837112) -->事务ID,diff是age Notification Queue: 0.0% Used Active Backends: 14 (max 100) -->活动会话计数 Recovery Mode? no -->生产库 ``` 主机信息 ``` System Information: Hostname: localhost.localdomain CPU Cores: 4 x Intel(R) Core(TM) i7-4790 CPU @ 3.60GHz Load Average: 7.28 Memory: used=332 MiB, free=121 MiB, buff=8.0 KiB, cache=3.3 GiB Swap: used=4.5 MiB, free=1020 MiB +---------------------------------+------------------+ | Setting | Value | +---------------------------------+------------------+ | shared_buffers | 262144 (2.0 GiB) | | work_mem | 4096 (4.0 MiB) | | maintenance_work_mem | 65536 (64 MiB) | | temp_buffers | 1024 (8.0 MiB) | | autovacuum_work_mem | -1 | | temp_file_limit | -1 | | max_worker_processes | 8 | | autovacuum_max_workers | 5 | | max_parallel_workers_per_gather | 2 | | effective_io_concurrency | 1 | +---------------------------------+------------------+ ``` WAL文件信息 ``` WAL Files: WAL Archiving? no WAL Files: 79 +--------------------+----------------+ | Setting | Value | +--------------------+----------------+ | wal_level | replica | | archive_timeout | 0 | | wal_compression | off | | max_wal_size | 8192 (128 GiB) | | min_wal_size | 128 (2.0 GiB) | | checkpoint_timeout | 900 | | full_page_writes | on | | wal_keep_segments | 0 | +--------------------+----------------+ ```` 后台BG Writer进程 ``` BG Writer: Checkpoint Rate: 0.02 per min Average Write: 3.3 MiB per checkpoint -->checkpoint的平均写入大小 Total Checkpoints: 1 sched (100.0%) + 0 req (0.0%) = 1 Total Write: 3.3 MiB, @ 1.3 KiB per sec -->写入统计 Buffers Allocated: 2547 (20 MiB) Buffers Written: 421 chkpt (100.0%) + 0 bgw (0.0%) + 0 be (0.0%) Clean Scan Stops: 0 BE fsyncs: 0 Counts Since: 19 Dec 2019 4:42:21 PM (42 minutes ago) +------------------------------+--------------+ | Setting | Value | +------------------------------+--------------+ | bgwriter_delay | 200 msec | | bgwriter_flush_after | 64 (512 KiB) | | bgwriter_lru_maxpages | 100 | | bgwriter_lru_multiplier | 2 | | block_size | 8192 | | checkpoint_timeout | 900 sec | | checkpoint_completion_target | 0.9 | +------------------------------+--------------+ ``` 当前后台进程(Session)信息 ``` Backends: Total Backends: 14 (14.0% of max 100) Problematic: 0 waiting on locks, 13 waiting on other, 0 xact too long, 0 idle in xact Other Waiting Backends: +------+------+---------+-------------+----------+-----------------------+------------------------+ | PID | User | App | Client Addr | Database | Wait | Query Start | +------+------+---------+-------------+----------+-----------------------+------------------------+ | 5459 | pg12 | pgbench | | db1 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM | | 5460 | pg12 | pgbench | | db1 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM | | 5461 | pg12 | pgbench | | db1 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM | | 5462 | pg12 | pgbench | | db1 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM | | 5480 | pg12 | pgbench | | db2 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM | | 5481 | pg12 | pgbench | | db2 | Client / ClientRead | 19 Dec 2019 5:24:21 PM | | 5482 | pg12 | pgbench | | db2 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM | | 5483 | pg12 | pgbench | | db2 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM | | 5505 | pg12 | pgbench | | db3 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM | | 5506 | pg12 | pgbench | | db3 | IO / WALSync | 19 Dec 2019 5:24:21 PM | | 5507 | pg12 | pgbench | | db3 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM | | 5508 | pg12 | pgbench | | db3 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM | | 9009 | pg12 | psql | | db1 | Client / ClientRead | 19 Dec 2019 5:16:46 PM | +------+------+---------+-------------+----------+-----------------------+------------------------+ ``` 锁信息 ``` Locks: +---------------+-------------+-------+ | Lock Type | Not Granted | Total | +---------------+-------------+-------+ | relation | 0 | 20 | | transactionid | 0 | 8 | | virtualxid | 0 | 10 | +---------------+-------------+-------+ | | 0 | 38 | +---------------+-------------+-------+ ``` Vacuum进程信息 ``` Vacuum Progress: No manual or auto vacuum jobs in progress. +------------------------------+----------------+ | Setting | Value | +------------------------------+----------------+ | maintenance_work_mem | 65536 (64 MiB) | | autovacuum | on | | autovacuum_analyze_threshold | 50 | | autovacuum_vacuum_threshold | 50 | | autovacuum_freeze_max_age | 200000000 | | autovacuum_max_workers | 5 | | autovacuum_naptime | 60 sec | | vacuum_freeze_min_age | 50000000 | | vacuum_freeze_table_age | 150000000 | +------------------------------+----------------+ ``` 角色 ``` Roles: +---------------------------+-------+------+-------+-----------+----------+------------+---------+---------+-- ------------------------------------------------------------+ | Name | Login | Repl | Super | Creat Rol | Creat DB | Bypass RLS | Inherit | Expires | Member Of | +---------------------------+-------+------+-------+-----------+----------+------------+---------+---------+-- ------------------------------------------------------------+ | pg12 | yes | yes | yes | yes | yes | yes | yes | | | | pg_monitor | | | | | | | yes | | p g_read_all_settings, pg_read_all_stats, pg_stat_scan_tables | | pg_read_all_settings | | | | | | | yes | | | | pg_read_all_stats | | | | | | | yes | | | | pg_stat_scan_tables | | | | | | | yes | | | | pg_signal_backend | | | | | | | yes | | | | pg_read_server_files | | | | | | | yes | | | | pg_write_server_files | | | | | | | yes | | | | pg_execute_server_program | | | | | | | yes | | | +---------------------------+-------+------+-------+-----------+----------+------------+---------+---------+-- ------------------------------------------------------------+ ``` 表空间信息 ``` Tablespaces: +------------+-------+-------------------------------+---------+---------------------------+------------------ ---------+ | Name | Owner | Location | Size | Disk Used | In ode Used | +------------+-------+-------------------------------+---------+---------------------------+------------------ ---------+ | pg_default | pg12 | $PGDATA = /data/pgsql/pg121db | 3.1 GiB | 62 GiB (56.8%) of 110 GiB | 237976 (0.4%) of 57569280 | | pg_global | pg12 | $PGDATA = /data/pgsql/pg121db | 398 KiB | 62 GiB (56.8%) of 110 GiB | 237976 (0.4%) of 57569280 | +------------+-------+-------------------------------+---------+---------------------------+------------------ ---------+ ``` 数据库信息 ``` Database #1: Name: postgres Owner: pg12 Tablespace: pg_default Connections: 0 (no max limit) Frozen Xid Age: 121949293 Transactions: 0 (0.0%) commits, 0 (0.0%) rollbacks Cache Hits: 0.0% Rows Changed: ins 0.0%, upd 0.0%, del 0.0% Total Temp: 0 B in 0 files Problems: 0 deadlocks, 0 conflicts Totals Since: Size: 7.6 MiB Database #2: Name: db1 Owner: pg12 Tablespace: pg_default Connections: 6 (no max limit) Frozen Xid Age: 121949293 Transactions: 6716016 (100.0%) commits, 1 (0.0%) rollbacks Cache Hits: 100.0% Rows Changed: ins 0.0%, upd 100.0%, del 0.0% Total Temp: 0 B in 0 files Problems: 0 deadlocks, 0 conflicts Totals Since: 19 Dec 2019 4:49:55 PM (34 minutes ago) Size: 8.9 MiB Installed Extensions: +---------+---------+------------------------------+ | Name | Version | Comment | +---------+---------+------------------------------+ | plpgsql | 1.0 | PL/pgSQL procedural language | +---------+---------+------------------------------+ Database #3: Name: db2 Owner: pg12 Tablespace: pg_default Connections: 4 (no max limit) Frozen Xid Age: 121949293 Transactions: 6691569 (100.0%) commits, 0 (0.0%) rollbacks Cache Hits: 100.0% Rows Changed: ins 0.0%, upd 100.0%, del 0.0% Total Temp: 0 B in 0 files Problems: 0 deadlocks, 0 conflicts Totals Since: 19 Dec 2019 4:51:16 PM (33 minutes ago) Size: 283 MiB Database #4: Name: db3 Owner: pg12 Tablespace: pg_default Connections: 4 (no max limit) Frozen Xid Age: 121949293 Transactions: 6675090 (100.0%) commits, 0 (0.0%) rollbacks Cache Hits: 100.0% Rows Changed: ins 0.0%, upd 100.0%, del 0.0% Total Temp: 0 B in 0 files Problems: 0 deadlocks, 0 conflicts Totals Since: 19 Dec 2019 4:51:29 PM (32 minutes ago) Size: 9.2 MiB Database #5: Name: testdb Owner: pg12 Tablespace: pg_default Connections: 0 (no max limit) Frozen Xid Age: 121949293 Transactions: 2533 (100.0%) commits, 1 (0.0%) rollbacks Cache Hits: 99.9% Rows Changed: ins 0.0%, upd 0.0%, del 0.0% Total Temp: 0 B in 0 files Problems: 0 deadlocks, 0 conflicts Totals Since: 19 Dec 2019 4:55:12 PM (29 minutes ago) Size: 2.8 GiB Table #1 in "db1": Name: db1.public.t_autovacuum_db1 Columns: 1 Manual Vacuums: never Manual Analyze: never Auto Vacuums: never Auto Analyze: 31, last 20 seconds ago Post-Analyze: 641.1% est. rows modified Row Estimate: 83.4% live of total 11986 Rows Changed: ins 0.0%, upd 100.0%, del 0.0% HOT Updates: 100.0% of all updates Seq Scans: 0, 0.0 rows/scan Idx Scans: 6715855, 1.0 rows/scan Cache Hits: 100.0% (idx=100.0%) Size: 664 KiB Bloat: 272 KiB (41.0%) +-----------------------+-------+---------+-----------------+------------+---------+----------------+--------- ----------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fet ched/Scan | +-----------------------+-------+---------+-----------------+------------+---------+----------------+--------- ----------+ | t_autovacuum_db1_pkey | btree | 456 KiB | 296 KiB (64.9%) | 100.0% | 6715855 | 1.0 | 1.0 | +-----------------------+-------+---------+-----------------+------------+---------+----------------+--------- ----------+ (END) [pg12@localhost pgmetrics_1.7.1_linux_amd64]$ ``` **未来发展** 相信绝大多数人都经历过医院的体检,体检报告中列出结果指标和正常范围值指标,然后给出定性的偏高+/偏低-,按此思路来改进pgmetrics不失为一个方向. **参考资料** [pgmetrics](https://pgmetrics.io/)