PPAS下安装 pg_stat_statements过程记录-白红宇

PPAS下安装 pg_stat_statements过程记录

阅读量：4449 次

发布时间：2019-06-07

本文共 13558 字，大约阅读时间需要 45 分钟。

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

回到上一级页面：回到顶级页面：

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

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

[root@rhjp001 ~]# su - enterprisedb-bash-3.2$ pwd/opt/PostgresPlus/9.2AS-bash-3.2$ ./bin/psql -d edbpsql (9.2.1.3)"help" でヘルプを表示します.edb=# select count(*) from pg_stat_statements;ERROR:  pg_stat_statements must be loaded via shared_preload_librariesedb=#

实验二：直接创建：

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

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

回到上一级页面：回到顶级页面：

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

转载于:https://www.cnblogs.com/gaojian/p/3257581.html

你可能感兴趣的文章

java.lang.ClassNotFoundException错误原因汇总

查看>>

设计模式