В ожидании 8.4 - pg_stat_statements

Перевод Waiting for 8.4 - pg_stat_statements с select * from depesz;

4 января Tom Lane применил патч от Takahiro Itagaki, добавляющий новый contrib модуль - pg_stat_statement:

Добавляет contrib/pg_stat_statements для сбора статистики выполнения запросов в рамках всего сервера.

Takahiro Itagaki

Для чего же это? На самом деле это поможет избавиться от некоторых трудностей таким проектам как pgFoouine или мой analyze.pgsql.logs.pl.

В данный момент, если вы хотите увидеть статистику запросов, вам надо логировать их, а затем использовать какое-либо ПО, которое разберёт лог, нормализует запросы и сформирует по ним сводные данные.

Теперь же часть с разбором лога больше не требуется [*].

Вот как это работает.

Во первых, вам потребуется изменить ваш postgresql.conf. Откройте его и найдите параметр shared_preload_libraries. Добавьте туда pg_stat_statements, следующим образом:

shared_preload_libraries = 'pg_stat_statements'     # (change requires restart)

Как видно из комментария, изменения требуют перезапуска сервера. Но, перед этим добавим в .conf файл ещё несколько опций:

pg_stat_statements.max = 100
pg_stat_statements.track = top
pg_stat_statements.save = off

Для того чтобы это заработало нам надо добавить "pg_stat_statement" в опцию "custom_variable_classes", которая обычно пустая, но если она у вас уже определена, то просто дополните её вот так:

custom_variable_classes = 'depesz,pg_stat_statements'       # list of custom variable class names

Затем можно перезапустить PostgreSQL..

Теперь отслеживание запросов включено, но для просмотра статистики нужно создать соответствующие функции и вью, выполнив pg_stat_statements.sql на любой базе данных:

# \i work/share/postgresql/contrib/pg_stat_statements.sql
SET
CREATE FUNCTION
CREATE FUNCTION
CREATE VIEW
GRANT
REVOKE

(конечно же путь моет быть другим).

Что же, посмотрим как это работает. Первым делом проверим (сразу после коннекта) пустую статистику:

# select * from pg_stat_statements;
 userid | dbid | query | calls | total_time | rows
--------+------+-------+-------+------------+------
(0 rows)

И повторим последний запрос:

# select * from pg_stat_statements;
 userid | dbid  |               query               | calls | total_time | rows
--------+-------+-----------------------------------+-------+------------+------
     10 | 16389 | select * from pg_stat_statements; |     1 |   0.000131 |    0
(1 row)

Вау! Работает.

Теперь очистим статистику (select pg_stat_statements_reset();) и выполним несколько тестов:

(pgdba@[local]:5840) 15:42:41 [pgdba]
# select 1 + 2;
 ?column?
———-
        3
(1 row)

(depesz@[local]:5840) 15:40:39 [depesz]
# select 2 + 3;
 ?column?
———-
        5
(1 row)

(depesz@[local]:5840) 15:43:13 [depesz]
# select count(*) from pg_class where relkind = ‘r’;
 count
——-
    50
(1 row)

Как же теперь выглядит наша статистика?

# select * from pg_stat_statements;
 userid | dbid  |                       query                        | calls | total_time | rows
--------+-------+----------------------------------------------------+-------+------------+------
  16384 | 16388 | select count(*) from pg_class where relkind = 'r'; |     1 |   0.000271 |    1
     10 | 16389 | select 1 + 2;                                      |     1 |    1.9e-05 |    1
  16384 | 16388 | select 2 + 3;                                      |     1 |    2.2e-05 |    1
     10 | 16389 | select pg_stat_statements_reset();                 |     1 |    3.3e-05 |    1
(4 rows)

Здорово. И как это будет работать с prepared statements?

# select pg_stat_statements_reset();
 pg_stat_statements_reset
--------------------------

(1 row)

# prepare x(int4, int4) as select $1 + $2;
PREPARE
# execute x(1,2);
 ?column?
----------
        3
(1 row)

# execute x(2,3);
 ?column?
----------
        5
(1 row)

(pgdba@[local]:5840) 15:45:54 [pgdba]
# prepare y(int4, int4) as select $1 + $2;
PREPARE

(pgdba@[local]:5840) 15:46:00 [pgdba]
# execute y(3,4);
 ?column?
———-
        7
(1 row)

(pgdba@[local]:5840) 15:46:05 [pgdba]
# select * from pg_stat_statements;
 userid | dbid  |                  query                   | calls | total_time | rows
——–+——-+——————————————+——-+————+——
     10 | 16389 | prepare y(int4, int4) as select $1 + $2; |     1 |    1.7e-05 |    1
     10 | 16389 | select pg_stat_statements_reset();       |     1 |    3.4e-05 |    1
     10 | 16389 | prepare x(int4, int4) as select $1 + $2; |     2 |    3.3e-05 |    2
(3 rows)

Интересно. Смотрится так как будто "prepare" был выполнен столько раз, сколько он был запущен. Не смотря на этот момент - выглядит хорошо.

И так, я настроил pg_stat_statements на хранение 100 различных запросов. Что же случится после сотого? Какой же будет уделён?

Эта простая команда добавит 100 разных запросов:

( echo "SELECT pg_stat_statements_reset();"; for a in $( seq 1 99 ); do echo "select $a;"; done ) | psql

# select count(*) from pg_stat_statements;
 count
-------
   100
(1 row)

Но "select count(*) from pg_stat_statements" будет также добавлен. Так что, что-то должно быть удалено. Или, может, count(*) не был добавлен к статистике? Давайте проверим:

# select * from pg_stat_statements order by query;
...

Вероятно, pg_stat_statements удаляет случайную запись с наименьшим количеством вызовов. Т.е. в ста записях вызванных по одному разу, мы не сможем найти ту, которая будет удалена, при появлении нового запроса. Но в общем, это не так уж и важно.

В заключении, я думаю, что польза от модуля будет намного больше, если он будет сохранять запросы без параметров (т.е. вместо "select 2 + 3" -> "select $1 + $2", как-то так), иначе, на реальных базах данных, буфер запросов будет заполняться слишком быстро, и не будет заметен факт того, что "select * from table where id = 3" и "select * from table where id = 23" практически одно и тоже [*].

Но, по крайней мере уже есть какой-то аналитический инструмент для небольших систем.

От автора перевода:

Вообще странно, по моему автор оригинала что-то путает, в документации по модулю всё выглядит намного лучше - факт того, что "select * from table where id = 3" и "select * from table where id = 23", будет учитываться, т.е. запросы нормализуются. Кроме того, автор почему-то не упомянул о такой важной вещи как "pg_stat_statements.track = all", отслеживании вложенных запросов, например, внутри функций.

UPD.
Я был не прав - Hubert описал всё верно, неточность в незаконченной документации к версии 8.4. Тут наш с ним небольшой диалог, где он представил объяснение и результаты тестов.