1. 云图网首页
  2. 技术专区
  3. 数据库

Pgstatspack_version_2.3.1 报告内容分析

• 数据库

pgstatspack_version_2.3.1 去年就已经出来了，一直没怎么去用，今天测试了下新版的 pgstatspack，新版的 statspack 报告的内容比以前详细很多，越来越像 Oracle 的 awr 报告了。关闭 pgstatspack 的安装 ，报告生成可以参考以前写的 blog: https://postgres.fun/20100810142325.html

今天主要演示新版 pgstatspack 报告内容。

Pgstatspack 报告内容

以下是在很空闲的测试库上生成的 pgstatspack 报告，带”备注”是分析。 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
###########################################################################################################  
PGStatspack version 2.3 by [uwe.bartels@gmail.com](mailto:uwe.bartels@gmail.com)  
###########################################################################################################
Snapshot information  
Begin snapshot :  
snapid | ts | description  
--------+---------------------------+----------------------  
 1 | 2012-05-30 09:34:10.05015 | my first pgstatspack  
(1 row)
End snapshot :  
snapid | ts | description  
--------+----------------------------+-----------------------  
 2 | 2012-05-30 09:35:17.261398 | my second pgstatspack  
(1 row)
Seconds in snapshot: 67.211248

备注：报告中需要根据 “Begin snapshot” 和 “ End snapshot “ 才能生成，这和 oracle 的 statpack 一样。

数据库版本和统计信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
Database version  
 version  
----------------------------------------------------------------------------------------------------------  
PostgreSQL 9.2beta1 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 32-bit  
(1 row)
current_database | dbsize  
------------------+--------  
francs | 312 MB  
(1 row)
  
Database statistics  
database | tps | hitrate | lio_ps | pio_ps | rollbk_ps  
-----------+------+---------+--------+--------+-----------  
francs | 0.18 | 99.00 | 99.48 | 0.71 | 0.00  
postgres | 0.06 | 99.00 | 3.84 | 0.00 | 0.00  
template1 | 0.03 | 98.00 | 0.82 | 0.00 | 0.00  
template0 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00  
(4 rows)

备注：这里统计了快照之间数据库的信息.

  • tps: 快照之间提交的事务数。 (pg_stat_database.xact_commit)
  • hitrate： 快照之间缓存命中率 pg_stat_database.blks_hit – cache reads
  • pg_stat_database.blks_read – physical reads
  • lio_ps: 快照之间每秒逻辑读次数 (pg_stat_database.blks_hit + pg_stat_database.blks_read)
  • pio_ps: 快照之间每秒物理读次数 (pg_stat_database.blks_read )
  • rollbk_ps: 快照之间事务回滚的次数 (pg_stat_database.xact_rollback)

表数据变化 top 20

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
Top 20 tables ordered by table size changes  
 table | table_growth | index_growth  
------------------------------------+--------------+--------------  
pg_catalog.pg_shseclabel | | 0  
pg_toast.pg_toast_2396 | | 0  
pg_catalog.pg_ts_config | | 0  
public.pgstatspack_tables | | 8192  
pg_catalog.pg_ts_dict | | 0  
pg_catalog.pg_foreign_server | | 0  
francs.test_float4 | |  
pg_catalog.pg_language | | 0  
pg_toast.pg_toast_3596 | | 0  
francs.test_check | | 0  
pg_catalog.pg_cast | | 0  
public.pgstatspack_indexes | | 8192  
francs.test_dropindex | | 0  
pg_catalog.pg_depend | | 0  
public.pgstatspack_sequences | | 8192  
public.pgstatspack_database | | 0  
pg_catalog.pg_largeobject_metadata | | 0  
pg_catalog.pg_authid | | 0  
pg_catalog.pg_namespace | | 0  
pg_catalog.pg_extension | | 0  
(20 rows)

Top 20 tables ordered by high table to index read ratio

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
table | system_read_pct | table_read_pct | index_read_pct  
--------------------------+-----------------+----------------+----------------  
francs.test_1 | 38 | 100 | 0  
pg_catalog.pg_class | 22 | 90 | 9  
pg_catalog.pg_proc | 19 | 99 | 0  
public.pgstatspack_names | 8 | 88 | 11  
pg_catalog.pg_index | 4 | 91 | 8  
pg_catalog.pg_attribute | 2 | 0 | 100  
pg_catalog.pg_opclass | 1 | 0 | 100  
pg_catalog.pg_am | 0 | 100 | 0  
pg_catalog.pg_database | 0 | 54 | 45  
pg_catalog.pg_namespace | 0 | 25 | 74  
pg_catalog.pg_amproc | 0 | 0 | 100  
pg_catalog.pg_type | 0 | 0 | 100  
pg_catalog.pg_amop | 0 | 0 | 100  
pg_catalog.pg_constraint | 0 | 0 | 100  
pg_catalog.pg_cast | 0 | 0 | 100  
pg_catalog.pg_authid | 0 | 0 | 100  
pg_catalog.pg_rewrite | 0 | 0 | 100  
pg_catalog.pg_statistic | 0 | 0 | 100  
pg_catalog.pg_operator | 0 | 0 | 100  
pg_catalog.pg_aggregate | 0 | 0 | 100  
(20 rows)

