12.12.2018 13:59
nitro2005 20 3500 Источник
Что делать, когда имеется приложение с закрытым исходным кодом, которое обращается к БД не самым оптимальным образом? Как потюнить запросы, не меняя приложение, а возможно и саму БД?

Если вы не задавались такими вопросами — вы очень успешный и строгий DBA.

Ну а если задавались — позвольте поделиться страданиями и опытом.

Нужно хранить больше данных, или постановка задачи


Этот раздел можно смело пролистать, если не интересна история вопроса.

Изначально у нас была проприетарная система, которая парсила свои данные из закрытого формата в базу PostgreSQL, откуда мы эти данные читали, анализировали и обрабатывали.

Кроме того, инструментарий этой системы также использовал эту базу для неких операций, так что отказаться от нее и создать копию со своей структурой казалось бесперспективной идеей.

По умолчанию система автоматически удаляла записи старше недели, так что проблем с производительностью на стенде не наблюдалось.

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

Поэтому очевидным решением было сделать партиционирование и триггеры на операции INSERT. Фокус достаточно простой и эффективный. Данные вставляются в нужные партиции, удаление старых записей отключили, вроде бы все хорошо.

Пока не прошло пару лет и данных хорошенько не поднакопилось.

Тут «внезапно» оказалось, что запросы, которые делает инструментарий используемой системы, не ограничивает выборку по дате (а точнее ограничивает ее не по тому полю, по которому идет разбиение на партиции). Т.е. если мы что-то ищем — поиск идет по всем партициям. Также начали тормозить и операции UPDATE — в условиях там использовался только ID-шник.

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

Конечно, первое что приходит в голову — обратиться к разработчику.

Однако, в большинстве случаев его либо уже нет в зоне доступа, либо он запросит стоимость еще одной такой системы за доработку в несколько строк.

Поэтому возникла идея, что наверное уже есть какой-то прокси, который может нам помочь.

Нам нужен прокси


Быстрое гугление не нашло четкого ответа на вопрос, как можно переписать поступивший запрос на стороне PostgreSQL или какого-то стороннего софта.

Поэтому (ну и just for fun тоже, конечно) была написана довольно простая софтина, которая принимает подключения от клиентов и проксирует их в PostgreSQL. При этом поступающие SQL-запросы читаются, и, если нужно, заменяются.

Делюсь ссылкой на github

Пока никаких бинарных пакетов не делал, руки не дошли. Но сборка довольно простая. Написано все на C++/Qt, т.к. уже давно на этом пишу…

Конфиг довольно простой:

Указываем какой интерфейс и порт слушать:

listen_address=0.0.0.0
listen_port=5433

Заставляем нерадивый софт подключаться по указанному адресу вместо прямого подключения к PostgreSQL-серверу.

Прописываем куда пробрасывать соединения (в данном примере прокси размещен на той же машине что и PostgreSQL-сервер):

dst_address=127.0.0.1
dst_port=5432

Задаем регулярное выражение для отлова нужного запроса:

query = SELECT \* FROM tablename WHERE (.+)

Говорим что нужно его переписать:

action = rewrite

Говорим как переписать:

rewrite = SELECT * FROM tablename WHERE (col3 >= '$(now-1M)') AND $(1)

В данном примере добавляем в условия запроса фильтр по колонке с датой, указывая что нас интересуют только записи за последний месяц.

Можно было бы написать и так:

rewrite = SELECT * FROM tablename WHERE (col3 >= now() - interval '1 month') AND $(1)

Но тогда запрос не будет оптимальным из-за наличия функции now() — поиск все равно будет осуществляться по всем партициям. Для того чтобы искать только в нужных, необходимо указать значение-константу. Поэтому наш прокси подставляет вместо конструкции $(now-1M) таймстемп уже со сдвигом на один месяц.

Результат (из лога):

ORIGINAL query: SELECT * FROM tablename WHERE id=1;
MODIFIED query (rule 1): SELECT * FROM tablename WHERE (col3 >= '2018-11-12 11:25:23.0+00') AND id=1;

