在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不在这个目录下，需要修改脚本中的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, 0, true), + | | | pg_catalog.pg_get_constraintdef(con.oid, true), 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) ########## 一些统计指标的解释 ########## *Database statistics Database level statistics. tps - transactions per second Number of committed transactions between snapshots, devided by the number of seconds between the two snapshots. pg_stat_database.xact_commit - number of committed transactions hitrate Number of cache reads as a percentage of the total number of reads (cache and physical) between the two snapshots. pg_stat_database.blks_hit - cache reads pg_stat_database.blks_read - physical reads lio_ps Number of logical reads between snapshots, devided by the number of seconds between the two snapshots. Logical reads is in postgresql the number of physical reads + the number of cache reads. pg_stat_database.blks_hit - cache reads pg_stat_database.blks_read - physical reads pio_ps Number of physical reads between snapshots, devided by the number of seconds between the two snapshots. pg_stat_database.blks_read - physical reads rollbk_ps Number of rollbacks between snapshots, devided by the number of seconds between the two snapshots. pg_stat_database.xact_rollback *Tables ordered by percentage of tuples scanned Table level statistics. table The name of the table pg_stat_all_tables.relname rows_read_pct The total number of rows read from the table, either via table or via index between the two snapshots. pg_stat_all_tables.seq_tup_read - number of rows fetched by table scan / seq scan pg_stat_all_tables.idx_tup_fetch - number of rows fetched by index scan tab_hitrate Number of cache reads as a percentage of the total number of reads (cache and physical) of table scans of the individual table between the snapshots. pg_statio_all_tables.heap_blks_hit - number of blocks found in cache for a table read pg_statio_all_tables.heap_blks_read - number of blocks read from disk for a table read idx_hitrate Number of cache reads as a percentage of the total number of reads (cache and physical) of index scans of an individual table between the snapshots. pg_statio_all_tables.idx_blks_hit - number of blocks found in cache for a table read pg_statio_all_tables.idx_blks_read - number of blocks read from disk for a table read tab_read Number of physical reads for a table block between snapshots. pg_statio_all_tables.heap_blks_read - number of blocks read from disk for a table read tab_hit Number of cache reads for a table block between snapshots. pg_statio_all_tables.heap_blks_hit - number of blocks found in cache for a table read idx_read Number of physical reads for an index block between snapshots. pg_statio_all_tables.idx_blks_read - number of blocks read from disk for an index read idx_hit Number of cache reads for an index block between snapshots. pg_statio_all_tables.idx_blks_hit - number of blocks read from cache for an index read *Indexes ordered by scans Index level statistics. index The name of the index. pg_stat_all_indexes.indexrelname - name of the index table The name of the table which column(s) are used. pg_stat_all_indexes.relname - name of the table on which the index is based scans The number of scans initiated on the index between snapshots. pg_stat_all_indexes.idx_scan - number of scans initiated tup_read The number of index entries returned by scans on that index between snapshots. pg_stat_all_indexes.idx_tup_read tup_fetch The number of live table rows fetched by simple index scan on that index (can be less if dead or not-yet committed rows are fetched) between snapshots. pg_stat_all_indexes.idx_tup_fetched idx_blks_read The number of physical blocks read for that index between snapshots. pg_statio_all_indexes.idx_blks_read idx_blks_hit The number of cache reads for that index between snapshots. pg_statio_all_indexes.idx_blks_hit *Sequences Sequence statistics schema The schema in which the sequence is created. pg_statio_all_sequences.schemaname name The name of the sequence. pg_statio_all_sequences.relname blks_read The number of physical blocks read for that sequence between snapshots. pg_statio_all_sequences.blks_read blks_hit The number of cache reads for that sequence between snapshots. pg_statio_all_sequences.blks_hit *Parameters The non-default parameters of the postgresql instance. name The parameter name pg_settings.name start_setting The setting of the parameter at the moment of the begin snapshot. pg_settings.setting stop_setting The setting of the parameter at the moment of the end snapshot. pg_settings.setting source The location/method how the setting is done pg_settings.source