表数据插入 top 20

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
Top 20 tables ordered by inserts  
 table | table_inserts  
------------------------------------+---------------  
public.pgstatspack_names | 325  
public.pgstatspack_indexes | 135  
public.pgstatspack_tables | 103  
public.pgstatspack_settings | 45  
pg_catalog.pg_statistic | 29  
public.pgstatspack_database | 4  
public.pgstatspack_sequences | 3  
public.pgstatspack_bgwriter | 1  
public.pgstatspack_snap | 1  
pg_catalog.pg_foreign_server | 0  
pg_catalog.pg_description | 0  
pg_catalog.pg_cast | 0  
francs.test_dropindex | 0  
pg_catalog.pg_depend | 0  
pg_catalog.pg_ts_dict | 0  
francs.test_check | 0  
pg_catalog.pg_rewrite | 0  
pg_catalog.pg_namespace | 0  
pg_catalog.pg_authid | 0  
pg_catalog.pg_largeobject_metadata | 0  
(20 rows)

表数据修改 top 20

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
Top 20 tables ordered by updates  
 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_catalog.pg_cast | 0  
pg_catalog.pg_foreign_server | 0  
public.pgstatspack_sequences | 0  
pg_catalog.pg_db_role_setting | 0  
pg_catalog.pg_namespace | 0  
francs.test_check | 0  
pg_catalog.pg_description | 0  
public.pgstatspack_indexes | 0  
francs.test_dropindex | 0  
pg_catalog.pg_depend | 0  
information_schema.sql_parts | 0  
public.pgstatspack_database | 0  
pg_catalog.pg_largeobject_metadata | 0  
pg_catalog.pg_authid | 0  
pg_catalog.pg_rewrite | 0  
francs.test_float4 | 0  
(20 rows)

表数据删除 top 20

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
Top 20 tables ordered by deletes  
 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_catalog.pg_cast | 0  
pg_catalog.pg_foreign_server | 0  
public.pgstatspack_sequences | 0  
pg_catalog.pg_db_role_setting | 0  
pg_catalog.pg_namespace | 0  
francs.test_check | 0  
pg_catalog.pg_description | 0  
public.pgstatspack_indexes | 0  
francs.test_dropindex | 0  
pg_catalog.pg_depend | 0  
information_schema.sql_parts | 0  
public.pgstatspack_database | 0  
pg_catalog.pg_largeobject_metadata | 0  
pg_catalog.pg_authid | 0  
pg_catalog.pg_rewrite | 0  
francs.test_float4 | 0  
(20 rows)

表记录数据读取 top 20

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
Tables ordered by percentage of tuples scanned  
 table | rows_read_pct | tab_hitrate | idx_hitrate | tab_read | tab_hit | idx_read | idx_hit  
--------------------------+---------------+-------------+-------------+----------+---------+----------+---------  
francs.test_1 | 38 | 97 | 0 | 0 | 45 | 0 | 0  
pg_catalog.pg_class | 22 | 99 | 99 | 0 | 732 | 0 | 623  
pg_catalog.pg_proc | 19 | 99 | 88 | 0 | 125 | 0 | 8  
public.pgstatspack_names | 8 | 99 | 99 | 5 | 626 | 7 | 1351  
pg_catalog.pg_index | 4 | 99 | 99 | 0 | 124 | 0 | 105  
pg_catalog.pg_attribute | 2 | 99 | 99 | 0 | 309 | 1 | 611  
pg_catalog.pg_opclass | 1 | 99 | 97 | 0 | 116 | 0 | 36  
pg_catalog.pg_aggregate | 0 | 66 | 80 | 0 | 2 | 0 | 4  
pg_catalog.pg_attrdef | 0 | 75 | 83 | 0 | 3 | 0 | 5  
pg_toast.pg_toast_2618 | 0 | 41 | 88 | 6 | 5 | 0 | 8  
pg_catalog.pg_tablespace | 0 | 75 | 83 | 0 | 3 | 0 | 5  
pg_catalog.pg_amproc | 0 | 95 | 97 | 0 | 20 | 0 | 45  
pg_catalog.pg_am | 0 | 80 | 0 | 0 | 4 | 0 | 0  
pg_catalog.pg_type | 0 | 94 | 96 | 0 | 16 | 0 | 32  
pg_catalog.pg_amop | 0 | 97 | 98 | 0 | 34 | 0 | 65  
pg_catalog.pg_database | 0 | 96 | 96 | 0 | 26 | 0 | 32  
pg_catalog.pg_constraint | 0 | 50 | 66 | 0 | 1 | 0 | 2  
pg_catalog.pg_cast | 0 | 96 | 99 | 0 | 25 | 0 | 109  
pg_catalog.pg_namespace | 0 | 99 | 99 | 0 | 149 | 0 | 145  
pg_catalog.pg_authid | 0 | 90 | 94 | 0 | 9 | 0 | 16  
pg_catalog.pg_rewrite | 0 | 58 | 92 | 4 | 7 | 0 | 12  
pg_catalog.pg_statistic | 0 | 97 | 99 | 0 | 39 | 0 | 132  
pg_catalog.pg_operator | 0 | 94 | 97 | 0 | 17 | 0 | 41  
(23 rows)

