image

Данный инструмент написан из спортивного интереса, когда мною было обнаружено, что вьюха 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 проект.

Ещё слайды:


image

image

image

Комментарии (13)


  1. Ruslan_Y
    06.06.2018 17:17

    Спасибо за утилиту, попробую! По плану запроса, в Oracle план реально выполняемого запроса и план через explain могли сильно разнится и вводить в заблуждение, тут и спасал sqlid с актуальным планом выполняемого запроса. В PG такие проблемы актуальны или он все же стабильней, плану через explain можно верить?


    1. dcvetkov Автор
      08.06.2018 11:50

      Тут надо определиться, что мы считаем «разными» планами.
      Если мы говорим про такие поля, как COST и ROWS, то они конечно же будут отличаться, т.к. при EXPLAIN они формируются на основе статистики и некой абстрактной стоимости операций чтения и т.п.
      А если мы говорим про план, как про непосредственно метод доступа к данным, то тут надо понимать, что если между моментом фактического выполнения запроса и моментом выполнения EXPLAIN случилось нечто, что влияет на план (например, удаление индекса), то результаты тоже могут не совпасть.
      Однако, учитывая, что мы посылаем команду EXPLAIN в ту же секунду, что и сам запрос, то вероятность описанного выше события крайне низка.


  1. robert_ayrapetyan
    06.06.2018 17:31

    Ой, sourceforge… Действительно, что-ли, «github — все»?


    1. dcvetkov Автор
      06.06.2018 19:23

      Выложил на github — github.com/dbacvetkov/PASH-Viewer.
      Просто разобрался не сразу. Использую первый раз, поэтому не факт, что всё сделал правильно.


  1. vazir
    06.06.2018 20:16

    — Удалено…


  1. Kiread
    08.06.2018 11:40

    Не запускается :(
    SQL Exception occured: ERROR: column «backend_type» does not exist
    Позиция: 76
    java.lang.NullPointerException


    1. dcvetkov Автор
      08.06.2018 11:42

      Полагаю, что у вас не PostgreSQL 10.
      В 9-ой версии такого поля действительно нет. Я мог бы без него и обойтись в запросе, но там и ожиданий очень мало — вряд ли будет интересно наблюдать. Если я для вас скомпилирую версию под 9-ку — выложите мне в ответ её скриншоты под нагрузкой?


      1. Kiread
        08.06.2018 11:44

        Прямо вот большую нагрузку не обещаю, но что будет — пришлю
        версия 9.6.2
        Было бы здорово общую версию иметь, где поля нет — просто ограниченный функицонал.



        1. dcvetkov Автор
          08.06.2018 15:36

          1. Kiread
            08.06.2018 16:13

            0.3 запустилась. Попробую снять скриншоты


          1. Kiread
            08.06.2018 17:58

            image
            Скриншот — планы не показывает для запросов, в остальном работает.
            В нашем проекте исторические планы пока не так важны, нас устраивает :)
            Еще какие-то нужны?


  1. dcvetkov Автор
    08.06.2018 11:42

    del