pgstatspack
[root@test01 soft]# wget http://pgfoundry.org/frs/download.php/3151/pgstatspack_version_2.3.1.tar.gz
--2016-06-12 21:16:11-- http://pgfoundry.org/frs/download.php/3151/pgstatspack_version_2.3.1.tar.gz
Resolving pgfoundry.org... 188.227.186.71
Connecting to pgfoundry.org|188.227.186.71|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 17049 (17K) [application/binary]
Saving to: “pgstatspack_version_2.3.1.tar.gz”
100%[============================================================================================>] 17,049 3.78K/s in 4.4s
2016-06-12 21:16:19 (3.78 KB/s) - “pgstatspack_version_2.3.1.tar.gz” saved [17049/17049]
[root@test01 soft]# cd ..
[root@test01 opt]# mkdir pgstatspack
[root@test01 opt]# cp /opt/soft/pgstatspack_version_2.3.1.tar.gz ./pgstatspack/
[root@test01 opt]# cd pgstatspack/
[root@test01 pgstatspack]# tar zxvf pgstatspack_version_2.3.1.tar.gz
[root@test01 pgstatspack]# cd pgstatspack
[root@test01 pgstatspack]# ls
bin pgstatspack_sample_report.txt README sql
install_pgstats.sh pgstatspack_stat_explanation.txt remove_pgstats.sh upgrade_pgstatspack.sh
pgstatspack需要pg_stat_statements包的支持，需要预先设置
在install_pgstats.sh文件中加载pg_env.sh环境变量文件或者指定psql的路径
. /opt/PostgreSQL/9.5/pg_env.sh
pgstatspack/bin目录下
delete_snapshot.sh pgstatspack_report.sh snapshot.sh
脚本也需要指定psql的环境变量
[root@test01 pgstatspack]# ./install_pgstats.sh
Password:
Results for database template1
Password:
Password:
Installing Statistics Package for database template1
Password:
Password:
Password:
Password:
Results for database benchmarksql
Password:
Password:
Installing Statistics Package for database benchmarksql
Password:
Password:
Password:
Password:
Results for database pg_monitor
Password:
Password:
Installing Statistics Package for database pg_monitor
Password:
Password:
Password:
Password:
snapshot.sh脚本是生成统计信息快照的，可以运行多次，本示例运行两次，可以在多次snapshot之间选择两个任意两个snapshot来查看这段时间内的数据库性能报告。
创建一次snapshot
[root@test01 bin]# ./snapshot.sh
Password:
Results for database benchmarksql
Password:
pgstatspack_snap
------------------
1
(1 row)
Results for database pg_monitor
Password:
pgstatspack_snap
------------------
1
(1 row)
产生数据
-bash-4.1$ pgbench -i -F 100 -s 128 -h 127.0.0.1 -p 5432 -U postgres -d postgres
做一次压力测试
-bash-4.1$ pgbench -c 10 -j 10 -T 180 -h localhost -p 5432 -U postgres -d postgres
transaction type: TPC-B (sort of)
scaling factor: 128
query mode: simple
number of clients: 10
number of threads: 10
duration: 180 s
number of transactions actually processed: 22226
latency average: 80.986 ms
tps = 122.490072 (including connections establishing)
tps = 122.505293 (excluding connections establishing)
[root@test01 bin]# ./snapshot.sh
Password:
Results for database benchmarksql
Password:
pgstatspack_snap
------------------
2
(1 row)
Results for database pg_monitor
Password:
pgstatspack_snap
------------------
2
(1 row)
[root@test01 bin]# ./pgstatspack_report.sh
/opt/soft/pgstatspack/bin /opt/soft/pgstatspack/bin
Password for user postgres:
List of available databases:
1 . benchmarksql
2 . pg_monitor
Please select a number from the above list [ 1 - 2 ]
1
Password for user postgres:
Password for user postgres:
snapid | ts | description
--------+----------------------------+---------------------
2 | 2016-06-12 22:08:35.276569 | cron based snapshot
1 | 2016-06-12 21:54:27.891945 | cron based snapshot
(2 rows)
Enter start snapshot id : 1
Enter stop snapshot id : 2
Using file name: /tmp/pgstatreport_benchmarksql_1_2.txt
###########################################################################################################
PGStatspack version 2.3 by uwe.bartels@gmail.com
###########################################################################################################
Snapshot information
Begin snapshot :
Password for user postgres:
snapid | ts | description
--------+----------------------------+---------------------
1 | 2016-06-12 21:54:27.891945 | cron based snapshot
(1 row)
End snapshot :
Password for user postgres:
snapid | ts | description
--------+----------------------------+---------------------
2 | 2016-06-12 22:08:35.276569 | cron based snapshot
(1 row)
Seconds in snapshot: Password for user postgres:
847.384624
Database version
Password for user postgres:
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit
(1 row)
Database information
Password for user postgres:
current_database | dbsize
------------------+--------
benchmarksql | 10 GB
(1 row)
Database statistics
Password for user postgres:
database | tps | hitrate | lio_ps | pio_ps | rollbk_ps
--------------+-------+---------+---------+--------+-----------
postgres | 26.36 | 50.00 | 1671.91 | 820.64 | 0.00
benchmarksql | 0.04 | 96.00 | 10.90 | 0.33 | 0.00
pg_monitor | 0.04 | 96.00 | 10.58 | 0.33 | 0.00
template1 | 0.03 | 98.00 | 1.24 | 0.02 | 0.00
template0 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00
(5 rows)
Password for user postgres:
Top 20 tables ordered by table size changes
Password for user postgres:
table | table_growth | index_growth
------------------------------------+--------------+--------------
pg_catalog.pg_transform | | 0
pg_catalog.pg_ts_config | | 0
public.pgstatspack_tables | | 8192
pg_toast.pg_toast_3596 | | 0
pg_catalog.pg_ts_dict | | 0
pg_catalog.pg_cast | | 0
pg_catalog.pg_foreign_server | | 0
pg_catalog.pg_event_trigger | | 0
public.pgstatspack_sequences | | 8192
public.pgbench_branches | | 0
pg_catalog.pg_namespace | | 0
pg_toast.pg_toast_13102 | | 0
public.pgstatspack_indexes | | 8192
pg_catalog.pg_depend | | 0
public.pgstatspack_database | | 0
public.pgbench_tellers | | 0
pg_catalog.pg_language | | 0
pg_catalog.pg_largeobject_metadata | | 0
pg_catalog.pg_authid | | 0
pg_toast.pg_toast_13097 | | 0
(20 rows)
Top 20 tables ordered by high table to index read ratio
Password for user postgres:
table | system_read_pct | table_read_pct | index_read_pct
--------------------------+-----------------+----------------+----------------
pg_catalog.pg_class | 61 | 95 | 4
pg_catalog.pg_proc | 18 | 97 | 2
public.pgstatspack_names | 7 | 84 | 15
pg_catalog.pg_index | 4 | 90 | 9
pg_catalog.pg_opclass | 2 | 0 | 100
pg_catalog.pg_attribute | 2 | 0 | 100
pg_catalog.pg_database | 1 | 56 | 43
pg_catalog.pg_am | 0 | 100 | 0
pg_catalog.pg_namespace | 0 | 38 | 61
pg_catalog.pg_language | 0 | 0 | 100
pg_catalog.pg_tablespace | 0 | 0 | 100
pg_catalog.pg_amproc | 0 | 0 | 100
pg_toast.pg_toast_2619 | 0 | 0 | 100
pg_catalog.pg_shdepend | 0 | 0 | 100
pg_catalog.pg_type | 0 | 0 | 100
pg_catalog.pg_amop | 0 | 0 | 100
pg_toast.pg_toast_2618 | 0 | 0 | 100
pg_catalog.pg_cast | 0 | 0 | 100
pg_catalog.pg_authid | 0 | 0 | 100
pg_catalog.pg_rewrite | 0 | 0 | 100
(20 rows)
Top 20 tables ordered by inserts
Password for user postgres:
table | table_inserts
------------------------------------+---------------
public.pgstatspack_names | 329
public.pgstatspack_indexes | 134
public.pgstatspack_tables | 95
public.pgstatspack_settings | 57
pg_catalog.pg_statistic | 33
public.pgstatspack_database | 5
public.pgstatspack_sequences | 2
public.pgstatspack_bgwriter | 1
public.pgstatspack_snap | 1
pg_catalog.pg_cast | 0
pg_catalog.pg_description | 0
pg_catalog.pg_namespace | 0
pg_toast.pg_toast_13102 | 0
pg_catalog.pg_depend | 0
pg_catalog.pg_ts_dict | 0
pg_catalog.pg_authid | 0
public.pgbench_branches | 0
pg_catalog.pg_largeobject_metadata | 0
information_schema.sql_parts | 0
pg_catalog.pg_db_role_setting | 0
(20 rows)
Top 20 tables ordered by updates
Password for user postgres:
table | table_updates
------------------------------------+---------------
pg_toast.pg_toast_3596 | 0
pg_catalog.pg_ts_dict | 0
pg_catalog.pg_language | 0
public.pgstatspack_names | 0
pg_toast.pg_toast_13102 | 0
pg_catalog.pg_cast | 0
pg_catalog.pg_foreign_server | 0
pg_catalog.pg_db_role_setting | 0
public.pgstatspack_sequences | 0
public.pgbench_branches | 0
pg_catalog.pg_namespace | 0
pg_catalog.pg_description | 0
public.pgstatspack_indexes | 0
pg_catalog.pg_depend | 0
public.pgstatspack_database | 0
public.pgbench_tellers | 0
information_schema.sql_parts | 0
pg_catalog.pg_largeobject_metadata | 0
pg_catalog.pg_authid | 0
pg_catalog.pg_event_trigger | 0
(20 rows)
Top 20 tables ordered by deletes
Password for user postgres:
table | table_deletes
------------------------------------+---------------
pg_toast.pg_toast_3596 | 0
pg_catalog.pg_ts_dict | 0
pg_catalog.pg_language | 0
public.pgstatspack_names | 0
pg_toast.pg_toast_13102 | 0
pg_catalog.pg_cast | 0
pg_catalog.pg_foreign_server | 0
pg_catalog.pg_db_role_setting | 0
public.pgstatspack_sequences | 0
public.pgbench_branches | 0
pg_catalog.pg_namespace | 0
pg_catalog.pg_description | 0
public.pgstatspack_indexes | 0
pg_catalog.pg_depend | 0
public.pgstatspack_database | 0
public.pgbench_tellers | 0
information_schema.sql_parts | 0
pg_catalog.pg_largeobject_metadata | 0
pg_catalog.pg_authid | 0
pg_catalog.pg_event_trigger | 0
(20 rows)
Tables ordered by percentage of tuples scanned
Password for user postgres:
table | rows_read_pct | tab_hitrate | idx_hitrate | tab_read | tab_hit | idx_read | idx_hit
--------------------------+---------------+-------------+-------------+----------+---------+----------+---------
pg_catalog.pg_class | 61 | 99 | 98 | 1 | 1601 | 7 | 781
pg_catalog.pg_proc | 18 | 73 | 91 | 72 | 198 | 20 | 221
public.pgstatspack_names | 7 | 99 | 99 | 5 | 722 | 7 | 1462
pg_catalog.pg_index | 4 | 98 | 96 | 1 | 144 | 4 | 131
pg_catalog.pg_opclass | 2 | 98 | 93 | 3 | 271 | 4 | 71
pg_catalog.pg_attribute | 2 | 94 | 99 | 18 | 342 | 5 | 721
pg_catalog.pg_database | 1 | 99 | 99 | 0 | 320 | 0 | 394
pg_catalog.pg_aggregate | 0 | 0 | 0 | 1 | 0 | 2 | 0
pg_catalog.pg_attrdef | 0 | 33 | 40 | 1 | 1 | 2 | 2
pg_toast.pg_toast_1255 | 0 | 33 | 25 | 1 | 1 | 2 | 1
pg_catalog.pg_language | 0 | 0 | 0 | 1 | 0 | 2 | 0
pg_catalog.pg_tablespace | 0 | 94 | 96 | 0 | 16 | 0 | 30
pg_catalog.pg_amproc | 0 | 94 | 95 | 1 | 36 | 3 | 86
pg_toast.pg_toast_2619 | 0 | 0 | 0 | 1 | 0 | 2 | 0
pg_catalog.pg_am | 0 | 87 | 0 | 1 | 14 | 0 | 0
pg_catalog.pg_shdepend | 0 | 60 | 50 | 1 | 3 | 2 | 3
pg_catalog.pg_type | 0 | 91 | 90 | 5 | 61 | 7 | 77
pg_catalog.pg_amop | 0 | 92 | 93 | 5 | 77 | 6 | 108
pg_toast.pg_toast_2618 | 0 | 20 | 66 | 7 | 2 | 2 | 6
pg_catalog.pg_cast | 0 | 93 | 98 | 2 | 40 | 2 | 213
pg_catalog.pg_namespace | 0 | 98 | 96 | 1 | 149 | 4 | 141
pg_catalog.pg_authid | 0 | 97 | 98 | 0 | 35 | 0 | 70
pg_catalog.pg_rewrite | 0 | 50 | 76 | 6 | 7 | 2 | 10
pg_catalog.pg_statistic | 0 | 78 | 96 | 12 | 48 | 4 | 160
pg_catalog.pg_operator | 0 | 81 | 89 | 14 | 67 | 8 | 73
(25 rows)
Indexes ordered by scans
Password for user postgres:
index | table | scans | tup_read | tup_fetch | idx_blks_read | idx_blks_hit
----------------------------------------------------+-------------------------------+-------+----------+-----------+---------------+--------------
pg_catalog.pg_class_oid_index | pg_catalog.pg_class | 697 | 1049 | 811 | 1 | 711
public.idx_pgstatspack_names_name | public.pgstatspack_names | 377 | 370 | 370 | 5 | 1132
pg_catalog.pg_attribute_relid_attnum_index | pg_catalog.pg_attribute | 354 | 842 | 842 | 5 | 721
pg_catalog.pg_db_role_setting_databaseid_rol_index | pg_catalog.pg_db_role_setting | 280 | 0 | 0 | 0 | 280
pg_catalog.pg_cast_source_target_index | pg_catalog.pg_cast | 213 | 42 | 42 | 2 | 213
pg_catalog.pg_database_oid_index | pg_catalog.pg_database | 170 | 170 | 170 | 0 | 240
pg_catalog.pg_namespace_oid_index | pg_catalog.pg_namespace | 137 | 137 | 137 | 2 | 137
pg_catalog.pg_index_indexrelid_index | pg_catalog.pg_index | 97 | 97 | 97 | 2 | 110
pg_catalog.pg_proc_oid_index | pg_catalog.pg_proc | 87 | 87 | 87 | 10 | 165
pg_catalog.pg_database_datname_index | pg_catalog.pg_database | 84 | 84 | 84 | 0 | 154
pg_catalog.pg_type_oid_index | pg_catalog.pg_type | 63 | 62 | 62 | 2 | 63
pg_catalog.pg_statistic_relid_att_inh_index | pg_catalog.pg_statistic | 48 | 11 | 11 | 4 | 160
pg_catalog.pg_opclass_oid_index | pg_catalog.pg_opclass | 42 | 42 | 42 | 2 | 55
pg_catalog.pg_amproc_fam_proc_index | pg_catalog.pg_amproc | 37 | 62 | 62 | 3 | 86
pg_catalog.pg_amop_fam_strat_index | pg_catalog.pg_amop | 37 | 37 | 37 | 3 | 73
pg_catalog.pg_class_relname_nsp_index | pg_catalog.pg_class | 37 | 25 | 25 | 5 | 70
pg_catalog.pg_proc_proname_args_nsp_index | pg_catalog.pg_proc | 32 | 47 | 47 | 10 | 56
pg_catalog.pg_authid_oid_index | pg_catalog.pg_authid | 21 | 21 | 21 | 0 | 42
pg_catalog.pg_operator_oid_index | pg_catalog.pg_operator | 21 | 21 | 21 | 3 | 41
pg_catalog.pg_index_indrelid_index | pg_catalog.pg_index | 21 | 29 | 29 | 2 | 21
pg_catalog.pg_operator_oprname_l_r_n_index | pg_catalog.pg_operator | 18 | 86 | 86 | 5 | 32
pg_catalog.pg_amop_opr_fam_index | pg_catalog.pg_amop | 18 | 45 | 45 | 3 | 35
pg_catalog.pg_opclass_am_name_nsp_index | pg_catalog.pg_opclass | 16 | 674 | 674 | 2 | 16
pg_catalog.pg_tablespace_oid_index | pg_catalog.pg_tablespace | 16 | 16 | 16 | 0 | 30
pg_catalog.pg_authid_rolname_index | pg_catalog.pg_authid | 14 | 14 | 14 | 0 | 28
pg_catalog.pg_rewrite_rel_rulename_index | pg_catalog.pg_rewrite | 11 | 13 | 13 | 2 | 10
pg_catalog.pg_type_typname_nsp_index | pg_catalog.pg_type | 9 | 4 | 4 | 5 | 14
pg_toast.pg_toast_2618_index | pg_toast.pg_toast_2618 | 7 | 20 | 20 | 2 | 6
pg_catalog.pg_namespace_nspname_index | pg_catalog.pg_namespace | 5 | 4 | 4 | 2 | 4
pg_catalog.pg_shdepend_reference_index | pg_catalog.pg_shdepend | 4 | 4 | 4 | 2 | 3
pg_catalog.pg_attrdef_adrelid_adnum_index | pg_catalog.pg_attrdef | 2 | 2 | 2 | 2 | 2
pg_toast.pg_toast_1255_index | pg_toast.pg_toast_1255 | 2 | 4 | 4 | 2 | 1
pg_catalog.pg_aggregate_fnoid_index | pg_catalog.pg_aggregate | 1 | 1 | 1 | 2 | 0
pg_toast.pg_toast_2619_index | pg_toast.pg_toast_2619 | 1 | 1 | 1 | 2 | 0
pg_catalog.pg_language_oid_index | pg_catalog.pg_language | 1 | 1 | 1 | 2 | 0
public.pgstatspack_indexes_pk | public.pgstatspack_indexes | 0 | 0 | 0 | 2 | 134
public.pgstatspack_settings_pk | public.pgstatspack_settings | 0 | 0 | 0 | 2 | 57
public.pgstatspack_sequences_pk | public.pgstatspack_sequences | 0 | 0 | 0 | 2 | 2
public.pgstatspack_names_pkey | public.pgstatspack_names | 0 | 0 | 0 | 2 | 330
pg_catalog.pg_class_tblspc_relfilenode_index | pg_catalog.pg_class | 0 | 0 | 0 | 1 | 0
public.pgstatspack_bgwriter_pk | public.pgstatspack_bgwriter | 0 | 0 | 0 | 2 | 0
public.pgstatspack_tables_pk | public.pgstatspack_tables | 0 | 0 | 0 | 2 | 95
public.pgstatspack_database_pk | public.pgstatspack_database | 0 | 0 | 0 | 2 | 5
(43 rows)
Sequences ordered by blks_read
Password for user postgres:
sequence | blks_read | blks_hit
--------------------------+-----------+----------
public.pgstatspacknameid | 1 | 328
public.pgstatspackid | 1 | 0
(2 rows)
Top 20 SQL statements ordered by total_time
Password for user postgres:
calls | total_time | total_time_percent | rows | user | query
-------+------------+--------------------+------+------+-------
(0 rows)
Top 20 user functions ordered by total_time
Password for user postgres:
funcid | function_name | calls | total_time | self_time
--------+---------------+-------+------------+-----------
(0 rows)
background writer stats
Password for user postgres:
checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc
-------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------
3 | 0 | 11489 | 21518 | 33 | 628075 | 43004
(1 row)
background writer relative stats
Password for user postgres:
checkpoints_timed | minutes_between_checkpoint | buffers_checkpoint | buffers_clean | buffers_backend | total_writes | avg_checkpoint_write
-------------------+----------------------------+--------------------+---------------+-----------------+--------------+----------------------
100% | 5 | 1% | 3% | 95% | 6.095 MB/s | 29.000 MB
(1 row)
Parameters
Password for user postgres:
name | start_setting | stop_setting | source
------------------------------------+------------------------------------------+------------------------------------------+----------------------
max_stack_depth | 2048 | 2048 | environment variable
hba_file | /opt/PostgreSQL/9.5/data/pg_hba.conf | /opt/PostgreSQL/9.5/data/pg_hba.conf | override
lc_time | en_US.UTF-8 | en_US.UTF-8 | configuration file
log_destination | csvlog | csvlog | configuration file
auto_explain.log_analyze | on | on | configuration file
autovacuum | on | on | configuration file
ident_file | /opt/PostgreSQL/9.5/data/pg_ident.conf | /opt/PostgreSQL/9.5/data/pg_ident.conf | override
max_connections | 100 | 100 | configuration file
log_truncate_on_rotation | on | on | configuration file
TimeZone | PRC | PRC | configuration file
log_connections | on | on | configuration file
pg_stat_statements.max | 10000 | 10000 | configuration file
dynamic_shared_memory_type | posix | posix | configuration file
auto_explain.log_min_duration | 0 | 0 | configuration file
client_min_messages | notice | notice | configuration file
port | 5432 | 5432 | configuration file
application_name | psql.bin | psql.bin | client
lc_numeric | en_US.UTF-8 | en_US.UTF-8 | configuration file
wal_buffers | 512 | 512 | override
lc_ctype | en_US.UTF-8 | en_US.UTF-8 | override
log_statement | ddl | ddl | configuration file
log_lock_waits | on | on | configuration file
data_checksums | off | off | override
log_duration | on | on | configuration file
client_encoding | UTF8 | UTF8 | client
auto_explain.log_verbose | on | on | configuration file
config_file | /opt/PostgreSQL/9.5/data/postgresql.conf | /opt/PostgreSQL/9.5/data/postgresql.conf | override
transaction_deferrable | off | off | override
lc_collate | en_US.UTF-8 | en_US.UTF-8 | override
log_filename | postgresql-%Y-%m-%d_%H%M%S.log | postgresql-%Y-%m-%d_%H%M%S.log | configuration file
lc_messages | en_US.UTF-8 | en_US.UTF-8 | configuration file
transaction_isolation | read committed | read committed | override
log_replication_commands | on | on | configuration file
default_text_search_config | pg_catalog.english | pg_catalog.english | configuration file
server_encoding | UTF8 | UTF8 | override
log_directory | pg_log | pg_log | configuration file
transaction_read_only | off | off | override
auto_explain.log_timing | on | on | configuration file
pg_stat_statements.track_utility | off | off | configuration file
log_file_mode | 0600 | 0600 | configuration file
log_checkpoints | on | on | configuration file
lc_monetary | en_US.UTF-8 | en_US.UTF-8 | configuration file
log_hostname | on | on | configuration file
logging_collector | on | on | configuration file
log_line_prefix | %t%u%a%d%r%e | %t%u%a%d%r%e | configuration file
auto_explain.log_triggers | on | on | configuration file
log_disconnections | on | on | configuration file
auto_explain.log_buffers | on | on | configuration file
log_error_verbosity | verbose | verbose | configuration file
DateStyle | ISO, MDY | ISO, MDY | configuration file
listen_addresses | * | * | configuration file
auto_explain.log_nested_statements | on | on | configuration file
shared_buffers | 16384 | 16384 | configuration file
data_directory | /opt/PostgreSQL/9.5/data | /opt/PostgreSQL/9.5/data | override
log_timezone | PRC | PRC | configuration file
pg_stat_statements.track | all | all | configuration file
shared_preload_libraries | pg_stat_statements,auto_explain | pg_stat_statements,auto_explain | configuration file
(57 rows)
This report is saved as /tmp/pgstatreport_benchmarksql_1_2.txt
/opt/soft/pgstatspack/bin
[root@test01 bin]# cd /tmp/
[root@test01 tmp]# ls
bitrock_installer_25349.log hsperfdata_root postgresql_installer_cbd1bc9d85
bitrock_installer.log pgstatreport_benchmarksql_1_2.txt