[Высокая производительность, PostgreSQL, SQL, Администрирование баз данных] Агрегаты в БД — прокси-таблицы
Автор
Сообщение
news_bot ®
Стаж: 6 лет 9 месяцев
Сообщений: 27286
Мы заканчиваем мини-серию статей о работе с агрегатами в 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% по дисковой нагрузке, ускорили вставку в таблицу агрегатов и избавились от ожидания блокировок на них.
===========
Источник:
habr.com
===========
Похожие новости:
- [SQLite, Xcode, Swift] Видеомонтаж, машинное обучение и взломанный xml — все в одной программе
- [PostgreSQL, SQL] PostgreSQL 14: Часть 4 или «январское наступление» (Коммитфест 2021-01)
- [Python, SQL] Создаем схему базы данных на SQLAlchemy
- [Oracle, SQL] Oracle: разница между deterministic и result_cache (перевод)
- [MySQL, Администрирование баз данных] Советы по хранению Percona Backup в облаке (перевод)
- [Высокая производительность, Системное программирование, Хранение данных] Бесплатное решение для создания программного гибрида SSD и HDD на домашнем ПК Windows Tiered Storage Spaces
- [Высокая производительность, C++, Компиляторы, Компьютерное железо] Threadripper 3990X: компилируем 1 миллиард строк C++ на 64 ядрах (перевод)
- [PHP, Анализ и проектирование систем, NoSQL] Паспортный контроль, или Как сжать полтора гигабайта до 42 мегабайт
- [SQL, Big Data, Google Cloud Platform] Работа с Google BigQuery. Считаем деньги
- [Высокая производительность, Разработка под iOS, Разработка мобильных приложений, Разработка под Android, Микросервисы] Envoy как универсальный сетевой примитив
Теги для поиска: #_vysokaja_proizvoditelnost (Высокая производительность), #_postgresql, #_sql, #_administrirovanie_baz_dannyh (Администрирование баз данных), #_postgresql, #_sql, #_explain.tensor.ru, #_agregatsija (агрегация), #_blog_kompanii_tenzor (
Блог компании Тензор
), #_vysokaja_proizvoditelnost (
Высокая производительность
), #_postgresql, #_sql, #_administrirovanie_baz_dannyh (
Администрирование баз данных
)
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 22-Ноя 13:22
Часовой пояс: UTC + 5
| Автор | Сообщение |
|---|---|
|
news_bot ®
Стаж: 6 лет 9 месяцев |
|
|
Мы заканчиваем мини-серию статей о работе с агрегатами в PostgreSQL: И сегодня поговорим о том, как можно снизить суммарные задержки на вставку множества изменений в таблицы агрегатов за счет использования промежуточных таблиц и внешней обработки.  Поговорим об этом на примере коллектора нашего сервиса анализа PostgreSQL-логов, о котором я уже рассказывал в предыдущих статьях: C точки зрения дальнейшей работы с таблицей агрегатов, всегда выгодно все-таки иметь единственный экземпляр записи в ней, а не пытаться перезаписывать его многократно, поэтому в нашем распоряжении оказываются варианты промежуточной агрегации в памяти процесса и отдельного "накатывания" изменений.Если первая оптимизация достаточно очевидна (посылать в базу не "10 раз +1", а "1 раз +10"), то о второй стоит рассказать подробнее."Все, что нажито непосильным трудом!.."Итак, мы хотим достаточно оперативно видеть статистику из какой таблицы/индекса насколько активно (и бессмысленно) читают. Примерно вот такая картинка должна получиться:  Давайте посмотрим, из чего конкретно состоит время вставки нового экземпляра записи агрегата.Я уже рассказывал, что для вставки в агрегаты мы используем тот же самый COPY, что и в таблицы "фактов" + триггер, который преобразует вставку в INSERT ON CONFLICT ... DO UPDATE. Агрегатов у нас существенно меньше, чем летящих в них фактов - то есть, практически каждая вставка приводит к UPDATE! А что это у нас "технически"?..
CREATE UNLOGGED TABLE px$agg(
LIKE agg ); В нашей архитектуре для этого как раз удачно оказался диспетчер-балансировщик, описанный в статье "Динамическая балансировка нагрузки в pull-схеме".
Если не знаете, как совместить вычисление разных агрегатов в одном запросе, стоит прочитать "SQL HowTo: 1000 и один способ агрегации".
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; 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;
 Мы получили снижение примерно на 10% по дисковой нагрузке, ускорили вставку в таблицу агрегатов и избавились от ожидания блокировок на них. =========== Источник: habr.com =========== Похожие новости:
Блог компании Тензор ), #_vysokaja_proizvoditelnost ( Высокая производительность ), #_postgresql, #_sql, #_administrirovanie_baz_dannyh ( Администрирование баз данных ) |
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 22-Ноя 13:22
Часовой пояс: UTC + 5