Мониторим активные сессии PostgreSQL 10, как в Oracle

    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, 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

    image

    image
    Поделиться публикацией

    Комментарии 16

      0
      Спасибо за утилиту, попробую! По плану запроса, в Oracle план реально выполняемого запроса и план через explain могли сильно разнится и вводить в заблуждение, тут и спасал sqlid с актуальным планом выполняемого запроса. В PG такие проблемы актуальны или он все же стабильней, плану через explain можно верить?
        0
        Тут надо определиться, что мы считаем «разными» планами.
        Если мы говорим про такие поля, как COST и ROWS, то они конечно же будут отличаться, т.к. при EXPLAIN они формируются на основе статистики и некой абстрактной стоимости операций чтения и т.п.
        А если мы говорим про план, как про непосредственно метод доступа к данным, то тут надо понимать, что если между моментом фактического выполнения запроса и моментом выполнения EXPLAIN случилось нечто, что влияет на план (например, удаление индекса), то результаты тоже могут не совпасть.
        Однако, учитывая, что мы посылаем команду EXPLAIN в ту же секунду, что и сам запрос, то вероятность описанного выше события крайне низка.
          0
          Плану через explain можно верить?
          Ответ: и да, и нет

          Да — потому, что это всё же план, но действительный только на момент запроса самого плана

          Нет — т.к. зависит от весьма конкретных вещей, к примеру от параметров запроса: у компании А 10 платежей, а у Б — 500000, и какой нить индексскан спокойно превращается в битмапу с речеками и дополнительной сортировкой чтобы оффсетом (омг) отсечь ещё сотню другую записей. + explain отличается от explain (analyze, buffers)

          И получается, что план раз в час, как предложено выше, в принципе так себе идея

          Если нужен актуальный план реально ступившего запроса — посмотрите тут
          В целом автор форка мог бы доработать приложение, дав пользователям возможность указывать файл с логами, для поиска актуального плана выполнения от автоэксплейна (по времени, иду транзакции, да как угодно)
            0
            Дописал приложение. Теперь можно поймать разные планы для запроса. Пример:image
            Что для этого нужно, написано здесь: How to catch execution plans for queries.
            Вашу статью про auto_explain прочитал — инструмент хороший, но я не вижу приличного способа как-то парсить в десктопном приложении текстовый лог, расположенный на сервере БД.
          0
          Ой, sourceforge… Действительно, что-ли, «github — все»?
            0
            Выложил на github — github.com/dbacvetkov/PASH-Viewer.
            Просто разобрался не сразу. Использую первый раз, поэтому не факт, что всё сделал правильно.
            0
            — Удалено…
              0
              Не запускается :(
              SQL Exception occured: ERROR: column «backend_type» does not exist
              Позиция: 76
              java.lang.NullPointerException
                0
                Полагаю, что у вас не PostgreSQL 10.
                В 9-ой версии такого поля действительно нет. Я мог бы без него и обойтись в запросе, но там и ожиданий очень мало — вряд ли будет интересно наблюдать. Если я для вас скомпилирую версию под 9-ку — выложите мне в ответ её скриншоты под нагрузкой?
              0
              del

              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

              Самое читаемое