✔️Ожидания в избытке: как лишние индексы тормозят PostgreSQL и чем поможет pg_expecto

"Избыток индексов — это не оптимизация, а замаскированная проблема производительности."

«Мы пожинаем wait_event посеянных нами индексов. pg_expecto — это наш урожайный калькулятор.»

ℹ️Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозиториях GitFlic и GitHub

Цель эксперимента

Используя pg_expecto[1], в процессе нагрузочного тестирования[2], выявить ключевые события ожидания (wait_event), негативно влияющие на производительность базы данных, при избыточном количестве индексов на тестовых таблицах.

Конфигурация тестовой виртуальной машины

• CPU: 8 ядер
• RAM: 8GB
• ОС: RED OS MUROM (7.3.4)
• PostgreSQL: Postgres Pro (enterprise certified) 17.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Soft 11.4.0-1), 64-bit
• Размер тестовой БД: 10GB

Конфигурационные параметры СУБД

shared_buffers = 1919MB

Используемые термины и определения

Операционная скорость : Сумма завершенных SQL операций и числа строк полученных или затронутых оператором за промежуток времени[7]

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

Корреляционный анализ ожиданий СУБД PostgreSQL[6]

Эксперимент-1

Базовый тест с использование индексов, созданных для тестовых таблиц:

Table "public.pgbench_accounts"

Indexes:

"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

Table "public.pgbench_branches"

Indexes:

"pgbench_branches_pkey" PRIMARY KEY, btree (bid)

Table "public.pgbench_tellers"

Indexes:

"pgbench_tellers_pkey" PRIMARY KEY, btree (tid)

Эксперимент-2

Дополнительные индексы на тестовых таблицах:

Table "public.pgbench_branches"

"pgbench_branches_idx1" btree (bbalance)

"pgbench_branches_idx2" btree (filler)

Table "public.pgbench_history"

"pgbench_history_idx1" btree (tid)

"pgbench_history_idx2" btree (bid)

"pgbench_history_idx3" btree (aid)

"pgbench_history_idx4" btree (delta)

"pgbench_history_idx5" btree (mtime)

"pgbench_history_idx6" btree (filler)

Table "public.pgbench_tellers"

"pgbench_tellers_idx1" btree (bid)

"pgbench_tellers_idx2" btree (tbalance)

"pgbench_tellers_idx3" btree (filler)

Table "public.pgbench_accounts"

"pgbench_accounts_idx1" btree (bid)

"pgbench_accounts_idx2" btree (abalance)

"pgbench_accounts_idx3" btree (filler)

Нагрузка на СУБД

Изменение нагрузки в ходе нагрузочного тестирования

План нагрузочного тестирования

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

· Select only (чтение данных): вес 0.5 (50%)

· Select + Update (чтение и обновление): вес 0.35 (35%)

· Insert only (добавление записей): вес 0.15 (15%)

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

PG_EXPECTO : Нагрузочное тестирование СУБД PostgreSQL[2]

Результаты эксперимента

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

Для построения графиков используются отчеты по результатам нагрузочного тестирования [3][4]

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

Графики операционной скорости в ходе Эксперимента-1(SPEED-index) и Эксперимента-2(SPEED-extra index)

Среднее уменьшение операционной скорости составило 13.97%

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

Графики ожиданий СУБД в ходе Эксперимента-1(SPEED-index) и Эксперимента-2(SPEED-extra index)

Типы ожиданий СУБД (wait_event_type)

Абсолютные значения ожиданий и типов ожиданий в ходе Эксперимента-1(index) и Эксперимента-2(extra index)

Характерные события ожидания (wait_event)

Для формирования таблиц используются отчеты по результатам нагрузочного тестирования [5]

Количество и относительная доля событий ожидания в ходе Эксперимента-1(index) и Эксперимента-2(extra index), составляющие 80% среди событий ожиданий(wait_event) по типам ожиданий(wait_event_type).

Итог экспериментов

Операционная скорость в ходе Эксперимента-2 снизилась в среднем ~14%.

Характерные события ожидания в ходе Эксперимента-2, существенно изменились. Наибольший рост(более 50%) отмечен по событиям ожидания типа LWLock:

• LockManager : 100%
• BufferContent: > 60%

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

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

Таблица 27.12. События ожидания, относящиеся к типу LWLock [8]

Ссылки на используемые материалы:

1.PG_EXPECTO : Статистический анализ производительности СУБД PostgreSQL

2.PG_EXPECTO : Нагрузочное тестирование СУБД PostgreSQL

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

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

5.PG_EXPECTO : Диаграмма Парето по событиям ожидания СУБД

6.Корреляционный анализ ожиданий СУБД PostgreSQL - презентация по докладу, не попавшему на конференцию PGConf.СПб 2025

7.Словарь терминов , используемых при корреляционном анализе.

8.Таблица 27.12. События ожидания, относящиеся к типу LWLock