这篇文章主要介绍“怎么使用PostgreSQL的插件pgmetries”，在日常操作中，相信很多人在怎么使用PostgreSQL的插件pgmetries问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”怎么使用PostgreSQL的插件pgmetries”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

严格上来说，pgmetries不能成为插件，是一个用于统计pg数据库的一个工具。

安装
安装很简单，在github上下载相应的binary文件，解压即可使用。

[pg12@localhostsoftware]$cdpgmetrics_1.7.1_linux_amd64[pg12@localhostpgmetrics_1.7.1_linux_amd64]$lsLICENSEpgmetricsREADME.md[pg12@localhostpgmetrics_1.7.1_linux_amd64]$[pg12@localhostpgmetrics_1.7.1_linux_amd64]$./pgmetrics--helppgmetricscollectsPostgreSQLinformationandmetrics.Usage:pgmetrics[OPTION]...[DBNAME]Generaloptions:-t,--timeout=SECSindividualquerytimeoutinseconds(default:5)-i,--input=FILEdon'tconnecttodb,insteadreadanddisplaythispreviouslysavedJSONfile-V,--versionoutputversioninformation,thenexit-?,--help[=options]showthishelp,thenexit--help=variableslistenvironmentvariables,thenexitCollectionoptions:-S,--no-sizesdon'tcollecttablespaceandrelationsizes-c,--schema=REGEXPcollectonlyfromschema(s)matchingPOSIXregexp-C,--exclude-schema=REGEXPdoNOTcollectfromschema(s)matchingPOSIXregexp-a,--table=REGEXPcollectonlyfromtable(s)matchingPOSIXregexp-A,--exclude-table=REGEXPdoNOTcollectfromtable(s)matchingPOSIXregexp--omit=WHATdoNOTcollecttheitemsspecifiedasacomma-separatedlistof:"tables","indexes","sequences","functions","extensions","triggers","statements"--sql-length=LIMITcollectonlyfirstLIMITcharactersofallSQLqueries(default:500)--statements-limit=LIMITcollectonlyutmostLIMITnumberofrowfrompg_stat_statements(default:100)--only-listedcollectinfoonlyaboutthedatabaseslistedascommand-lineargs(usewithHeroku)Outputoptions:-f,--format=FORMAToutputformat;"human","json"or"csv"(default:"human")-l,--toolong=SECSforhumanoutput,transactionsrunninglongerthanthisareconsideredtoolong(default:60)-o,--output=FILEwriteoutputtothespecifiedfile--no-pagerdonotinvokethepagerforttyoutputConnectionoptions:-h,--host=HOSTNAMEdatabaseserverhostorsocketdirectory(default:"/data/run/pg12")-p,--port=PORTdatabaseserverport(default:5120)-U,--username=USERNAMEdatabaseusername(default:"pg12")-w,--no-passwordneverpromptforpasswordFormoreinformation,visit<https://pgmetrics.io>.

体验
执行pgmetries，输入password

[pg12@localhostpgmetrics_1.7.1_linux_amd64]$./pgmetrics-hlocalhostdb1Password:[pg12@localhostpgmetrics_1.7.1_linux_amd64]$./pgmetrics-hlocalhost-Upg12db1Password:pgmetricsrunat:19Dec20195:24:20PM(1secondago)

这是数据库Cluster的概要信息

PostgreSQLCluster:Name:ServerVersion:12.1-->版本ServerStarted:19Dec20195:03:01PM(21minutesago)-->启动时间SystemIdentifier:6761668844610171990-->数据库IDTimeline:1-->时间线LastCheckpoint:19Dec20195:18:00PM(6minutesago)-->最后一次checkpoint时间REDOLSN:1A/8CF526B8-->LSNCheckpointLSN:1A/906502D0(55MiBsinceREDO)-->checkpoint的LSNTransactionIDs:479to117837591(diff=117837112)-->事务ID,diff是ageNotificationQueue:0.0%UsedActiveBackends:14(max100)-->活动会话计数RecoveryMode?no-->生产库

主机信息

SystemInformation:Hostname:localhost.localdomainCPUCores:4xIntel(R)Core(TM)i7-4790CPU@3.60GHzLoadAverage:7.28Memory:used=332MiB,free=121MiB,buff=8.0KiB,cache=3.3GiBSwap:used=4.5MiB,free=1020MiB+---------------------------------+------------------+|Setting|Value|+---------------------------------+------------------+|shared_buffers|262144(2.0GiB)||work_mem|4096(4.0MiB)||maintenance_work_mem|65536(64MiB)||temp_buffers|1024(8.0MiB)||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文件信息

