Насильственная оптимизация запросов PostgreSQL

Что делать, когда имеется приложение с закрытым исходным кодом, которое обращается к БД не самым оптимальным образом? Как потюнить запросы, не меняя приложение, а возможно и саму БД?

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

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

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

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

Автор: nitro2005

Источник