PostgreSQL: аналитика для DBA
Многие пользователи СУБД PostgreSQL знают, что сервер во время своей работы собирает разнообразную статистику, но не все знают, что ее полезно анализировать и как ее извлекать для этого. В этом небольшом тулките собраны несколько полезных запросов, дающих некоторое представление о том, как использовать это «скрытое знание», которое постоянно копится. Эти запросы можно использовать для мониторинга состояния PostgreSQL (ручного или с помощью плагинов для систем мониторинга вроде Nagios, Cacti или Zabbix), для поиска узких мест в работе сервера и многих других подобных задач. Помните, что это лишь верхушка айсберга; в документации можно найти описания нескольких десятков системных представлений, которые также могут быть полезны администратору PostgreSQL.
Для корректной работы тулкита необходимо включить опции stats_block_level и stats_row_level в postgresql.conf, а также настроить параметр stats_reset_on_server_start по своему усмотрению. Если при каждом перезапуске сервера PostgreSQL вы меняете какие-то существенные параметры его конфигурации, имеет смысл обнулять статистику, чтобы отслеживать эффект внесенных изменений. Если же вас интересует долгосрочная перспектива и рестарт производится не вследствие изменения конфигурации PostgreSQL, ставьте параметр stats_reset_on_server_start в значение off.
Отношение hit / read
При выполнении запроса PostgreSQL сначала смотрит, есть ли нужные в запросе данные в разделяемой памяти (shared buffers). Если они найдены, засчитывается hit, если нет — делается сравнительно медленный системный вызов fread для поднятия данных с диска или из дискового кеша операционной системы и засчитывается read. В среднем, верно правило: чем больше отношение hit/read, тем лучше настроен PostgreSQL, так как он очень мало читает с диска, в основном извлекая данные из разделяемой памяти. Для большинства не очень больших баз это отношение должно лежать в пределах 5000-10000. Не стремитесь, однако, искусственно завысить настройку shared_buffers, которая прямо определяет hit/read: слишком большие размеры разделяемой памяти ведут к потере производительности в базах с интенсивной записью. Также стоит помнить, что fread может быть довольно быстрым, если данные находятся в дисковом кеше ОС.
SELECT datname, CASE WHEN blks_read = 0 THEN 0 ELSE blks_hit / blks_read END AS ratio FROM pg_stat_database;
Количество модификаций, произошедших в таблице
Список по таблицам: какое количество записей в них было добавлено, изменено и удалено с момента последнего сброса статистики. Администратор БД должен представлять, какие таблицы являются самыми нагруженными в текущей базе данных, а также каково соотношение между различными типами модифицирующих запросов к ним.
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del FROM pg_stat_user_tables ORDER BY n_tup_upd DESC;
Статистика seq scan / index scan
Список по таблицам: какое количество запросов к ним было выполнено посредством последовательного просмотра; какое количество запросов было выполнено с использованием индексов; а также отношение этих двух чисел. Позволяет оценить, все ли нужные индексы созданы в данной таблице. Если ваши таблицы содержат более нескольких тысяч рядов, последовательный просмотр будет выполняться медленнее просмотра индекса, поэтому в идеальном случае seqscan-ов в таких таблицах быть не должно. Если у вас они все же есть, анализируйте запросы к таким таблицам и создавайте соответствующие индексы. При этом важно не перестараться: чем больше индексов по колонкам таблицы, тем дороже становятся операции обновления данных.
Также не забывайте, что после создания индекса таблице нужно делать ANALYZE, иначе планировщик запросов не заметит изменений в структуре таблицы.
SELECT relname, seq_scan, idx_scan, CASE WHEN idx_scan = 0 THEN 100 ELSE seq_scan / idx_scan END AS ratio FROM pg_stat_user_tables ORDER BY ratio DESC;
Статистика по индексам
Список по индексам: сколько записей из индекса были возвращены в запросах по этому индексу; сколько рядов при этом все же пришлось просматривать в родительской таблице; отношение этих двух чисел. Суть данной статистики проста: если у вас много fetch-ей, значит индекс устарел и при выполнении запроса PostgreSQL вынужден смотреть непосредственно в таблицу, как в источник самых актуальных данных, что замедляет его работу. В этом случае необходимо перестроить данный индекс, чтобы он соответствовал реальным данным в таблице.
SELECT indexrelname, idx_tup_read, idx_tup_fetch, CASE WHEN idx_tup_fetch = 0 THEN 100 ELSE idx_tup_read / idx_tup_fetch END AS ratio FROM pg_stat_user_indexes ORDER BY ratio DESC;
Выполняющиеся запросы с их продолжительностью
Простой список выполняющихся сервером в настоящий момент запросов. Бывает полезен, когда вы недостаточно хорошо знаете систему или просто не успели ее настроить — с его помощью можно найти и прервать «плохой» запрос, который мешает работе сервера (колонка procpid содержит PID процесса, которому можно сделать kill при необходимости). Помните, однако, что простой периодический просмотр выполняющхся запросов ни в коем случае не заменит замечательный анализатор логов pgFouine. Также не стоит забывать, что процесс, в котором вы выполняете данный запрос, тоже попадает в результирующий список.
SELECT datname, NOW() - query_start AS duration, procpid, current_query FROM pg_stat_activity ORDER BY duration DESC;
Список текущих блокировок
Список текущих блокировок с указанием типа блокировки, таблицы и базы данных, на которой она выставлена и номера транзакции, которая выставила блокировку. Не стоит пугаться, если запрос выдает длинный список lock-ов — не все они являются критическими и блокирующими таблицу от всех возможных изменений и даже чтения. Для анализа списка блокировок обязательно стоит ознакомиться с документацией об их типах в PostgreSQL и о том, когда и какими запросами они выставляются. Один из распространенных случаев, когда список блокировок может пригодиться: выполняя команду ps aux | grep ^postgres вы видите, что в статусе одного из процессов postgres-а написано waiting — это как раз и означает, что данный процесс ждет, когда будет снята нужная ему блокировка. Какая именно — выясняйте, запустив данный запрос.
SELECT l.mode, d.datname, c.relname, l.granted, l.transactionid FROM pg_locks AS l LEFT JOIN pg_database AS d ON l.database= d.oid LEFT JOIN pg_class AS c ON l.relation = c.oid;
Терминология
В последующем тексте, термин сайт означает хост машину, на которой установлен Postgres . Т.к. возможно установить более одной базы данных Postgres на один хост, то этот термин более точно указывает на любой отдельный набор установленных исполняемых файлов и баз данных Postgres .
Суперпользователь Postgres — это имя пользователя postgres , которому принадлежат исполняемые и файлы баз данных Postgres . Для суперпользователя, все механизмы защиты пропускаются и доступны любые произвольные данные. К тому же, суперпользователю Postgres позволено запускать некоторые программы поддержки, которые, в основном, недоступны для всех пользователей. Заметим, что суперпользователь Postgres — это не суперпользователь Unix (на который будем ссылаться как на root ). Суперпользователь должен иметь не нулевой идентификатор пользователя ( UID ) по причинам безопасности.
Администратор базы данных или DBA — это человек, который отвечает за установку механизмов Postgres в соответствии с политикой безопасности сайта. DBA может добавлять новых пользователей по методике, описанной ниже и поддерживать набор шаблонов баз данных для использования с createdb .
postmaster — это процесс, который работает как расчётная палата для запросов к системе Postgres . Клиентские приложения подключаются к postmaster , который отслеживает любые системные ошибки и взаимодействие с процессами сервера. postmaster может принимать некоторые аргументы из командной строки для настройки своего поведения. Однако, указывать аргументы необходимо только если ты намереваешься запускаться на нескольких сайтах или не на сайте по умолчанию.
Сервер Postgres (т.е. исполняемая программа postgres ) может быть запущена прямо из пользовательского shell суперпользователем Postgres (с именем базы данных в качестве аргумента). Однако, из-за этого не принимается во внимание разделяемый буферный пул и блокировка таблиц, связанных с postmaster/сайтом, поэтому это не рекомендуется на многопользовательском сайте.
| Предыдущий | Начало | Следующий |
| Введение | В начало главы | Нотация |
DBA2
Администрирование PostgreSQL 13. Настройка и мониторинг
Основные сведения
Продолжительность: 4 дня
- владение ОС Unix,
- знакомство с PostgreSQL в объеме курса DBA1.
- настройка различных конфигурационных параметров исходя из понимания внутренней организации сервера,
- мониторинг сервера с использованием обратной связи для итеративной настройки параметров,
- настройки, связанные с локализацией,
- управление расширениями и знакомство с процедурой обновления сервера.
Руководство слушателя
Перед началом самостоятельного прохождения курса ознакомьтесь с руководством слушателя. В нем вы найдете инструкцию по настройке рабочего места и указания по выполнению практических заданий.
Темы
| Введение | учебные материалы | видео | |
| Многоверсионность | |||
| 1. | Изоляция | учебные материалы | видео |
| 2. | Страницы и версии строк | учебные материалы | видео |
| 3. | Снимки данных | учебные материалы | видео |
| 4. | HOT-обновления | учебные материалы | видео |
| 5. | Очистка | учебные материалы | видео |
| 6. | Автоочистка | учебные материалы | видео |
| 7. | Заморозка | учебные материалы | видео |
| Журналирование | |||
| 8. | Буферный кеш | учебные материалы | видео |
| 9. | Журнал предзаписи | учебные материалы | видео |
| 10. | Контрольная точка | учебные материалы | видео |
| 11. | Настройка журнала | учебные материалы | видео |
| Блокировки | |||
| 12. | Блокировки объектов | учебные материалы | видео |
| 13. | Блокировки строк | учебные материалы | видео |
| 14. | Блокировки в оперативной памяти | учебные материалы | видео |
| Задачи администрирования | |||
| 15. | Управление расширениями | учебные материалы | видео |
| 16. | Локализация | учебные материалы | видео |
| 17. | Обновление сервера | учебные материалы | видео |
Дополнительно
- учтены нововведения версий PostgreSQL 11, 12 и 13;
- вместо сборки из исходных кодов используется установка из пакета;
- материал многих тем переработан, чтобы демонстрации чередовались с теорией.
Версия курса по PostgreSQL 10
Администрирование PostgreSQL 10. Настройка и мониторинг
Основные сведения
Продолжительность: 4 дня
- владение ОС Unix,
- знакомство с PostgreSQL в объеме курса DBA1.
- настройка различных конфигурационных параметров исходя из понимания внутренней организации сервера,
- мониторинг сервера с использованием обратной связи для итеративной настройки параметров,
- настройки, связанные с локализацией,
- управление расширениями и знакомство с процедурой обновления сервера.
Руководство слушателя
Перед началом самостоятельного прохождения курса ознакомьтесь с руководством слушателя. В нем вы найдете инструкцию по настройке рабочего места и указания по выполнению практических заданий.
Темы
| Введение | слайды | видео | |||
| Многоверсионность | |||||
| 1. | Изоляция | слайды | демо | ответы | видео |
| 2. | Страницы и версии строк | слайды | демо | ответы | видео |
| 3. | Снимки данных | слайды | демо | ответы | видео |
| 4. | HOT-обновления | слайды | демо | ответы | видео |
| 5. | Очистка | слайды | демо | ответы | видео |
| 6. | Автоочистка | слайды | демо | ответы | видео |
| 7. | Заморозка | слайды | демо | ответы | видео |
| Журналирование | |||||
| 8. | Буферный кэш | слайды | демо | ответы | видео |
| 9. | Журнал предзаписи | слайды | демо | ответы | видео |
| 10. | Контрольная точка | слайды | демо | ответы | видео |
| 11. | Настройка журнала | слайды | демо | ответы | видео |
| Блокировки | |||||
| 12. | Блокировки объектов | слайды | демо | ответы | видео |
| 13. | Блокировки строк | слайды | демо | ответы | видео |
| 14. | Блокировки в памяти | слайды | демо | ответы | видео |
| Задачи администрирования | |||||
| 15. | Управление расширениями | слайды | демо | ответы | видео |
| 16. | Локализация | слайды | демо | ответы | видео |
| 17. | Обновление сервера | слайды | демо | ответы | видео |
Видеозапись курса проводилась с 8 по 15 апреля 2019 года в Москве.
Версия курса по PostgreSQL 9.5
Администрирование PostgreSQL 9.5. Настройка и мониторинг
Темы
| Введение | |||||
| 1. | Архитектура PostgreSQL | слайды | практика | видео | |
| Изоляция и многоверсионность | |||||
| 2. | Изоляция транзакций | слайды | демо | практика | видео |
| 3. | Страницы и версии строк | слайды | демо | практика | видео |
| 4. | Снимки и блокировки | слайды | демо | практика | видео |
| 5. | Очистка | слайды | демо | практика | видео |
| 6. | Автоочистка и заморозка | слайды | демо | практика | видео |
| Журналирование | |||||
| 7. | Буферный кэш | слайды | демо | практика | видео |
| 8. | Упреждающий журнал | слайды | демо | практика | видео |
| 9. | Контрольная точка | слайды | демо | практика | видео |
| Репликация | |||||
| 10. | Файловая репликация | слайды | демо | практика | видео |
| 11. | Потоковая репликация | слайды | демо | практика | видео |
| 12. | Переключение на реплику | слайды | демо | практика | видео |
| 13. | Репликация: варианты | слайды | практика | видео | |
| Основы оптимизации | |||||
| 14. | Обработка запроса | слайды | демо | практика | видео |
| 15. | Методы доступа | слайды | демо | практика | видео |
| 16. | Способы соединения | слайды | демо | практика | видео |
| 17. | Статистика | слайды | демо | практика | видео |
| 18. | Использование памяти | слайды | демо | практика | видео |
| 19. | Профилирование | слайды | демо | практика | видео |
| 20. | Оптимизация запросов | слайды | демо | практика | |
| Разные темы | |||||
| 21. | Секционирование | слайды | демо | практика | видео |
| 22. | Локализация | слайды | демо | практика | видео |
| 23. | Обновление сервера | слайды | демо | практика | видео |
| 24. | Управление расширениями | слайды | демо | практика | видео |
| 25. | Внешние данные | слайды | демо | практика | видео |
Видеозапись курса проводилась с февраля по май 2016 года в Москве. Ей соответствует данная версия материалов.
PostgreSQL: 3 кейса от DBA
Ситуация с idle in transaction – приложение открыло транзакцию, отправило изменения в базу данных, а закрыть транзакцию забыло, транзакция висит.
Как решать инженеру такие ситуации? В Интернете можно найти много статей на тему: что такое idle in transaction, с чем оно связано и даже на практике посмотреть примеры реализации этой ситуации, но не получите самую главную информацию – как решать такие проблемы?
Иван Чувашов
Ведущий инженер Okko
У нас была ситуация, когда появились idle in transaction, и приложение начало тормозить – пул подключений в базе данных был забит. Они блокировали ресурсы, и нам нужно было срочно принимать какое-то решение, мы пошли самым простым путём, который напрашивается и предлагается вашему вниманию.
Первый вариант, который может быть – это выполнение команд
select pg_cancel_backend(pid) from pg_stat_activity where state = ‘idle in transactions’ and datname = ‘название_БД’;
или
select pg_terminate_backend(pid) from pg_stat_activity where state = ‘idle in transactions’ and datname = ‘название_БД’;
Иван Чувашов
Сертифицированный администратор PostgreSQL: PostgresPro, 10 уровень «Эксперт»
А может ли упасть PostgreSQL?
Когда выполняете pg_terminate_backend – это не нулевая вероятность. Допустим, у вас есть процесс, который считывает данные и вы выполняете команду pg_terminate_backend , запрос подтягивает эти же данные в оперативную память PostgreSQL. Если в результате данные стали грязными, то их нельзя считывать другим процессам. Поэтому postmaster-у нужно очистить оперативную память, что он и делает, перезагружая себя.
Точечно отстреливать запросы, можно вызвать групповую команду по маске – очевидные плюсы. Но эти команды на самом деле не решают проблему с idle in transaction. Когда ситуация возникает на стороне бэкенда и мы выполнили команду, которая группой срубила запросы, то через некоторое время ситуация может повториться и тут же появятся от бэкенда запросы в статусе idle in transaction.
Другой вариант – перезагрузка сервера PostgreSQL
Иван Чувашов
Спикер курса по PostgeSQL
Какие в этом решении могут быть минусы? Конечно же, остановка сервера. К тому же часто бывает, что на одном кластере находится не одна база, а несколько или даже с десяток разных баз.
При остановке сервера PostgreSQL мы убиваем подключения к другим базам или подключения других сервисов. Все это усугубляется ситуацией, когда приложение автоматически не может переподключится к базе. Да бывает и такое! Что может привести к каскадному эффекту в перезагрузке сервисов.
Не знаю, если ли тут плюсы?
Небольшой лайхак. Чтобы быстрее перезапустить кластер, перед перезагрузкой выполните команду checkpoint .
Ещё вариант – перезагрузка бэкендов
Иван Чувашов
13 лет работает с базами данных
Через pg_stat_activity определяем IP проблемного сервиса, с которого произошло подключение. Начинаем их перезагружать. В мире IT микроархитектуры этот процесс не будет являться существенной проблемой.
В нашей ситуации получилось таким образом: увидели idle in transaction – начали пачками отключать запросы. Но количество соединений с базой данных не изменялось. Тут же появлялись новые в этом же статусе. Потом мы подумали: нужно перезагружать PostgreSQL.
От этой идеи быстро отказались, потому что у нас были другие сервисы и была вероятность, того, что после перезагрузки PostgreSQL они не поднимут заново соединение с базой. Поэтому мы через pg_stat_activity нашли бэкенды, которые забивают весь пул подключений, и их перезагрузили.
Еще есть другой вариант, не рассмотренный нами выше. Если вы используете инструмент управления пулом соединений, например pgbouncer, то ситуация решилась бы довольно просто:
- установка pgbouncer на паузу – команда pause;
- перезагрузка сервера PostgreSQL;
- снятие pgbouncer с паузы – команда resume.
ЕЩЁ
В августе 2021 провели митап с Иваном по нюансам работы с PostgreSQL.
На канале Слёрм: https://youtu.be/Qx2NoGCHco8.
Иван Чувашов
Больше 6 лет работает архитектором БД и DBA
СУБД PostgreSQL работает с диском, оперативной памятью, процессором. Если выходит аппаратная часть оборудования, то идём и чиним. Но иногда бывают и скрытые проблемы, например сбой в дисковом массиве, который мы можем заметить не сразу, можем вообще не знать об этом.
Давайте воспроизведем проблему сбоя дисковой подсистемы, затем покажем её решение. У нас есть PostgreSQL – тринадцатая версия. Создадим базу и инициализируем pgbench. Она существует, чтобы нагружать сервер, снимать метрики производительности. Но нам она нужна для других целей.
Рассмотрим таблицу pgbench_branches, в ней есть три поля и одна запись.
Найдем, где находится физически эта таблица на диске:
psql -p5432 -d test -c «select pg_relation_filepath(‘pgbench_branches’)»;
Остановим кластер PostgreSQL. Откроем файл base/16839/16853 и допишем любой текст в середине файла. Сохраним его.
Можно воспользоваться просто командой:
sed -i ‘s/@/@123@/’ ~/13/main/base/16839/16853
Запустим PostgreSQL. Попробуем прочитать данные из таблицы.
В таблице также одна запись, но теперь в ней просто пустые строки.
Мы знаем, что файл у нас поврежден, но при этом PostgreSQL об этом не знает, и это достаточно серьезная проблема, с которой можно встретится в PostgreSQL.
Как проблему можно решить? Если мы посмотрим: включена ли у нас контрольная сумма страниц данных в кластере, то мы увидим, что off. Когда PostgreSQL обращается к данным, он рассчитывает контрольную сумму данных страницы, сравнивая её с сохраненной в заголовке страницы, и если она не соответствует, то он выдаёт ошибку.
Насколько это просаживает перформанс? Есть статистика, что – от одного до трёх процентов, но при этом вы точно знаете, что данные у вас повреждены или не повреждены. Это стоит того, чтобы включать контрольную сумму данных у себя. В девелоперской базе неважно. В препроде на ваше усмотрение. А в продовской обязательно должно быть включено.
Иван Чувашов
Поддерживает техническую инфраструктуру компании Окко (dev, preprod, prod) в части баз данных
В двенадцатой версии появилась хорошая утилита pg_checksums . Если раньше до двенадцатой версии вам приходилось создавать новый кластер уже с включенной контрольной суммой данных и в него переносить данные, то с двенадцатой версии можно выключить текущий сервер PostgreSQL и запустить эту утилиту, и она просмотрит все страницы и запишет в заголовках контрольные суммы данных.
Спросите про прострой? Она настолько производительна, что будет упираться в ваш диск. Когда мы переводили кластер полтора-терабайтный во включенную контрольную сумму данных, у нас это заняло сорок минут.
Проделаем ту же процедуру с повреждением данных, что и ранее.
И теперь если прочитаем данные из таблицы pgbench_branches увидим, что у нас появилась ошибка о несовпадении контрольных сумм.
Но если мы всё-таки хотим извлечь эти данные, то есть флаг ignore_checksum_failure . Когда мы его включаем, у нас выдаётся предупреждение, что контрольная сумма данных не совпадает, но запрос исполняется.
Часто приложения используют только оперативные данные. PostgreSQL не обращается к старым страницам данных. И если в них есть повреждения, то мы можем узнать об этом слишком поздно, когда в резервных копиях тоже будут содержаться они.
Для проверки каталога данных можно воспользоваться командой checkdb в утилите pg_probackup. Хотя данная утилита создана для создания/восстановления резервных копий, в ней есть дополнительный инструмент проверки рабочего каталога базы данных и целостности индексов.
Иван Чувашов
Строил отказоустойчивые кластеры на базе СУБД PostgreSQL и GreenPlum 6x;
Пример из жизни. Запросы шли в базу и некоторые их них повисали. На сутки, двое, трое. Потом пул запросов стало большим и они начал забирать всю оперативную память. Приходил omm killer и убивал PostgreSQL.
Контрольные суммы страниц не были включены на том кластере. Мы не предполагали, что данные повреждены (любые проверки утверждали, что каталог данных и индексы не содержит повреждений) и думали, что у нас сложный запрос, который пытается вытащить много данных, висит и занимает всю оперативную память (что являлось фантастическим предположением).
- что-то с картой видимостью,
- что-то с индексами на этой таблице,
- что-то с данными в самой таблице.
Как вышли из ситуации? Создали новую пустую таблицу и по блокам перетаскивали данные со старой таблицы в новую. Потом били блоки на меньшего размера и так до тех пор пока не выявили семнадцать битых строк, для которых были ссылки в других таблицах, но в целевой отсутствовали.
ЕЩЁ
23–25 сентября 2021 года Иван проводит второй поток обучения продвинутого курса по PostgreSQL.
Посмотреть программу: https://slurm.club/3zk7PGq.
Кейс разбит на три ситуации и они о предотвращении проблем, а не исправлении.
Ситуация номер один
У нас есть три таблички: заказы, продукты и таблица, которая связывает многие ко многим. В какой-то момент времени бизнес решил, что нужно сравнивать значения в одно регистре (обратите внимание на тип у колонки id в таблице orders). Можно со стороны приложения переводить все данные к нижнему или верхнему регистру и делать сравнение в запросе. Но можно воспользоваться встроенным типом данных citext. Рассмотрим, как разработчики решили переходить на новый тип данных.
Иван Чувашов
Регулярно выступает на IT-конференциях
Первая команда у нас создаст эксклюзивную блокировку, которая дропнет constraint. Достаточно быстрая операция. Вторая – по изменению типа, он относится к одному виду типов, поэтому быстрее заменится, проблем с этим не будет. Далее меняем тип на связные таблицах и пытаемся создать constraint.
Что у нас получается – эксклюзивная блокировка на две таблицы product orders и orders, чтобы данные не изменялись. И это будет выполняться в одном потоке. Когда у нас 100-200 записей, то проблем нет – это доли секунды. Если записей стало больше, миллионы, тогда эти внешний ключ будет накатываться очень долго.
Разработчики выкатывают релиз, и у нас останавливается сервис. Моя была ошибка, что пропустил этот pull request. Разобрались, срубили запрос. Ночью мы остановили сервис бэкенда, накатили sql-скрипты.
Какие еще есть варианты решения? Можно воспользоваться конструкцией:
ADD CONSTRAINT . NOT VALID
VALIDATE CONSTRAINT
В этом случае будут наложены более легкие блокировки.
Вариант 2, более специфичный, но рабочий. С десятой версии Postgres появилась логическая репликация product_orders в product_orders_replic , в которой уже есть внешний ключ product_orders_product_id_fkey . Когда мы скопировали все данные, мы взяли и поменяли таблички: product_orders -> product_orders_tmp, product_orders_replic -> product_orders. Это можно делать всё в одной транзакции, и будет достаточно быстро.