![image](https://habrastorage.org/webt/cf/oq/am/cfoqamnhzl-kk2e5sn4spofkjqg.png)
Данный инструмент написан из спортивного интереса, когда мною было обнаружено, что вьюха pg_stat_activity в PostgreSQL 10 имеет поля wait_event_type и wait_event, очень похожие по сути на оракловые wait_class и event из v$session.
Активно работая в данный момент с программой ASH-Viewer от akardapolov мне стало любопытно — насколько сложно переписать этот продукт под Postgres. Учитывая, что я не профессиональный разработчик, было не просто, но очень интересно. По ходу дела даже нашёл, как мне кажется, пару значительных багов, которые проявляются и в оригинальной программе для Oracle, по кр.мере для Standard Edition.
Принципы работы PASH-Viewer:
Не нужны никакие расширения. Берём данные исключительно из встроенной вьюхи pg_stat_activity.
Раз в секунду делается запрос активных сессий:
текст запроса к pg_stat_activity
SELECT current_timestamp, datname, pid, usesysid, usename, application_name,
backend_type, coalesce(client_hostname, client_addr::text, 'localhost') as client_hostname,
wait_event_type, wait_event, query, query_start,
1000 * EXTRACT(EPOCH FROM (clock_timestamp()-query_start)) as duration
from pg_stat_activity
where state='active'
and pid != pg_backend_pid();
Раз в 15 секунд данные за последние 15 снимков усредняются и выводятся на график.
SQL id, который нужен для группировки запросов в разделе Top SQL, я генерирую сам, он не имеет никакого отношения к queryid из pg_stat_statements. Я думал, как использовать queryid, но к сожалению не нашёл способа сопоставить запросы из этих двух представлений. Было бы здорово, если бы разработчики добавили поле queryid в pg_stat_activity.
SQL id = первые 13 символов от md5 (нормализованный текст запроса).
Нормализованный текст запроса — это запрос, в котором удалены символы новых строк и лишние пробелы, а литералы заменены на $1, $2 и т.д… Написать хорошую функцию нормализации запроса для меня было сложно. Я написал плохую. Текст привожу, но вы его пожалуйста не смотрите, а то мне стыдно. Лучше пришлите хорошую.
NormalizeSQL
public static String NormalizeSQL(String sql) {
sql = sql.replaceAll("\\n", " ");
sql = sql.replaceAll("\\(", " ( ");
sql = sql.replaceAll("\\)", " ) ");
sql = sql.replaceAll(",", " , ");
sql = sql.replaceAll("'", " ' ");
sql = sql.replaceAll("=", " = ");
sql = sql.replaceAll("<", " < ");
sql = sql.replaceAll(">", " > ");
sql = sql.replaceAll(";", "");
sql = sql.replaceAll("[ ]+", " ");
sql = sql.replaceAll("> =", ">=");
sql = sql.replaceAll("< =", "<=");
sql = sql.toLowerCase().trim();
String[] array = sql.split(" ", -1);
int var_number = 0;
String normalized_sql = "";
Boolean quote_flag = false;
for (int i = 0; i < array.length; i++) {
if (array[i].equals("'")) {
if (!quote_flag) {
quote_flag = true;
var_number++;
normalized_sql += "$" + var_number + " ";
} else {
quote_flag = false;
}
} else if (quote_flag) {
continue;
} else if (array[i].matches("-?\\d+(\\.\\d+)?")) {
var_number++;
normalized_sql += "$" + var_number + " ";
} else if (array[i].equals("order")) {
for (int j = i; j < array.length; j++) {
normalized_sql += array[j] + " ";
}
return normalized_sql.trim();
} else {
normalized_sql += array[i] + " ";
}
}
return normalized_sql.trim();
}
С планом выполнения запроса было сложно. Это к Oracle ты приходишь и говоришь «Дай мне план для sqlid=...», и он тебе отвечает — «Тебе самый последний, или за вчера, или показать все за последний месяц со статистикой выполнения по каждому?». А PostgreSQL тебе отвечает — «А что такое sqlid?».
Поэтому для запросов вида SELEСT/UPDATE/INSERT/DELETE посылаем в БД команду EXPLAIN и сохраняем результат локально. Делаем это не чаще 1 раза в час. В процессе отладки обнаружилось, что EXPLAIN висит на блокировке так же, как висел бы сам запрос, для которого мы хотим узнать план. Поэтому пришлось добавить setQueryTimeout(1).
И работает это только в том случае, если запрос выполнялся в той же БД, к который вы подключились (указывается при настройке соединения). И только если вы подключились к БД под суперюзером (postgres), чего некоторые, возможно, побоятся. Поэтому можно создать специального пользователя для мониторинга. Будет работать всё, кроме отображения планов.
CREATE USER pgmonuser WITH password 'pgmonuser';
GRANT pg_monitor TO pgmonuser;
Скачать с GitHub: https://github.com/dbacvetkov/PASH-Viewer/releases
UPD:
В версии 0.3 добавил поддержку PostgreSQL 9.6 (там всего два класса ожиданий — Lock и LWLock, всё остальное идёт как «CPU») и PostgreSQL 9.4 — 9.5 (там вообще либо CPU либо ожидание Lock).
В версии 0.3.1 добавил поле Backend Type в Top Sessions и избавился от белых полос на графике.
В версии 0.3.2 улучшил работу с планами, добавил некоторую статистику по запросам (AVG Duration, Calls Count) и возможность просматривать исторические данные:
How-to-create-pg_stat_activity-historical-table.
Спасибы и приветы:
Александру Кардаполову за ASH-Viewer.
Антону Глушакову за консультацию и тестирование.
Дмитрию Рудопысову за то, что объяснил, как компилировать и запускать скачанный с github проект.
Ещё слайды:
![image](https://habrastorage.org/webt/f2/4s/yt/f24sytk_inxgiav3wuajcjjifro.png)
![image](https://habrastorage.org/webt/1k/gg/d-/1kggd-5didlnncoeyuoezmuvg3i.png)
![image](https://habrastorage.org/webt/lw/yp/kg/lwypkg-wu4timhbp6ge7p3nf8ok.png)