Postgresql + Производительность

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

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

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

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

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

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

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

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

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

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

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

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

Результат

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

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

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

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

• 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 выражений, имеющих ожидания , оказывающие наибольшее влияние на ожидания СУБД.

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

• 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 , имеющего наибольшую долю ожиданий заданного типа.

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

• 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. Для более точной настройки рекомендуется провести анализ текущего использования ресурсов и оптимизировать параметры в соответствии с конкретными требованиями вашей системы.

Особенно о проблеме среднего, что будет сказано

https://dzen.ru/a/Z1--cZ4sHEmzLsy5

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

https://dzen.ru/a/Z7gmHgBGAHo5m2SS

Ну, что ж , без ложной скромности - приоритет в идее за мной. Мне не жалко, пользуйтесь, можете даже спрашивать, там по пути много встретится поворотов и граблей. А я довольно далеко уже ушел от анализа бенчмарков, думаю до лета уже практические работы по PostgreSQL performance engineering начнутся. А сейчас - сбор результатов экспериментов и причёсывание алгоритмов методологии.

Начало

"Индикатор снижения скорости" как сигнал для начала корреляционного анализа ожиданий СУБД.

Продолжение

Практическое применение индикатора для проведения анализа инцидентов снижения скорости СУБД .

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

Провести анализ инцидентов производительности СУБД-1 по данным мониторинга индикатора снижения скорости.

Порядок проведения анализа инцидентов производительности СУБД

Определение инцидента

Инцидентом снижения производительности СУБД является событие снижения операционной скорости СУБД и одновременный рост ожиданий СУБД в течении заданного периода времени.

Приоритет инцидента определяется абсолютным значением коэффициента корреляции между значениями операционной скорости и ожиданиями :

• Приоритет 4 : |коэффициент корреляции| < 0.5

• Приоритет 3 : |коэффициент корреляции| >= 0.5

Окончание инцидента - отсутствие корреляции между операционной скоростью и ожиданиями СУБД.

1.Отчет по инцидентам

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

• ID : идентификатор инцидента

• START TIME : время начала инцидента

• FINISH TIME : время окончания инцидента

• SPEED REGRESSION LINE SLOPE : угол наклона линии наименьших квадратов для значений операционной скорости СУБД

• WAITINGS REGRESSION LINE SLOPE : угол наклона линии наименьших квадратов для значений ожиданий СУБД

• CORRELATION : коэффициент корреляции между операционной скоростью и ожиданиями

• IO CORRELATION : коэффициент корреляции между ожиданиями СУБД и ожиданиями типа IO

• IPC CORRELATION : коэффициент корреляции между ожиданиями СУБД и ожиданиями типа IPC

• LWLOCK CORRELATION : коэффициент корреляции между ожиданиями СУБД и ожиданиями типа LWLOCK

Результаты отчета:

• Наибольшая корреляция между ожиданиями и типом ожидания IPC

• Наименьшая корреляция между ожиданиями и типом IO

Результаты отчета:

• Наибольшая корреляция между ожиданиями и типом ожидания IPC

• Наименьшая корреляция между ожиданиями и типом IO

Итог:

Наибольшее влияние на снижение операционной скорости в заданный период играют ожидания типа IPC

Серверный процесс ожидает взаимодействия с другим процессом.

2.Отчеты по SQL запроса и типам ожидания на примере анализа по типу IPC

Ожидания типа IPC

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

• QUERYID : значение queryid из представления pgpro_stats

• PGPRO_PWR QUERYID : шестнадцатеричное значение queryid для использования в отчетах pgpro_pwr.

• CALLS : количество выполнений за период инцидентов

• WAITINGS : количество ожиданий типа IPC за период инцидентов

• WAITINGS TO CALLS : отношение количеств ожиданий типа IPC по SQL выражению к количеству выполнений. Ожиданий на одно выполнение.

• WAITINGS PPM : относительное значение( в промилле ) ожиданий по типу IPC для данного SQL выражения ко всем ожиданиям типа IPC.

Результат

В результате анализа данных отчета , можно установить SQL запрос/запросы оказывающие наибольшее влияние на количество ожиданий типа IPC. И оптимизация которых окажет наибольший эффект на скорость СУБД.

Анализ по типам ожидания IO , LWLock проводится аналогичным образом.

3. SQL выражения, имеющие набольшие значения ожиданий

В результате анализа данных отчета , можно установить SQL запрос/запросы оказывающие наибольшее влияние на ожидания СУБД . И оптимизация которых окажет наибольший эффект на скорость СУБД.

Итоги и планы развития

Мониторинг инцидентов скорости СУБД и сбор статистической информации по инцидентам позволит собрать начальные данные для проведения анализа производительности СУБД за более длительный период времени - неделя, месяц, квартал. И послужит базой для начала работ по процессу управления проблемами производительности СУБД.

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

Разработать и проверить практическое применение индикатора снижения скорости СУБД для создания инцидента деградации производительности СУБД.

Решение задачи

Как было указано ранее

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение .

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

Например:

Однако , важным следствием из определения понятия корреляции является то, что отрицательное значение может быть также и в случае - если операционная скорость растет, а ожидания снижаются. Является ли подобная ситуация инцидентом ? Конечно - нет.

Возможны следующие комбинации роста/cнижения значение операционной скорости и ожиданий:

1. Скорость растет, ожидания растут => положительная корреляция.

2. Скорость снижается, ожидания снижаются => положительная корреляция.

3. Скорость растет, ожидания снижаются => отрицательная корреляция.

4. Скорость снижается, ожидания растут => отрицательная корреляция.

Только вариант 4 является инцидентом снижения производительности.

Итак, для корректной работы индикатора и создания оповещения мониторинга о возможной проблеме скорости СУБД необходимо определять возникновение ситуации - отрицательная корреляция & операционная скорость снижается.

Признак снижения операционной скорости

Для определения снижения операционной скорости используется встроенная функция PostgreSQL regr_slope(Y, X)

наклон линии, полученной методом наименьших квадратов по данным (X

, Y)

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

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

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

Если значение наклона линии наименьших квадратов < 0 ,

И

значение коэффициента корреляции между скоростью и ожиданиями < 0

ТО

Создать оповещение мониторинга "Инцидент деградации производительности".

В качестве уровня важности оповещения , можно использовать абсолютное значение коэффициента корреляции :

• < 0.5 : низкий уровень

• >= 0.5 : высокий уровень.

Практическая реализация

Итог

Использование индикатора снижения скорости СУБД позволяет автоматически создавать инцидент о деградации производительности СУБД и избавляет DBA от необходимости непрерывно следить за показателями метрик производительности и состояния СУБД.

Текущее состояние работ по использованию комплекса pg_hazel для анализа производительности СУБД PostgreSQL.

Оперативно-тактический комплекс pg_hazel

Метрики производительности СУБД доступные для мониторинга

1. Операционная скорость СУБД.

2. Ожидания СУБД.

3. Коэффициент корреляции между операционной скоростью и ожиданиями СУБД.

4. Индикатор уровня снижения скорости СУБД - заметное снижение /сильное снижение.

Отчеты корреляционного анализа

• Скорость , ожидания и типы ожидания кластера .

• Статистика выполнения и ожиданий SQL выражений по заданному типу ожиданий wait_event_type.

• Статистика выполнения (calls) , ожиданий(wait_event_type), событий ожиданий (wait_event) для заданного SQL выражения(queryid) и типа ожидания (wait_event_type) .

Планы развития

Тестирование методики корреляционного анализа и обнаружения проблемных SQL выражений при продуктивной нагрузке на СУБД.