注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

pgstatspack_version_2.3.1 报告内容分析  

2012-05-30 13:40:12|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 

          pgstatspack_version_2.3.1 去年就已经出来了,一直没怎么去用,今天测试了下新版的 pgstatspack,
新版的 statspack 报告的内容比以前详细很多,越来越像 Oracle 的 awr 报告了。关闭 pgstatspack 的安装
,报告生成可以参考以前写的 blog: http://francs3.blog.163.com/blog/static/40576727201071022325942/
今天主要演示新版 pgstatspack 报告内容。

 

一 pgstatspack 报告内容

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

 ###########################################################################################################
PGStatspack version 2.3 by 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 一样。

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
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
          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
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
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
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
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)

 

##索引扫描次数排序
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
....


##序列被读取,用于查找竞争激烈的序列
Sequences ordered by blks_read
         sequence         | blks_read | blks_hit
--------------------------+-----------+----------
 public.pgstatspackid     |         0 |        1
 public.pgstatspacknameid |         0 |      325
(2 rows)

 

## 执行时间 top 20 的 sql
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
Top 20 user functions ordered by total_time
 funcid | function_name | calls | total_time | self_time
--------+---------------+-------+------------+-----------
(0 rows)


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)

 

##当前参数配置
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 里,参考如下

  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()以下代码:

  SELECT current_timestamp - interval '30 days' INTO old_snap_time;
   

 

  评论这张
 
阅读(820)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017