PostgreSQLスキルアップノート（自己啓発のための個人サイト）
pg_statsinfo2.4.1収集・レポート出力編
【一覧に戻る】
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ pg_statsinfo2.4.1収集・レポート出力編
■■■■
■■■■
■■■■ 2013/02/16
■■■■ 使用環境：PostgreSQL9.1.7 (CentOS6.2) pg_statsinfo2.4.1
(C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【マニュアル】
pg_statsinfoマニュアル→●[マニュアル]
【参考記事】
−
■１■ 収集状況の確認
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
pg_statsinfoの-lオプションでリポジトリを作成したＤＢを指定して確認する。
【□】 pg_statsinfo -d postgres -l
SnapshotID InstanceID Host Port Timestamp Comment Execute Time Size
---------------------------------------------------------------------------------------------------
1 1 pg91 5432 2013-02-16 17:00:00 00:00:01 320 KiB
2 1 pg91 5432 2013-02-16 17:10:00 00:00:00 104 KiB
3 1 pg91 5432 2013-02-16 17:20:00 00:00:00 88 KiB
4 1 pg91 5432 2013-02-16 17:30:00 00:00:00 88 KiB
5 1 pg91 5432 2013-02-16 17:40:00 00:00:00 104 KiB
:
:
【参考】リポジトリを直接参照する方法
statsrepo.snapshotが元情報なので、これをSELECTしても同じような確認が可能。
【□】 select * from statsrepo.snapshot;
snapid | instid | time | comment | exec_time | snapshot_increase_size
--------+--------+-------------------------------+---------+-----------------+------------------------
1 | 1 | 2013-02-16 17:00:00.186781+09 | | 00:00:00.717997 | 327680
2 | 1 | 2013-02-16 17:10:00.116063+09 | | 00:00:00.365352 | 106496
3 | 1 | 2013-02-16 17:20:00.139896+09 | | 00:00:00.340143 | 90112
4 | 1 | 2013-02-16 17:30:00.199112+09 | | 00:00:00.278763 | 90112
5 | 1 | 2013-02-16 17:40:00.043042+09 | | 00:00:00.425777 | 106496
■２■ 手動スナップショット
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】 pg_statsinfo -d postgres -S 'COMMENT'
-Sで任意のコメントを記録できる。
■３■ レポートの生成
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
pg_statsinfo -r REPORTID [-i INSTANCEID] [-b SNAPID] [-e SNAPID] [-B DATE] [-E DATE] [-o FILENAME] [connection-options]
１．すべての項目・snapid指定
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
snapid 55〜56を指定
【□】 pg_statsinfo -d postgres -r all -b 55 -e 56 -o reportfile1.txt
２．指定した項目・snapid指定
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
snapid 55〜56を指定・DiskUsageのみ
【□】 pg_statsinfo -d postgres -r DiskUsage -b 55 -e 56
指定できる項目
Summary
DatabaseStatistics
InstanceActivity
OSResourceUsage
DiskUsage
LongTransactions
NotableTables
CheckpointActivity
AutovacuumActivity
QueryActivity
LockConflicts
ReplicationActivity
SettingParameters
SchemaInformation
Profiles
All
■■レポート出力例
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
pg_statsinfo -d postgres -r all -b 55 -e 56 -o reportfile1.txt
[postgres@pg91 ~]$ cat reportfile1.txt
---------------------------------------------
STATSINFO Report (host: pg91, port: 5432)
---------------------------------------------
----------------------------------------
/* Summary */
----------------------------------------
Database System ID : 5836285924358471599
Host : pg91
Port : 5432
PostgreSQL Version : 9.1.7
Snapshot Begin : 2013-02-17 23:00:00
Snapshot End : 2013-02-17 23:07:23
Snapshot Duration : 00:07:23
Total Database Size : 20 MiB
Total Commits : 212
Total Rollbacks : 0
----------------------------------------
/* Database Statistics */
----------------------------------------
Database Name : postgres
Database Size : 14 MiB
Database Size Increase : 0 MiB
Commit/s : 0.391
Rollback/s : 0.000
Cache Hit Ratio : 99.800 %
Block Read/s (disk+cache) : 63.675
Block Read/s (disk) : 0.102
Rows Read/s : 446.115
Temporary Files : (N/A)
Temporary Bytes : (N/A)
Deadlocks : (N/A)
Block Read Time : (N/A)
Block Write Time : (N/A)
Database Name : database1
Database Size : 6 MiB
Database Size Increase : 0 MiB
Commit/s : 0.088
Rollback/s : 0.000
Cache Hit Ratio : 100.000 %
Block Read/s (disk+cache) : 5.281
Block Read/s (disk) : 0.002
Rows Read/s : 20.109
Temporary Files : (N/A)
Temporary Bytes : (N/A)
Deadlocks : (N/A)
Block Read Time : (N/A)
Block Write Time : (N/A)
/** Transaction Statistics **/
-----------------------------------
DateTime Database Commit/s Rollback/s
-----------------------------------------------------------------
2013-02-17 23:07 database1 0.088 0.000
2013-02-17 23:07 postgres 0.391 0.000
/** Database Size **/
-----------------------------------
DateTime Database Size
-----------------------------------------------------
2013-02-17 23:00 database1 6.430 MiB
2013-02-17 23:00 postgres 13.868 MiB
2013-02-17 23:07 database1 6.430 MiB
2013-02-17 23:07 postgres 14.016 MiB
/** Recovery Conflicts **/
-----------------------------------
Database Conflict Tablespace Conflict Lock Conflict Snapshot Conflict Bufferpin Conflict Deadlock
-----------------------------------------------------------------------------------------------------------------
postgres 0 0 0 0 0
database1 0 0 0 0 0
----------------------------------------
/* Instance Activity */
----------------------------------------
/** WAL Statistics **/
-----------------------------------
WAL Write Total : 0.528 MiB
WAL Write Speed : 0.001 MiB/s
-----------------------------------
DateTime Location Segment File Write Size Write Size/s
------------------------------------------------------------------------------------------------
2013-02-17 23:07 0/817B598 000000010000000000000008 0.528 MiB 0.001 MiB
/** Instance Processes Ratio **/
-----------------------------------
Back-end Idle Ratio : 95.47 %
Back-end Idle In Xact Ratio : 4.53 %
Back-end Waiting Ratio : 0.00 %
Back-end Running Ratio : 0.00 %
/** Instance Processes **/
-----------------------------------
DateTime Idle Idle In Xact Waiting Running
---------------------------------------------------------------------------
2013-02-17 23:00 92.31 % 7.69 % 0.00 % 0.00 %
2013-02-17 23:07 100.00 % 0.00 % 0.00 % 0.00 %
----------------------------------------
/* OS Resource Usage */
----------------------------------------
/** CPU Usage **/
-----------------------------------
User Mode Ratio : 0.19 %
System Mode Ratio : 0.16 %
Idle Mode Ratio : 99.22 %
IOwait Mode Ratio : 0.43 %
-----------------------------------
DateTime User System Idle IOwait
-------------------------------------------------------------------
2013-02-17 23:07 0.20 % 0.20 % 99.20 % 0.40 %
/** Load Average **/
-----------------------------------
DateTime 1 Min 5 Min 15 Min
----------------------------------------------
2013-02-17 23:00 0.000 0.000 0.000
2013-02-17 23:07 0.000 0.000 0.000
/** IO Usage **/
-----------------------------------
Device Including TabelSpaces Total Read Total Write Total Read Time Total Write Time Current IO Queue Total IO Time
----------------------------------------------------------------------------------------------------------------------------------------------
dm-0 {pg_default,pg_global,tablespace01} 0 MiB 5 MiB 0 ms 14297 ms 0.000 14297 ms
-----------------------------------
DateTime Device Read Size/s Write Size/s Read Time/s Write Time/s
---------------------------------------------------------------------------------------------------
2013-02-17 23:07 dm-0 0.00 KiB 13.43 KiB 0.00 ms 32.29 ms
/** Memory Usage **/
-----------------------------------
DateTime Memfree Buffers Cached Swap Dirty
-----------------------------------------------------------------------------------------
2013-02-17 23:00 170.05 MiB 161.01 MiB 559.63 MiB 0.01 MiB 0.00 MiB
2013-02-17 23:07 166.89 MiB 161.13 MiB 560.70 MiB 0.01 MiB 0.00 MiB
----------------------------------------
/* Disk Usage */
----------------------------------------
/** Disk Usage per Tablespace **/
-----------------------------------
Tablespace Location Device Used Avail Remain
-----------------------------------------------------------------------------------------------------------
pg_default /db1/pgsql/data 253:0 3465 MiB 32700 MiB 90.417 %
pg_global /db1/pgsql/data 253:0 3465 MiB 32700 MiB 90.417 %
tablespace01 /db1/pgsql/data 253:0 3465 MiB 32700 MiB 90.417 %
/** Disk Usage per Table **/
-----------------------------------
Database Schema Table Size Table Reads Index Reads Toast Reads
---------------------------------------------------------------------------------------------------------------
postgres statsrepo column_20130217 0 MiB 8 3 0
postgres statsrepo column_20130216 2 MiB 8 3 0
postgres statsrepo index_20130216 0 MiB 3 1 0
postgres statsrepo tablespace 0 MiB 2 0 0
postgres statsrepo schema 0 MiB 1 1 0
postgres statsrepo function 0 MiB 1 1 0
postgres statsrepo statement 0 MiB 2 0 0
postgres statsrepo role 0 MiB 0 1 0
postgres statsrepo table 0 MiB 0 1 0
postgres statsrepo table_20130217 0 MiB 1 0 0
----------------------------------------
/* Long Transactions */
----------------------------------------
PID Client Address When To Start Duration Query
-----------------------------------------------------------------------------------------
----------------------------------------
/* Notable Tables */
----------------------------------------
/** Heavily Updated Tables **/
-----------------------------------
Database Schema Table INSERT Rows UPDATE Rows DELETE Rows Total Rows HOT Ratio(%)
-----------------------------------------------------------------------------------------------------------------------------
postgres statsrepo column_20130217 443 0 0 443 0.000
postgres statsrepo table_20130217 39 0 0 39 0.000
postgres statsrepo index_20130217 31 0 0 31 0.000
postgres statsrepo statement 30 0 0 30 0.000
postgres statsrepo function 23 0 0 23 0.000
postgres statsrepo setting 22 0 0 22 0.000
postgres statsrepo role 12 0 0 12 0.000
postgres statsrepo schema 11 0 0 11 0.000
postgres statsrepo autoanalyze 6 0 0 6 0.000
postgres statsrepo inherits 6 0 0 6 0.000
postgres statsrepo tablespace 3 0 0 3 0.000
postgres statsrepo database 2 0 0 2 0.000
postgres statsrepo snapshot 1 1 0 2 100.000
postgres statsrepo device 1 0 0 1 0.000
postgres statsrepo xlog 1 0 0 1 0.000
postgres statsrepo activity 1 0 0 1 0.000
postgres statsrepo memory 1 0 0 1 0.000
postgres statsrepo checkpoint 1 0 0 1 0.000
postgres statsrepo cpu 1 0 0 1 0.000
postgres statsrepo loadavg 1 0 0 1 0.000
/** Heavily Accessed Tables **/
-----------------------------------
Database Schema Table Seq Scans Read Rows Read Rows/Scan Cache Hit Ratio(%)
-----------------------------------------------------------------------------------------------------------------------
postgres statsrepo index_20130217 2 800 400.000 99.500
postgres statsrepo statement 4 1530 382.500 99.200
postgres statsrepo tablespace 1 165 165.000 85.700
postgres statsrepo database 635 68648 108.107 100.000
postgres statsrepo loadavg 1 55 55.000 100.000
postgres statsrepo memory 1 55 55.000 100.000
postgres statsrepo activity 3 165 55.000 100.000
postgres statsrepo xlog 2 110 55.000 100.000
postgres statsrepo snapshot 1198 65397 54.588 100.000
postgres statsrepo checkpoint 2 102 51.000 100.000
postgres statsrepo device 5 220 44.000 100.000
postgres statsrepo cpu 6 220 36.667 100.000
database1 public t1 21 168 8.000 95.200
postgres statsrepo instance 2 2 1.000 100.000
/** Low Density Tables **/
-----------------------------------
Database Schema Table Live Tuples Logical Pages Physical Pages Logical Page Ratio(%)
----------------------------------------------------------------------------------------------------------------------------
/** Fragmented Tables **/
-----------------------------------
Database Schema Table Column Correlation
---------------------------------------------------------------------------------------
postgres statsrepo table_20130216 tbl 0.025
postgres statsrepo column_20130217 tbl 0.038
postgres statsrepo column_20130217 attnum 0.041
postgres statsrepo table_20130217 tbl 0.041
postgres statsrepo column_20130216 tbl 0.052
postgres statsrepo column_20130216 attnum 0.053
postgres statsrepo index_20130216 idx 0.062
postgres statsrepo setting name 0.085
postgres statsrepo role userid 0.101
postgres statsrepo index_20130217 idx 0.113
postgres statsrepo schema nsp 0.146
postgres statsrepo tablespace name 0.358
postgres statsrepo database dbid 0.514
postgres statsrepo schema dbid 0.519
postgres statsrepo inherits inhrelid 0.547
postgres statsrepo table_20130216 dbid 0.762
postgres statsrepo table_20130217 dbid 0.799
postgres statsrepo statement dbid 0.857
postgres statsrepo index_20130216 dbid 0.933
postgres statsrepo index_20130217 dbid 0.943
postgres statsrepo column_20130216 dbid 0.948
postgres statsrepo column_20130217 dbid 0.959
postgres statsrepo statement snapid 0.994
postgres statsrepo function snapid 1.000
postgres statsrepo setting snapid 1.000
----------------------------------------
/* Checkpoint Activity */
----------------------------------------
Total Checkpoints : 2
Checkpoints By Time : 2
Checkpoints By XLOG : 0
Written Buffers Average : 85.500
Written Buffers Maximum : 132.000
Write Duration Average : 17.439 sec
Write Duration Maximum : 26.990 sec
----------------------------------------
/* Autovacuum Activity */
----------------------------------------
/** Basic Statistics (Average) **/
-----------------------------------
Table Count Index Scans Removed Rows Remain Rows Duration Duration(Max)
--------------------------------------------------------------------------------------------------------------------
----------------------------------------
/* Query Activity */
----------------------------------------
/** Functions **/
-----------------------------------
OID Database Schema Function Calls Total Time Self Time Time/Call
----------------------------------------------------------------------------------------------------------------------
17383 postgres statsrepo partition_insert 513 84.000 ms 84.000 ms 0.164 ms
17378 postgres statsrepo get_snap_date 513 39.000 ms 39.000 ms 0.076 ms
17415 postgres statsrepo alert 1 10.000 ms 10.000 ms 10.000 ms
17350 postgres statsrepo get_xlog_tendency 2 6.000 ms 1.000 ms 3.000 ms
17366 postgres statsrepo get_flagmented_tables 1 4.000 ms 4.000 ms 4.000 ms
17365 postgres statsrepo get_low_density_tables 1 4.000 ms 4.000 ms 4.000 ms
17318 postgres statsrepo xlog_location_diff 6 4.000 ms 1.000 ms 0.667 ms
17376 postgres statsrepo get_schema_info_indexes 1 3.000 ms 3.000 ms 3.000 ms
17361 postgres statsrepo get_disk_usage_table 1 3.000 ms 3.000 ms 3.000 ms
17375 postgres statsrepo get_schema_info_tables 1 3.000 ms 3.000 ms 3.000 ms
17380 postgres statsrepo partition_new 3 3.000 ms 2.000 ms 1.000 ms
17382 postgres statsrepo create_partition 1 3.000 ms 0.000 ms 3.000 ms
17351 postgres statsrepo get_xlog_stats 1 3.000 ms 0.000 ms 3.000 ms
17319 postgres statsrepo pg_size_pretty 56 2.000 ms 2.000 ms 0.036 ms
17317 postgres statsrepo convert_hex 24 2.000 ms 2.000 ms 0.083 ms
17363 postgres statsrepo get_heavily_updated_tables 1 2.000 ms 2.000 ms 2.000 ms
17364 postgres statsrepo get_heavily_accessed_tables 1 2.000 ms 2.000 ms 2.000 ms
17340 postgres statsrepo get_summary 1 2.000 ms 2.000 ms 2.000 ms
17341 postgres statsrepo get_dbstats 1 2.000 ms 2.000 ms 2.000 ms
16949 postgres statsinfo sample 88 1.000 ms 1.000 ms 0.011 ms
/** Statements **/
-----------------------------------
User Database Calls Total Time Time/Call Block Read Time Block Write Time Query
------------------------------------------------------------------------------------------------------------------------
postgres postgres 22 0.001 sec 0.000 sec (N/A) (N/A) INSERT INTO statsrepo.setting VALUES ($1, $2, $3, $4)
postgres postgres 12 0.001 sec 0.000 sec (N/A) (N/A) INSERT INTO statsrepo.role VALUES ($1, $2, $3)
postgres postgres 11 0.001 sec 0.000 sec (N/A) (N/A) INSERT INTO statsrepo.schema VALUES ($1, $2, $3, $4)
postgres postgres 6 0.001 sec 0.000 sec (N/A) (N/A) INSERT INTO statsrepo.autoanalyze VALUES ($1, $2::timestamptz - interval '1sec' * $6, $3, $4, $5, $6)
----------------------------------------
/* Lock Conflicts */
----------------------------------------
Database Schema Relation Duration Blockee PID Blocker PID Blocker GID
Blockee Query
Blocker Query
--------------------------------------------------------------------------------------------------------
----------------------------------------
/* Replication Activity */
----------------------------------------
----------------------------------------
/* Setting Parameters */
----------------------------------------
Name Setting Source
-----------------------------------------------------------------------------------------------
default_text_search_config pg_catalog.english configuration file
lc_messages C configuration file
log_autovacuum_min_duration 0 configuration file
log_checkpoints on configuration file
log_destination csvlog override
log_min_messages log configuration file
log_rotation_size 0 configuration file
log_truncate_on_rotation on configuration file
logging_collector on override
max_stack_depth 2048 environment variable
pg_stat_statements.max 100 configuration file
pg_statsinfo.enable_maintenance 3 configuration file
pg_statsinfo.syslog_line_prefix %t %p %c-%l %x %q(%u, %d, %r, %a) configuration file
pg_statsinfo.syslog_min_messages error configuration file
pg_statsinfo.textlog_line_prefix %t %p %c-%l %x %q(%u, %d, %r, %a) configuration file
server_encoding UTF8 override
shared_buffers 4096 configuration file
shared_preload_libraries pg_statsinfo,pg_stat_statements configuration file
track_activity_query_size 4096 configuration file
track_functions all configuration file
transaction_isolation read committed override
wal_buffers 128 override
----------------------------------------
/* Schema Information */
----------------------------------------
/** Tables **/
-----------------------------------
Database Schema Table Columns Row Width Size Size Incr Table Scans Index Scans
-----------------------------------------------------------------------------------------------------------------------------------
database1 public t1 2 byte 0 MiB 0 MiB 21 0
database1 public t11 1 byte 0 MiB 0 MiB 0 0
database1 public t1_rireki 5 byte 0 MiB 0 MiB 0 0
database1 scm01 t1 1 byte 0 MiB 0 MiB 0 0
database1 u0 t1 1 byte 0 MiB 0 MiB 0 0
database1 user01 t2 1 byte 0 MiB 0 MiB 0 0
postgres public t1 1 byte 0 MiB 0 MiB 0 0
/** Indexes **/
-----------------------------------
Database Schema Index Table Size Size Incr Index Scans Rows/Scan Disk Reads Cache Reads Index Key
-------------------------------------------------------------------------------------------------------------------------------------------------------------
database1 public t1_pkey t1 0 MiB 0 MiB 0 0.000 0 0 c1
----------------------------------------
/* Profiles */
----------------------------------------
Processing Executes
---------------------------------------------
以上
