Postgresql загружает процессор на 100

Обновлено: 21.11.2024

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

Разрешение

Показатели Amazon CloudWatch

Вы можете использовать метрики CloudWatch для определения моделей загрузки ЦП в течение продолжительных периодов времени. Сравните графики WriteIOPs, ReadIOPs, ReadThroughput и WriteThroughput с загрузкой ЦП, чтобы узнать, когда рабочая нагрузка вызывала высокую загрузку ЦП.

После того как вы определили временные рамки, вы можете просмотреть данные расширенного мониторинга, связанные с вашим экземпляром БД. Вы можете настроить расширенный мониторинг для сбора данных с интервалом в 1, 5, 10, 15, 30 или 60 секунд. Это позволяет собирать данные на более детальном уровне, чем CloudWatch. Дополнительные сведения см. в разделе Различия между показателями CloudWatch и Enhanced Monitoring.

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

Расширенный мониторинг обеспечивает представление на уровне операционной системы (ОС), что может помочь определить причину высокой загрузки ЦП на детальном уровне. Например, вы можете просмотреть среднюю загрузку, распределение ЦП (системный % или приятный %) и список процессов ОС.

С помощью расширенного мониторинга вы можете проверять данные loadAverageMinute с интервалами в 1, 5 и 15 минут. Если среднее значение нагрузки превышает количество виртуальных ЦП, это означает, что экземпляр находится под большой нагрузкой. Кроме того, если средняя нагрузка меньше количества виртуальных ЦП для класса экземпляра БД, регулирование ЦП может не быть причиной задержки приложения. Проверяйте среднюю загрузку, чтобы избежать ложных срабатываний при диагностике причины использования ЦП.

Например, если у вас есть инстанс БД, использующий класс инстансов db.m5.2xlarge с 3000 Provisioned IOPS, который достигает предела ЦП, вы можете просмотреть следующие примеры метрик, чтобы определить основную причину высокой загрузки ЦП. . В следующем примере класс экземпляра имеет восемь связанных с ним виртуальных ЦП. Для той же средней нагрузки превышение 170 указывает на то, что машина находится под большой нагрузкой в ​​течение измеряемого периода времени:

Средняя нагрузка в минутах
Пятнадцать 170,25
Пять 391,31
Один 596,74
Использование ЦП
Пользователь (%) 0,71
Система (%) 4,9
Хорошо (%) 93,92
Всего (%) 99,97

Примечание. Amazon RDS придает вашей рабочей нагрузке более высокий приоритет по сравнению с другими задачами, выполняемыми в инстансе БД. Чтобы приоритизировать эти задачи, задачи рабочей нагрузки имеют более высокое значение Nice. В результате в расширенном мониторинге Nice% представляет собой объем ЦП, используемый вашей рабочей нагрузкой для базы данных.

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

Вы можете объединить результаты расширенного мониторинга с результатами pg_stat_activity, чтобы определить использование ресурсов запросами.

Статистика эффективности

Вы можете использовать Amazon RDS Performance Insights, чтобы определить запрос, ответственный за загрузку базы данных, после проверки вкладки SQL, соответствующей определенному периоду времени.

Собственный вид и каталоги PostgreSQL

На уровне ядра базы данных, если проблема возникает в режиме реального времени, вы можете использовать pg_stat_activity или pg_stat_statements. Это может помочь вам сгруппировать компьютеры, клиенты и IP-адреса, отправляющие наибольший трафик. Вы также можете использовать эти данные, чтобы увидеть, есть ли увеличение с течением времени, увеличение количества серверов приложений или наличие зависших сеансов сервера приложений или проблем с блокировкой. Дополнительные сведения см. в документации PostgreSQL для pg_stat_activity и pg_stat_statements. Чтобы включить pg_stat_statements, измените существующую группу настраиваемых параметров и установите следующие значения:

  • Добавить pg_stat_statements в shared_preload_libraries
  • track_activity_query_size = 4096
  • pg_stat_statements.track = ВСЕ
  • pg_stat_statements.max = 10000

Выберите «Применить немедленно» и перезагрузите экземпляр БД. Затем выполните команду, подобную следующей, для базы данных, которую вы хотите отслеживать:

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

После настройки pg_stat_statements вы можете отслеживать выходные данные одним из следующих способов:

  • Составьте список запросов по total_time и узнайте, какой запрос больше всего времени проводит в базе данных:
  • Список запросов с указанием общего количества вызовов, общего количества строк и возвращенных строк:
  • Создайте список запросов по каждому выполнению для выборки запросов с течением времени:

Параметры ведения журнала PostgreSQL

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

