Типовые ошибки в приложениях, которые ведут к bloat в PostgreSQL

Доклад был предоставлен на РИТ++ 2018
В нем рассмотрены 3 стандартные проблемы в приложениях которые ведут к распуханию таблиц и индексов в базе данных PostgreSQL, а так же как следствие к снижению производительности и эффективности приложения, работающего с базой данных.

Transcript

1. Типовые ошибки в приложениях, которые ведут к bloat в postgresql Сальников Андрей Data Egret (DBA)
2. Пролог. • Не ошибается только тот, кто ничего не делает. • Три истории ошибок: • Графики из мониторинга с проблемой. • Механизм возникновения проблемы. • Как избежать такой ошибки? • Как исправить последствия ошибки? • Вспомогательные инструменты. • Полезные ссылки.
3. История первая, в которой мы забываем про открытые транзакции в Postgresql
4. Акт первый. Штатная работа • Таблица ~ 2Mb. • Индекс ~ 0,5Mb. • Самая длительная транзакция ~ 120ms. • Среднее время запроса по таблице < 1ms. • Количество запросов к таблице ~ 2k per second. • Загрузка запросом CPU – неизмеримо малая. • Перебор памяти запросом – крайне малый.
7. Акт второй. Забытая транзакция • Причины: • Начали обращаться к внешнему сервису. • Произошел exception в приложении. • Некачественный код приложения. • Последствия: • Рост размеров таблицы и индекса. • Фатальное увеличение времени ответа базы данных. • Фатальное увеличение нагрузки на сервере. • Проблемы в работе приложения.
10. Акт третий. Возвращение к жизни? • Нашли и остановили транзакцию. • Ситуация стабилизировалась. • Приложение работает не так шустро, как раньше. • Запросы выполняются в полтора-два раза дольше. • Нагрузка на сервер выше, чем до аварии. • Что за …?
13. Акт четвертый. Теория • Autovacuum, что он делает? DML – Insert, Update, Delete Autovacuum DML
14. Акт пятый. Расследование • Что же случилось на самом деле? DML – Insert, Update, Delete Autovacuum DML
15. Акт шестой. Последствия • Таблица и индекс выросли в 150 раз. • Их размер не уменьшается. • База данных тратит ресурсы сервера впустую. • Эффективность приложения постепенно снижается. • Как все вернуть назад?
16. Акт седьмой. Исправительные работы • Как найти проблемные таблицы? • Расширение pgstattuple • Как сжать таблицы? • VACUUM FULL • pg_repack • pgcompacttable • Как предотвратить аварию? • Следить за длительностью сессий в состоянии idle in transaction. • Тестировать код.
19. История вторая, в которой мы распределяем нагрузку и оптимизируем серверные ресурсы.
20. Акт первый. ETL и отчеты • Бизнес требует отчеты. • Отчеты многочасовые. • Нагрузка распределена. • Все работает отлично!
23. Акт второй. Решаем проблемы ETL • Все работает отлично, но … • Иногда запросы для отчетов прерываются базой данных. • Поставлена задача устранить эту проблему: • max_standby_streaming_delay = 3h • hot_standby_feedback = on • А что там с мастером?
26. Акт третий. Магнитные бури • Ищем причину проблем на мастере. • Длинных транзакций нет. • Серверное оборудование в норме. • Подняты все: администраторы, разработчики и директор. • Неожиданно все самостоятельно исправляется.
29. Акт четвертый. Быть или не быть? • Как делать правильно? • Никогда не включать hot_standby_feedback? • Увеличить max_standby_streaming_delay? • Контролировать длительные сессии на репликах. • Разделять быстрые и длительные запросы по репликам. • Как устранить последствия? • Найти раздутые таблицы. • Сжать их наиболее удобным инструментом.
30. История третья, в которой происходит миграция
31. Акт первый. Новый шаг в развитии • Внедряем новый функционал. • Старый формат данных не устраивает нас. • Мы используем автоматизированный контроль версий базы данных. • Существующие значения в таблицах требуют изменений. • Плановые работы одобрены, проводим миграцию.
34. Акт второй. Снова проблемы • Миграция прошла успешно, но: • Старые запросы стали выполняться дольше. • Таблица существенно выросла в размерах. • Нагрузка на сервер снова больше, чем ранее. • Новый функционал еще не включали. • Не может быть! Это снова bloat!
37. Акт третий. Правильный подход • Большие миграции не делают автоматически. • Необходим контроль со стороны DBA. • Новая схема базы данных подготавливается этапами: • Добавляются новые поля. • Данные из старых полей мигрируют небольшими частями. • Между каждой миграцией дается время на работу autovacuum. • Добавляется триггер, для записи новых значений в новое поле. • Если необходимо старое наименование поля, после завершения миграции - поля переименовываются. • Можно запускать новую версию приложения.
38. Эпилог. Инструменты • Выявление bloat, расширение pgstattuple • https://github.com/dataegret/pg-utils/blob/master/sql/table_bloat.sql • https://github.com/dataegret/pg- utils/blob/master/sql/table_bloat_approx.sql • 20% bloat - допустимый размер в большинстве случаев. • Лечение bloat • VACUUM FULL • pg_repack • pgcompacttable
39. Ссылки • https://www.slideshare.net/alexius2/where-is-the-space- postgres • https://github.com/dataegret/pg-utils • https://github.com/reorg/pg_repack • https://github.com/dataegret/pgcompacttable • http://blog.dataegret.com/2018/03/postgresql-bloatbusters.html Email: Andrey.Salnikov@dataegret.com