使用PostgreSQL中的pg_stat_statements来查找慢查询。(使用PostgreSQL的pg_stat_statements功能来检索慢查询。)

简要概述

在PostgreSQL10中查找慢查询的安装和使用pg_stat_statements模块的步骤。在9.6中的步骤也相同。

环境

• CentOS 7.5

postgres (PostgreSQL) 10.10

安装PostgreSQL10

安装PostgreSQL10。

$ yum -y localinstall https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
$ sudo yum -y install postgresql10-server

确认版本

$ /usr/pgsql-10/bin/postgres --version
postgres (PostgreSQL) 10.10

初始化数据库

$ sudo /usr/pgsql-10/bin/postgresql-10-setup initdb
Initializing database ... OK

启用，开始

$ sudo systemctl enable postgresql-10
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-10.service to /usr/lib/systemd/system/postgresql-10.service.
$ sudo systemctl start postgresql-10

登录

$ sudo -u postgres /usr/pgsql-10/bin/psql -U postgres
psql (10.10)
Type "help" for help.

postgres=#

安装pg_stat_statements模块。

提前确认

使用 pg_config 命令来确认库的存储位置。
不使用 /usr/bin/pg_config，而是使用适用于 pgsql-10 的命令。

$ /usr/pgsql-10/bin/pg_config --libdir
/usr/pgsql-10/lib

# 見当たらなければ以下で探す
$ sudo find / -name pg_config

确认是否存在 pg_stat_statements.so。如果不存在，则继续安装下一个postgresql-contrib。

$ find `/usr/pgsql-10/bin/pg_config --libdir` -name pg_stat_statements.so

安装PostgreSQL10-contrib

# CentOSの場合
$ sudo yum install -y postgresql10-contrib

# Ubuntuの場合
$ sudo apt-get install postgresql-contrib-10

已安装pg_stat_statements.so。

$ find `/usr/pgsql-10/bin/pg_config --libdir` -name pg_stat_statements.so
/usr/pgsql-10/lib/pg_stat_statements.so

启用pg_stat_statements

确认 pg_stat_statements 是否可用作扩展（Extension）。如果显示如下，则表示有效。然而，由于 installed_version 为空白，所以尚未安装。

$ sudo -u postgres /usr/pgsql-10/bin/psql -U postgres
psql (10.10)
Type "help" for help.

postgres=# \x
postgres=# select * from pg_available_extensions where name = 'pg_stat_statements';
-[ RECORD 1 ]-----+----------------------------------------------------------
name              | pg_stat_statements
default_version   | 1.6
installed_version |
comment           | track execution statistics of all SQL statements executed

启用pg_stat_statements。

postgres=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

如果需要刪除的話，可以使用 DROP EXTENSION pg_stat_statements。

已安装的版本中插入了版本并启用了模块。

postgres=# select * from pg_available_extensions where name = 'pg_stat_statements';
-[ RECORD 1 ]-----+----------------------------------------------------------
name              | pg_stat_statements
default_version   | 1.6
installed_version | 1.6
comment           | track execution statistics of all SQL statements executed

修改postgresql.conf文件

$ sudo find / -name postgresql.conf
/var/lib/pgsql/10/data/postgresql.conf

将`/var/lib/pgsql/10/data/postgresql.conf`文件进行如下修改。

-#shared_preload_libraries = ''          # (change requires restart)
+shared_preload_libraries = 'pg_stat_statements'

重新启动PostgreSQL

$ sudo systemctl restart postgresql-10

设置已完成。

檢查緩慢的查詢速度

你可以使用下面的SQL进行查询。

postgres=# \x
postgres=# SELECT * FROM pg_stat_statements order by total_time desc limit 1;
-[ RECORD 1 ]-------+------------------------------------------------------
userid              | 10
dbid                | 13808
queryid             | 3494477430
query               | select * from pg_available_extensions where name = $1
calls               | 1
total_time          | 1.105203
min_time            | 1.105203
max_time            | 1.105203
mean_time           | 1.105203
stddev_time         | 0
rows                | 1
shared_blks_hit     | 0
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

请参考

pg_stat_statementsインストール・設定編