Публикация была переведена автоматически. Исходный язык: Русский
Есть типичная история про PostgreSQL, которая повторяется в разных компаниях.
Живая продовая база, нагрузка нормальная, графики зелёные. И вдруг - алерт от мониторинга: на диске осталось меньше 10–15 %.
Смотришь на размер базы - да, большая, но вроде бы без катастроф. Начинаешь разбираться по таблицам и видишь знакомую картину: одна схема занимает львиную долю, внутри - пара особо жирных таблиц. Количество живых строк примерно стабильно, а файл таблицы растёт месяц за месяцем.
Дальше обычно всплывает то, что любая книжка по Postgres честно объясняет, но в реальной жизни до этого мало кто дочитывает.
- каждое обновление строки - это новая версия в файле таблицы
- каждое удаление - это пометка, а не моментальное исчезновение
- autovacuum помечает старые версии как свободные, но сам файл не уменьшается
Если таблица активно живёт, особенно с большим количеством update, внутри неё постепенно образуется слой мёртвых строк и дырок. В процентах это иногда выглядит страшно: из серии 30 % живых данных и 70 % мусора.
Мы ровно так и уперлись в bloat:
autovacuum ходил, но освободить место на диске уже не мог - только внутри файла.
Первая реакция понятная - вспомнить про VACUUM FULL. Но у него две проблемы:
- Нужен эксклюзивный лок на таблицу. Для горячей продовой таблицы это почти всегда мимо.
- Требуется столько же свободного места, сколько весит таблица, потому что под капотом создаётся новый файл.
Дальше обычно вспоминают про pg_repack. Инструмент хороший, многие им пользуются. Он умеет перепаковывать таблицу почти без блокировок, но с точки зрения диска там та же идея - нужна полноценная копия данных и индексов. Если место уже кончилось, такое упражнение делать поздно.
Мы как раз оказались в этой точке. Таблица большая, свободного места мало, нагрузка не даёт окна на долгий эксклюзивный лок. Стало понятно, что нужен более деликатный подход: без полной копии и без резких движений по IO.
В какой-то момент, перебирая статьи про bloat, наткнулись на блог Data Egret и репозиторий pgcompacttable на GitHub (https://github.com/dataegret/pgcompacttable).
Идея инструмента простая, но очень практичная:
- это отдельный скрипт на Perl, который подключается к базе как обычный клиент
- он использует системное поле ctid и обычные SQL-запросы
- строки из конца таблицы постепенно переписываются в свободное место ближе к началу файла
- после серии таких шагов запускается VACUUM, который может реально откусить освободившийся хвост
По сути, это аккуратная миграция живых строк вверх по файлу. Никакой полной копии таблицы не создаётся. Индексы перестраиваются по одному, от меньших к большим. Нужный запас по диску - максимум размер самого большого индекса, а не вся таблица плюс все индексы.
Для нашего кейса это звучало как раз то, что надо.
У pgcompacttable есть несколько требований:
- доступ к базе с правами суперпользователя или близко к тому
- установленное расширение pgstattuple - без него он не умеет считать bloat
- понятный список таблиц, с которыми можно работать прямо сейчас
Мы начали с простого:
CREATE EXTENSION IF NOT EXISTS pgstattuple;Потом посмотрели, что у нас происходит по bloat для подозрительных таблиц. В простом варианте это делается так:
SELECT *
FROM pgstattuple('public.operations');Там видно примерно процент живых строк, мёртвых и неиспользуемого пространства. Для некоторых таблиц цифры были в стиле:
- approximate percent dead tuples - 40+
- free space - ещё процентов 20
То есть половина файла жила ради пустоты.
Мы не стали сразу бросаться на самый большой монстр. Взяли пару средних таблиц и прогнали их в явном режиме, с максимально болтливым логированием.
Команда выглядела примерно так:
pgcompacttable \
--dbname mydb \
--schema public \
--table operations_log \
--verboseВажно, что по умолчанию утилита не трогает таблицы с небольшим bloat. В настройках есть минимальный порог процента. Это полезно - меньше шансов случайно устроить лишнюю работу.
Параллельно с запуском мы внимательно смотрели:
- диск по iostat
- лаг репликации
- нагрузку на CPU и количество блокировок
На небольших таблицах всё прошло довольно спокойно: нагрузка по диску выше обычной, но без фанатизма, реплика немного отстала и догнала.
Когда стало понятнее поведение инструмента, добрались до главной проблемы - условной таблицы operations на десятки гигабайт.
Для неё команда была уже с некоторыми параметрами аккуратности:
pgcompacttable \
--dbname mydb \
--schema public \
--table operations \
--verbose \
--min-age 4h \
--delay-ratio 0.5Смысл:
- не трогаем совсем свежие страницы - уменьшается шанс гоняться за активно изменяемыми строками
- через delay-ratio утилита делает паузы между пачками операций, чтобы не забить диск
Что мы увидели по факту:
- процесс переупаковки занял заметное время - это не минутное действие
- по мере продвижения по таблице размер файла медленно, но упрямо снижался
- после завершения и очередного вакуума общий размер упал на десятки процентов
- свободное место на диске реально появилось, а не только в отчётах pgstattuple
При этом база продолжала обслуживать запросы. Были локи на уровне строк и страниц, но до серьёзных проблем не дошло. Это, конечно, заслуга и аккуратных параметров, и того, что мы делали всё не в час пик.
Инструмент удобный, но не волшебный. Несколько вещей, о которые легко споткнуться:
- Репликация.Любые манипуляции с большим количеством строк - это WAL. Если реплика и так еле успевает, лаг может вырасти. У нас было два стенда - на одном инструмент шёл мягко, на другом пришлось ещё сильнее уменьшать агрессивность через параметры.
- Таблицы, где bloat уже невелик.В кластере часто есть мода на “применить новую игрушку везде”. Здесь это не нужно. Если pgstattuple показывает вменяемые проценты, оставили такие таблицы в покое.
- Ложное ощущение, что проблема решена навсегда.Если приложение как генерило bloat, так и продолжает, через какое-то время всё вернётся. pgcompacttable - это хорошая метла, но не замена нормальному дизайну.
Главное, что нам дал этот опыт - не только освобождённый диск.
- Появился план жизни с bloat, а не подход “дотянем до упора, потом в панике придумаем что-нибудь”.
- Мы стали внимательнее относиться к тому, как приложение обновляет данные.Например:заменили часть массовых update на вставки в отдельные исторические таблицы где-то отказались от частых “обновили одно поле по всей таблице”, когда это было не критично
- заменили часть массовых update на вставки в отдельные исторические таблицы
- где-то отказались от частых “обновили одно поле по всей таблице”, когда это было не критично
- В регулярных задачах администрирования появилась понятная рутинная операция:раз в какое-то время прогоняем pgstattuple по ключевым таблицам смотрим динамику если что-то полезло в красную зону, планируем окно для pgcompacttable
- раз в какое-то время прогоняем pgstattuple по ключевым таблицам
- смотрим динамику
- если что-то полезло в красную зону, планируем окно для pgcompacttable
Инструмент хорошо вписывается в этот режим, потому что не требует от нас резко освобождать в два раза больше диска под копии таблиц.
Есть типичная история про PostgreSQL, которая повторяется в разных компаниях.
Живая продовая база, нагрузка нормальная, графики зелёные. И вдруг - алерт от мониторинга: на диске осталось меньше 10–15 %.
Смотришь на размер базы - да, большая, но вроде бы без катастроф. Начинаешь разбираться по таблицам и видишь знакомую картину: одна схема занимает львиную долю, внутри - пара особо жирных таблиц. Количество живых строк примерно стабильно, а файл таблицы растёт месяц за месяцем.
Дальше обычно всплывает то, что любая книжка по Postgres честно объясняет, но в реальной жизни до этого мало кто дочитывает.
- каждое обновление строки - это новая версия в файле таблицы
- каждое удаление - это пометка, а не моментальное исчезновение
- autovacuum помечает старые версии как свободные, но сам файл не уменьшается
Если таблица активно живёт, особенно с большим количеством update, внутри неё постепенно образуется слой мёртвых строк и дырок. В процентах это иногда выглядит страшно: из серии 30 % живых данных и 70 % мусора.
Мы ровно так и уперлись в bloat:
autovacuum ходил, но освободить место на диске уже не мог - только внутри файла.
Первая реакция понятная - вспомнить про VACUUM FULL. Но у него две проблемы:
- Нужен эксклюзивный лок на таблицу. Для горячей продовой таблицы это почти всегда мимо.
- Требуется столько же свободного места, сколько весит таблица, потому что под капотом создаётся новый файл.
Дальше обычно вспоминают про pg_repack. Инструмент хороший, многие им пользуются. Он умеет перепаковывать таблицу почти без блокировок, но с точки зрения диска там та же идея - нужна полноценная копия данных и индексов. Если место уже кончилось, такое упражнение делать поздно.
Мы как раз оказались в этой точке. Таблица большая, свободного места мало, нагрузка не даёт окна на долгий эксклюзивный лок. Стало понятно, что нужен более деликатный подход: без полной копии и без резких движений по IO.
В какой-то момент, перебирая статьи про bloat, наткнулись на блог Data Egret и репозиторий pgcompacttable на GitHub (https://github.com/dataegret/pgcompacttable).
Идея инструмента простая, но очень практичная:
- это отдельный скрипт на Perl, который подключается к базе как обычный клиент
- он использует системное поле ctid и обычные SQL-запросы
- строки из конца таблицы постепенно переписываются в свободное место ближе к началу файла
- после серии таких шагов запускается VACUUM, который может реально откусить освободившийся хвост
По сути, это аккуратная миграция живых строк вверх по файлу. Никакой полной копии таблицы не создаётся. Индексы перестраиваются по одному, от меньших к большим. Нужный запас по диску - максимум размер самого большого индекса, а не вся таблица плюс все индексы.
Для нашего кейса это звучало как раз то, что надо.
У pgcompacttable есть несколько требований:
- доступ к базе с правами суперпользователя или близко к тому
- установленное расширение pgstattuple - без него он не умеет считать bloat
- понятный список таблиц, с которыми можно работать прямо сейчас
Мы начали с простого:
CREATE EXTENSION IF NOT EXISTS pgstattuple;Потом посмотрели, что у нас происходит по bloat для подозрительных таблиц. В простом варианте это делается так:
SELECT *
FROM pgstattuple('public.operations');Там видно примерно процент живых строк, мёртвых и неиспользуемого пространства. Для некоторых таблиц цифры были в стиле:
- approximate percent dead tuples - 40+
- free space - ещё процентов 20
То есть половина файла жила ради пустоты.
Мы не стали сразу бросаться на самый большой монстр. Взяли пару средних таблиц и прогнали их в явном режиме, с максимально болтливым логированием.
Команда выглядела примерно так:
pgcompacttable \
--dbname mydb \
--schema public \
--table operations_log \
--verboseВажно, что по умолчанию утилита не трогает таблицы с небольшим bloat. В настройках есть минимальный порог процента. Это полезно - меньше шансов случайно устроить лишнюю работу.
Параллельно с запуском мы внимательно смотрели:
- диск по iostat
- лаг репликации
- нагрузку на CPU и количество блокировок
На небольших таблицах всё прошло довольно спокойно: нагрузка по диску выше обычной, но без фанатизма, реплика немного отстала и догнала.
Когда стало понятнее поведение инструмента, добрались до главной проблемы - условной таблицы operations на десятки гигабайт.
Для неё команда была уже с некоторыми параметрами аккуратности:
pgcompacttable \
--dbname mydb \
--schema public \
--table operations \
--verbose \
--min-age 4h \
--delay-ratio 0.5Смысл:
- не трогаем совсем свежие страницы - уменьшается шанс гоняться за активно изменяемыми строками
- через delay-ratio утилита делает паузы между пачками операций, чтобы не забить диск
Что мы увидели по факту:
- процесс переупаковки занял заметное время - это не минутное действие
- по мере продвижения по таблице размер файла медленно, но упрямо снижался
- после завершения и очередного вакуума общий размер упал на десятки процентов
- свободное место на диске реально появилось, а не только в отчётах pgstattuple
При этом база продолжала обслуживать запросы. Были локи на уровне строк и страниц, но до серьёзных проблем не дошло. Это, конечно, заслуга и аккуратных параметров, и того, что мы делали всё не в час пик.
Инструмент удобный, но не волшебный. Несколько вещей, о которые легко споткнуться:
- Репликация.Любые манипуляции с большим количеством строк - это WAL. Если реплика и так еле успевает, лаг может вырасти. У нас было два стенда - на одном инструмент шёл мягко, на другом пришлось ещё сильнее уменьшать агрессивность через параметры.
- Таблицы, где bloat уже невелик.В кластере часто есть мода на “применить новую игрушку везде”. Здесь это не нужно. Если pgstattuple показывает вменяемые проценты, оставили такие таблицы в покое.
- Ложное ощущение, что проблема решена навсегда.Если приложение как генерило bloat, так и продолжает, через какое-то время всё вернётся. pgcompacttable - это хорошая метла, но не замена нормальному дизайну.
Главное, что нам дал этот опыт - не только освобождённый диск.
- Появился план жизни с bloat, а не подход “дотянем до упора, потом в панике придумаем что-нибудь”.
- Мы стали внимательнее относиться к тому, как приложение обновляет данные.Например:заменили часть массовых update на вставки в отдельные исторические таблицы где-то отказались от частых “обновили одно поле по всей таблице”, когда это было не критично
- заменили часть массовых update на вставки в отдельные исторические таблицы
- где-то отказались от частых “обновили одно поле по всей таблице”, когда это было не критично
- В регулярных задачах администрирования появилась понятная рутинная операция:раз в какое-то время прогоняем pgstattuple по ключевым таблицам смотрим динамику если что-то полезло в красную зону, планируем окно для pgcompacttable
- раз в какое-то время прогоняем pgstattuple по ключевым таблицам
- смотрим динамику
- если что-то полезло в красную зону, планируем окно для pgcompacttable
Инструмент хорошо вписывается в этот режим, потому что не требует от нас резко освобождать в два раза больше диска под копии таблиц.
Поделиться
