Отслеживание высокой загрузки процессора на Amazon Aurora PostgreSQL

Высокая загрузка процессора на Aurora RDS

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

Будьте проще

Встроенные расширения PostgreSQL pg_stat_statements, pg_stat_activity и pg_stat_user_tables являются отличными отправными точками и могут быстро помочь вам собрать информацию о ваших лучших SQL, недостающих индексах, собрать информацию о блокировке и определить заблокированные запросы вместе с блокирующими PID’ами/запросами.

https://medium.com/media/2f24ca9c7a72fe833d90317710c48f82/href

Логирование медленных запросов

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

# Example that logs statements executing for > 500ms.
log_min_duration_statement=500

# Useful in determining if lock waits are causing poor performance 
log_lock_waits=1

Выполните EXPLAIN ANALYZE и поищите улучшения.

Некоторые рекомендации по поиску:

• Ищите разницу в расчетных и фактических строках.
• Нет индекса, неправильный индекс (кардинальность)
• Большое количество прочитанных буферов (проверьте рабочий набор и подходит ли он под shared_buffers).
• Большое количество строк, отфильтрованных предикатом post join.
• Чтение большего количества данных, чем необходимо (обрезка, кластеризация, только для индексов).
• Ищите медленные узлы в ваших планах (SORT[AGG], NOT IN, OR, большие seq_scans, CTE, COUNT, использование функций в фильтрах и т.д.).

Обратите внимание, что последовательное сканирование в некоторых случаях может быть быстрее, чем сканирование по индексу, особенно когда SELECT возвращает более 5-10% всех строк в таблице. Это связано с тем, что индексное сканирование требует нескольких операций ввода-вывода для каждой строки, в то время как последовательное сканирование требует только одного ввода-вывода для каждой строки или даже меньше, поскольку блок (страница) на диске содержит более одной строки, поэтому более одной строки может быть получено с помощью одной операции ввода-вывода. Более ограниченные запросы лучше информируют оптимизатора и могут помочь выбрать правильную стратегию сканирования.

Анализ плана иногда может оказаться непосильной задачей, поэтому можно использовать такие инструменты, как dalibo и depesz, которые помогут визуализировать ваши объясняемые планы (обязательно ознакомьтесь с политикой хранения данных в этих инструментах и в идеале анонимизируйте свои запросы в целях безопасности перед загрузкой планов)!

Инсайты производительности

Включение Performance Insights на кластере Aurora PostgreSQL — еще один отличный способ получить подробную информацию о производительности и использовании ресурсов. С помощью Performance Insights у вас есть быстрый способ нарезать ваши запросы на фрагменты с разбивкой на top SQL, top wait и т.д., что может пригодиться для постоянного мониторинга производственных нагрузок.

Performance Insights показывает высокую загрузку процессора в разрезе лучших SQL.

Еще один важный параметр, на который стоит обратить внимание, — это ожидание и определение того, где ваша база данных проводит больше всего времени. Ниже метрики разбиты по Top SQL и отсортированы по наибольшему ожиданию.

Performance Insights показывает метрики, отсортированные по Top SQL и ожиданию.

Если вам нужен хороший обзор и понимание Performance Insights, я настоятельно рекомендую посмотреть доклад по Performance Insights на AWS re:Invent.

Конфигурация

shared_buffers

Один из распространенных подводных камней при установке большого объема shared_buffers заключается в том, что память используется для кэширования страниц и не может быть использована для других целей, таких как временная память для сортировки, хэширования и материализации (work_mem) или вакуумирования и построения индексов (maintenance_work_mem).

Если вы не можете уместить всю рабочую нагрузку в shared_buffers, то есть ряд причин для того, чтобы она была относительно небольшой. Если рабочий набор больше, чем shared_buffers, то большинство обращений к буферам не попадут в буферный кэш базы данных и вызовут ошибку страницы в ОС; очевидно, что нет смысла выделять большой объем памяти под кэш с низким коэффициентом попадания.

https://medium.com/media/1d635554846d95a294535ffb62d847dd/href

wal_buffers

Процессы бэкенда PostgreSQL сначала записывают свои записи журнала в эти буферы, а затем буферы сбрасываются на диск. Как только содержимое любого буфера размером 8 кБ оказывается на диске, буфер можно использовать повторно. Поскольку вставки и записи выполняются последовательно, буферы WAL фактически представляют собой кольцевой буфер, при этом вставки заполняют буфер, а смывы WAL опустошают его. Производительность страдает, когда буфер заполняется и больше не может быть вставлен WAL, пока не завершится текущая очистка. Эффект смягчается тем, что, когда synchronous_commit не выключен, каждая транзакция ожидает, пока ее запись WAL будет сброшена на диск; таким образом, при небольших транзакциях с низким уровнем параллелизма большой буфер не является критичным. В PostgreSQL 14 вы можете получить больше информации о ваших wal_buffers с помощью pg_stat_wal.

