使用PostgreSQL下的statspack

概述在Oracle下有一个查看分析数据库的工具叫statspack，在PostgreSQL下也有这样的一个工具。下面将介绍如何使用这个工具。 安装： 在网站上下载： http://pgfoundry.org/projects/pgstatspack,在这个页面中可以看到下载链接，把pgStatspack下载下来。我下载的是pgstatspack_version_2.2.tar.gz，把这个包解压到一个

在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) 总结

以上是内存溢出为你收集整理的使用PostgreSQL下的statspack全部内容，希望文章能够帮你解决使用PostgreSQL下的statspack所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。