Таким образом можно подменить, в принципе, любой запрос. Ответы от сервера не изменяются и передаются клиенту как есть. Таким образом минимизируется задержка передачи. Кроме того, приложение обычно ждет ответа определенного формата, поэтому набор колонок в запросе и ответе менять нежелательно.

Также можно легко вывести все интересующие запросы в лог:

query = .+
action = log

В репозитории есть конфиг с примерами и более детальным описанием.

Кстати, легко определить насколько разработчик грамотно пишет работу с БД. Например, если увидите такой часто выполняющийся запрос, значит кому-то пора покурить мануалы.

INSERT INTO tablename (col1, col2, col3) VALUES('value1', 1, '2018-12-31')

Должно быть вот так:

INSERT INTO tablename (col1, col2, col3) VALUES($1::varchar, $2::integer, $3::date)

К сожалению, пока что наш прокси так писать не умеет :/ но сделать это не сложно. Возможно, в будущем можно будет с его помощью переписывать первый запрос на второй.

Да, важный момент — пока что SSL не поддерживается, так что все соединения от клиентов к прокси будут без шифрования.

Буду рад комментариям и замечаниям.

Если будет активный интерес пользователей, возможно буду дальше развивать проект.

Можно добавить работу и с другими базами.

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


  1. Sioln
    12.12.2018 17:21
    #19490038

    Прикольно, что .NET Entity Framework такое умеет из коробки.
    www.entityframeworktutorial.net/EntityFramework6/database-command-interception.aspx

    Т.е. можно положить рядом с приложением свою сборку, модифицировать app.config приложения (но не трогать строки подключения, меняется другая часть) и ваша сборка будет перехватывать запросы не-важно-к-какой СУБД.


    1. nitro2005 Автор
      12.12.2018 17:27
      #19490080

      Оно будет перехватывать запросы от приложения, написанного не на .NET и соединяющегося с PostgreSQL по TCP?


      1. Sioln
        12.12.2018 20:58
        #19490900

        Нет. Это для расширения кругозора. Что в каких-то платформах такие вещи предусмотрены by design.


        1. nitro2005 Автор
          12.12.2018 21:03
          #19490908

          Понял. С .NET никогда не сталкивался, но спасибо:)


  1. sentyaev
    12.12.2018 17:22
    #19490042

    Конечно, первое что приходит в голову — обратиться к разработчику.
    Однако, в большинстве случаев его либо уже нет в зоне доступа, либо он запросит стоимость еще одной такой системы за доработку в несколько строк :)

    А могли бы вы поделится, что в вашем случае было? Почему было принято решение фиксить систему костылем?


    1. nitro2005 Автор
      12.12.2018 17:30
      #19490090

      Потому что костыль — мое второе имя версия используемого ПО уже не поддерживается разработчиком, даже за деньги. Да и не будет он ради нас воротить партиционирование, т.к. это у него не самый важный продукт. Но к его чести, советы как лучше сделать партиционирование он все-таки дает, правда за деньги :)


      1. sentyaev
        12.12.2018 17:52
        #19490178

        ПО уже не поддерживается разработчиком, даже за деньги

        Ясно-понятно, как решение текущей проблемы костыли, это норм, я лично ничего страшного не вижу, возникает другой вопрос.
        Учитывая то, что версия ПО не поддерживается, какая стратегия на будущее? Ведь это огромный риск для бизнеса, что в один прекрасный день все может встать колом.


        1. nitro2005 Автор
          12.12.2018 17:55
          #19490192

          Хехе, это уже не мой вопрос. Моим решением было бы очевидное — все переписать заново самостоятельно (по деньгам бы дешевле вышло чем поддержка+обновления до актуальных версий). Но бизнес к такому не готов :)


  1. gecube
    12.12.2018 17:31
    #19490100

    Краткий и дурацкий вопрос. Зачем это, если уже есть github.com/wgliang/pgproxy?

    You can do:

    * database read and write separation
    * database services disaster recovery
    * proxy database
    * rewrite sql statement


    1. nitro2005 Автор
      12.12.2018 17:35
      #19490114

      Покажете как переписать на нем запрос?


      1. Ilya_ilf
        12.12.2018 18:11
        #19490286

        Только посредством оборачивания запроса в функцию. А внутри уже воротите, что хотите. Но, как я понял, в вашем случае это не спасёт. Слишком уж крива эта ваша «Закрытая система»


  1. ultrinfaern
    12.12.2018 17:42
    #19490138

    А скажите, пожалуйста, так как вы уже писали триггеры на таблицу, почему не написали триггер на DELETE, в котором можно было удаляемые данные отсаживать в другую таблицу. Плюс в том, что программа остаётся со своей структурой, а вы получаете полную свою таблицу.


    1. nitro2005 Автор
      12.12.2018 17:51
      #19490176

      Вариантов решения всегда много… Наверное, тогда просто опыта не хватило это сделать, хотя сейчас действительно это кажется верным вариантом. Но сейчас уже не хочется этим заниматься, т.к. все это в куче мест уже в эксплуатации. Поэтому и ищутся быстрые костыли :)


  1. Joka
    12.12.2018 21:05
    #19490916

    habr.com/company/oleg-bunin/blog/309330 — попробуйте так еще. вы можете все старые данные вынести на отдельный сервер просто.


  1. Naves
    12.12.2018 22:59
    #19491334

    Должно быть вот так
    VALUES($1::varchar, $2::integer, $3::date)

    Насколько вырастает производительность такого кода? Ведь тогда пропадает возможность относительно простого просмотра лога: «какие же там такие кривые значения вставляются, что потом все виснет»
    В моем случае лет 5 назад была база firebird. Запросы смотрел через wireshark, а исправлять пришлось ковыряя исполняемый файл, где-то правкой строк и вставкой first 100 или where 1=2/*date=%s*/ или вообще ассемблерным хулиганством и вставкой MOV EAX,1; NOP вместо CALL очень важной функции, которая в десятый раз подряд узнает сколько же всего записей в бд
    И это не считая создания индексов, про которые забыл разработчик.


    1. nitro2005 Автор
      12.12.2018 23:29
      #19491432

      Насчет производительности не скажу, теоретически парсеру меньше работы + появляется возможность кешировать результаты парсинга.
      В документации указано, что главный плюс — это безопасность:

      The primary advantage of PQexecParams over PQexec is that parameter values can be separated from the command string, thus avoiding the need for tedious and error-prone quoting and escaping.… has some usefulness as an extra defense against SQL-injection attacks.


      А чтобы все висло из-за значений… ну не знаю. Это постараться надо :)
      На досуге надо глянуть, интересно как отобразится такой запрос в pg_stat_activity — с уже конкретными значениями или нет.


  1. kenjichka
    13.12.2018 09:47
    #19492552

    Почему не использовалось представление(view), внутри которого было бы нужное условие по дате(партиции)?


    1. nitro2005 Автор
      13.12.2018 09:48
      #19492556

      Не очень представляю как это поможет… Может я мысль не понял — уточните?


      1. kenjichka
        13.12.2018 12:26
        #19493484

        Я уже начал было писать, что имел ввиду, пока писал понял где ошибаюсь.

        Вкратце: переименовать таблицу в prefix_tablename.
        Затем создать представление с именем tablename, которое выбирает данные из prefix_tablename с условием по дате, а приложение выбирало бы данные из представления с условием по ID(или любому другому).
        Не учел, что новые записи в таблицу заносит приложение.


  1. tgregory
    13.12.2018 14:27
    #19494212

    В постгресе вьюхи создаются посредством правил, т.е. создаётся таблица на которую вешается SELECT правило. Можно создать также INSERT, UPDATE, DELETE правила. Там есть ряд кривостей и ограничений, но в зависимости от того, как вам надо было переписывать запросы, можно было бы ограничиться переименованием таблиц и созданием таких вьюх. Обращаю внимание, что правила это не триггеры, а именно синтаксическое переписывание запроса.