Хорошо, допустим, Вы сделали свою работу действительно правильно:
- Нормализация / денормализация создала идеально сбалансированную схему
- Ограничения применяются тщательно
- Вы подумали об индексах, выбрали правильные столбцы на основе вашего фильтра и критериев заказа
- Ваши 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();
Хотите оптимизировать базу данных на максимум — наши специалисты рады помочь!