На всех конференциях - стандартный вопрос, по окончании практически любого доклада о очередном инструменте анализа производительности СУБД :

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

С методологической точки зрения, вариантов использования сбора и анализа метрик производительности всего 2 :

1. Не использовать сбор и мониторинг метрик производительности СУБД и не иметь никакой достоверной информации о причинах изменения производительности СУБД. Зато - никакого влияния.

2. Использовать сбор метрик производительности СУБД, иметь информацию о причинах изменения производительности и учитывать влияние сбора метрик .

Или проще говоря

• в первом случае: меньше знаешь - крепче спишь ,

• во-втором : за все надо платить.

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

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

PG_HAZEL : Влияние расчета медианного времени на производительность СУБД.

Взято с основного технического канала Postgres DBA

Начало экспериментов :

PG_HAZEL : ожидания СУБД PostgreSQL при отсутствии индексов.

Задача эксперимента

Определение и анализ характерных ожиданий, вызванных использованием индексов при массовых операциях INSERT.

Сравнительные эксперименты

Эксперимент-1 : Стандартный сценарий "Insert only"

Эксперимент-2 : Cценарий "Insert only" с использование индексов на таблице.

Сценарий "Insert only"

INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)

VALUES ( current_tid , current_bid , current_aid , current_delta , CURRENT_TIMESTAMP );

Тестовая таблица

Table "public.pgbench_history"

Column | Type | Collation | Nullable | Default

--------+-----------------------------+-----------+----------+---------

tid | integer | | |

bid | integer | | |

aid | integer | | |

delta | integer | | |

mtime | timestamp without time zone | | |

filler | character(22) | | |

Foreign-key constraints:

"pgbench_history_aid_fkey" FOREIGN KEY (aid) EFERENCES pgbench_accounts(aid)

"pgbench_history_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)

"pgbench_history_tid_fkey" FOREIGN KEY (tid) REFERENCES pgbench_tellers(tid)

Тестовая таблица с добавленными индексами (индексы по столбцам aid , delta, mtime)

Table "public.pgbench_history"

Column | Type | Collation | Nullable | Default

--------+-----------------------------+-----------+----------+---------

tid | integer | | |

bid | integer | | |

aid | integer | | |

delta | integer | | |

mtime | timestamp without time zone | | |

filler | character(22) | | |

Indexes:

"pgbench_history_idx1" btree (aid)

"pgbench_history_idx2" btree (delta)

"pgbench_history_idx3" btree (mtime)

Foreign-key constraints:

"pgbench_history_aid_fkey" FOREIGN KEY (aid) REFERENCES pgbench_accounts(aid)

"pgbench_history_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)

"pgbench_history_tid_fkey" FOREIGN KEY (tid) REFERENCES pgbench_tellers(tid)

Операционная скорость и медианное время тестового SQL запроса

Результат

Создание дополнительных индексов ухудшило скорость на 16-18% и увеличило время на 24-28%.

Корреляция между типами ожиданий и ожиданиями СУБД

Результат

1. Использование индексов резко увеличивает ожидания типа IO и LWLock.

Корреляция между типом ожидания и событиями ожидания при выполнении тестового запроса

Тип ожидания "IO"

Результат

• Резкий рост корреляции с ожиданием DataFileRead

Тип ожидания "Lock"

Тип ожидания "LWLock"

Результат

Резкий рост корреляции с событием ожидания CheckpointerComm.

Итог и результаты анализа

Отключение индексов при массовых операциях вставки данных дает прирост операционной скорости 16-18% .

Характерными признаками наличия лишних индексов при преобладании операция вставки по таблице являются:

1. Высокое значение коэффициента корреляции с событием ожидания IO/DataFileRead , LWLock/BufferMapping и LWLock/CheckpointerComm

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

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

Взято с основного технического канала Postgres DBA ( возможны правки и дополнения в исходной статье ).

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

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

Постановка эксперимента

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

Конфигурация ВМ и СУБД

• Postgres Pro (enterprise certified) 15.10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Soft 11.4.0-1), 64-bit

• CPU 50

• RAM 88GB

• RED OS 7.3

Приоритеты инцидентов

Подробнее о приоритетах

Результат

• свыше 80% инцидентов производительности имеют приоритет 4

Количество SQL запросов по инцидентам

SQL запросы участвующие в более 80% инцидентов

• Количество SQL запросов участвующих во всех инцидентах = 5

• Количество SQL запросов участвующих в 80% инцидентов = 29

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

wait_event_type

wait_event

SQL запросы для оптимизации

queryid = 1214551160677155501

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

Статистика ожиданий по типу IO

История выполнения и событий ожидания по типу IO для queryid = 1214551160677155501

Статистика ожиданий по типу IPC

История выполнения и событий ожидания по типу IPC для queryid = 1214551160677155501

Результаты анализа по SQL queryid = 1214551160677155501

1. Событий ожидания типа IPC существенно больше чем событий по типу IO.

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

3. После добавления индексов , провести анализ результатов .