使用PostgreSQL下的statspack

来源：互联网发布：棋牌游戏源码多少钱编辑：程序博客网时间：2024/05/16 06:14

在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

------------------

(1 row)

osdba@osdba-laptop:~/pgstatspack/bin$ ./snapshot.sh

~/pgstatspack/bin ~/pgstatspack/bin

Results for database osdba

pgstatspack_snap

------------------

(1 row)

osdba@osdba-laptop:~/pgstatspack/bin$ ./snapshot.sh

~/pgstatspack/bin ~/pgstatspack/bin

Results for database osdba

pgstatspack_snap

------------------

(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 ]

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

-----------+------+---------+--------+--------+-----------

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

--------------------------+---------------+-------------+-------------+----------+---------+----------+---------

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

----------------------------------------------------+-------------------------------+-------+----------+-----------+---------------+--------------

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