加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

使用PostgreSQL下的statspack

发布时间：2020-12-13 17:44:31 所属栏目：百科 来源：网络整理
导读：在Oracle下有一个查看分析数据库的工具叫statspack，在PostgreSQL下也有这样的一个工具。下面将介绍如何使用这个工具。 安装： 在网站上下载： http://pgfoundry.org/projects/pgstatspack,在这个页面中可以看到下载链接，把pgStatspack下载下来。我下载的是

在Oracle下有一个查看分析数据库的工具叫statspack，在PostgreSQL下也有这样的一个工具。下面将介绍如何使用这个工具。


安装：
在网站上下载： http://pgfoundry.org/projects/pgstatspack,在这个页面中可以看到下载链接，把pgStatspack下载下来。我下载的是pgstatspack_version_2.2.tar.gz，把这个包解压到一个目录中，
如我解压到 ~/pgstatspack目录下。运行脚本~/pgstatspack/install_pgstats.sh就完成了安装。
注意脚本中psql的路径为/usr/bin/psql，如果你安装的psql不在这个目录下，需要修改install_pgstats.sh脚本中的psql的路径

pgstatspack需要pg_stat_statements包的支持，如果数据库没有安装pg_stat_statements，需要装上，安装pg_stat_statements的方法如下：
到PostgreSQL的源码的contrib/pg_stat_statements目录下，运行
make && make install
然后再运行psql -f pg_stat_statements.sql就完成了pg_stat_statements安装，这时还需要把pg_stat_statements加到数据库的postgresql.conf文件中的shared_preload_libraries参数中：
shared_preload_libraries = 'pg_stat_statements'
然后再重新启数据库。

使用：
完成安装后，就可以使用pgstatspack了。
使用的方法是运行~/pgstatspack/bin下的脚本， 这个目录下有如下脚本：
osdba@osdba-laptop:~/pgstatspack/bin$ ls -l
总用量 24
-rwxr-xr-x 1 osdba osdba 250 2008-08-28 03:22 delete_snapshot.sh
-rwxr-xr-x 1 osdba osdba 13822 2010-09-30 16:49 pgstatspack_report.sh
-rwxr-xr-x 1 osdba osdba 289 2010-09-30 16:50 snapshot.sh
./snapshot.sh脚本是生成统计信息快照的，这里可以运行一次，过一会再运行一次，生成两次快照后，就可以查看两次快照间数据库的性能数据了。
注意这些脚本中psql的路径为/usr/bin/psql，如果你安装的psql不在这个目录下，需要修改in脚本中的psql的路径

下面是我运行的情况：
osdba@osdba-laptop:~/pgstatspack/bin$ ./snapshot.sh
~/pgstatspack/bin ~/pgstatspack/bin
Results for database osdba
pgstatspack_snap
------------------
5
(1 row)

osdba@osdba-laptop:~/pgstatspack/bin$ ./snapshot.sh
~/pgstatspack/bin ~/pgstatspack/bin
Results for database osdba
pgstatspack_snap
------------------
6
(1 row)

osdba@osdba-laptop:~/pgstatspack/bin$ ./snapshot.sh
~/pgstatspack/bin ~/pgstatspack/bin
Results for database osdba
pgstatspack_snap
------------------
7
(1 row)

~/pgstatspack/bin
osdba@osdba-laptop:~/pgstatspack/bin$ ./pgstatspack_report.sh
~/pgstatspack/bin ~/pgstatspack/bin
Please specify a username:
osdba
List of available databases:

1 . osdba

Please select a number from the above list [ 1 - 1 ]
1
snapid | ts | description
--------+----------------------------+---------------------
7 | 2010-09-30 16:50:16.732587 | cron based snapshot
6 | 2010-09-30 16:48:04.497724 |
5 | 2010-09-30 16:47:22.715314 |
(3 rows)

Enter start snapshot id : 5
Enter stop snapshot id : 6
No filename defined using default /tmp/pgstatreport_5_6.txt
###########################################################################################################
PGStatspack version 0.3 by frits.hoogland@interaccess.nl
###########################################################################################################

Snapshot information
Begin snapshot :
snapid | ts | description
--------+----------------------------+-------------
5 | 2010-09-30 16:47:22.715314 |
(1 row)

End snapshot :
snapid | ts | description
--------+----------------------------+-------------
6 | 2010-09-30 16:48:04.497724 |
(1 row)

Seconds in snapshot: 41.78241


Database version
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 9.0.0 on x86_64-unknown-linux-gnu,compiled by GCC gcc (Ubuntu 4.4.3-4ubuntu5) 4.4.3,64-bit
(1 row)

Database information
current_database | dbsize
------------------+---------
osdba | 5865 kB
(1 row)


