Мы заканчиваем мини-серию статей о работе с агрегатами в PostgreSQL:
И сегодня поговорим о том, как можно снизить суммарные задержки на вставку множества изменений в таблицы агрегатов за счет использования промежуточных таблиц и внешней обработки.
Поговорим об этом на примере коллектора нашего сервиса анализа PostgreSQL-логов, о котором я уже рассказывал в предыдущих статьях:
C точки зрения дальнейшей работы с таблицей агрегатов, всегда выгодно все-такииметь единственный экземпляр записи в ней, а не пытаться перезаписывать его многократно, поэтому в нашем распоряжении оказываются вариантыпромежуточной агрегации в памятипроцесса иотдельного "накатывания" изменений.
Если первая оптимизация достаточно очевидна (посылать в базу не "10 раз +1", а "1 раз +10"), то о второй стоит рассказать подробнее.
"Все, что нажито непосильным трудом!.."
Итак, мы хотим достаточно оперативно видеть статистику из какой таблицы/индекса насколько активно (и бессмысленно) читают. Примерно вот такая картинка должна получиться:
Давайте посмотрим, из чего конкретно состоит время вставки нового экземпляра записи агрегата.
Я уже рассказывал, что для вставки в агрегаты мы используем тот
же самыйCOPY
, что и в таблицы "фактов" + триггер,
который преобразует вставку вINSERT ON CONFLICT ... DO
UPDATE
. Агрегатов у нас существенно меньше, чем летящих в
них фактов - то есть, практически каждая вставка приводит
кUPDATE
! А что это у нас "технически"?..
-
накладываемRowExclusiveLock
-
находим по индексу и вычитываемтекущий образ записи
-
прописываем служебное поле xmaxв нем
-
вставляем новый образ записис измененными данными вWAL-файли heap таблицы
-
вписываем изменения вовсе относящиеся индексы- если повезло, будет HOT update с чуть меньшей нагрузкой
Как-то очень много всяких "читаем" и "пишем" получается. А в
активный агрегат через секунду прилетит следующий
UPDATE
, и следующий...
Создаем прокси-таблицу
А вот было бы хорошо, если бы можно было вставлять сразу в такую
табличку - чтобыни индексов, ни WAL-файлов, ни
UPDATE'ов - только "чистые"INSERT
... А ведь
так можно сделать!
Давайте породим промежуточную таблицу по формату целевой:
CREATE UNLOGGED TABLE px$agg( LIKE agg);
Зачем нужен UNLOGGED
и что он дает, можно подробно
прочитать в статье "DBA: грамотно организовываем синхронизации и
импорты".
Теперь все что мы хотим записать - будем писать прямо сюда без всяких триггеров и индексов.
Обрабатываем прокси-таблицу
Но в целевую-то таблицу данные у нас теперь никак не попадают - значит, надо кому-то это поручить. Причем, чтобы нам не пришлось решать никаких конфликтов параллельности, поручим этокому-то одному, вообще постороннемуотносительно вставки данных.
В нашей архитектуре для этого как раз удачно оказался диспетчер-балансировщик, описанный в статье "Динамическая балансировка нагрузки в pull-схеме".
Мы можемкаждые 10 секунд вычитывать все
содержимоев прокси-таблице (да, это будет Seq
Scan
, но это самый быстрый способ доступа ко "всем" данным
таблицы), динамически агрегировать в памяти процесса PostgreSQL без
передачи на клиента, исразу вставлять в целевую
таблицуза один запрос.
Если не знаете, как совместить вычисление разных агрегатов в одном запросе, стоит прочитать "SQL HowTo: 1000 и один способ агрегации".
После того, как мы все вычитали, сагрегировали и вставили -
просто и быстро зачистим прокси с помощьюTRUNCATE
:
BEGIN; INSERT INTO agg SELECT pk1 ... , pkN , <aggfunc>(val1) -- sum/min/max/... , <aggfunc>(val2) ... FROM px$agg -- тот самый Seq Scan GROUP BY -- агрегация в памяти в разрезе PK таблицы агрегатов = (pk1, ..., pkN) pk1 ... , pkN; TRUNCATE px$agg;COMMIT;
Замечу, что это надоделать в одной транзакции,
поскольку мало ли какие ошибки могут произойти при вставке,
аTRUNCATE
вне этой транзакции все зачистит, и данные -
потеряем.
Защита от параллельной вставки
Но вот беда, пока мы читали-агрегировали, в таблицу кто-то мог что-то еще записать. А мы этозачистим, не обработав.
Можно, конечно, повесить перед началомLOCK
на всю
таблицу, чтобы уж никто точно ничего не смог ничего с ней сделать,
но это не наш вариант - ведь ждать будет слишком много вставок.
Зато... мы можем практически мгновенноподменить ее на пустую такую же!
BEGIN; SET LOCAL lock_timeout = '100ms'; -- ждем блокировку не дольше 100мс LOCK TABLE px$agg IN ACCESS EXCLUSIVE MODE; -- собственно, блокируем от всех ALTER TABLE px$agg RENAME TO px$agg_swap; -- обменяли имена двух табличек ALTER TABLE px$agg_ RENAME TO px$agg; ALTER TABLE px$agg_swap RENAME TO px$agg_;COMMIT;
Как только мы дождемся блокировку, так сразу и переименование пройдет. Если не дождемся - то попробуем в следующий раз, не страшно.
В принципе, можно обойтись и двумя RENAME
, но
прокси-таблиц тогда должно быть больше:
-
px -> px0, px1 -> px
-
px -> px1, px0 -> px
Примерно так же живет внутренняя "механика" брокера очередей PgQ и базирующейся на ней репликации Londiste.
Пруфы
Теперь самое интересное - сколько это все дало:
Мы получили снижениепримерно на 10% по дисковой нагрузке, ускорили вставку в таблицу агрегатов и избавились от ожидания блокировок на них.