Поддержка PostgreSQL - это больше, чем просто план обслуживания

«Нам нужно добиться НУЛЕВОГО времени простоя и высокой производительности в нашем приложении базы данных»,  - сказали мне во время разговора с менеджером направления бизнеса во время конференции в прошлом году. Мы часто слышим это от наших клиентов и постоянно видим это в наших обсуждениях на форумах.

Большинство менеджеров центров обработки и управления базами данных, системных администраторов и администраторов баз данных слышали это от «Бизнеса» не один раз. На самом деле это часто означает, что база данных должна оставаться доступной, а проблемы должны устраняться в сроки, требуемые бизнесом.

Когда вы спрашиваете кого-нибудь, как избежать этих проблем, один из самых популярных вариантов:

«Реализуйте комплексную стратегию управления базами данных».

Каковы общие задачи управления базами данных?

Когда мы немного углубимся в то, что это означает, мы получим совет вроде списка общих задач управления базами данных:

Вакуумный анализ каждую неделю, обновляйте статистику
Повторно индексируйте сильно обновленные таблицы каждый месяц
Планирование событий полного вакуума
Следите за своими подключениями и загрузкой

Все хорошие советы, всякая помощь, но всегда ли они гарантируют, что вам не понадобятся простои? Действительно ли простои можно избежать или это нечто большее, чем просто техническое обслуживание или управления базами данных?

Техническое обслуживание и управление базами данных может ввести в заблуждение

В прошлом мы сталкивались с некоторыми интересными ситуациями, когда обычные задачи управления базами данных непреднамеренно компенсировали неоптимизированную базу данных, отказ которой приводил к значительному простою.  

Настройка параметров автоочистки PostgreSQL

В одном из случаев пользователь запланировал сценарий управления базами данных, который будет очищать его базу данных каждые несколько дней. У него была хорошая производительность, и все шло хорошо, пока однажды база данных просто не перестала принимать соединения, и он увидел ошибку в своих журналах:

Мы смогли исправить базу данных, запустив «Полный вакуум» в однопользовательском режиме, что заняло около 13 часов. В ходе расследования было обнаружено, что база данных пользователя неуклонно росла в течение определенного периода времени, в то время как его настройки автоочистки никогда не корректировались с учетом этого.

В результате автоочистка некоторых больших таблиц прерывалась и никогда не могла завершиться из-за пользовательских транзакций. Вся работа с вакуумом управления базами данных была сделана его плановым обслуживанием, так уж получилось, что его cron был отключен во время одного из мероприятий по обслуживанию ОС, поэтому теперь очистки не было. В конечном итоге это привело к тому, что кластер достиг порога цикла.

Можно было бы возразить, что ему следовало быть более осторожным со своим cron, однако, если бы были более осведомленные и скорректированные их параметры вакуума управления базами данных, чтобы приспособиться к увеличенному размеру базы данных; автовакуум мог бы предотвратить наложение даже без обслуживания.

Еще один интересный момент, который не привел к отключению, но все же вызвал сильную изжогу, - это периодические всплески нагрузки в системе, которые сопровождались медленным выполнением запросов. Мы проверили планы запросов, и они не изменились, даже когда производительность была плачевной. Взгляд на pg_stat_activities во время одного из таких случаев показал следующее с запросами из приложения:

Стол подвергался вакуумированию, чтобы предотвратить оборачивание, был часто используемым столом. Запросы, которые должны были выполняться против него, должны были ждать управления базами данных, что оказывало эффект снежного кома на системных ресурсах. У пользователя был сценарий обслуживания и управления базами данных, но он запускался недостаточно часто, чтобы свести на нет влияние рабочей нагрузки. Таблица должна была проходить кучу массовых обновлений каждые несколько часов как часть бизнес-логики, что делало необходимость частой уборки еще более актуальной.

Это вызвало вопрос: «Как часто мне следует проводить техническое обслуживание  и управления базами данных?» На этот вопрос было нелегко ответить, поскольку частота будет зависеть от скорости изменения данных в таблице. Только при наличии скрипта, который мог бы часто проверять статистику и запускать вакуум.