Database statistics
database | tps | hitrate | lio_ps | pio_ps | rollbk_ps
-----------+------+---------+--------+--------+-----------
osdba | 0.36 | 94.00 | 80.42 | 4.33 | 0.00
postgres | 0.05 | 92.00 | 16.68 | 1.17 | 0.00
template1 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00
template0 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00
(4 rows)


Top 20 tables ordered by table size changes
table | table_growth | index_growth
-------------------------------+--------------+--------------
public.pgstatspack_indexes | 16384 | 0
pg_catalog.pg_statistic | 8192 | 0
pg_catalog.pg_database | 0 | 0
pg_catalog.pg_db_role_setting | 0 | 0
pg_catalog.pg_description | 0 | 0
pg_catalog.pg_proc | 0 | 0
pg_catalog.pg_rewrite | 0 | 0
pg_catalog.pg_shdescription | 0 | 0
pg_catalog.pg_trigger | 0 | 0
public.pgstatspack_sequences | 0 | 0
public.pgstatspack_settings | 0 | 0
pg_catalog.pg_attrdef | 0 | 0
public.pgstatspack_statements | 0 | 0
pg_catalog.pg_constraint | 0 | 0
(14 rows)


Top 20 tables ordered by high table to index read ratio
table | system_read_pct | table_read_pct | index_read_pct
--------------------------+-----------------+----------------+----------------
pg_catalog.pg_proc | 58 | 0 | 100
pg_catalog.pg_statistic | 19 | 0 | 100
pg_catalog.pg_database | 11 | 60 | 40
pg_catalog.pg_rewrite | 6 | 0 | 100
pg_catalog.pg_constraint | 5 | 88 | 11
(5 rows)


Top 20 tables ordered by inserts
table | table_inserts
-------------------------------+---------------
public.pgstatspack_indexes | 112
public.pgstatspack_settings | 23
pg_catalog.pg_statistic | 8
public.pgstatspack_sequences | 1
pg_catalog.pg_attrdef | 0
pg_catalog.pg_constraint | 0
pg_catalog.pg_database | 0
pg_catalog.pg_db_role_setting | 0
pg_catalog.pg_description | 0
pg_catalog.pg_proc | 0
pg_catalog.pg_rewrite | 0
pg_catalog.pg_shdescription | 0
pg_catalog.pg_trigger | 0
public.pgstatspack_statements | 0
(14 rows)


Top 20 tables ordered by updates
table | table_updates
-------------------------------+---------------
pg_catalog.pg_attrdef | 0
pg_catalog.pg_constraint | 0
pg_catalog.pg_database | 0
pg_catalog.pg_db_role_setting | 0
pg_catalog.pg_description | 0
pg_catalog.pg_proc | 0
pg_catalog.pg_rewrite | 0
pg_catalog.pg_shdescription | 0
pg_catalog.pg_statistic | 0
pg_catalog.pg_trigger | 0
public.pgstatspack_indexes | 0
public.pgstatspack_sequences | 0
public.pgstatspack_settings | 0
public.pgstatspack_statements | 0
(14 rows)


Top 20 tables ordered by deletes
table | table_deletes
-------------------------------+---------------
pg_catalog.pg_attrdef | 0
pg_catalog.pg_constraint | 0
pg_catalog.pg_database | 0
pg_catalog.pg_db_role_setting | 0
pg_catalog.pg_description | 0
pg_catalog.pg_proc | 0
pg_catalog.pg_rewrite | 0
pg_catalog.pg_shdescription | 0
pg_catalog.pg_statistic | 0
pg_catalog.pg_trigger | 0
public.pgstatspack_indexes | 0
public.pgstatspack_sequences | 0
public.pgstatspack_settings | 0
public.pgstatspack_statements | 0
(14 rows)


