pg_statsinfo 1.2
Project Top page Previous page
How to read summary report
This document shows way of looking at summary part of the report. Generally the report infromation consists two
specified (or latest and olodest in specified range) snapshot diferrence, we use latest value or average values partly.
Please refer to the following explanatory notes given to the head of the item.
- [d]：difference value
- [n]：latest value
- [a]：average value
- [m]：max value
You can see following infromation in summary report.
Report Information
Report Information has following composition. You can check report and used snapshot status.
---------------------------------------------------------------------
Report Information
---------------------------------------------------------------------
Begin SnapShotID : Start snapshot ID
End SnapShotID : End snapshot ID
Begin Snap Time : Start snaoshot timestamp
End Snap Time : End snapshot timestamp
Snap Level : # Not used
Description : Description about snapshot
Host Information
Host Information has following composition. These are taken from latest snapshot.
You can check some status of HW and OS.
---------------------------------------------------------------------
Host(Instance) System Information
---------------------------------------------------------------------
Host Name : Host name
Database Name : Traget DB name
Port : Port number of DB cluster
User Name : User name
Kernel Version : Kernel version
Processors : Number of CPU
Total Mem Size : Size of physical memory
Swap Mem Size : Size of swap memory
PostgreSQL version : PostgreSQL version
Device Usage Information
Device Usage Information has following composition. These are taken from latest snapshot.
You can check device usage ratio that has DB cluster, pg_xlog, archive_dir...
---------------------------------------------------------------------
Instance Disk Usage Information
---------------------------------------------------------------------
Space_name : Tablespace name or directory name (ex pg_xlog)
Device_ID : Device ID
Device_name : Device name
Location : Location of each tablespace, directories
USED : Device used
AVAIL : Device free space
TOTAL : Device total amount
RATIO : Device usage ratio
[Point of View]
Check device usage ratio of pg_xlog and archive_dir.
Long Transaction Information
Long Transaction Information has following composition. You can check transactions ran over 1min in
specified time range.
---------------------------------------------------------------------
Instance Long Transaction Information
---------------------------------------------------------------------
DB : DB that long transaction occured
PID : Process ID
User name : User name
waiting : Lock waiting flag (If 't', this process is waiting lock release)
Elapsed time : Duration since transaction start
Check time : Check this long transaction time
Query : Query in transaction
[Point of View]
Long transaction blocks garbage collection of VACUUM and HOT. If you confirmed long transaction,
take some measures to avoid it.
DB Load Profile
DB Load Profile has following composition. You can check activity and what-like process executing in
target DB.
----------------------------------------
Activity Information
----------------------------------------
[n]Database Size : DB size(Byte)
[a]Connection Average Number : Average number of connections
[n]Database Frozen Xid(Age) : Age of FREEZE XID
[d]Commit Number : Number of commited transaction
[d]Rollback Number : Number if rollbacked transaction
[d]Block Read : Number of block read times
[d]Block Hit : Number of cache git times
[d]Cache Hit Ratio : Cache hit ratio(%)
[d]Return Tuples : Number of rowd fetched by seq scan
[d]Fetched Tuples : Number of rowd fetched by index scan
[d]INSERT Tuples : Number of INSERTed records
[d]UPDATE Tuples : Number of UPDATEed records
[d]DELETE Tuples : Number of DELETEed records
[n]Total User Table Number : Number of user tables
[Point of View]
Please confirm whether unexpected seq scans or connects is not carried out. And also check
cache hit ratio is reasonable?
DB Setting Information
DB Setting Information has following composition. These are taken from latest snapshot.
You can check parameters that is not default value.
----------------------------------------
Setting Information
----------------------------------------
Parameter : Parameter Name
Value : Values
Source : way to change
(ver1.1-)SQL Ordered By Elapsed Time Top 10
SQL Ordered By Elapsed Time Top 10 has following composition. You can check SQLs that consumed times at a great rate.
---------------------------------------------------------------------
SQL Ordered Top 10
---------------------------------------------------------------------
User name : Use name who executed the SQL
DB name : DB name in which the SQL was executed
calls : Number of times exected
total_time : Total time spent in the SQL execution (sec)
rows : Total number of rows retrieved or affected by the SQL
[QUERY] : SQL statements
Function Ordered By Elapsed Time Top 10
Function Ordered By Elapsed Time Top 10 has following composition. You can check Functions that consumed times at a great rate.。
---------------------------------------------------------------------
Function Ordered Top 10
---------------------------------------------------------------------
Schema name : Schema name in which the function was executed
Function name : Function name
calls : Number of times exected
total_time : Total time spent in the Function execution (mili sec) (includes the time spent in functions called by this one.)
self_time : Total time spent in only the Function execution (mili sec)
Fragmented Table Top 10
Fragmented Table Top 10 has following composition. You can check tables that have much
dead records from UPDATE and DELETE.
[Attention] If you do not ANALYZE enough times, some errors may be observed.
---------------------------------------
Fragmented Table worst 10
---------------------------------------
[n]Schema : Schema name
[n]Table name : Table name
[d]dead record : Number of dead records
[d]live record : Number of live records
[d]dead record ratio : Dead record ratio
[Point of View]
Please confirm whether tabels has not many live records and high dead record ration. Ideally, dead record ratio should be under 10
(except small size table).
Much Access Tables Top 10
Much Access Tables Top 10 has following composition. You can check tables that much seq scan are carried out.
---------------------------------------
Much Access Table Top 10
---------------------------------------
[n]Schema : Schema name
[n]Table name : Table name
[d]seq_scan : Number of seq scan times
[d]records : Number of rows fetched by seq scan
[d]records per scan : Number of rows per seq scan
[Point of View]
Please confirm whether tabels that unexepected seq scan are not carried out. (ANALYZE missing or forget to
create necessary index ?) As well, It is not problem much seq scan exexuted in small table.
Low Density Tables Top 10
Low Density Tables Top 10 has following composition. These are taken from latest snapshot.
You can check tables that has much dead and blank fields.
[Attention] If you do not ANALYZE enough times, some errors may be observed.
---------------------------------------
Low Density Table Worst 10
---------------------------------------
Schema : Schema name
Table name : Table name
reltuples : Number of records
logical_pages : Number of pages that records filled logically
physical_pages : Number of pages that records filled physically
density : Table denisty
[Point of View]
If you can see some low density tables, VACUUM missing or low FSM values as the main cause of it. Please recheck
VACUUM executing plan and FSM values. It effective measure to return appropriate density by using
pg_reorg or CLUSTER.
Fragmneted Cluster-key Top 10
Fragmneted Cluster-key Top 10 has following composition. These are taken from latest snapshot.
You can check the correlation of column for using Cluster key.
---------------------------------------
Cluster-key Column Correlation Worst 10
---------------------------------------
Schema : Schema name
Table name : Table name
column_name : Column name
correlation : Correlation sort of logical and physical (more fragmented vanishingly)
[Point of View]
Ideally, correlation of Cluster-key column is nearly 1. If correlation is nealy 0, performance decrement of range scan
by using this column is ocuured. Please reorganization the table with the column by using
pg_reorg or CLUSTER.
Checkpoint Information
Checkpoint Information has following composition. Filtering log message to get checkpoint information and aggregate them.
You can check the checkpoint activity history.
----------------------------------------
checkpoint Information
----------------------------------------
[d]Start By time-threshold : Number of checkpoint times (start by checkpoint_timeout)
[d]Start By xlog-threshold : Number of checkpoint times (start by checkpoint_segment)
[a]Written Buffers(Average) : Average number of written dirty buffers at checkpoint
[a]Generated WAL Average Numbers : Average number of generated WAL
[a]Deleted WAL Average Numbers : Average number of deleted WAL
[a]Recycled WAL Average Numbers : Average number of recycled WAL
[a]Write Average eplased Time(sec) : Average duration for write (sec)
[m]Write Max eplased Time(sec) : Max duration for write (sec)
[a]Sync Average Elapsed Time (sec) : Average duration for sync (sec)
[m]Sync Max Elapsed Time (sec) : Max duration for sync (sec)
[m]Total Max Eplased Time(sec) : Duration of the checkpoint which taken most duration (sec)
[Point of View]
If you can see high "Start By xlog-threshold" value, "checkpoint_segments" may be too small.
Autovacuum Information
Autovacuum Information has following composition. Filtering log message to get autovacuum information and aggregate them.
It shows information for each tables. You can check the autovacuum activity history.
----------------------------------------
autovacuum Information
----------------------------------------
------------------
Table name (Table)
------------------
[d]autovacuum Times : Number of autovacuum times
[a]Removable Average Pages : Average number of removable pages
[a]Remain Average Pages : Average number of remain pages
[a]Removable Average Records : Average number of removable records
[a]Remain Average Records : Average number of remain records
[a]autovacuum Average Elapsed Time (sec) : Average duration of autovacuum (sec)
[m]autovacuum Max Elapsed Time (sec) : Duration of the autovacuum which taken most duration (sec)
[Point of View]
If you can see too small "Removable Average Records" and too many "Remain Average Records", autovacuum activity may be
blocked by long transaction.
Autovacuum Starting Time Information
Autovacuum Starting Time Information has following composition.Filtering log message to get autovacuum information and aggregate them.
It shows all information about autovacuum which performed in specified range of snapshots.
----------------------------------------
autovacuum performe Information
----------------------------------------
table : Table name which autovacuum was performed
autovacuum start time : Start time of autovacuum
duration : Duration of autovacuum (sec)
Project Top page Previous page