«Мы пожинаем
wait_eventпосеянных нами индексов.pg_expecto— это наш урожайный калькулятор.»
Цель эксперимента
Используя инструментарий на основе свободного расширения pg_expecto[1], в процессе нагрузочного тестирования[2], выявить ключевые события ожидания (wait_event), негативно влияющие на производительность базы данных, при избыточном количестве индексов на тестовых таблицах.
Дополнительная информация и контакты по теме:
https://dzen.ru/kznalp
kznalp@yandex.ru
Конфигурация тестовой виртуальной машины
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]
Эксперимент-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]
Операционная скорость СУБД
Среднее уменьшение операционной скорости составило 13.97%
Ожидания СУБД
Типы ожиданий СУБД (wait_event_type)
Характерные события ожидания (wait_event)
Для формирования таблиц используются отчеты по результатам нагрузочного тестирования [5]
Итог экспериментов
Операционная скорость в ходе Эксперимента-2 снизилась в среднем ~14%.
Характерные события ожидания в ходе Эксперимента-2, существенно изменились. Наибольший рост(более 50%) отмечен по событиям ожидания типа LWLock:
LockManager : 100%
BufferContent: > 60%
|
Серверный процесс ожидает лёгкую блокировку. В большинстве своём такие блокировки защищают определённые структуры данных в общей памяти. |
|
Ожидание при обращении к странице данных в памяти. |
|
Ожидание при чтении или изменении информации о «тяжёлых» блокировках. |
Таблица 27.12. События ожидания, относящиеся к типу
LWLock[8]
Ссылки на используемые материалы:
1.PG_EXPECTO : Статистический анализ производительности СУБД PostgreSQL
2.PG_EXPECTO : Нагрузочное тестирование СУБД PostgreSQL
3.PG_EXPECTO : Показатели производительности и ожиданий СУБД в ходе нагрузочного тестирования
5.PG_EXPECTO : Диаграмма Парето по событиям ожидания СУБД
7.Словарь терминов , используемых при корреляционном анализе.
8.Таблица 27.12. События ожидания, относящиеся к типу LWLock
baldr
После вашей второй статьи я решил посмотреть - что же там в первой.
Я совсем не DBA, но даже у меня кровь из глаз от таких индексов. Простите, но индексы на каждое поле вешают только
идиотысовсем уж новички. Ещё и индексы на только одно поле..Вы измеряете тривиальную ерунду, и, судя по отсутствию каких-либо выводов, вы сами не очень понимаете что вы делаете.
Не бывает таких таблиц с индексами в жизни, и вместо таблиц с графиками вы бы лучше объяснили - почему они не имеют смысла.