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

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

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

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

Используя инструментарий на основе свободного расширения 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

postgresql.auto.conf

track_io_timing = 'on'
listen_addresses = '0.0.0.0'
max_connections = '100'
logging_collector = 'on'
log_directory = '/log/pg_log'
log_destination = 'stderr'
log_rotation_size = '0'
log_rotation_age = '1d'
log_filename = 'postgresql-%u.log'
log_line_prefix = '%m| %d| %a| %u| %h| %p| %e| '
log_truncate_on_rotation = 'on'
log_checkpoints = 'on'
archive_mode = 'on'
archive_command = 'true'
archive_timeout = '30min'
checkpoint_timeout = '15min'
checkpoint_warning = '60'
checkpoint_completion_target = '0.9'
min_wal_size = '2GB'
max_wal_size = '8GB'
synchronous_commit = 'on'
wal_compression = 'on'
random_page_cost = '1.1'
effective_io_concurrency = '300'
wal_sender_timeout = '0'
autovacuum_naptime = '1s'
autovacuum_vacuum_scale_factor = '0.01'
autovacuum_analyze_scale_factor = '0.005'
autovacuum_vacuum_cost_delay = '2ms'
autovacuum_max_workers = '4'
autovacuum_work_mem = '256MB'
vacuum_cost_limit = '4000'
bgwriter_delay = '10ms'
bgwriter_lru_multiplier = '4'
bgwriter_lru_maxpages = '400'
max_locks_per_transaction = '256'
max_pred_locks_per_transaction = '256'
shared_buffers = '1919MB'
effective_cache_size = '5757MB'
work_mem = '14MB'
temp_buffers = '14MB'
maintenance_work_mem = '479MB'
max_worker_processes = '8'
max_parallel_workers = '8'
max_parallel_workers_per_gather = '4'
idle_in_transaction_session_timeout = '1h'
statement_timeout = '8h'
pg_stat_statements.track_utility = 'off'
max_parallel_maintenance_workers = '4'
hash_mem_multiplier = '2'
autovacuum_vacuum_insert_scale_factor = '0.01'
shared_preload_libraries = 'pg_stat_statements , pg_wait_sampling'
commit_delay = '1000'
log_autovacuum_min_duration = '0'
wipe_file_on_delete = 'on'
wipe_heaptuple_on_delete = 'on'
wipe_mem_on_free = 'on'
wipe_memctx_on_free = 'on'
wipe_xlog_on_free = 'on'
log_connections = 'on'
log_disconnections = 'on'

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

Операционная скорость

Сумма завершенных 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 ^[1]

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

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

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

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

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

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

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

Автор: pg_expecto

Источник ^[9]

Сайт-источник PVSM.RU: https://www.pvsm.ru

Путь до страницы источника: https://www.pvsm.ru/postgresql/434046

Ссылки в тексте:

[1] PG_EXPECTO : Статистический анализ производительности СУБД PostgreSQL: https://dzen.ru/a/aO8Wser3fg1TOao7?share_to=link

[2] PG_EXPECTO : Нагрузочное тестирование СУБД PostgreSQL: https://dzen.ru/a/aO90kwEztw-GQVba?share_to=link

[3] PG_EXPECTO : Показатели производительности и ожиданий СУБД в ходе нагрузочного тестирования: https://dzen.ru/a/aO-4wHPeSynCa4v0?share_to=link

[4] PG_EXPECTO : Построение графиков производительности и ожиданий по результатам нагрузочного тестирования СУБД: https://dzen.ru/a/aO-6mXBJY0Sn02Hf?share_to=link

[5] PG_EXPECTO : Диаграмма Парето по событиям ожидания СУБД: https://dzen.ru/a/aO_LKe9570wexH-J?share_to=link

[6] Корреляционный анализ ожиданий СУБД PostgreSQL - презентация по докладу, не попавшему на конференцию PGConf.СПб 2025: https://dzen.ru/a/aJsZb6lzqxEd1KmR?share_to=link

[7] Словарь терминов , используемых при корреляционном анализе.: https://dzen.ru/a/Z7h6qwE1CT3q-E73?share_to=link

[8] Таблица 27.12. События ожидания, относящиеся к типу LWLock : https://postgrespro.ru/docs/enterprise/15/monitoring-stats#WAIT-EVENT-LWLOCK-TABLE

[9] Источник: https://habr.com/ru/articles/958320/?utm_source=habrahabr&utm_medium=rss&utm_campaign=958320

Нажмите здесь для печати.