Типовой шаблон анализа инцидентов производительности СУБД PostgreSQL с использованием PG_HAZEL .

Обнаружение - Анализ - Решение

✖️В архив

Постановка задачи

Подготовить примерный шаблон действий выполняемых при анализе инцидентов производительности с использованием оперативно-тактического комплекса "pg_hazel".

Общий алгоритм действий

1. Обнаружение инцидента производительности СУБД
2. Отчет по инцидентам производительности
3. Анализ SQL выражений , имеющих ожидания , оказывающие наибольшее влияние на ожидания СУБД.
4. История выполнения и ожиданий для SQL , имеющего наибольшую долю ожиданий заданного типа
5. Использование ChatPPG для начала процесса Problem Management.

Подробное описание шагов на примере реального инцидента производительности продуктивной СУБД

1.Обнаружение инцидента производительности СУБД

Метрика мониторинга "Индикатор снижения скорости СУБД". Подробнее об индикаторе :

История показаний за прошедший день

Дашборд Zabbix

Результат

Инциденты снижения скорости СУБД с приоритетом 4 по СУБД-1 и СУБД-3.

В качестве примера выбрана СУБД-3.

Ось X - точка времени. Ось Y - значение метрики операционной скорости,

2. Отчет по инцидентам производительности

Статистические данные по инциденту производительности. Типа ожиданий с коэффициентом корреляции = 0 : не показаны.

Столбцы таблицы:

• ID : идентификатор инцидента
• START TIME / FINISH TIME : время начала и окончания инцидента
• DURATION : длительность инцидента
• PRIORITY : приоритет
• SPEED REGRESSION LINE ANGLE : угол наклона линии наименьших квадратов по значениям операционной скорости за отрезок [START TIME;START TIME - 1 час]
• WAITINGS REGRESSION LINE ANGLE : угол наклона линии наименьших квадратов по ожиданиям за отрезок [START TIME;START TIME - 1 час]
• CORRELATION : коэффициент корреляция между значениями операционной скорости и ожиданий на отрезке [START TIME;START TIME - 1 час]
• IPC CORRELATION : коэффициент корреляции между всеми значениями ожиданий и ожиданиями типа IPC.

Промежуточный результат:

1. Коэффициент корреляции между операционной скоростью и ожиданиями низкий. Несмотря на большой угол наклона линии наименьших квадратов ожиданий. Следовательно - влияние ожиданий на снижение скорости СУБД - незначительно.
2. Коэффициент корреляции между всеми ожиданиями и ожиданиями типа IPC - наивысший. Следовательно - наибольшее влияние на ожидания СУБД оказывают ожидания типа IPC.
3. Дальнейший анализ SQL выражений необходимо проводить для SQL выражений имеющих ожидания IPC.

3. Анализ SQL выражений, имеющих ожидания , оказывающие наибольшее влияние на ожидания СУБД.

Таблица статистических данных по SQL выражениям имеющим ожидания IPC

Столбцы таблицы:

• QUERYID : queryid SQL выражения , из представления pgpro_stats.
• PGPRO_PWR_QUERYID : шестнадцатеричное значение queryid , для использования в отчетах pgpro_pwr.
• CALLS : количество выполнений SQL выражения
• WAITINGS : количество ожиданий
• WAITINGS TO CALLS : количество ожиданий на одно выполнение
• WAITINGS PPM : доля(в промилле) ожиданий типа IPC по данному SQL среди всех ожиданий по всем SQL за анализируемый период.

Результаты:

Ожидания типа IPC в течении анализируемого периода имеет только SQL выражение queryid=1622895052665899717 .

4. История выполнения и ожиданий для SQL , имеющего наибольшую долю ожиданий заданного типа.

История выполнения и ожиданий для queryid=1622895052665899717

Столбцы таблицы:

• timestamp : точка времени сбора статистических данных уровня SQL.
• datname : База данных, в которой выполнялся SQL запрос.
• rolname : Роль, под которой выполнялся SQL запрос.
• CALLS : Количество выполнений запроса на точку времени (медианное значение).
• WAITINGS : Количество ожиданий wait_event_type = IPC , на точку времени (медианное значение).
• WAITINGS TO CALLS : количество ожиданий на одно выполнение.
• WAIT_EVENTS : события ожидания wait_event , возникающие при выполнении SQL запроса на точку времени.
• SQL : текст SQL запроса (не приведен).

События ожидания возникающие при выполнении SQL запроса:

• BgWorkerShutdown : Ожидание завершения фонового рабочего процесса.
• MessageQueueReceive : Ожидание получения байтов из общей очереди сообщений.
• ParallelFinish : Ожидание завершения вычислений параллельными рабочими процессами.

5. Использование ChatPPG для начала процесса Problem Management

@ChatPPGSupport https://t.me/chatppgbot

Вопрос

Как уменьшить количество ожиданий IPC?

Ответ

Чтобы уменьшить количество ожиданий типа IPC в PostgreSQL, можно предпринять следующие шаги:

1. Уменьшение числа подключений:

Если количество ожиданий IPC вызвано большим числом подключений, можно уменьшить значение параметра max_connections. Это снизит количество семафоров, которые PostgreSQL использует для управления подключениями. Например:

max_connections = 100

2. Настройка параметров ядра:

Убедитесь, что параметры ядра, такие как SEMMNS и SEMMNI, настроены правильно. Эти параметры определяют максимальное количество семафоров и идентификаторов семафоров, которые могут существовать в системе. Например, для Linux можно изменить эти параметры с помощью команды sysctl:

sysctl -w kern.ipc.semmns=50000

sysctl -w kern.ipc.semmni=1000

Чтобы изменения сохранялись после перезагрузки, добавьте их в файл /etc/sysctl.conf.

3. Использование альтернативных методов IPC:

Если возможно, рассмотрите использование альтернативных методов IPC, таких как POSIX семафоры, вместо семафоров System V. Это может быть полезно, если вы используете систему, где семафоры System V являются ограничением.

4. Оптимизация использования памяти:

Убедитесь, что параметры разделяемой памяти настроены правильно. Например, увеличьте значение SHMMAX и SHMALL, если это необходимо для вашего кластера баз данных. Для Linux это можно сделать с помощью команды sysctl:

sysctl -w kernel.shmmax=17179869184

sysctl -w kernel.shmall=4194304

5. Проверка на наличие других процессов, использующих IPC:

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

ipcs -s

ipcs -m

6. Использование меньшего числа фоновых процессов:

Уменьшение числа фоновых процессов, таких как автовакуум и передатчик WAL, также может помочь уменьшить количество семафоров. Например, уменьшите значения параметров autovacuum_max_workers, max_wal_senders и max_worker_processes.

Пример настройки параметров ядра для Linux:

sysctl -w kern.ipc.semmns=50000

sysctl -w kern.ipc.semmni=1000

sysctl -w kernel.shmmax=17179869184

sysctl -w kernel.shmall=4194304

Эти шаги помогут уменьшить количество ожиданий типа IPC и улучшить производительность PostgreSQL. Для более точной настройки рекомендуется провести анализ текущего использования ресурсов и оптимизировать параметры в соответствии с конкретными требованиями вашей системы.