Постойте, был один «автовакуум», просто параметры лучше выставили.

Пользователь был введен в заблуждение, полагая, что он сделал достаточно со своими сценариями вакуума управления базами данных. В обоих случаях одних задач обслуживания базы данных было недостаточно для поддержания оптимальной работы сервера.

Шаг назад

Когда дело доходит до обслуживание postgresql - или, на самом деле, любой базой данных - мы начинаем с выявления симптомов и ищем средства для устранения проблем, которые их вызывают. Стратегии управления базами данных также создаются со стратегией в том же континууме, но ранее; цель состоит в том, чтобы предотвратить боль или периодически лечить ее, прежде чем она действительно станет серьезной травмой. Проблема в том, что иногда ваше обслуживание и управления базами данных настолько хорошее, что вы никогда не обнаруживаете недостатков, которые могут быть в самом дизайне. В нашем случае это база данных, которая может нуждаться в помощи, все медлительности не вызваны плохой статистикой. Все временные файлы не создаются из-за небольшого размера work_mem.   

Что делать для лучшего обслуживания PostgreSQL

Начните с того, в каком состоянии находится ваша база данных
Это более короткий список вещей, над которыми нужно работать, чем то, что может показаться на поверхности.
 
Ваша система испытывает частые скачки нагрузки?

Для начала стоит посмотреть на загрузку ЦП, использование памяти и нагрузку ввода-вывода на самом компьютере. Наблюдение за ними и обнаружение процессов, которые используют наибольшее количество ресурсов, а также того, какая часть системных ресурсов подвергается воздействию, является хорошим ключом к пониманию того, что необходимо решить и сделать управление базами данных. Но это само по себе указывает на наличие проблем в БД, но не дает ответа на вопрос, в чем проблема.

Просмотр запросов, ожидающих блокировки

Запросы постоянно блокируют друг друга, или конкуренция за ресурсы - еще один признак того, что существует ещё более глубокая проблема, которая может быть связана с бизнес-логикой или структурами таблиц.
 
Снижение производительности

Это немного субъективно или его градиент. Чтобы сделать вывод, что вы страдаете от потери производительности, вам действительно нужны тесты и управление базами данных; каково было ваше «заведомо хорошее состояние», когда все работало хорошо? История выполнения запросов в хорошие времена должна быть доступна из предыдущих запусков, чтобы вы могли понять, что привело к плохим временам, и предотвратить или исключить риск возникновения этих проблем в будущем. Если вы еще не разработали этот базовый план, один полезный подход объясняется в блоге Марка «База данных работает медленно!»
 
Ошибки чтения, отсутствующие файлы

Я никому не желаю этого проклятия, но предположим, что ваши журналы действительно заполнены ошибками об отсутствии файлов или ошибками при чтении файлов. При чтении таблиц возникают ошибки из-за отсутствия файлов, поврежденных блоков данных или кортежей, или строк, которые отображаются в таблице, но не в ее индексах; есть даже ошибки, связанные только с подключением к самой базе данных. Это пугающая находка, которая часто указывает на повреждение базы данных, когда мы видим эти вещи в Службе технической поддержки.  
 
Гремлины в базе данных?

Если ответ на один или несколько из вышеперечисленных вопросов - «да», то, в конечном итоге, вам предстоит выполнить две основные задачи при управление базами данных: решить текущую проблему и предотвратить ее появление в будущем. Закатайте рукава и будьте готовы копаться, прежде чем приступить к составлению плана обслуживания для хорошего здоровья.

Когда начать

Иногда бывает сложно понять, с чего начать. Я подозреваю, что с БД дела обстоят не лучшим образом. Как мне начать поиск проблем и их устранение?

Низкие технологии: начните с журналов

Ваши файлы журналов могут быть неизмеримо ценными, если выбраны правильные значения, они могут предоставить вам точную информацию о том, что происходит внутри базы данных, будь то выполнение запроса, контрольные точки, блокировки. Ниже приведен набор значений, которые я предпочитаю устанавливать на небольшой период времени, когда мне предоставляется новая база данных для управления:

Более подробные сведения о параметрах ведения журнала и управления базами данных были прекрасно описаны Ричардом в его блоге. Выявление и устранение проблем быстрее путем ведения журнала действий PostgreSQL.

Для удобства чтения тенденций в журналах базы данных вы можете использовать pgBadger, блог об этом был написан Амитом PgBadger.

Кроме того, настройте мониторинг ресурсов вашей ОС, чтобы иметь возможность генерировать отчет об использовании ЦП, памяти и статистике ввода-вывода за период времени, когда вы собираете журналы базы данных.

Когда вы начинаете просматривать их, перечисляя необычные события из обоих; вы начнете видеть закономерности.

Например, пользователь жаловался на случайные скачки производительности процессора. Мы увеличили уровни журнала, чтобы захватывать запросы с их идентификаторами сеанса в журналах БД, а также, как мы знали время, вывод команды 'top', когда наблюдался всплеск. Ниже приведены некоторые важные записи, которые мы видели:

Идентификатор процесса 8578 показался интересным, поэтому мы поискали его в журналах и обнаружили следующее:

У нас был огромный запрос с объединениями между большими таблицами, который потреблял ресурсы. Затем мы приступили к оптимизации запроса, и всплески исчезли.

Выше была одна из самых простых ситуаций, когда мы могли зафиксировать выход TOP в момент, когда происходил всплеск. В ситуациях, когда нам нужно сослаться только на графики ЦП, мне лично нравится помещать их в своего рода сетку со строками, содержащими наиболее распространенные сообщения в журналах БД, и столбцами, показывающими наблюдения в System. Затем я пытаюсь отметить пересекающийся блок событиями, которые произошли в то же время. Как и в приведенном выше случае, у меня был всплеск ЦП, и в журналах у меня был запрос, связанный с PID, который, казалось, вызвал всплеск. Моя таблица выглядела бы так:

Высокий процессор

Высокий ввод / вывод

Высокая память

Контрольно-пропускные пункты
 
Медленный запрос

Икс

Вакуум

Примечание: обратите внимание, что выше - это подход, который я считаю полезным на основании своего опыта - это не лучший сценарий. Идея состоит в том, чтобы продемонстрировать, что при работе над проблемой нужно смотреть более чем на один фрагмент данных.  

Если вы обнаружите отдельные медленные запросы в управлении базами данных, вам придется выполнить некоторую настройку запросов. Ричард снова отлично поработал над этим в своем блоге, пожалуйста, прочтите статью Как использовать EXPLAIN ANALYZE .

Если вы начнете с базы данных, которая работает, но имеет «неизвестное» состояние работоспособности, вы никогда не узнаете, какими должны быть ваши базовые показатели или насколько хорошо вы можете масштабировать производительность или доступность. Хотя достижение нулевого времени простоя никогда не является разумной целью, вы можете по крайней мере значительно улучшить ситуацию с помощью правильного подхода.

Если вы обнаружите, что унаследовали решение от консультанта или предыдущего администратора баз данных, помните:

Всегда начинайте со своего базового поведения
Внедрить систему мониторинга и оповещения
Поймите оба атрибута существующей системы ...
... и ожидания ваших приложений от базы данных

Определите какие-либо симптомы «это не работает» или «это не работает достаточно хорошо».

Составьте план, который больше ориентирован на «выздоравливай», и постарайтесь решить как можно больше индивидуальных проблем.
Определите правильные методы обслуживания, которые:
Позвольте вам избежать ручных или радикальных решений.
Принимает во внимание любые нерешенные проблемы, связанные с вашей работой «выздоравливай», и позволяет вам сознательно компенсировать, когда необходимо управление базами данных, вместо того, чтобы скрывать проблему от понимания.
 
Дополнительные сведения о методах и решениях мониторинга можно найти в блоге Ajay Patel Monitor PostgreSQL , а также в блоге Amit Sharma и Mohini Ogale, Как использовать анализатор журналов pgBadger и postgresql.conf для решения проблем с производительностью запросов .