Корреляция + Производительность

Шаблон решения задачи

https://dzen.ru/a/Z-4mhu9oFCnB-jp9

Отчетность для анализа

https://dzen.ru/a/Z-6YLjZRni5hLvtD

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

Ожидания и корреляция по инцидентам снижения скорости СУБД

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

• ID : идентификатор инцидента снижения скорости СУБД

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

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

• № : порядковый номер

• IO : количество ожидания типа IO на время начала инцидента

• IO CORRELATION : коэффициент корреляции между операционной скоростью и ожиданиями IO за отрезок [ START TIME - 1 ЧАС ; START TIME ]

• LWLock : количество ожидания типа LWLock на время начала инцидента

• LWLock CORRELATION : коэффициент корреляции между операционной скоростью и ожиданиями LWLock за отрезок [ START TIME - 1 ЧАС ; START TIME ]

Количество ожидания типа IO , LWLock по инцидентам

Особенности инцидентов 34 , 36 :

1. Коэффициент корреляции между ожиданиями СУБД в целом и ожиданиями типа LWLock больше , чем между ожиданиями СУБД в целом и ожиданиями типа IO.

2. Количество ожидания типа LWLock меньше чем количество ожидания типа IO.

Графики операционной скорости и ожиданий по инцидентам снижения скорости

Инцидент 34

Инцидент 36

Для справки: ожидания типа IO , LWLock по данным отчета "Top wait events" pgpro_pwr

Инцидент 34

Инцидент 36

2. Установить причины снижения скорости СУБД

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

Инцидент 34

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

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

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

• QUERYID : queryid SQL выражения , из представления pgpro_stats.

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

• CALLS : количество выполнений SQL выражения

• WAITINGS : количество ожиданий

• WAITINGS TO CALLS : количество ожиданий на одно выполнение

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

Результат:

Запрос queryid=2092406791392746781 имеет набольшую долю ожидания типа IO и LWLock среди всех запросов имеющих корреляцию ожидания с типом IO, LWLock.

Инцидент 36

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

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

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

• QUERYID : queryid SQL выражения , из представления pgpro_stats.

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

• CALLS : количество выполнений SQL выражения

• WAITINGS : количество ожиданий

• WAITINGS TO CALLS : количество ожиданий на одно выполнение

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

Результат:

Запрос queryid=2092406791392746781 имеет набольшую долю ожидания типа IO и LWLock среди всех запросов имеющих корреляцию ожидания с типом IO, LWLock.

Главная причина снижения скорости СУБД

Запрос queryid=2092406791392746781 имеет набольшую долю ожидания типа IO и LWLock среди всех запросов имеющих корреляцию ожидания с типом IO, LWLock.

Текст запроса

Доступен в pgpro_pwr

План выполнения запроса

Доступен в pgpro_pwr

События ожидания при выполнении запроса 2092406791392746781

Инцидент 34

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

• timestamp : точка времени сбора статистических данных уровня SQL.

• datname : База данных, в которой выполнялся SQL запрос.

• rolname : Роль, под которой выполнялся SQL запрос.

• CALLS : Количество выполнений запроса .

• WAITINGS : Количество ожиданий типа IO , LWLock .

• WAITINGS TO CALLS : количество ожиданий на одно выполнение.

• WAIT_EVENTS : события ожидания wait_event , возникающие при выполнении SQL запроса .

• SQL : текст SQL запроса (не приведен).

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

• IO / DSMFillZeroWrite : Ожидание заполнения нулями файла, применяемого для поддержки динамической общей памяти.

• LWLock / ParallelHashJoin : Ожидание синхронизации рабочих процессов в процессе выполнения узла плана Parallel Hash Join.

Инцидент 36

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

• timestamp : точка времени сбора статистических данных уровня SQL.

• datname : База данных, в которой выполнялся SQL запрос.

• rolname : Роль, под которой выполнялся SQL запрос.

• CALLS : Количество выполнений запроса .

• WAITINGS : Количество ожиданий типа IO , LWLock .

• WAITINGS TO CALLS : количество ожиданий на одно выполнение.

• WAIT_EVENTS : события ожидания wait_event , возникающие при выполнении SQL запроса .

• SQL : текст SQL запроса (не приведен).

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

• IO / DSMFillZeroWrite : Ожидание заполнения нулями файла, применяемого для поддержки динамической общей памяти.

• LWLock / ParallelHashJoin : Ожидание синхронизации рабочих процессов в процессе выполнения узла плана Parallel Hash Join.

• LWLock / BufferMapping : Ожидание при связывании блока данных с буфером в пуле буферов.

• LWLock / ProcArray : Ожидание при обращении к общим структурам данных в рамках процесса (например, при получении снимка или чтении идентификатора транзакции в сеансе).

3. Cписок мероприятий для устранения причин снижения скорости СУБД .

Мероприятия для снижения ожиданий DSMFillZeroWrite

События ожидания DSMFillZeroWrite в PostgreSQL связаны с операциями записи в разделяемую память (shared memory), где необходимо заполнить область нулями перед использованием.

