Ищем самые дорогие query в PostgreSQL

PostgreSQL стал тормозить? Давай разбираться!

Установка экстеншенов

Для получения необходимой инфы, нужно поставить 2 extension:

• pg_stat_statements
• pg_stat_kcache

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_stat_kcache;

Есть 2 ментальных ловушки

Не хочу еще больше грузить бедную БД

Оба этих расширения создают несколько системных табличек и вьюшек, в которые скидывается инфа о выполнении запросов.

Звучит страшно?

На практике при нагрузке 5000 запросов/сек включение этих экстеншенов никак не повлияло ни на CPU, ни на latency.

Не хочу что-то качать и хачить БД

Оба расширения стандартные и ставятся из коробки с PostgreSQL. Не стоит бояться слова CREATE. Реально, они просто включаются.

Анализируем

Короткий способ

select
substr(query, 0, 250),
calls,
to_char(total_time/(60*60), '999,999,9999,999') as "Cumulative Time (hrs)", rows,
to_char(total_time/calls, '999.999') as per_call_ms
from pg_stat_statements
order by total_time desc
limit 10;

Результат выполнения короткого SQL запроса

Запрос взят отсюда

http://hjhart.blog/2016/09/06/reset-postgresql-statistics.html

Длинный способ

SELECT rolname, queryid, round(total_time::numeric, 2) AS total_time, calls,
pg_size_pretty((shared_blks_hit+shared_blks_read)*8192 - reads) AS memory_hit,
pg_size_pretty(reads) AS disk_read,
pg_size_pretty(writes) AS disk_write,
round(blk_read_time::numeric, 2) AS blk_read_time,
round(blk_write_time::numeric, 2) AS blk_write_time,
round(user_time::numeric, 2) AS user_time,
round(system_time::numeric, 2) AS system_time
FROM pg_stat_statements s
JOIN pg_stat_kcache() k USING (userid, dbid, queryid)
JOIN pg_database d ON s.dbid = d.oid
JOIN pg_roles r ON r.oid = userid
WHERE datname != 'postgres' AND datname NOT LIKE 'template%'
ORDER BY total_time DESC LIMIT 10;

Результат выполнения длинного SQL запроса

Потом достаем самые проблемные запросы по queryid

SELECT * FROM pg_stat_statements WHERE queryid = 404091552;

Результат выбора самого долгого query по длинному SQL запросу

Запрос взят отсюда

https://github.yandex-team.ru/mail/salt/blob/9d4334e6cc6795472f61bfacbe0ecf40202722cd/salt/components/postgres/conf/sql/queries_total.sql

Там, кстати, в папочке есть еще несколько крутых запросов.

А можно сначала?

Да, можно сбросить статистику. Это происходит моментально и не влияет на производительность БД.

select pg_stat_statements_reset();

Вывод

Хватит уже бояться анализировать производительность запросов. Попробуй сейчас! Это просто и бесплатно.

Ну и пиши вопросы, интересные истории в комменты. Например, такие: «Ого оказывается мой запрос выполнялся 45 секунд, потому что я забыл создать индекс 😱»