索引扫描次数排序

1
2
3
4
5
6
7
8
9
 
Indexes ordered by scans  
 index | table | scans | tup_read | tup_fetch | idx_blks_read | idx_blks_hit  
----------------------------------------------------+-------------------------------+-------+----------+-----------+---------------+--------------  
pg_catalog.pg_class_oid_index | pg_catalog.pg_class | 547 | 547 | 547 | 0 | 550  
pg_catalog.pg_attribute_relid_attnum_index | pg_catalog.pg_attribute | 304 | 736 | 736 | 1 | 611  
public.idx_pgstatspack_names_name | public.pgstatspack_names | 284 | 277 | 277 | 5 | 1025  
pg_catalog.pg_namespace_oid_index | pg_catalog.pg_namespace | 138 | 138 | 138 | 0 | 140  
....
略

序列读取信息

用于查找竞争激烈的序列

1
2
3
4
5
6
 
Sequences ordered by blks_read  
 sequence | blks_read | blks_hit  
--------------------------+-----------+----------  
public.pgstatspackid | 0 | 1  
public.pgstatspacknameid | 0 | 325  
(2 rows)

执行时间 top 20 的 sql

1
2
3
4
 
Top 20 SQL statements ordered by total_time  
calls | total_time | total_time_percent | rows | user | query  
-------+------------+--------------------+------+------+-------  
(0 rows)

备注：这个模块是新版的亮点，记得老版本没有统计 SQL 信息，查找 SQL 信息还得安装 pg_stat_statements 模块。

执行时间 top 20 的 function

1
2
3
4
 
Top 20 user functions ordered by total_time  
funcid | function_name | calls | total_time | self_time  
--------+---------------+-------+------------+-----------  
(0 rows)

Backgroud Writer 进程信息

1
2
3
4
5
6
7
8
9
10
11
 
background writer stats  
checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc  
-------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------  
 0 | 0 | 0 | 0 | 0 | 0 | 47  
(1 row)
  
background writer relative stats  
checkpoints_timed | minutes_between_checkpoint | buffers_checkpoint | buffers_clean | buffers_backend | total_writes | avg_checkpoint_write  
-------------------+----------------------------+--------------------+---------------+-----------------+--------------+----------------------  
 | | | | | 0.000 MB/s |  
(1 row)

当前参数配置

1
2
3
4
5
6
7
8
9
10
11
 
Parameters  
 name | start_setting | stop_setting | source  
----------------------------+----------------------------------------+----------------------------------------+----------------------  
max_stack_depth | 2048 | 2048 | environment variable  
hba_file | /opt/pgdata9.2/pg_root/pg_hba.conf | /opt/pgdata9.2/pg_root/pg_hba.conf | override  
lc_time | C | C | configuration file  
archive_command | /bin/date | /bin/date | configuration file  
log_destination | csvlog | csvlog | configuration file  
autovacuum | on | on | configuration file  
.....
略

关于快照的删除

如果在生产库上部署了 pgstatpack ，则建议定期删除快照，否则上述的快照相关表会很大，快照删除脚本可写在 crontab 里，参考如下

1
 
2 3 * * * /somepath/pgstatspack/bin/delete_snapshot.sh 1> /some_path/log_file 2>&1

快照删除脚本 delete_snapshot.sh 会调用函数 pgstatspack_delete_snap()，这个函数中可以设置删除多久以前的快照，默认删除 30 天 前的快照，如需修改，修改函数pgstatspack_delete_snap()以下代码：

1
 
SELECT current_timestamp - interval '30 days' INTO old_snap_time;

原创文章，作者：6024010，如若转载，请注明出处：https://blog.ytso.com/237861.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论