Ниже вы можете видеть высокий уровень CPU, но также и высокий уровень WAL:write, что может дать нам некоторые подсказки для дальнейшей настройки базы данных, например, выделение дополнительной памяти для wal_buffers.

Performance Insights показывает метрики, нарезанные по наибольшему количеству ожиданий.

стоимость случайной_страницы

По умолчанию установлено значение 4. Хранилища с низкой стоимостью случайного чтения по сравнению с последовательным, такие как твердотельные диски, лучше моделировать с более низким значением для random_page_cost, например, 1.0. Лучше всего настроить базы данных Aurora с 1.0 и измерить улучшения.

work_mem, max_parallel_workers_per_gather

Для сложного запроса параллельно может выполняться несколько операций сортировки или хэширования. Кроме того, несколько запущенных сессий могут выполнять такие операции параллельно. Поэтому общее количество используемой памяти может во много раз превышать значение work_mem и должно быть настроено соответствующим образом. Слишком низкое или слишком высокое значение может повлиять на производительность. Значение по умолчанию (4 МБ) для OLTP-нагрузок является хорошей отправной точкой. Оно может быть увеличено до более высокого значения для не OLTP нагрузок.

Аналогично, параллельный запрос с использованием 4 рабочих может использовать в 5 раз больше процессорного времени, памяти, пропускной способности ввода-вывода и так далее. Рекомендуемая конфигурация formax_parallel_workers_per_gather для высоко параллельных OLTP рабочих нагрузок, охватывающих несколько соединений, — установить значение 0 или выключить. Для низкого параллелизма может быть достаточно значений по умолчанию. Может потребоваться медленное увеличение и оценка производительности для не OLTP рабочих нагрузок.

Предотвращение разрастания

Удаление мертвых кортежей имеет двоякое значение. Мертвые кортежи не только снижают эффективность использования пространства, но и могут привести к проблемам с производительностью базы данных. Когда таблица имеет большое количество мертвых кортежей, ее размер увеличивается намного больше, чем ей на самом деле нужно — обычно это называется bloat. Раздутие приводит к каскадному эффекту для вашей базы данных, например, последовательное сканирование раздутой таблицы имеет больше страниц для прохождения, что требует дополнительных затрат на ввод-вывод и занимает больше времени, раздутый индекс приводит к большему количеству ненужных операций ввода-вывода, что замедляет поиск и сканирование индекса, и т.д.

Для баз данных с большим объемом операций записи скорость роста мертвых кортежей может быть высокой. Кроме того, по умолчанию значение параметра autovacuum_max_workers равно 3. Рекомендуем следить за раздутием базы данных, проверяя мертвые кортежи в таблицах с высоким параллелизмом.

-- monitor dead tuples
SELECT relname, n_dead_tup FROM pg_stat_user_tables;

-- monitor auto vacuum
SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;

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

Усиление записи, fillfactor и HOT Updates

«Коэффициент заполнения для таблицы — это процент от 10 до 100. По умолчанию используется значение 100 (полная упаковка). Если указан меньший коэффициент заполнения, операции INSERT упаковывают страницы таблицы только до указанного процента; оставшееся пространство на каждой странице резервируется для обновления строк на этой странице».

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

Обновления только из кучи кортежей (HOT) являются эффективным способом предотвращения усиления записи в PostgreSQL. Снижение fillfactor может оказать положительное влияние, увеличив процент HOT-обновлений. Более низкий fillfactor может стимулировать больше обновлений HOT, т.е. меньше операций записи. Поскольку мы пишем меньше, мы также генерируем меньше записей в WAL. Еще одним преимуществом HOT-обновлений является то, что они облегчают задачи обслуживания таблицы. После выполнения HOT-обновления старая и новая версии строки находятся на одной странице. Это делает очистку одной страницы более эффективной, а операция вакуума требует меньше работы.

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

-- review your fillFactor
SELECT 
 pc.relname AS ObjectName,
    pc.reloptions AS ObjectOptions
FROM pg_class AS pc
INNER JOIN pg_namespace AS pns 
 ON pns.oid = pc.relnamespace
WHERE pns.nspname = 'public';

HOT поставляется с некоторыми компромиссами по производительности, которые влияют на производительность чтения при сканировании индексов. Поэтому осторожно уменьшите коэффициент заполнения (например, до 85%) в таблицах, которые получают много обновлений, и измерьте разницу в производительности!

Надеемся, что что-то вроде инициативы zheap storage engine поможет нам преодолеть эти узкие места в будущем. До тех пор мы, возможно, не сможем предотвратить разрастание, но, безусловно, сможем минимизировать его последствия.

Расширенный мониторинг

Включение расширенного мониторинга может дать вам полезные сведения на уровне узла базы данных, а также списка процессов. Это особенно полезно, если вам нужно отследить конкретный процесс (PID), потребляющий много CPU, и сопоставить его с pg_stat_activity для получения более подробной информации о запросе. Также это дает вам отличную метрику для сравнения IOPS чтения/записи, памяти и т.д. с загрузкой ЦП.

