Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use PostgreSQL's plug-in pgmetries". In daily operation, I believe many people have doubts about how to use PostgreSQL's plug-in pgmetries. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions of "how to use PostgreSQL's plug-in pgmetries". Next, please follow the editor to study!

Strictly speaking, pgmetries cannot be a plug-in and is a tool for statistical pg databases.

Installation

Installation is very simple, download the corresponding binary file on github, decompress it and use it.

[pg12@localhost software] $cd pgmetrics_1.7.1_linux_ AMD64 [pg12 @ localhost pgmetrics_1.7.1_linux_amd64] $lsLICENSE pgmetrics README.md [pg12@localhost pgmetrics_1.7.1_linux_amd64] $[pg12@localhost pgmetrics_1.7.1_linux_amd64] $. / pgmetrics-- helppgmetrics 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 exitCollection 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 toolong (default: 60)-o,-- output=FILE write output to the specified file-- no-pager do not invoke the pager for tty outputConnection 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 username (default: "pg12")-w,-- no-password never prompt for passwordFor more information, visit.

Experience

Execute pgmetries, enter password

[pg12@localhost pgmetrics_1.7.1_linux_amd64] $. / pgmetrics-h localhost db1Password: [pg12@localhost pgmetrics_1.7.1_linux_amd64] $. / pgmetrics-h localhost-U pg12 db1Password: pgmetrics run at: 19 Dec 2019 5:24:20 PM (1 second ago)

This is the summary of the database Cluster

PostgreSQL Cluster: Name: Server Version: 12.1-> version Server Started: 19 Dec 2019 5:03:01 PM (21 minutes ago)-> start time System Identifier: 6761668844610171990-> Database ID Timeline: 1 -- > timeline Last Checkpoint: 19 Dec 2019 5:18:00 PM (6 minutes ago)-- > Last checkpoint time REDO LSN: 1A/8CF526B8-- > LSN Checkpoint LSN: 1A/906502D0 (55 MiB since REDO)-- > LSN Transaction IDs of checkpoint: 479to 117837591 (diff = 117837112)-- > transaction ID Diff is age Notification Queue: 0. 0% Used Active Backends: 14 (max 100)-- > active session count Recovery Mode? No-- > production library

Host information

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 file information

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 (GiB) | | checkpoint_timeout | 900 | | full_page_writes | on | | wal_keep_segments | 0 | +-| -+ + `

Background BG Writer process

Average write size of 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-- > Writing Statistics 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 | +-| -+

Current background process (Session) information

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 | +-| -+

Lock information

Locks: +-+ | Lock Type | Not Granted | Total | +-+ | relation | 0 | 20 | | transactionid | 0 | 8 | | virtualxid | 0 | 10 | +-+ | | 0 | 38 | +-+

Vacuum process Information

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 | +-+ +

Role

Roles: +-- -+ | Name | Login | Repl | Super | Creat Rol | Creat DB | Bypass RLS | Inherit | Expires | Member Of | +- -+- -- + | pg12 | yes | | | | pg_monitor | yes | | pg_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 | + -+- -+

Tablespace information

Tablespaces: +-- +-- + -- + | Name | Owner | Location | Size | Disk Used | Inode 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 110GiB | 237976 (0.4%) of 57569280 | +-- +-- +- -+

Database information

Database # 1: Name: postgres Owner: pg12 Tablespace: pg_default Connections: 0 (no max limit) Frozen Xid Age: 121949293 Transactions: 0 (0%) commits, 0 (0%) rollbacks Cache Hits: 0% Rows Changed: ins 0%, upd 0% Del 0.0% Total Temp: 0B in 0 files Problems: 0 deadlocks 0 conflicts Totals Since: Size: 7.6MiBDatabase # 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.02% Total Temp: 0B 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: 0B in 0 files Problems: 0 deadlocks 0 conflicts Totals Since: 19 Dec 2019 4:51:16 PM (33 minutes ago) Size: 283 MiBDatabase # 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: 0B in 0 files Problems: 0 deadlocks 0 conflicts Totals Since: 19 Dec 2019 4:51:29 PM (32 minutes ago) Size: 9.2 MiBDatabase # 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: 0B in 0 files Problems: 0 deadlocks 0 conflicts Totals Since: 19 Dec 2019 4:55:12 PM (29 minutes ago) Size: 2.8 GiBTable # 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 Fetched/Scan | +- -+-+ | t_autovacuum_db1_pkey | btree | 456 KiB | 296 KiB (64.9%) | 100.0% | 6715855 | 1.0 | +-+- +-+ (END) [pg12@localhost pgmetrics_1.7.1_linux_amd64] $

Future development

It is believed that most people have experienced the physical examination in the hospital, and the result index and normal range index are listed in the physical examination report, and then the qualitative high + / low-is given. It is a direction to improve pgmetrics according to this train of thought.

At this point, the study on "how to use PostgreSQL's plug-in pgmetries" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.