Это может происходить при работе с большими объектами или при высокой нагрузке на систему.

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

1. Оптимизация запросов и транзакций:

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

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

2. Настройка параметров конфигурации:

- Увеличьте размер разделяемой памяти (shared_buffers), чтобы уменьшить частоту операций записи в разделяемую память.

- Настройте параметры, связанные с кэшированием и буферизацией, чтобы уменьшить количество операций записи.

3. Оптимизация использования разделяемой памяти:

- Убедитесь, что ваши приложения и расширения эффективно используют разделяемую память.

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

Мероприятия для снижения ожиданий ParallelHashJoin

Ожидания ParallelHashJoin могут возникать из-за того, что PostgreSQL использует параллельные запросы для выполнения операций, таких как Hash Join. Это может привести к увеличению количества ожиданий, особенно если у вас много одновременных запросов или ограниченные ресурсы.

1. Отключить параллельные запросы:

- Вы можете отключить параллельные запросы, установив параметр max_parallel_workers_per_gather в 0. Это отключит использование параллельных рабочих процессов для операций, таких как Hash Join.

2. Оптимизировать индексы:

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

3. Анализ и вакуумизация таблиц:

- Периодически выполняйте команды ANALYZE и VACUUM для обновления статистики и очистки мертвых строк. Это поможет оптимизатору запросов выбрать более эффективные планы выполнения.

4. Настройка параметров планировщика:

- Настройте параметры, такие как random_page_cost и cpu_tuple_cost, чтобы повлиять на выбор плана выполнения запроса. Например, уменьшение random_page_cost может сделать индексные сканирования более привлекательными.

5. Использование правильных операторов JOIN:

- Попробуйте использовать другие типы соединений, такие как Nested Loop или Merge Join, если они подходят для вашего запроса. Вы можете временно отключить Hash Join, установив параметр enable_hashjoin в off.

6. Оптимизация запросов:

- Проверьте, можно ли оптимизировать сами запросы, например, добавив дополнительные условия в WHERE-clause или используя более эффективные подзапросы.

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

-- Отключить параллельные запросы

SET max_parallel_workers_per_gather = 0;

-- Отключить Hash Join

SET enable_hashjoin = off;

-- Установить параметры планировщика

SET random_page_cost = 1.1;

SET cpu_tuple_cost = 0.01;

Мероприятия для снижения ожиданий BufferMapping

Ожидания на BufferMapping в PostgreSQL могут возникать из-за интенсивных операций чтения, когда база данных часто обращается к данным на диске вместо кэша. Это может происходить, когда рабочий набор данных превышает доступную память, что приводит к частым операциям ввода-вывода (I/O).

1. Увеличение shared_buffers:

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

2. Оптимизация запросов:

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

3. Увеличение effective_cache_size:

- Параметр effective_cache_size помогает PostgreSQL лучше оценивать доступную память для кэширования данных. Увеличение этого параметра может улучшить планирование запросов.

4. Увеличение work_mem и maintenance_work_mem:

- Увеличение параметров work_mem и maintenance_work_mem может помочь уменьшить количество операций ввода-вывода, особенно при выполнении операций сортировки и хранения данных.

5. Анализ и оптимизация индексов:

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

6. Обновление аппаратного обеспечения:

- Если возможно, обновите аппаратное обеспечение, особенно увеличьте объем оперативной памяти и используйте более быстрые диски (например, SSD).

7. Распределение нагрузки:

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

Мероприятия для снижения ожиданий ProcArrayLock

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

Для уменьшения задержек ProcArray можно рассмотреть следующие шаги:

1. Оптимизация рабочих процессов:

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

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

2. Настройка параметров конфигурации:

- Уменьшите значение параметра max_standby_streaming_delay, чтобы уменьшить задержку репликации.

- Настройте параметры, связанные с параллелизмом, такие как max_parallel_workers_per_gather и max_worker_processes, чтобы управлять количеством рабочих процессов.

3. Оптимизация хранения данных:

- Убедитесь, что у вас используется оптимальное хранилище данных, например, AWS EBS GP3, для уменьшения задержек ввода-вывода.

4. Итог

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

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

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

Database-1

База данных для сбора статистической информации производительности .

Database-2

Тестовая база данных для проведения нагрузочного тестирования .

Нагрузка создается пользовательским сценарием pgbench.

Рост количества подключений pgbench - экспоненциально от 6 до 111.

Сценарии нагрузочного тестирования и результаты экспериментов

Сценарий 1 - "Select Only"

Только читающая нагрузка на тестовую БД .

Сценарий 2 - "Insert Only"

Только пишущая нагрузка на тестовую БД .

Сценарий 3 - "OLTP"

Имитация теста TPC-B . Нагрузка "SELECT + UPDATE" в тестовой БД .

Характерные признаки сценариев нагрузочного тестирования

Сценарий 1 - "Select Only"

Сценарий характеризуется

