Данный инструмент написан из спортивного интереса, когда мною было обнаружено, что вьюха 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, datid, datname, pid, usesysid,
coalesce(usename, backend_type, 'unknown') as usename,
coalesce(client_hostname, client_addr::text, 'localhost') as client_hostname,
application_name, wait_event_type, wait_event, state, backend_type, query
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("\\(", " ( ");
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("\\n", " ");
sql = sql.replaceAll(";", "");
sql = sql.replaceAll("[ ]+", " ");
sql = sql.toLowerCase().trim();
String[] array = sql.split(" ", -1);
int bvn = 0;
String nsql = "";
for (int i = 0; i < array.length; i++) {
if (array[i].matches("-?\\d+(\\.\\d+)?")) {
bvn++;
array[i] = "$" + bvn;
} else if ((array[i].charAt(0) == '\'') && (array[i].charAt(array[i].length() - 1) == '\'')) {
bvn++;
array[i] = "$" + bvn;
}
nsql += array[i] + " ";
}
return nsql;
}
С планом выполнения запроса было сложно. Это к Oracle ты приходишь и говоришь «Дай мне план для sqlid=...», и он тебе отвечает — «Тебе самый последний, или за вчера, или показать все за последний месяц со статистикой выполнения по каждому?». А PostgreSQL тебе отвечает — «А что такое sqlid?».
Поэтому для запросов вида SELEСT/UPDATE/INSERT/DELETE посылаем в БД команду EXPLAIN и сохраняем результат локально. Делаем это не чаще 1 раза в час.
И работает это только в том случае, если запрос выполнялся в той же БД, к который вы подключились (указывается при настройке соединения). И только если вы подключились к БД под суперюзером (postgres), чего некоторые, возможно, побоятся. Поэтому можно создать специального пользователя для мониторинга. Будет работать всё, кроме отображения планов.
CREATE USER pgmonuser WITH password 'pgmonuser';
GRANT pg_monitor TO pgmonuser;
Скачать тут: https://sourceforge.net/projects/pash-viewer/files/PASH-Viewer-0.3.zip/download
GitHub: https://github.com/dbacvetkov/PASH-Viewer
UPD: в версии 0.3 добавил поддержку PostgreSQL 9.6 — там просто мало ожиданий, всего в двух классах — Lock и LWLock. Отсутствует класс IO.
Спасибы и приветы:
Александру Кардаполову за ASH-Viewer.
Антону Глушакову за консультацию и тестирование.
Дмитрию Рудопысову за то, что объяснил, как компилировать и запускать скачанный с github проект.
Ещё слайды:
Комментарии (13)
robert_ayrapetyan
06.06.2018 17:31Ой, sourceforge… Действительно, что-ли, «github — все»?
dcvetkov Автор
06.06.2018 19:23Выложил на github — github.com/dbacvetkov/PASH-Viewer.
Просто разобрался не сразу. Использую первый раз, поэтому не факт, что всё сделал правильно.
Kiread
08.06.2018 11:40Не запускается :(
SQL Exception occured: ERROR: column «backend_type» does not exist
Позиция: 76
java.lang.NullPointerExceptiondcvetkov Автор
08.06.2018 11:42Полагаю, что у вас не PostgreSQL 10.
В 9-ой версии такого поля действительно нет. Я мог бы без него и обойтись в запросе, но там и ожиданий очень мало — вряд ли будет интересно наблюдать. Если я для вас скомпилирую версию под 9-ку — выложите мне в ответ её скриншоты под нагрузкой?
Ruslan_Y
Спасибо за утилиту, попробую! По плану запроса, в Oracle план реально выполняемого запроса и план через explain могли сильно разнится и вводить в заблуждение, тут и спасал sqlid с актуальным планом выполняемого запроса. В PG такие проблемы актуальны или он все же стабильней, плану через explain можно верить?
dcvetkov Автор
Тут надо определиться, что мы считаем «разными» планами.
Если мы говорим про такие поля, как COST и ROWS, то они конечно же будут отличаться, т.к. при EXPLAIN они формируются на основе статистики и некой абстрактной стоимости операций чтения и т.п.
А если мы говорим про план, как про непосредственно метод доступа к данным, то тут надо понимать, что если между моментом фактического выполнения запроса и моментом выполнения EXPLAIN случилось нечто, что влияет на план (например, удаление индекса), то результаты тоже могут не совпасть.
Однако, учитывая, что мы посылаем команду EXPLAIN в ту же секунду, что и сам запрос, то вероятность описанного выше события крайне низка.