Снизить нагрузку на ЦП

После того, как вы определите запросы, вызывающие высокую загрузку ЦП, вы можете использовать следующие методы для дальнейшего снижения загрузки ЦП:

Мы все любим PostgreSQL, но иногда, обычно около 3 часов ночи, он может достигать 100% загрузки ЦП, что может раздражать нас и быть катастрофой для наших клиентов. Так как же найти основную причину и устранить ее?

1. Найдите основную причину

Причин 100% загрузки ЦП может быть много. Если вы используете CloudWatch, EverSQL или любое другое решение для мониторинга PostgreSQL, вы можете начать расследование с него.

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

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

    • Когда это началось?
    • Был ли у вас значительный рост трафика?
    • Вы выпустили новую функцию или основной выпуск?
    • Вы что-нибудь изменили? (параметры конфигурации базы данных? Вы включили постоянное подключение? Обновили? Перешли на новую инфраструктуру?)

    2. Устранить проблему с производительностью

    Причин внезапного увеличения загрузки ЦП может быть много. Вот несколько советов, как это исправить:

      Проверьте, какие пользователи PostgreSQL вошли в систему. Возможно, есть конкретный новый пользователь, который забивает систему. Это можно сделать с помощью следующего запроса:

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

    Вывод включает единицу измерения, например: 193784 КБ

    Обычно рекомендуется иметь не менее 25 % памяти в вашем экземпляре. Если у вас большая рабочая нагрузка, вы можете увеличить ее до 40% памяти экземпляра, поскольку PostgreSQL официально упоминает, что более 40%, скорее всего, не улучшит производительность. Обратите внимание, что если вы измените значение shared_buffers, вы также должны увеличить max_wal_size (журнал упреждающей записи), чтобы помочь процессам записи использовать больший кэш.

    3. Убедитесь, что это больше не повторится.

    Вы не хотите, чтобы вас снова застали врасплох в 2 часа ночи, когда ваш процессор загружен на 100%, поэтому вам следует установить какое-либо решение для мониторинга базы данных, чтобы получать оповещения как можно скорее.
    Существует несколько инструментов мониторинга, таких как NewRelic, AppDynamics и другие, но для PostgreSQL вы можете использовать датчик EverSQL, который автоматически собирает десятки сигналов и предоставляет вам аналитические данные PostgreSQL на основе искусственного интеллекта, такие как:

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

    4. Резюме — высокая загрузка ЦП PostgreSQL

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

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

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

    Существует множество способов устранения узких мест в производительности, но в целях обсуждения вот несколько способов рассмотрения этой проблемы:

    • Настройка параметров производительности
    • Подключения к сеансу
    • Раздувание
    • Автоочистка: базовая
    • Автоочистка: расширенный уровень
    • Точки доступа к данным
    • Конкурирующие процессы приложений
    • Задержка репликации
    • Серверная среда

    О мониторинге и статистике

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

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

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

    Метод 1: дистрибутив Percona для PostgreSQL


    Метод 2: компиляция и установка (репозиторий postgres сообщества)

    Создание расширения

    изменить системный набор shared_preload_libraries = 'pg_stat_monitor'; br / > создать расширение pg_stat_monitor ;

    Перезагрузите сервер

    О «Категориях» и «Потенциальном воздействии»

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

    • Сложность
      • Низкий
      • Средний
      • Высокий
      • Низкий-средний-высокий
      • Низкий
      • Средний
      • Высокий
      • Низкий-средний-высокий

      Настройка параметров производительности

      Сложность: низкая
      Потенциальное влияние: высокая

      Не так давно было время, когда современная версия postgres еще могла работать на i386. Хотя настройки по умолчанию с тех пор были обновлены, параметры по-прежнему настроены на использование наименьшего количества ресурсов при установке.

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

      • эффективный_размер_кэша ~ от 50 до 75%
      • shared_buffers ~ 1/4–1/3 всей оперативной памяти системы
      • рабочая_память ~ 10 МБ

      Давайте поговорим об этих переменных.

      Рекомендуемое значение эффективного кэша, хотя и является типичным, может быть установлено точно, ссылаясь на «верхнее», т. е. свободная + кэшированная оперативная память.

      Настройка общих буферов — интересная головоломка. Есть два взгляда на этот параметр: если у вас небольшая база данных, вы можете установить общие буферы достаточно большими, чтобы иметь резидентную систему базы данных в ОЗУ. В противном случае загрузка тех таблиц и индексов, которые чаще всего используются службой, настраивается так, чтобы они оставались в оперативной памяти (старое правило 80/20). Установка 1/3 системной ОЗУ раньше была рекомендуемой настройкой, но со временем она была снижена до 1/4 по мере того, как машины приобретали больше ОЗУ, потому что существует такая вещь, как слишком много выделенного для общих буферов. Слишком мало оперативной памяти означает большую нагрузку на ЦП и более высокий ввод-вывод. Вы узнаете, когда параметр общего буфера слишком высок, когда загрузка ЦП и производительность ввода-вывода достигают плато.

      Еще один фактор, который следует учитывать, – кэш операционной системы. при наличии достаточного объема оперативной памяти Linux будет кэшировать таблицы и индексы в оперативной памяти и может, в зависимости от различных настроек, заставить PostgreSQL поверить, что он читает данные с диска, а не из оперативной памяти. Производительность повышается за счет увеличения избыточности за счет частого копирования одних и тех же страниц, найденных в общем буфере, в кеш ОС, что является еще одной причиной избегать слишком большого кеша общего буфера. Для перфекционистов среди нас взгляните на расширение pg_buffercache, которое считывает использование кэша в режиме реального времени (СОВЕТ: посмотрите на эту таблицу).

      Установка слишком низкого значения work_mem гарантирует низкую производительность, поскольку сортировки обрабатываются как временные файлы на диске.С другой стороны, хотя установка высокого значения не влияет на производительность, существует риск нехватки оперативной памяти сервера, если одновременно активно слишком много подключений. Часть оперативной памяти, равная рабочей памяти, используется для каждой операции сортировки. Вам нужно будет немного посчитать, сколько экземпляров ОЗУ используется каждым запросом и сеансом. СОВЕТ: используйте EXPLAIN ANALYZE, чтобы увидеть, где выполняются операции сортировки, и, изменяя значение в данном сеансе, можно увидеть, когда оно переносится на диск.

      Можно многому научиться, сравнив систему с небольшими переменными настройками.

      Подключения к сеансу: управление

      Сложность: низкая
      Потенциальное влияние: низкая-средняя-высокая

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

      Был случай с экземпляром AWS RDS. Загрузка процессора составила 100% даже после замены t2.medium на m3.xlarge. Проблема заключалась в том, что некоторые запросы зависали и выполнялись часами. Это заставляло ЦП быть загруженным. Тот же запрос при запуске через оболочку psql дает результат через 4-5 секунд. 4-5 секунд тоже много. Хотя попытка того же запроса из консоли выполняется успешно, но иногда он зависал и продолжал работать часами.

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

      Сначала проверьте следующие системные ресурсы:

      Дисковое пространство: у вас должно быть 10% свободного места на диске для раздела данных PostgreSQL. Дисковое пространство может колебаться во время очистки PostgreSQL во время высоких нагрузок записи.

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

      Использование операций ввода-вывода. Если PostgreSQL работает медленно, сначала измерьте процентную долю операций ввода-вывода/ожидания ЦП, которая указывает время, в течение которого компьютер ожидает чтения/записи диска. Вы можете проверить это с помощью команды «top» в Linux.

      Теперь проверьте показатели PostgreSQL:

      1. Общее количество подключений

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

      2. Количество подключений по штатам

      Есть четыре возможных состояния соединения:
      (a) активное — соединение, в настоящее время выполняющее запросы транзакции.
      (b) бездействие – соединение не выполняет транзакцию.
      (c) бездействие в транзакции – соединение в длительно выполняющейся транзакции, т.е. не выполняющий запрос.
      (d) бездействие в транзакции (прервано) ) — Соединение в ситуации, когда транзакция не откатилась из-за ошибки.

      3. Соединения, ожидающие блокировки

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

      4. Максимальный срок транзакции

      Транзакции должны быть короткими, чтобы их можно было выполнить в течение минуты. Длительные транзакции не позволяют PostgreSQL очищать старые данные, это может привести к останову базы данных из-за переноса идентификатора транзакции (xid). Если выход более одного часа, это повод для беспокойства. Это связано с тем, что запрос выполняется в течение длительного времени, что приводит к занятости ресурсов. Измените параметр максимального срока (в коде приложения) соединения на минимально возможное значение, например 2–3 секунды, в соответствии со средним временем ответа на запрос к базе данных.

      5. Интервал контрольной точки

      Частые проверки снижают производительность. PostgreSQL отобразит эти контрольные точки в своем журнале.
      Кроме того, вы можете проверить их частоту в таблице pg_stat_bgwriter.

      6. Время выполнения запроса

      Вы должны измерять его на уровне приложения или путем периодической настройки и анализа запросов журнала log_min_duration_statement=0 или путем мониторинга модуля pg_stat_statements.

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

      Читайте также: