1. Большая тестовая БД ( 10GB )/Слабая СУБД (CPU = 2 RAM=2TB)
pgbench_accounts = 68 499 073 строк
HOT UPDATE RATIO
pgbench_accounts = 87.87% ( 8 305 904 к 9 452 290 )
2. Огромная тестовая БД ( 10TB )/Мощная СУБД (CPU = 200 RAM=1TB)
pgbench_accounts = 70 150 127 616 строк
HOT UPDATE RATIO
pgbench_accounts = 1.23% (1 804 973 к 46 518 021 )
Тестовый сценарий (SELECT + UPDATE )
current_delta = (ROUND( random ())::bigint)*10 + 1 ;
SELECT MIN(aid) INTO min_i FROM pgbench_accounts ;
SELECT MAX(aid) INTO max_i FROM pgbench_accounts ;
current_aid = floor(random() * (max_i - min_i + 1)) + min_i ;
UPDATE pgbench_accounts SET abalance = abalance + current_delta WHERE aid = current_aid ;
SELECT abalance INTO test_rec FROM pgbench_accounts WHERE aid = current_aid ;
SELECT MIN(tid) INTO min_i FROM pgbench_tellers ;
SELECT MAX(tid) INTO max_i FROM pgbench_tellers ;
current_tid = floor(random() * (max_i - min_i + 1)) + min_i ;
UPDATE pgbench_tellers SET tbalance = tbalance + current_delta WHERE tid = current_tid ;
SELECT MIN(bid) INTO min_i FROM pgbench_branches ;
SELECT MAX(bid) INTO max_i FROM pgbench_branches ;
current_bid = floor(random() * (max_i - min_i + 1)) + min_i ;
UPDATE pgbench_branches SET bbalance = bbalance + current_delta WHERE bid = current_bid ;
Гипотеза по объяснению причины
Парадокс объясняется фундаментальным различием в поведении буферного кеша (shared buffers) и механизма HOT (Heap-Only Tuples) при разном соотношении размера рабочего набора данных (working set) и доступной оперативной памяти (RAM). Вот ключевые причины:
❓1. Размер рабочего набора данных vs. Размер RAM:
* Таблица 60 млн строк (несколько ГБ): Весь рабочий набор данных (активно обновляемые страницы) с высокой вероятностью помещается в `shared_buffers` ❓(размер которых обычно составляет ~75% от 2 ГБ RAM = ~1.5 ГБ)❓. Страницы, в которых происходят обновления, постоянно находятся в памяти.🤔
* Таблица 70 млрд строк (ТБ+): Рабочий набор данных (даже активно обновляемые страницы) ⚠️колоссально⚠️ превышает доступный `shared_buffers` (размер которых будет ~75% от 1 ТБ = ~750 ГБ). Подавляющее большинство страниц, к которым происходит обращение (включая обновления), не находятся в памяти и должны быть загружены с диска по требованию.
2. Механизм HOT и его зависимость от нахождения страницы в памяти:
* ⚠️Ключевое условие для HOT:⚠️ Чтобы обновление стало `HOT`, ⚠️страница, содержащая обновляемую строку (старую версию), ДОЛЖНА НАХОДИТЬСЯ В `shared_buffers`⚠️. Только тогда PostgreSQL может эффективно проверить, есть ли в этой же самой странице достаточно свободного места (`fillfactor`) для размещения новой версии строки, и выполнить обновление "на месте", не трогая индексы.
* Для маленькой таблицы (в памяти): Так как страницы почти всегда в буфере, PostgreSQL может проверить наличие свободного места и выполнить `HOT update` (если место есть). При 80% `HOT` свободное место в страницах присутствует (возможно, `fillfactor` настроен оптимально или autovacuum успевает его освобождать).
* Для огромной таблицы (не в памяти): Когда приходит запрос на обновление строки:
1. PostgreSQL понимает, что нужная страница не в `shared_buffers`.
❗2. Он должен загрузить эту страницу с диска в буферный кеш. ⚠️Это действие само по себе делает обновление "не-HOT"⚠️, даже если в странице после загрузки окажется достаточно свободного места! Механизм HOT не применяется к страницам, загружаемым "по требованию" для конкретного обновления. Основная цель HOT — оптимизировать обновления уже горячих (часто используемых и находящихся в памяти) страниц.
3. Роль Autovacuum:
* Для маленькой таблицы: Autovacuum (даже на 2 CPU) может успевать обрабатывать эту таблицу, вовремя замораживая старые версии строк (`FREEZE`) и освобождая место (`VACUUM`) внутри страниц для последующих `HOT`-обновлений, поддерживая высокий процент HOT.
* Для огромной таблицы: Хотя autovacuum на 200 CPU гораздо мощнее, он физически не может обработать всю таблицу 70 млрд строк мгновенно. Он фокусируется на самых "грязных" участках. Даже если он освобождает много места на страницах (что объясняет возможность HOT в принципе), ⚠️основным ограничителем становится пункт 2: страница просто не была в памяти в момент обновления.⚠️
4. Производительность ввода-вывода (I/O Bottleneck):
* Для огромной таблицы: Основным узким местом становится дисковый I/O. Необходимость загружать страницы с диска для каждого обновления (или большого их процента) доминирует над временем выполнения. Даже если бы HOT был применим чаще, выигрыш был бы мал по сравнению с затратами на чтение страницы с диска. Сами обновления и фиксация транзакций становятся основными операциями после чтения.
ℹ️Итоговая причина низкого % HOT на большой таблице:
Подавляющее большинство обновлений (99%) работают со страницами, которых не было в `shared_buffers` в момент начала выполнения запроса `UPDATE`. Необходимость загружать эти страницы с диска автоматически делает обновление "не-HOT" (non-HOT), независимо от наличия свободного места на странице. Механизм HOT эффективен только для горячих данных, постоянно находящихся в оперативной памяти. При рабочем наборе данных, многократно превышающем RAM, страницы постоянно вытесняются из кеша, и HOT почти не применяется.
Дополнительные факторы для большой таблицы:
* Конкуренция за кеш: Даже страницы, в которые только что было сделано обновление, могут быть очень быстро вытеснены из `shared_buffers` другими данными из-за огромного общего объема.
* Fillfactor: Увеличение `fillfactor` (меньше свободного места на странице) может немного снижать потенциальный процент HOT, но не является основной причиной 1% в данном случае (основная причина — отсутствие страниц в кеше).
Рекомендации для улучшения % HOT на больших таблицах (сложно, но возможно частично):
1. Оптимизация рабочего набора: Выявление и выделение в отдельные таблицы/партиции самых горячих данных, чтобы их рабочий набор помещался в RAM.
2. Партиционирование: Разбиение таблицы на множество мелких партиций по ключу обновления (например, по дате). Это позволяет autovacuum эффективнее работать с отдельными партициями, а также повышает шанс, что активная ("горячая") партиция будет находиться в памяти.
3. Увеличение RAM / Оптимизация shared_buffers: Хотя 1 ТБ RAM много, для 70 млрд строк это может быть мало. Мониторинг hit ratio кеша (`pg_stat_bgwriter`, `pg_statio_user_tables`) покажет, насколько эффективно используется память. Возможно, потребуется еще больше RAM или оптимизация запросов/индексов для уменьшения рабочего набора.
4. Агрессивный Autovacuum для горячих партиций: Установка более агрессивных параметров autovacuum (`autovacuum_vacuum_cost_limit`, `autovacuum_vacuum_scale_factor`) для критически важных партиций, чтобы быстрее освобождать место.
5.Fillfactor: Проверить и, возможно, *уменьшить* `fillfactor` для горячих таблиц/партиций, чтобы оставлять больше места для новых версий строк в странице (но это увеличит общий размер таблицы на диске).
——————————
Hot update — это обновление строки, при котором не требуется перезаписывать всю страницу, а просто обновляется указанный столбец. Для выполнения hot update необходимо, чтобы страница, содержащая обновляемую строку, находилась в shared_buffers.
⚠️Если страница, содержащая обновляемую строку, не находится в shared_buffers, то hot update не будет выполнен.⚠️
В этом случае система будет вынуждена выполнить heap-only tuple (HOT) обновление, но только в том случае, если строка уже находится в памяти (в shared_buffers). ⚠️Если же строки нет в памяти, то будет выполнено обычное обновление, при котором вся страница будет перезаписана⚠️, что приведет к увеличению объема данных и, возможно, к необходимости выполнения VACUUM.
⚠️Таким образом, hot update возможен только если страница с обновляемой строкой находится в shared_buffers. ⚠️
Дополнительная информация: [PostgreSQL: Hot Standby and Hot Updates](https://www.postgresql.org/docs/current/hot-updates.html)