Удалось выяснить, почему создание индексов на новой таблице без dead tuples все равно вызывает bloat?
Если кратко, то это эффект используемого процесса: переливка батчами+триггеры для синхронизации. B-Tree индекс у нас использовался для полей с монотонно возрастающими айдишниками. Триггеры вставляли в новую таблицу строки со «свежими» (большими) айдишниками, пока лились батчи со «старыми» (меньшими) айдишниками. Из-за этого вставки в B-Tree перестали быть монотонно возрастающими и это (из-за всяких внутренних оптимизаций) приводило к раздуванию практически всех страниц индекса.
Какие выводы сделали для того, чтобы поддерживать bloat на низком уровне
Перевод таблиц на партиции + архивация. Пустые партиции можно удалять и тем самым уменьшать реальный размер таблиц.
Рассматривали ли постановку VACUUM FULL на крон в моменты низкой активности
Для нас и наших объёмов данных это совсем не вариант, так как простой будет длительный и влияние на пользователей будет значительным даже в моменты низкой активности
Я ж написал: вы “изобрели” логическую репликацию. Логическая репликация, по крайней мере слоники (slony-1) и собственно логическая репликация, занимаются ровно тем, что вы описали: переливается дамп с таблиц (используется постгресовая COPY), после этого накатываются изменения.
Да, slony-1 и londiste копируют данные и синхронизируют изменения. Только они это делают для реплик. Это же инструменты репликации (и судя по всему асинхронной репликации), а значит использоваться они будут с момента ввода в эксплуатацию БД, а это в свою очередь означает, что на репликах будут такие же проблемы с блоат как и на мастере.
Наверное, можно доработать эти инструменты для очистки блоат. Например, демон/сабскрайбер можно посадить на мастер-ноду и доработать его, чтобы он писал в подменную таблицу, а потом переставлять таблицы местами и при этом решать вопросы асинхронного обновления. Но зачем?
Для очистки блоат есть pg_repack, pgcompacttable. И dbms_redefinition, о котором я узнал из комментариев к статье, тоже можно использовать, хоть он не совсем для этого задумывался.
Если по каким-то причинам данные инструменты не получается использовать, то можно самостоятельно собрать решение на основе изложенного в статье. В ней не описан чёрный ящик - всё можно проверить, оценить и решить: сработает ли это в каком-то конкретном случае или нет.
Оченно интересно, почему не одобрено использование проверенных временем инструментов.
Инструменты нужно проверить и внедрить в платформу, чтобы их могли использовать все тех. команды в компании. На тот момент данные решения в платформе не поддерживались. Решение частных случаев в очень большой компании может существенно усложнить работу команды DBA, что скажется на обслуживании остальных БД.
Вы “изобрели” логическую репликацию. Только вот slony-1 скорее мёртв, чем жив, londiste - примерно так же, логическая репликация на принципах публикаций/подписчиков
Впервые вижу упоминание этих инструментов в контексте очистки блоат. Было бы интересно почитать статью про то, как с помощью них можно выполнить чистку.
У проекта, судя по описанию, своего АБД нет
Каждому проекту по личному DBA? Это был бы прекрасный, идеальный мир )
В статье не хватает только кода воркера для дублирования данных. Дан только CTE-запрос, который он использует. Собрать аналогичное решение - дело одного дня, кмк. Без учёта времени тестирования, конечно.
Если знать как, то не сложно. В этом и была цель статьи - показать, что достаточно просто сделать свой, полностью контролируемый инструмент для решения подобных проблем.
Вы изобрели dbms_redefinition. О нем был доклад Мельникова , он планировал выдожить свой код, но вроде не выложил.
Похоже, что у вас ссылка не на тот доклад, скорее всего вы этот имели в виду "Изменение структуры таблиц". Инструмент интересный, можно поизучать, спасибо.
В pg_repack, dbms_redefinition и у нас подход один: копирование+дублирование+свап (в нашем случае, правда, не нужны права администратора). pgcompacttable в этом плане интереснее работает.
Много апдейтов, после запуска архивации добавились ещё и удаления, плюс щадящий режим работы автовакуума.
У вас настройки автовакуума по умолчанию ?
Нет, настройки выбраны DBA такие, чтобы автовакуум не сильно влиял на работу БД.
Кастомных настроек для отдельных горячих таблиц не было ?
Кастомных настроек не было, а когда мы обнаружили проблему с bloat уже поздно было пить боржоми. Сейчас думается, что при запуске архивации можно было бы поиграться с настройками autovacuum_vacuum_scale_factor и autovacuum_vacuum_threshold. Хотя не уверен, что это сильно бы улучшило ситуацию.
Дополнительно (в статье не указано) у нас стояла цель уменьшить занимаемое место, чтобы удовлетворить требованиям по месту от DBA, а автовакуум редко возвращает место ОС.
Если кратко, то это эффект используемого процесса: переливка батчами+триггеры для синхронизации. B-Tree индекс у нас использовался для полей с монотонно возрастающими айдишниками. Триггеры вставляли в новую таблицу строки со «свежими» (большими) айдишниками, пока лились батчи со «старыми» (меньшими) айдишниками. Из-за этого вставки в B-Tree перестали быть монотонно возрастающими и это (из-за всяких внутренних оптимизаций) приводило к раздуванию практически всех страниц индекса.
Перевод таблиц на партиции + архивация. Пустые партиции можно удалять и тем самым уменьшать реальный размер таблиц.
Для нас и наших объёмов данных это совсем не вариант, так как простой будет длительный и влияние на пользователей будет значительным даже в моменты низкой активности
Да, slony-1 и londiste копируют данные и синхронизируют изменения. Только они это делают для реплик. Это же инструменты репликации (и судя по всему асинхронной репликации), а значит использоваться они будут с момента ввода в эксплуатацию БД, а это в свою очередь означает, что на репликах будут такие же проблемы с блоат как и на мастере.
Наверное, можно доработать эти инструменты для очистки блоат. Например, демон/сабскрайбер можно посадить на мастер-ноду и доработать его, чтобы он писал в подменную таблицу, а потом переставлять таблицы местами и при этом решать вопросы асинхронного обновления. Но зачем?
Для очистки блоат есть pg_repack, pgcompacttable. И dbms_redefinition, о котором я узнал из комментариев к статье, тоже можно использовать, хоть он не совсем для этого задумывался.
Если по каким-то причинам данные инструменты не получается использовать, то можно самостоятельно собрать решение на основе изложенного в статье. В ней не описан чёрный ящик - всё можно проверить, оценить и решить: сработает ли это в каком-то конкретном случае или нет.
Да, в статье после скрипта есть уточнение о том, что перед дропом нужно провести дополнительные проверки на ненужность.
Инструменты нужно проверить и внедрить в платформу, чтобы их могли использовать все тех. команды в компании. На тот момент данные решения в платформе не поддерживались. Решение частных случаев в очень большой компании может существенно усложнить работу команды DBA, что скажется на обслуживании остальных БД.
Впервые вижу упоминание этих инструментов в контексте очистки блоат. Было бы интересно почитать статью про то, как с помощью них можно выполнить чистку.
Каждому проекту по личному DBA? Это был бы прекрасный, идеальный мир )
В статье не хватает только кода воркера для дублирования данных. Дан только CTE-запрос, который он использует. Собрать аналогичное решение - дело одного дня, кмк. Без учёта времени тестирования, конечно.
Не, долгих транзакций не было.
Если знать как, то не сложно. В этом и была цель статьи - показать, что достаточно просто сделать свой, полностью контролируемый инструмент для решения подобных проблем.
Похоже, что у вас ссылка не на тот доклад, скорее всего вы этот имели в виду "Изменение структуры таблиц". Инструмент интересный, можно поизучать, спасибо.
В pg_repack, dbms_redefinition и у нас подход один: копирование+дублирование+свап (в нашем случае, правда, не нужны права администратора). pgcompacttable в этом плане интереснее работает.
Спасибо за уточнение. Добавил информацию о pg_reorg в статью.
Много апдейтов, после запуска архивации добавились ещё и удаления, плюс щадящий режим работы автовакуума.
Нет, настройки выбраны DBA такие, чтобы автовакуум не сильно влиял на работу БД.
Кастомных настроек не было, а когда мы обнаружили проблему с bloat уже поздно было пить боржоми. Сейчас думается, что при запуске архивации можно было бы поиграться с настройками
autovacuum_vacuum_scale_factorиautovacuum_vacuum_threshold. Хотя не уверен, что это сильно бы улучшило ситуацию.Дополнительно (в статье не указано) у нас стояла цель уменьшить занимаемое место, чтобы удовлетворить требованиям по месту от DBA, а автовакуум редко возвращает место ОС.