Как идентифицировать медленные запросы в PostgreSQL

Хорошо, допустим, Вы сделали свою работу действительно правильно:

•     Нормализация / денормализация создала идеально сбалансированную схему
•     Ограничения применяются тщательно
•     Вы подумали об индексах, выбрали правильные столбцы на основе вашего фильтра и критериев заказа
•     Ваши PL/pgSQL хранимые процедуры и триггерный код следуют рекомендациям
•     Вы избежали очевидных ловушек, таких как разделение последовательностей на несколько таблиц
•     Вы определили разделы, которые дают почти одинаковые размеры сегментов
•     Сервер имеет правильный размер, IO является мощным
•     Конфигурация PostgreSQL сделана вдумчиво.

И все же, некоторые запросы медленные.

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

Во-первых, вы должны спросить себя: что значит медленный в вашем контексте? Какое максимальное время отклика вы можете допустить в своем приложении или услуге? В веб-приложении время ответа 100 мс может быть слишком большим, тогда как вы можете быть очень счастливы, когда большой финансовый квартальный отчет выполняется менее 8 часов.

Во время разработки и тестирования проблема могла не проявиться. Это очень типично, потому что слишком часто среда тестирования и производства не сравнимы (что является большой ошибкой и должно быть исправлено надлежащим образом в настройке DevOps).

log_min_duration_statement

Когда вы знаете, какое максимальное время выполнения запроса приемлемо, вы можете указать PostgreSQL регистрировать операторы, которые занимают больше времени, добавив это в postgresql.conf:

log_min_duration_statement=5000

Это будет регистрировать все вызовы, которые занимают более 5 секунд.

Чтобы активировать, попросите PostgreSQL повторно загрузить файл конфигурации в сеансе SQL:

SELECT pg_reload_conf();

Вы можете проверить это при выдаче

SELECT pg_sleep(7.5);

а затем посмотрите файл логов:

2019-03-07 18:44:12.727 CET [31308] postgres@postgres LOG:  duration: 7515.906 ms  statement: SELECT pg_sleep(7.5);

pg_stat_statements

Это расширение PostgreSQL, которое а) необходимо загружать во время запуска процесса сервера и б) активировать как расширение.

Для этого добавьте эту строку в postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Затем расширение необходимо привязать к базе данных, где вы хотите, чтобы оно было активным, подключиться к базе данных и выполнить:

CREATE EXTENSION pg_stat_statements;

Это добавляет очень полный набор информации для каждого выполняемого запроса. Ниже приведено определение схемы для представления ситуации.

         View "public.pg_stat_statements"
       Column        |       Type       | Collation | Nullable | Default
---------------------+------------------+-----------+----------+---------
 userid              | oid              |           |          |
 dbid                | oid              |           |          |
 queryid             | bigint           |           |          |
 query               | text             |           |          |
 calls               | bigint           |           |          |
 total_time          | double precision |           |          |
 min_time            | double precision |           |          |
 max_time            | double precision |           |          |
 mean_time           | double precision |           |          |
 stddev_time         | double precision |           |          |
 rows                | bigint           |           |          |
 shared_blks_hit     | bigint           |           |          |
 shared_blks_read    | bigint           |           |          |
 shared_blks_dirtied | bigint           |           |          |
 shared_blks_written | bigint           |           |          |
 local_blks_hit      | bigint           |           |          |
 local_blks_read     | bigint           |           |          |
 local_blks_dirtied  | bigint           |           |          |
 local_blks_written  | bigint           |           |          |
 temp_blks_read      | bigint           |           |          |
 temp_blks_written   | bigint           |           |          |
 blk_read_time       | double precision |           |          |
 blk_write_time      | double precision |           |          |

Пример:

select query,calls,total_time,min_time,max_time,mean_time,stddev_time,rows from pg_stat_statements order by mean_time desc;

который дает (строка запроса сокращена):

Вся эта информация должна помочь вам ограничить поиск самыми трудоемкими запросами, а затем определить причину.

Вы также можете использовать это, чтобы получить представление о конкретной рабочей нагрузке. Например, если вы знаете, что проблема появляется вскоре после определенного события или времени, вы можете сбросить статистику непосредственно перед этим и запросить представление, когда возникли проблемные детали. Для этого просто вызовите pg_stat_statements_reset () до желаемого времени или события:

SELECT pg_stat_statements_reset();

Хотите оптимизировать базу данных на максимум — наши специалисты рады помочь!