PPAS下安装 pg_stat_statements过程记录

磨砺技术珠矶,践行数据之道,追求卓越价值

回到上一级页面: PostgreSQL统计信息索引页     回到顶级页面:PostgreSQL索引页

PostgreSQL中,如何安装pg_stat_statements,网上有很多这方面的论述。这里说说如何在PPAS下安装它:

实验一:首先,看是否可以不安装,直接拿来用:

[root@rhjp001 ~]# su - enterprisedb
-bash-3.2$ pwd
/opt/PostgresPlus/9.2AS
-bash-3.2$ ./bin/psql -d edb
psql (9.2.1.3)
"help" でヘルプを表示します.

edb=# select count(*) from pg_stat_statements;
ERROR:  pg_stat_statements must be loaded via shared_preload_libraries
edb=# 

实验二:直接创建:

edb=# create extension pg_stat_statments;
ERROR:  拡張機能の制御ファイル "/opt/PostgresPlus/9.2AS/share/extension/pg_stat_statments.control" をオープンできませんでした: そのようなファイルやディレクトリはありません
edb=# 

实验三:改配置文件后,再创建:

-bash-3.2$ pwd
/opt/PostgresPlus/9.2AS/data
-bash-3.2$ vim postgresql.conf
-bash-3.2$ cat postgresql.conf | grep preload
shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen,pg_stat_statements'
#local_preload_libraries = ''
-bash-3.2$ 

重新启动后,创建,其实已经不用创建了。

[root@rhjp001 ~]# su - enterprisedb
-bash-3.2$ pwd
/opt/PostgresPlus/9.2AS
-bash-3.2$ ./bin/psql -d edb
psql (9.2.1.3)
"help" でヘルプを表示します.

edb=# create extension pg_stat_statements;
ERROR:  拡張機能 "pg_stat_statements" はすでに存在します
edb=# 
[root@rhjp001 ~]# su - enterprisedb
-bash-3.2$ ./bin/psql -d edb
psql (9.2.1.3)
"help" でヘルプを表示します.

edb=# select count(*) from pg_stat_statements;
 count 
-------
    47
(1 行)

edb=# 

也就是说,其实在PPAS安装好之后,如果想要使用 pg_stat_statements ,只要配置postgresql.conf的 shared_preload_libraries 就可以了。

卸载ppas,重新安装,然后再重新开始吧:

配置postgresql.conf:

把 shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen'

改成:

shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen,pg_stat_statements'

重新启动数据库: service ppas-9.2 restart

-bash-3.2$ ./bin/psql -d edb
psql (9.2.1.3)
"help" でヘルプを表示します.

edb=# select count(*) from pg_stat_statements;
 count 
-------
    42
(1 行)

edb=# 

这个pg_stat_statements,主要可以用于区分运行最慢的sql文:

例如:

edb=# \d pg_stat_statements;
    ビュー "enterprisedb.pg_stat_statements"
         列          || 修飾語 
---------------------+------------------+--------
 userid              | oid              | 
 dbid                | oid              | 
 query               | text             | 
 calls               | bigint           | 
 total_time          | double precision | 
 rows                | bigint           | 
 shared_blks_hit     | bigint           | 
 shared_blks_read    | bigint           | 
 shared_blks_dirtied | bigint           | 
 shared_blks_written | bigint           | 
 local_blks_hit      | bigint           | 
 local_blks_read     | bigint           | 
 local_blks_dirtied  | bigint           | 
 local_blks_written  | bigint           | 
 temp_blks_read      | bigint           | 
 temp_blks_written   | bigint           | 
 blk_read_time       | double precision | 
 blk_write_time      | double precision | 

edb=# 

查找最慢的10条sql文(这里用的是累计时间)

edb=# select * from pg_stat_statements order by total_time desc limit 10;
-[ RECORD 1 ]-------+------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
userid              | 10
dbid                | 14000
query               | drop extension pg_stat_statements;
calls               | 2
total_time          | 3099.557
rows                | 0
shared_blks_hit     | 673
shared_blks_read    | 27
shared_blks_dirtied | 9
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 0
blk_write_time      | 0
-[ RECORD 2 ]-------+------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
userid              | 10
dbid                | 14000
query               | create database gaodb owner gao;
calls               | 1
total_time          | 2068.82
rows                | 0
shared_blks_hit     | 54
shared_blks_read    | 4
shared_blks_dirtied | 7
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 0
blk_write_time      | 0
-[ RECORD 3 ]-------+------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
userid              | 10
dbid                | 14000
query               | CREATE TEMP TABLE pga_tmp_zombies(jagpid int4)
calls               | 3
total_time          | 1061.702
rows                | 0
shared_blks_hit     | 479
shared_blks_read    | 109
shared_blks_dirtied | 37
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 0
blk_write_time      | 0
-[ RECORD 4 ]-------+------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
userid              | 10
dbid                | 14000
query               | SELECT count(*) As count, pg_backend_pid() AS pid FROM pg_class cl JOIN pg_na
mespace ns ON ns.oid=relnamespace WHERE relname=? AND nspname=?
calls               | 3
total_time          | 929.614
rows                | 3
shared_blks_hit     | 6
shared_blks_read    | 15
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 0
blk_write_time      | 0
-[ RECORD 5 ]-------+------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
userid              | 10
dbid                | 14000
query               | SELECT COUNT(*)    FROM pg_proc  WHERE proname = ? AND pronamespace = (
SELECT oid    FROM pg_namespace  WHERE nspname = ?) AND prorettype = (SELECT oid   ROM pg_type  WHERE typnam e = ?) AND   proargtypes = ?                                     
calls               | 3
total_time          | 337.312
rows                | 3
shared_blks_hit     | 15
shared_blks_read    | 15
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 0
blk_write_time      | 0
-[ RECORD 6 ]-------+------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
userid              | 10
dbid                | 14000
query               | SELECT pgagent.pgagent_schema_version()
calls               | 3
total_time          | 321.264
rows                | 3
shared_blks_hit     | 12
shared_blks_read    | 12
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 0
blk_write_time      | 0
-[ RECORD 7 ]-------+------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
userid              | 10
dbid                | 14000
query               | DROP TABLE pga_tmp_zombies
calls               | 3
total_time          | 282.334
rows                | 0
shared_blks_hit     | 360
shared_blks_read    | 21
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 0
blk_write_time      | 0
-[ RECORD 8 ]-------+------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
userid              | 10
dbid                | 14000
query               | create extension pg_stat_statements;
calls               | 1
total_time          | 155.641
rows                | 0
shared_blks_hit     | 585
shared_blks_read    | 22
shared_blks_dirtied | 25
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 0
blk_write_time      | 0
-[ RECORD 9 ]-------+------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
userid              | 16684
dbid                | 16685
query               | create table gaotab(id integer);
calls               | 1
total_time          | 143.838
rows                | 0
shared_blks_hit     | 195
shared_blks_read    | 59
shared_blks_dirtied | 21
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 0
blk_write_time      | 0
-[ RECORD 10 ]------+------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
userid              | 16684
dbid                | 16685
query               | select pg_available_extensions();
calls               | 2
total_time          | 92.16
rows                | 100
shared_blks_hit     | 5
shared_blks_read    | 1
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 0
blk_write_time      | 0

edb=#  

需要注意,只有管理员用户才可以看到这个视图。

 

回到上一级页面: PostgreSQL统计信息索引页     回到顶级页面:PostgreSQL索引页

磨砺技术珠矶,践行数据之道,追求卓越价值 

posted @ 2013-08-14 18:01  健哥的数据花园  阅读(2293)  评论(0编辑  收藏  举报