Перевод 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. Тут наш с ним небольшой диалог, где он представил объяснение и результаты тестов.
Posts