1. Cильной корреляцией с событиями ожидания:

• LWLock/LockManager: Ожидание при чтении или изменении информации о «тяжёлых» блокировках.

• LWLock/ProcArray: Ожидание при обращении к общим структурам данных в рамках процесса (например, при получении снимка или чтении идентификатора транзакции в сеансе).

2. Очень низким относительной долей ожиданий: менее 1%

Сценарий 2 - "Insert Only"

Сценарий характеризуется

1. Очень сильной корреляцией с событиями ожидания:

• MultiXactOffsetSLRU: Ожидание при обращении к SLRU-кешу данных о смещениях мультитранзакций.

2. Не высокой относительной долей ожиданий: 17-35%

Сценарий 3 - "OLTP"

Сценарий характеризуется

1. Очень сильной корреляцией с событиями ожидания:

• Lock / transactionid: Ожидание завершения транзакции.

• Lock / tuple: Ожидание при запросе блокировки для кортежа.

2. Высокой относительной долей ожиданий: 62-95%.

Продолжение работ по теме

Анализ результатов нагрузочного тестирования СУБД PostgreSQL с использованием разных сценариев оперативно-тактического комплекса "PG_HAZEL".

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

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

Основное отличие от предыдущей методики анализа производительности.

Корреляционный анализ проводится не по СУБД в целом , а по отдельным базам данных - Database-1 , Database-2.

Статистические показатели производительности Баз Данных.

Анализ операционной скорости

Деградация производительности Database-2 существенно сильнее .

Ожидания

WAITING RATIO

Относительная доля(%), времени ожиданий от времени работы базы данных.

Анализ относительной доли ожиданий

Доля ожиданий , при работе Database-2 выше на порядки.

WAIT_EVENT_TYPE (Типы ожиданий)

Database-1

Database-2

Анализ типов ожиданий (WAIT_EVENT_TYPE)

Относительная доля ожиданий для Database-1 существенно ниже , чем по Database-2.

Типы ожиданий IO , Lock - отсутствуют при работе Database-1.

Общий корреляционный анализ ожиданий

Коэффициенты корреляции

• 
  SPEED CORR: коэффициент корреляции между количеством активных сессий к БД и операционной скоростью.

• BUFFERPIN CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа Bufferpin.

• EXTENSION CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа Extension.

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

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

• LOCK CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа Lock.

• LWLOCK CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа LWLock.

Итоги

1. Корреляция между активными сессиями и операционной скоростью для Database-1 очень слабая => Увеличение нагрузки на БД практически не ведет к снижению производительности БД.

2. Корреляция между активными сессиями и операционной скоростью для Database-2 очень сильная =>Увеличение нагрузки на БД ведет к заметному снижению производительности БД.

3. Для Database-1 отсутствует корреляция между операционной скоростью и ожиданиями => Снижение производительности БД не вызвано ожиданиями БД.

4. Для Database-2 наиболее сильная отрицательная корреляция между операционной скоростью и ожиданиями типа Lock =>Тяжелые блокировки оказывают наибольшее влияние на снижение производительности СУБД.

Корреляционный анализ ожиданий для Database-2

Для проведения корреляционного анализа используется

Основная гипотеза корреляционного анализа ожиданий СУБД

Корреляция между типом ожидания (wait_event_type) и событием ожидания(wait_event)

Наиболее коррелированные события ожидания(сильная корреляция):

1. Lock/extend: Ожидание при расширении отношения.

2. LWLock/BufferContent: Ожидание при обращении к странице данных в памяти.

Корреляция между событием ожидания(wait_event) и SQL запросами

Результат корреляционного анализа для Database-2

Результат корреляционного анализа для Database-2

Пользовательский запрос и события ожидания оказывающий наибольшее влияние на снижение производительности БД.

select custom_test( $1 )

События ожидания, оказывающие наибольшее влияние на снижение производительности БД

1. MultiXactOffsetSLRU: Ожидание при обращении к SLRU-кешу данных о смещениях мультитранзакций.

2. MultiXactGen: Ожидание при чтении или изменении общего состояния мультитранзакций.

3. extend: Ожидание при расширении отношения.

4. BufferContent: Ожидание при обращении к странице данных в памяти.

5. WALInsert: Ожидание при добавлении записей WAL в буфер в памяти.

6. ProcArray: Ожидание при обращении к общим структурам данных в рамках процесса (например, при получении снимка или чтении идентификатора транзакции в сеансе).

7. CheckpointerComm: Ожидание при управлении запросами fsync.

8. BufferMapping: Ожидание при связывании блока данных с буфером в пуле буферов.

9. DataFileExtend: Ожидание расширения файла данных отношения.

10. LockManager: Ожидание при чтении или изменении информации о «тяжёлых» блокировках.

Итог и практическое применение результатов корреляционного анализа

Для оптимизации и повышению производительности запроса "select custom_test( $1 )" необходимо выявить причины и оптимизировать работу с мультитранзакциями.

Планы на будущее и развитие

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