Campaign Classicv7 — 数据库模块pg_stat_statements的用途和用法

什么是数据库模块pg_stat_statements？

为什么客户端请求启用它？

如何激活它？

pg_stat_statements模块提供了一种方法，用于跟踪服务器执行的所有SQL语句的执行统计信息。

该模块必须通过将pg_stat_statements添加到postgresql.conf中的shared_preload_libraries来加载，因为它需要额外的共享内存。

这意味着需要重新启动服务器才能添加或删除模块。

链接https://www.postgresql.org/docs/9.4/pgstatstatements.html

在此表上实施视图类型模式，该模式允许我们监视需要很长时间才能完成的查询。

这样，客户端将能够调整这些并优化性能。

此扩展提供有关运行查询的量度：哪些查询最常运行，以及每个查询需要多长时间。

pg_stat_statements

1. 验证是否已安装postgres版本的正式PostgreSQL参与者包。

   code language-none
   $ dpkg -l | grep postgres postgresql-contrib-9.3

   如果未安装，请安装。

   code language-none
   $ apt-get install postgresql-contrib-9.3

2. 更改postgresql.conf以在启动时预加载模块。

   默认： shared_preload_libraries = ''

   新建： shared_preload_libraries = 'pg_stat_statements'

   如果同时使用pg_stat_statements和auto_explain，请使用 ONE 记录，并用逗号分隔。

   code language-none
   shared_preload_libraries = 'auto_explain,pg_stat_statements'

3. 在要监视的数据库中，执行CREATE查询：

   不需要为auto_explain创建扩展，只为pg_stat_statements。

   code language-none
   psql postgres=# \c mydatabase mydatabase=# CREATE EXTENSION pg_stat_statements;

4. 在postgresql.conf的自定义OPTIONS部分中，添加以下行：

   code language-none
   pg_stat_statements.max = 5000 pg_stat_statements.track = all pg_stat_statements.save = on

5. 重新启动postgresql服务。

示例：获取数据库中开销最大的5个查询。

SELECT query, calls, total_time, rows, 100.0*shared_blks_hit/nullif(shared_blks_hit+shared_blks_read,0) AS hit_percent
from pg_stat_statements as s inner join pg_database as d on d.oid = s.dbid
where d.datname = 'Database_Name'
order by total_time desc limit 5;