Управление планом запросов (QPM)

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

• Изменение статистики оптимизатора (вручную или автоматически)
• Изменение параметров конфигурации планировщика запросов
• Изменения в схеме, например, добавление нового индекса
• Изменения в переменных привязки, используемых в запросе
• Обновление версии базы данных PostgreSQL на минорную или мажорную. (Операция Analyze не выполняется после обновления для обновления таблицы pg_statistic?)
• Параметры конфигурации планировщика, такие как default_statistics_target, from_collapse_limit, join_collapse_limit и т.д.

QPM — это отличная функция, которая позволяет нам управлять планами запросов, предотвращать регрессии плана и повышать стабильность плана. QPM собирает статистику по планам и позволяет нам получить необходимые средства контроля для утверждения планов с более низкой оценкой затрат и/или позволить Aurora адаптироваться для автоматического выполнения плана с минимальными затратами.

QPM доступен на Amazon Aurora PostgreSQL версии 10.5 (Aurora 2.1.0) и более поздних версиях и может быть включен на производстве (минимальные накладные расходы) или включен/выключен на ваших тестовых рабочих наборах с помощью таких инструментов, как sysbench. Я настоятельно рекомендую включить эту функцию в тестовых средах, а также практиковать эволюцию планов (пересмотр и утверждение планов) перед применением QPM в производстве. После применения QPM в производстве необходимо будет периодически проверять, не нашел ли оптимизатор лучшие планы с более низкой оценкой затрат, которые необходимо утвердить.

Обнаружение аномалий

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

AWS DevOps Guru — один из альтернативных вариантов, который основан на ML и используется для выявления аномалий, таких как повышенная задержка, количество ошибок и нехватка ресурсов, а затем отправляет оповещения с описанием и действенными рекомендациями по устранению неполадок. С точки зрения базы данных, вы можете, например, оповещать о событиях ожидания высокой нагрузки (на основе метрики db_load) и превышения мощности процессора. Кроме того, DevOps Guru может улавливать аномалии в журналах, что является полезной функцией. Например, теперь вы можете оповещать о любых аномальных ошибках в журналах PostgreSQL.

Amazon Aurora на PostgreSQL 14

Недавно Amazon Aurora объявила о поддержке PostgreSQL основной версии 14 (14.3)! PostgreSQL 14 включает улучшения производительности для параллельных запросов, сильно текущих рабочих нагрузок, таблиц с разделами, логической репликации и вакуумирования. Кроме того, в этом выпуске улучшены наблюдаемость, опыт разработчиков и безопасность.

Для рабочих нагрузок, использующих множество соединений, обновление PostgreSQL 14 позволило добиться улучшения в 2 раза по некоторым контрольным показателям. Тяжелые рабочие нагрузки, а также рабочие нагрузки с большим количеством небольших операций записи, также выигрывают от новой возможности конвейерной обработки запросов к базе данных, которая может повысить производительность при использовании соединений с высокой задержкой. Эта функция на стороне клиента может быть использована с любой современной базой данных PostgreSQL с клиентом версии 14 или клиентским драйвером, созданным на основе версии 14 libpq.

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

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

Дополнительную информацию см. в обновлениях Amazon Aurora PostgreSQL.

Правильное определение размеров и альтернативные схемы архитектуры

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

Заключение

Постарайтесь не усложнять ситуацию и начните со встроенных инструментов и расширений, доступных в облачном движке базы данных, чтобы быстро определить и устранить проблемы с использованием ресурсов. Продолжайте следить за базами данных с помощью медленных журналов, Cloudwatch, Performance Insights, Enhanced Monitoring, DevOps Guru или APM по вашему выбору. И последнее, но не менее важное — уменьшите раздутость, чтобы в целом иметь большее влияние на производительность. Хотя эта статья в основном сосредоточена на Aurora PostgreSQL, вы можете достичь аналогичных результатов на Aurora Mysql, а также на RDS. Наконец, я хочу оставить несколько полезных ссылок, чтобы вы могли почитать по этой теме. Удачи!

Ссылки

• Устранение высокой загрузки процессора в Amazon RDS или Aurora для PostgreSQL
• Пример настройки Autovacuum в Amazon RDS для PostgreSQL | Amazon Web Services
• Amazon DevOps Guru для RDS под капотом | Amazon Web Services
• Amazon Aurora: Соображения по проектированию + Об избежании распределенного консенсуса для ввода-вывода, фиксаций и изменений членства
• Amazon Aurora теперь поддерживает PostgreSQL 14
• Введение в Aurora PostgreSQL Query Plan Management | Amazon Web Services
• Борьба с усилением записи в PostgreSQL с помощью обновлений HOT
• zheap: Обновленное хранилище PostgreSQL — CYBERTEC

Спасибо моим коллегам Саше Мандичу и Франсислани Кампос за их отзывы об этом посте!