WALFiles:WALArchiving?noWALFiles:79+--------------------+----------------+|Setting|Value|+--------------------+----------------+|wal_level|replica||archive_timeout|0||wal_compression|off||max_wal_size|8192(128GiB)||min_wal_size|128(2.0GiB)||checkpoint_timeout|900||full_page_writes|on||wal_keep_segments|0|+--------------------+----------------+`

后台BG Writer进程

BGWriter:CheckpointRate:0.02perminAverageWrite:3.3MiBpercheckpoint-->checkpoint的平均写入大小TotalCheckpoints:1sched(100.0%)+0req(0.0%)=1TotalWrite:3.3MiB,@1.3KiBpersec-->写入统计BuffersAllocated:2547(20MiB)BuffersWritten:421chkpt(100.0%)+0bgw(0.0%)+0be(0.0%)CleanScanStops:0BEfsyncs:0CountsSince:19Dec20194:42:21PM(42minutesago)+------------------------------+--------------+|Setting|Value|+------------------------------+--------------+|bgwriter_delay|200msec||bgwriter_flush_after|64(512KiB)||bgwriter_lru_maxpages|100||bgwriter_lru_multiplier|2||block_size|8192||checkpoint_timeout|900sec||checkpoint_completion_target|0.9|+------------------------------+--------------+

当前后台进程(Session)信息

Backends:TotalBackends:14(14.0%ofmax100)Problematic:0waitingonlocks,13waitingonother,0xacttoolong,0idleinxactOtherWaitingBackends:+------+------+---------+-------------+----------+-----------------------+------------------------+|PID|User|App|ClientAddr|Database|Wait|QueryStart|+------+------+---------+-------------+----------+-----------------------+------------------------+|5459|pg12|pgbench||db1|LWLock/WALWriteLock|19Dec20195:24:21PM||5460|pg12|pgbench||db1|LWLock/WALWriteLock|19Dec20195:24:21PM||5461|pg12|pgbench||db1|LWLock/WALWriteLock|19Dec20195:24:21PM||5462|pg12|pgbench||db1|LWLock/WALWriteLock|19Dec20195:24:21PM||5480|pg12|pgbench||db2|LWLock/WALWriteLock|19Dec20195:24:21PM||5481|pg12|pgbench||db2|Client/ClientRead|19Dec20195:24:21PM||5482|pg12|pgbench||db2|LWLock/WALWriteLock|19Dec20195:24:21PM||5483|pg12|pgbench||db2|LWLock/WALWriteLock|19Dec20195:24:21PM||5505|pg12|pgbench||db3|LWLock/WALWriteLock|19Dec20195:24:21PM||5506|pg12|pgbench||db3|IO/WALSync|19Dec20195:24:21PM||5507|pg12|pgbench||db3|LWLock/WALWriteLock|19Dec20195:24:21PM||5508|pg12|pgbench||db3|LWLock/WALWriteLock|19Dec20195:24:21PM||9009|pg12|psql||db1|Client/ClientRead|19Dec20195:16:46PM|+------+------+---------+-------------+----------+-----------------------+------------------------+

锁信息

Locks:+---------------+-------------+-------+|LockType|NotGranted|Total|+---------------+-------------+-------+|relation|0|20||transactionid|0|8||virtualxid|0|10|+---------------+-------------+-------+||0|38|+---------------+-------------+-------+

Vacuum进程信息

VacuumProgress:Nomanualorautovacuumjobsinprogress.+------------------------------+----------------+|Setting|Value|+------------------------------+----------------+|maintenance_work_mem|65536(64MiB)||autovacuum|on||autovacuum_analyze_threshold|50||autovacuum_vacuum_threshold|50||autovacuum_freeze_max_age|200000000||autovacuum_max_workers|5||autovacuum_naptime|60sec||vacuum_freeze_min_age|50000000||vacuum_freeze_table_age|150000000|+------------------------------+----------------+

角色

Roles:+---------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+|Name|Login|Repl|Super|CreatRol|CreatDB|BypassRLS|Inherit|Expires|MemberOf|+---------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+|pg12|yes|yes|yes|yes|yes|yes|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|||+---------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+

表空间信息

Tablespaces:+------------+-------+-------------------------------+---------+---------------------------+---------------------------+|Name|Owner|Location|Size|DiskUsed|InodeUsed|+------------+-------+-------------------------------+---------+---------------------------+---------------------------+|pg_default|pg12|$PGDATA=/data/pgsql/pg121db|3.1GiB|62GiB(56.8%)of110GiB|237976(0.4%)of57569280||pg_global|pg12|$PGDATA=/data/pgsql/pg121db|398KiB|62GiB(56.8%)of110GiB|237976(0.4%)of57569280|+------------+-------+-------------------------------+---------+---------------------------+---------------------------+

数据库信息

Database#1:Name:postgresOwner:pg12Tablespace:pg_defaultConnections:0(nomaxlimit)FrozenXidAge:121949293Transactions:0(0.0%)commits,0(0.0%)rollbacksCacheHits:0.0%RowsChanged:ins0.0%,upd0.0%,del0.0%TotalTemp:0Bin0filesProblems:0deadlocks,0conflictsTotalsSince:Size:7.6MiBDatabase#2:Name:db1Owner:pg12Tablespace:pg_defaultConnections:6(nomaxlimit)FrozenXidAge:121949293Transactions:6716016(100.0%)commits,1(0.0%)rollbacksCacheHits:100.0%RowsChanged:ins0.0%,upd100.0%,del0.0%TotalTemp:0Bin0filesProblems:0deadlocks,0conflictsTotalsSince:19Dec20194:49:55PM(34minutesago)Size:8.9MiBInstalledExtensions:+---------+---------+------------------------------+|Name|Version|Comment|+---------+---------+------------------------------+|plpgsql|1.0|PL/pgSQLprocedurallanguage|+---------+---------+------------------------------+Database#3:Name:db2Owner:pg12Tablespace:pg_defaultConnections:4(nomaxlimit)FrozenXidAge:121949293Transactions:6691569(100.0%)commits,0(0.0%)rollbacksCacheHits:100.0%RowsChanged:ins0.0%,upd100.0%,del0.0%TotalTemp:0Bin0filesProblems:0deadlocks,0conflictsTotalsSince:19Dec20194:51:16PM(33minutesago)Size:283MiBDatabase#4:Name:db3Owner:pg12Tablespace:pg_defaultConnections:4(nomaxlimit)FrozenXidAge:121949293Transactions:6675090(100.0%)commits,0(0.0%)rollbacksCacheHits:100.0%RowsChanged:ins0.0%,upd100.0%,del0.0%TotalTemp:0Bin0filesProblems:0deadlocks,0conflictsTotalsSince:19Dec20194:51:29PM(32minutesago)Size:9.2MiBDatabase#5:Name:testdbOwner:pg12Tablespace:pg_defaultConnections:0(nomaxlimit)FrozenXidAge:121949293Transactions:2533(100.0%)commits,1(0.0%)rollbacksCacheHits:99.9%RowsChanged:ins0.0%,upd0.0%,del0.0%TotalTemp:0Bin0filesProblems:0deadlocks,0conflictsTotalsSince:19Dec20194:55:12PM(29minutesago)Size:2.8GiBTable#1in"db1":Name:db1.public.t_autovacuum_db1Columns:1ManualVacuums:neverManualAnalyze:neverAutoVacuums:neverAutoAnalyze:31,last20secondsagoPost-Analyze:641.1%est.rowsmodifiedRowEstimate:83.4%liveoftotal11986RowsChanged:ins0.0%,upd100.0%,del0.0%HOTUpdates:100.0%ofallupdatesSeqScans:0,0.0rows/scanIdxScans:6715855,1.0rows/scanCacheHits:100.0%(idx=100.0%)Size:664KiBBloat:272KiB(41.0%)+-----------------------+-------+---------+-----------------+------------+---------+----------------+-------------------+|Index|Type|Size|Bloat|CacheHits|Scans|RowsRead/Scan|RowsFetched/Scan|+-----------------------+-------+---------+-----------------+------------+---------+----------------+-------------------+|t_autovacuum_db1_pkey|btree|456KiB|296KiB(64.9%)|100.0%|6715855|1.0|1.0|+-----------------------+-------+---------+-----------------+------------+---------+----------------+-------------------+(END)[pg12@localhostpgmetrics_1.7.1_linux_amd64]$

未来发展
相信绝大多数人都经历过医院的体检,体检报告中列出结果指标和正常范围值指标,然后给出定性的偏高+/偏低-,按此思路来改进pgmetrics不失为一个方向.

到此，关于“怎么使用PostgreSQL的插件pgmetries”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！