В мире СУБД общепринятая догма гласит: «Индексы ускоряют запросы». Но что, если в погоне за производительностью мы создали себе проблему? В этой статье, на практике показан парадоксальный сценарий, при котором удаление первичного ключа у таблицы pgbench_branch и последующее увеличение стоимости запроса привели к впечатляющему росту общей производительности PostgreSQL под нагрузкой. СУБД не так просты, как может показаться.
ℹ️Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозиториях GitFlic и GitHub
kznalp/PG_EXPECTO: Комплекс статистического анализа производительности СУБД PostgreSQL
Цель эксперимента
Используя инструментарий pg_expecto[1], в процессе нагрузочного тестирования[2], оценить влияние удаление индекса первичного ключа в таблице на производительность СУБД.
Конфигурация тестовой виртуальной машины
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 = 1919MBpostgresql.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]
Нагрузка на СУБД
План нагрузочного тестирования
Для проведения нагрузочного тестирования используются три сценария с разным распределением нагрузки:
· Select only (чтение данных): вес 0.5 (50%)
· Select + Update (чтение и обновление): вес 0.35 (35%)
· Insert only (добавление записей): вес 0.15 (15%)
Количество параллельных сессий для каждого сценария рассчитывается как общая нагрузка, умноженная на его вес.
PG_EXPECTO : Нагрузочное тестирование СУБД PostgreSQL[2]
Тестовая таблица pgbench_branches
Table "public.pgbench_branches"
Column | Type | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
bid | integer | | not null |
bbalance | integer | | |
filler | character(88) | | |
Indexes:
"pgbench_branches_pkey" PRIMARY KEY, btree (bid)
Referenced by:
TABLE "pgbench_accounts" CONSTRAINT "pgbench_accounts_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)
TABLE "pgbench_history" CONSTRAINT "pgbench_history_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)
TABLE "pgbench_tellers" CONSTRAINT "pgbench_tellers_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)
Планы выполнения тестовых запросов , в который участвует таблица pgbench_branches
Сценарий-1 "Select only" (для формирования плана, задан фактический параметр запроса)
Nested Loop (cost=0.84..5.28 rows=1 width=4)
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts acc (cost=0.57..2.79 rows=1 width=4)
Index Cond: (aid = 1000)
-> Index Scan using pgbench_branches_pkey on pgbench_branches br (cost=0.28..2.49 rows=1 width=8)
Index Cond: (bid = acc.bid)Сценарий-2 "Select + Update" (для формирования плана, задан фактический параметр запроса)
Update on pgbench_branches (cost=0.28..2.49 rows=0 width=0)
-> Index Scan using pgbench_branches_pkey on pgbench_branches (cost=0.28..2.49 rows=1 width=10)
Index Cond: (bid = 469)Эксперимент - удаление ограничения первичного ключа в таблице pgbench_branches
ALTER TABLE pgbench_branches DROP CONSTRAINT pgbench_branches_pkey CASCADE
pgbench_db=# ALTER TABLE pgbench_branches DROP CONSTRAINT pgbench_branches_pkey CASCADE ;
NOTICE: удаление распространяется на ещё 3 объекта
DETAIL: удаление распространяется на объект ограничение pgbench_tellers_bid_fkey в отношении таблица pgbench_tellers
удаление распространяется на объект ограничение pgbench_accounts_bid_fkey в отношении таблица pgbench_accounts
удаление распространяется на объект ограничение pgbench_history_bid_fkey в отношении таблица pgbench_historyИзменение планов выполнения
Сценарий-1 "Select only" (для формирования плана, задан фактический параметр запроса)
Hash Join (cost=2.80..372.68 rows=1 width=4)
Hash Cond: (br.bid = acc.bid)
-> Seq Scan on pgbench_branches br (cost=0.00..366.78 rows=1178 width=8)
-> Hash (cost=2.79..2.79 rows=1 width=4)
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts acc (cost=0.57..2.79 rows=1 width=4)
Index Cond: (aid = 1000)Сценарий-2 "Select + Update" (для формирования плана, задан фактический параметр запроса)
Update on pgbench_branches (cost=0.00..369.73 rows=0 width=0)
-> Seq Scan on pgbench_branches (cost=0.00..369.73 rows=1 width=10)
Filter: (bid = 469)Изменение производительности в ходе нагрузочного тестирования (Эксперимент-2) по сравнению с базовыми значениями (Эксперимент-1)
Операционная скорость
Для построения графиков используются отчеты по результатам нагрузочного тестирования [3][4]
Среднее увеличение операционной скорости в эксперименте-2 сост��вило ~20%
Ожидания СУБД
Характерные события ожидания (wait_event)
Для формирования таблиц используются отчеты по результатам нагрузочного тестирования [5]
Итог эксперимента
Для данной виртуальной машины, при данном характере и сценарии нагрузки , операционная скорость в ходе Эксперимента-2 увеличилась в среднем на 20%.
В ходе Эксперимента-2 кардинально снизилась доля событий ожиданий CheckpointerComm и кардинально увеличилась доля событий ожиданий BufferContent и BufferMapping.
| Ожидание при управлении запросами fsync. |
| Ожидание при обращении к странице данных в памяти. |
| Ожидание при связывании блока данных с буфером в пуле буферов. |
Таблица 27.12. События ожидания, относящиеся к типу LWLock[8]
Ссылки на используемые материалы:
1.PG_EXPECTO : Статистический анализ производительности СУБД PostgreSQL
2.PG_EXPECTO : Нагрузочное тестирование СУБД PostgreSQL
3.PG_EXPECTO : Показатели производительности и ожиданий СУБД в ходе нагрузочного тестирования
4.PG_EXPECTO : Построение графиков производительности и ожиданий по результатам нагрузочного тестирования СУБД
5.PG_EXPECTO : Диаграмма Парето по событиям ожидания СУБД
7.Словарь терминов , используемых при корреляционном анализе.
8.Таблица 27.12. События ожидания, относящиеся к типу LWLock
P.S. Мнение нейросети о причинах роста производительности СУБД в данном кейсе
🔍 Почему это работает для pgbench_branches
Конкретный сценарий pgbench
В стандартном TPC-B-подобном сценарии pgbench:
Таблица
pgbench_branchesсодержит очень мало строк (по умолчанию = scale factor)При scale=685 - всего 685 строк в таблице
Основная нагрузка - частые операции
UPDATEна эту таблицу
Источник повышения производительности
-- Стандартная операция pgbench, которая выполняется часто:
UPDATE pgbench_branches SET bbalance = bbalance + 123 WHERE bid = 1;Без индекса:
PostgreSQL выполняет последовательное сканирование маленькой таблицы
Нет затрат на обновление структуры B-дерева индекса
Меньше операций ввода-вывода
С индексом:
Каждый
UPDATEтребует обновления индексаДополнительные блокировки и конфликты
Больше нагрузки на WAL
⚙️ Технические детали
Ресурсы ВМ (8 CPU, 8GB RAM)
При 8GB RAM таблица размером 685 строк полностью помещается в памяти
Последовательное сканирование такой маленькой таблицы практически бесплатно
8 CPU позволяют эффективно обрабатывать высокую конкуренцию
📊 Ожидаемые результаты
Увеличение TPS (Transactions Per Second)
Без PK: Выше TPS из-за уменьшения накладных расходов
С PK: Ниже TPS, но гарантирована целостность данных
Снижение нагрузки
Меньше операций ввода-вывода
Подтверждено в ходе эксперимента: Количество ожиданий DataFileRead снизилось на 13%
Снижение использования CPU
Уменьшение конфликтов блокировок
Подтверждено в ходе эксперимента: Количество ожиданий relation снизилось на 100%
🎯 Вывод
Для данного конкретного случая (scale=685, 8 CPU, 8GB RAM) удаление индекса и PK с pgbench_branches может дать прирост производительности 15-25% в TPS за счет:
Подтверждено в ходе эксперимента: Производительность СУБД увеличилась на 20%
Устранения накладных расходов на обновление индекса
Уменьшения конфликтов блокировок при высокой конкуренции
Оптимизации использования ограниченных ресурсов RAM