Tables ordered by percentage of tuples scanned
table | rows_read_pct | tab_hitrate | idx_hitrate | tab_read | tab_hit | idx_read | idx_hit
--------------------------+---------------+-------------+-------------+----------+---------+----------+---------
pg_catalog.pg_proc | 58 | 75 | 89 | 23 | 72 | 18 | 154
pg_catalog.pg_statistic | 19 | 76 | 95 | 13 | 45 | 2 | 63
pg_catalog.pg_database | 11 | 91 | 76 | 0 | 11 | 2 | 10
pg_catalog.pg_rewrite | 6 | 33 | 72 | 7 | 4 | 2 | 8
pg_catalog.pg_constraint | 5 | 33 | 0 | 1 | 1 | 2 | 0
(5 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 | 342 | 342 | 342 | 0 | 343
pg_catalog.pg_cast_source_target_index | pg_catalog.pg_cast | 208 | 39 | 39 | 2 | 208
pg_catalog.pg_attribute_relid_attnum_index | pg_catalog.pg_attribute | 193 | 435 | 435 | 2 | 385
pg_catalog.pg_proc_oid_index | pg_catalog.pg_proc | 71 | 71 | 71 | 9 | 134
pg_catalog.pg_type_oid_index | pg_catalog.pg_type | 60 | 59 | 59 | 2 | 60
pg_catalog.pg_statistic_relid_att_inh_index | pg_catalog.pg_statistic | 55 | 34 | 34 | 2 | 63
pg_catalog.pg_class_relname_nsp_index | pg_catalog.pg_class | 44 | 26 | 26 | 5 | 84
pg_catalog.pg_amop_fam_strat_index | pg_catalog.pg_amop | 37 | 57 | 57 | 0 | 75
pg_catalog.pg_index_indexrelid_index | pg_catalog.pg_index | 35 | 35 | 35 | 0 | 36
pg_catalog.pg_amop_opr_fam_index | pg_catalog.pg_amop | 30 | 36 | 36 | 2 | 30
pg_catalog.pg_operator_oid_index | pg_catalog.pg_operator | 23 | 23 | 23 | 3 | 45
pg_catalog.pg_operator_oprname_l_r_n_index | pg_catalog.pg_operator | 19 | 97 | 97 | 5 | 34
pg_catalog.pg_index_indrelid_index | pg_catalog.pg_index | 17 | 27 | 27 | 2 | 17
pg_catalog.pg_amproc_fam_proc_index | pg_catalog.pg_amproc | 16 | 16 | 16 | 0 | 17
pg_catalog.pg_opclass_oid_index | pg_catalog.pg_opclass | 15 | 15 | 15 | 0 | 16
pg_catalog.pg_proc_proname_args_nsp_index | pg_catalog.pg_proc | 14 | 33 | 33 | 9 | 20
pg_catalog.pg_type_typname_nsp_index | pg_catalog.pg_type | 13 | 8 | 8 | 5 | 22
pg_catalog.pg_opclass_am_name_nsp_index | pg_catalog.pg_opclass | 11 | 440 | 440 | 2 | 11
pg_catalog.pg_rewrite_rel_rulename_index | pg_catalog.pg_rewrite | 9 | 11 | 11 | 2 | 8
pg_catalog.pg_namespace_nspname_index | pg_catalog.pg_namespace | 7 | 6 | 6 | 2 | 6
pg_catalog.pg_database_oid_index | pg_catalog.pg_database | 6 | 6 | 6 | 0 | 8
pg_catalog.pg_db_role_setting_databaseid_rol_index | pg_catalog.pg_db_role_setting | 6 | 0 | 0 | 0 | 6
pg_toast.pg_toast_2618_index | pg_toast.pg_toast_2618 | 4 | 13 | 13 | 2 | 3
pg_catalog.pg_database_datname_index | pg_catalog.pg_database | 2 | 2 | 2 | 2 | 2
pg_catalog.pg_aggregate_fnoid_index | pg_catalog.pg_aggregate | 2 | 2 | 2 | 2 | 1
pg_catalog.pg_tablespace_oid_index | pg_catalog.pg_tablespace | 2 | 2 | 2 | 2 | 1
pg_catalog.pg_namespace_oid_index | pg_catalog.pg_namespace | 1 | 1 | 1 | 2 | 0
pg_catalog.pg_depend_reference_index | pg_catalog.pg_depend | 1 | 1 | 1 | 3 | 0
pg_catalog.pg_authid_oid_index | pg_catalog.pg_authid | 1 | 1 | 1 | 2 | 0
pg_catalog.pg_language_oid_index | pg_catalog.pg_language | 1 | 1 | 1 | 2 | 0
pg_catalog.pg_constraint_oid_index | pg_catalog.pg_constraint | 1 | 1 | 1 | 2 | 0
pg_catalog.pg_inherits_parent_index | pg_catalog.pg_inherits | 1 | 0 | 0 | 1 | 0
pg_catalog.pg_inherits_relid_seqno_index | pg_catalog.pg_inherits | 1 | 0 | 0 | 1 | 0
public.pgstatspack_tables_pk | public.pgstatspack_tables | 0 | 0 | 0 | 2 | 13
public.pgstatspack_database_pk | public.pgstatspack_database | 0 | 0 | 0 | 2 | 3
public.pgstatspack_indexes_pk | public.pgstatspack_indexes | 0 | 0 | 0 | 7 | 128
public.t_pkey | public.t | 0 | 0 | 0 | 2 | 1
public.pgstatspack_sequences_pk | public.pgstatspack_sequences | 0 | 0 | 0 | 2 | 0
public.pgstatspack_settings_pk | public.pgstatspack_settings | 0 | 0 | 0 | 2 | 22
(39 rows)


Sequences ordered by blks_read
schema | name | blks_read | blks_hit
--------+---------------+-----------+----------
public | pgstatspackid | 1 | 0
(1 row)


Top 20 SQL statements ordered by total_time
calls | total_time | rows | query
-------+------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | 0.057520 | 1 | select pgstatspack_snap('');
1 | 0.014059 | 1 | insert into t values(100,'2222222222222');
1 | 0.008528 | 0 | SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '16391' ORDER BY inhseqno
1 | 0.004416 | 1 | select count(*) from t;
1 | 0.003287 | 11 | SELECT n.nspname as "Schema",+
| | | c.relname as "Name",+
| | | CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",+
| | | pg_catalog.pg_get_userbyid(c.relowner) as "Owner" +
| | | FROM pg_catalog.pg_class c +
| | | LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace +
| | | WHERE c.relkind IN ('r','v','S','') +
| | | AND n.nspname <> 'pg_catalog' +
| | | AND n.nspname <> 'information_schema' +
| | | AND n.nspname !~ '^pg_toast' +
| | | AND pg_catalog.pg_table_is_visible(c.oid) +
| | | ORDER BY 1,2;
1 | 0.001429 | 0 | SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '16391' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;
1 | 0.000522 | 1 | SELECT c2.relname,i.indisprimary,i.indisunique,i.indisclustered,i.indisvalid,pg_catalog.pg_get_indexdef(i.indexrelid,true),+
| | | pg_catalog.pg_get_constraintdef(con.oid,contype,condeferrable,condeferred,c2.reltablespace +
| | | FROM pg_catalog.pg_class c,pg_catalog.pg_class c2,pg_catalog.pg_index i +
| | | LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x')) +
| | | WHERE c.oid = '16391' AND c.oid = i.indrelid AND i.indexrelid = c2.oid +
| | | ORDER BY i.indisprimary DESC,i.indisunique DESC,c2.relname
1 | 0.000111 | 1 | SELECT c.oid,+
| | | n.nspname,+
| | | c.relname +
| | | FROM pg_catalog.pg_class c +
| | | LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace +
| | | WHERE c.relname ~ '^(t)$' +
| | | AND pg_catalog.pg_table_is_visible(c.oid) +
| | | ORDER BY 2,3;
1 | 0.000056 | 2 | SELECT a.attname,+
| | | pg_catalog.format_type(a.atttypid,a.atttypmod),+
| | | (SELECT substring(pg_catalog.pg_get_expr(d.adbin,d.adrelid) for 128) +
| | | FROM pg_catalog.pg_attrdef d +
| | | WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),+
| | | a.attnotnull,a.attnum +
| | | FROM pg_catalog.pg_attribute a +
| | | WHERE a.attrelid = '16391' AND a.attnum > 0 AND NOT a.attisdropped +
| | | ORDER BY a.attnum
1 | 0.000052 | 1 | insert into t values(200,'2222222222222');
1 | 0.000022 | 1 | SELECT c.relchecks,c.relkind,c.relhasindex,c.relhasrules,c.relhastriggers,c.relhasoids,'',c.reltablespace,CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END+
| | | FROM pg_catalog.pg_class c +
| | | LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) +
| | | WHERE c.oid = '16391' +
| | |
(11 rows)


Parameters
name | start_setting | stop_setting | source
----------------------------+-----------------------------+-----------------------------+----------------------
application_name | psql | psql | client
config_file | /opt/pgdata/postgresql.conf | /opt/pgdata/postgresql.conf | override
data_directory | /opt/pgdata | /opt/pgdata | override
DateStyle | ISO,YMD | ISO,YMD | configuration file
default_text_search_config | pg_catalog.simple | pg_catalog.simple | configuration file
hba_file | /opt/pgdata/pg_hba.conf | /opt/pgdata/pg_hba.conf | override
ident_file | /opt/pgdata/pg_ident.conf | /opt/pgdata/pg_ident.conf | override
lc_collate | zh_CN.UTF-8 | zh_CN.UTF-8 | override
lc_ctype | zh_CN.UTF-8 | zh_CN.UTF-8 | override
lc_messages | zh_CN.UTF-8 | zh_CN.UTF-8 | configuration file
lc_monetary | zh_CN.UTF-8 | zh_CN.UTF-8 | configuration file
lc_numeric | zh_CN.UTF-8 | zh_CN.UTF-8 | configuration file
lc_time | zh_CN.UTF-8 | zh_CN.UTF-8 | configuration file
log_timezone | PRC | PRC | command line
max_connections | 100 | 100 | configuration file
max_stack_depth | 2048 | 2048 | environment variable
server_encoding | UTF8 | UTF8 | override
shared_buffers | 4096 | 4096 | configuration file
shared_preload_libraries | pg_stat_statements | pg_stat_statements | configuration file
TimeZone | PRC | PRC | command line
timezone_abbreviations | Default | Default | command line
transaction_isolation | read committed | read committed | override
transaction_read_only | off | off | override
(23 rows)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同