Статистика запросов и pg_stat_statements

Иногда при эксплуатации проекта возникает вопрос, какие запросы в БД выполняются дольше всего или потребляют наибольшее количество времени или ресурсов.

До версии 9.2 неплохой ответ на этот вопрос можно было получить с помощью проекта pgBadger. Если прорваться через достаточно простую процедуру его настройки, описанную в документации, то в результате можно получить достаточно красивый отчет. К сожалению, этот подход имеет достаточно много слабых сторон. Во-первых, чтобы получить полную картину, необходимо писать логи всех запросов к БД, которые при значительной нагрузке отъедают огромное количество дискового пространства, а также производительность дисковой подсистемы. Во-вторых, в сухом остатке получается только суммарное время исполнения всех запросов и их количество. Это полезная информация, но хотелось бы знать много чего еще.

Такого же результата можно добиться с помощью сбора статистики на стороне клиента, например, так это сделано в newrelic.

Модуль pg_stat_statements появился в PostgreSQL уже достаточно давно, но только в 9.2 он научился «нормализовать» запросы, объединяя запросы, которые отличаются только параметрами, в один.

Чтобы воспользоваться этим модулем, необходимо добавить следующую строчку в postgresql.conf.

shared_preload_libraries = 'pg_stat_statements'         # (change requires restart)

После чего необходимо перезапустить сервер БД. После этого в БД, выполните следующую команду:

CREATE EXTENSION pg_stat_statements

После этого в БД, где вы выполнили эту команду, появится представление (view) pg_stat_statements.

$ psql dbname
dbname=# \x
Расширенный вывод включен.

doman=# select * from pg_stat_statements;

userid              | 10
dbid                | 16388
query               | SELECT  "words".* FROM "words"  WHERE "words"."id" = ? LIMIT ?
calls               | 27
total_time          | 0.277
rows                | 27
shared_blks_hit     | 76
shared_blks_read    | 6
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.051
blk_write_time      | 0

Для того, чтобы показывались последние две строчки, необходимо включить track_io_timing, для чего надо добавить в postgresql.conf следующую строчку.

track_io_timing = on

Давайте внимательнее посмотрим на вывод этого запроса. userid — это id пользователя, который выполнял запрос, dbid — id базы данных, в которой выполнялся этот запрос. Узнать его можно, выполнив select oid, * from pg_database. Далее следует нормализованный запрос (query), количество вызовов (calls), общее время выполнения всех вызовов (total_time).

Все это можно было узнать и из pgBadger, а вот дальше начинаются вкусности:

  • rows — суммарное количество вовзращенных строк;
  • shared_blks_hit — количество страниц, которые были в кэше БД;
  • shared_blks_read — количество страниц, которые были прочитаны с диска, чтобы выполнить запросы такого типа;
  • shared_blks_dirtied — количество страниц, которые были изменены;
  • shared_blks_written — количество страниц, которые были записаны на диск;
  • local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written — то же самое, что предыдущие 4, только для временных таблиц и индексов;
  • temp_blks_read — сколько страниц временных данных было прочитано;
  • temp_blks_written — сколько страниц временных данных было записано (используется при сортировке на диски, джойнах и других временных операциях);
  • blk_read_time — сколько времени суммарно заняло чтение с диска;
  • blk_write_time — сколько времени суммарно заняла запись на диск.

Вооружившись такой статистикой и вашим ясным аналитическим разумом, можно значительно увеличить производительность вашей БД.

Конечно, такая красота достается не бесплатно, pg_stat_statements создает дополнительную нагрузку на БД. Неплохой анализ этой нагрузки есть в конце этой отличной статьи.

comments powered by Disqus