Статистический анализ производительности СУБД PostgreSQL
Предисловие
Оптимизировать запрос в вакууме — просто. Но как он поведет себя, когда десятки таких же запросов одновременно борются за ресурсы?
Методология исследования
Тестовая среда и инструменты:
Тестовая ВМ: CPU = 8 / RAM = 8GB
Инструмент нагрузочного тестирования и статистического анализа производительности СУБД: pg_expecto
Тестовая база данных: "Демобаза 2.0" (большой размер, сложная схема)
Условия тестирования: параллельная нагрузка, ресурсоемкий запрос
СУБД: PostgreSQL 17
Конфигурационные параметры СУБД
track_io_timing = 'on' listen_addresses = '0.0.0.0' 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' pg_stat_statements.track = 'all' max_connections = '1000'
План нагрузочного тестирования (param.conf)
# НАСТРОЙКИ НАГРУЗОЧНОГО ТЕСТИРОВАНИЯ # Максимальная нагрузка finish_load = 20 # Тестовая БД testdb = demo # Веса сценариев scenario1 = 1.0
Нагрузка на СУБД
Тестовый запрос, используемый в качестве baseline : LEFT JOIN
Запрос демонстрирует:
LEFT JOIN bookings → tickets
Все бронирования, даже без привязанных билетовLEFT JOIN tickets → segments
Все билеты, даже без перелётовLEFT JOIN segments → flights
Все перелёты, даже без информации о рейсахLEFT JOIN segments → boarding_passes
Все перелёты, даже без посадочных талоновВыбирает случайную дату из существующих бронирований
Гарантирует, что период полностью входит в доступный диапазон дат
Такой запрос может быть полезен для анализа:
Неполных бронирований
Билетов без привязки к рейсам
Рейсов без процедуры посадки
Статистики по незавершённым операциям
Таблицы, используемые в тестовом запросе
demo=# \d bookings Unlogged table "bookings.bookings" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | not null | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | not null | Indexes: "bookings_pkey" PRIMARY KEY, btree (book_ref) "idx_bookings_book_date" btree (book_date) Referenced by: TABLE "tickets" CONSTRAINT "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref) demo=# \d tickets Unlogged table "bookings.tickets" Column | Type | Collation | Nullable | Default ----------------+--------------+-----------+----------+--------- ticket_no | text | | not null | book_ref | character(6) | | not null | passenger_id | text | | not null | passenger_name | text | | not null | outbound | boolean | | not null | Indexes: "tickets_pkey" PRIMARY KEY, btree (ticket_no) "idx_tickets_book_ref" btree (book_ref) "tickets_book_ref_passenger_id_outbound_key" UNIQUE CONSTRAINT, btree (book_ref, passenger_id, outbound) Foreign-key constraints: "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref) Referenced by: TABLE "segments" CONSTRAINT "segments_ticket_no_fkey" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no) demo=# \d segments Unlogged table "bookings.segments" Column | Type | Collation | Nullable | Default -----------------+---------------+-----------+----------+--------- ticket_no | text | | not null | flight_id | integer | | not null | fare_conditions | text | | not null | price | numeric(10,2) | | not null | Indexes: "segments_pkey" PRIMARY KEY, btree (ticket_no, flight_id) "idx_segments_flight_id" btree (flight_id) "idx_segments_flight_id_fare_conditions" btree (flight_id, fare_conditions) "idx_segments_ticket_no" btree (ticket_no) "segments_flight_id_idx" btree (flight_id) Check constraints: "segments_fare_conditions_check" CHECK (fare_conditions = ANY (ARRAY['Economy'::text, 'Comfort'::text, 'Business'::text])) "segments_price_check" CHECK (price >= 0::numeric) Foreign-key constraints: "segments_flight_id_fkey" FOREIGN KEY (flight_id) REFERENCES flights(flight_id) "segments_ticket_no_fkey" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no) Referenced by: TABLE "boarding_passes" CONSTRAINT "boarding_passes_ticket_no_flight_id_fkey" FOREIGN KEY (ticket_no, flight_id) REFERENCES segments(ticket_no, flight_id) demo=# \d boarding_passes Unlogged table "bookings.boarding_passes" Column | Type | Collation | Nullable | Default ---------------+--------------------------+-----------+----------+--------- ticket_no | text | | not null | flight_id | integer | | not null | seat_no | text | | not null | boarding_no | integer | | | boarding_time | timestamp with time zone | | | Indexes: "boarding_passes_pkey" PRIMARY KEY, btree (ticket_no, flight_id) "boarding_passes_flight_id_boarding_no_key" UNIQUE CONSTRAINT, btree (flight_id, boarding_no) "boarding_passes_flight_id_boarding_time" btree (flight_id, boarding_time) "boarding_passes_flight_id_seat_no_key" UNIQUE CONSTRAINT, btree (flight_id, seat_no) Foreign-key constraints: "boarding_passes_ticket_no_flight_id_fkey" FOREIGN KEY (ticket_no, flight_id) REFERENCES segments(ticket_no, flight_id) demo=# \d flights Unlogged table "bookings.flights" Column | Type | Collation | Nullable | Default ---------------------+--------------------------+-----------+----------+------------------------------ flight_id | integer | | not null | generated always as identity route_no | text | | not null | status | text | | not null | scheduled_departure | timestamp with time zone | | not null | scheduled_arrival | timestamp with time zone | | not null | actual_departure | timestamp with time zone | | | actual_arrival | timestamp with time zone | | | Indexes: "flights_pkey" PRIMARY KEY, btree (flight_id) "flights_route_no_scheduled_departure_key" UNIQUE CONSTRAINT, btree (route_no, scheduled_departure) "idx_flights_route_no" btree (route_no) "idx_flights_scheduled_departure" btree (scheduled_departure DESC) Check constraints: "flight_actual_check" CHECK (actual_arrival IS NULL OR actual_departure IS NOT NULL AND actual_arrival IS NOT NULL AND actual_arrival > actual_departure) "flight_scheduled_check" CHECK (scheduled_arrival > scheduled_departure) "flight_status_check" CHECK (status = ANY (ARRAY['Scheduled'::text, 'On Time'::text, 'Delayed'::text, 'Boarding'::text, 'Departed'::text, 'Arrived'::text, 'Cancelled'::text])) Referenced by: TABLE "segments" CONSTRAINT "segments_flight_id_fkey" FOREIGN KEY (flight_id) REFERENCES flights(flight_id)
Тестовый запрос - LEFT JOIN
WITH random_period AS ( SELECT start_date, start_date + INTERVAL '30 days' AS end_date FROM ( SELECT book_date AS start_date FROM bookings WHERE book_date <= (SELECT MAX(book_date) FROM bookings) - INTERVAL '30 days' ORDER BY RANDOM() LIMIT 1 ) AS random_date ) SELECT b.book_ref, b.book_date, t.ticket_no, t.passenger_name, s.flight_id, f.status, f.scheduled_departure, f.actual_departure, bp.seat_no, bp.boarding_no, rp.start_date AS period_start, rp.end_date AS period_end FROM random_period rp CROSS JOIN LATERAL ( SELECT * FROM bookings b WHERE b.book_date BETWEEN rp.start_date AND rp.end_date ) b LEFT JOIN tickets t ON b.book_ref = t.book_ref LEFT JOIN segments s ON t.ticket_no = s.ticket_no LEFT JOIN flights f ON s.flight_id = f.flight_id LEFT JOIN boarding_passes bp ON s.ticket_no = bp.ticket_no AND s.flight_id = bp.flight_id ORDER BY b.book_date, t.ticket_no;
План выполнения тестового запроса - LEFT JOIN
Sort (cost=2544355.52..2550077.32 rows=2288717 width=93) (actual time=58778.563..59050.999 rows=1146771 loops=1) Sort Key: b.book_date, t.ticket_no Sort Method: external merge Disk: 121832kB CTE random_period -> Subquery Scan on random_date (cost=66429.70..66429.71 rows=1 width=16) (actual time=2883.937..2883.945 rows=1 loops=1) -> Limit (cost=66429.70..66429.70 rows=1 width=16) (actual time=2883.919..2883.925 rows=1 loops=1) InitPlan 2 -> Result (cost=0.45..0.46 rows=1 width=8) (actual time=1.994..1.996 rows=1 loops=1) InitPlan 1 -> Limit (cost=0.43..0.45 rows=1 width=8) (actual time=1.989..1.991 rows=1 loops=1) -> Index Only Scan Backward using idx_bookings_book_date on bookings (cost=0.43..128154.91 rows=7113192 width=8) (actual time=1.988..1.989 rows=1 loops=1) Heap Fetches: 0 -> Sort (cost=66429.24..72356.90 rows=2371064 width=16) (actual time=2883.917..2883.918 rows=1 loops=1) Sort Key: (random()) Sort Method: top-N heapsort Memory: 25kB -> Index Only Scan using idx_bookings_book_date on bookings bookings_1 (cost=0.43..54573.91 rows=2371064 width=16) (actual time=2.183..1714.099 rows=6894590 loops=1) Index Cond: (book_date <= ((InitPlan 2).col1 - '30 days'::interval)) Heap Fetches: 0 -> Hash Left Join (cost=685482.92..2046557.85 rows=2288717 width=93) (actual time=49378.694..57307.629 rows=1146771 loops=1) Hash Cond: (s.flight_id = f.flight_id) -> Merge Right Join (cost=681880.13..2036946.95 rows=2288717 width=69) (actual time=49326.107..56627.660 rows=1146771 loops=1) Merge Cond: (s.ticket_no = t.ticket_no) -> Merge Left Join (cost=1.12..1264914.46 rows=20598448 width=25) (actual time=3.919..32375.016 rows=6564405 loops=1) Merge Cond: ((s.ticket_no = bp.ticket_no) AND (s.flight_id = bp.flight_id)) -> Index Only Scan using segments_pkey on segments s (cost=0.56..424624.50 rows=20598448 width=18) (actual time=3.875..1950.364 rows=6564405 loops=1) Heap Fetches: 0 -> Index Scan using boarding_passes_pkey on boarding_passes bp (cost=0.56..637941.24 rows=19870556 width=25) (actual time=0.033..25169.883 rows=6564405 loops=1) -> Materialize (cost=681879.01..690532.20 rows=1730639 width=58) (actual time=21308.253..22022.391 rows=1146771 loops=1) -> Sort (cost=681879.01..686205.61 rows=1730639 width=58) (actual time=21308.245..21812.491 rows=862459 loops=1) Sort Key: t.ticket_no Sort Method: external merge Disk: 58600kB -> Nested Loop Left Join (cost=0.87..464448.40 rows=1730639 width=58) (actual time=2886.101..18909.459 rows=862459 loops=1) -> Nested Loop (cost=0.43..31711.08 rows=790355 width=31) (actual time=2884.013..3268.567 rows=397632 loops=1) -> CTE Scan on random_period rp (cost=0.00..0.02 rows=1 width=16) (actual time=2883.941..2883.946 rows=1 loops=1) -> Index Scan using idx_bookings_book_date on bookings b (cost=0.43..23807.51 rows=790355 width=15) (actual time=0.063..308.659 rows=397632 loops=1) Index Cond: ((book_date >= rp.start_date) AND (book_date <= rp.end_date)) -> Index Scan using idx_tickets_book_ref on tickets t (cost=0.43..0.52 rows=3 width=34) (actual time=0.038..0.038 rows=2 loops=397632) Index Cond: (book_ref = b.book_ref) -> Hash (cost=2247.35..2247.35 rows=108435 width=28) (actual time=52.383..52.385 rows=108435 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 7377kB -> Seq Scan on flights f (cost=0.00..2247.35 rows=108435 width=28) (actual time=0.024..22.614 rows=108435 loops=1) Planning Time: 37.456 ms Execution Time: 59151.413 ms
Эксперимент-1 : вариант с использованием EXISTS
Эксперимент-2 : вариант с оптимизацией структуры запроса (TUNING)
Эксперимент-3 : Частичная агрегация
Эксперимент-4 «Временная таблица»
Временная таблица
-- Создаем временную таблицу для отфильтрованных бронирований CREATE TEMPORARY TABLE temp_filtered_bookings AS WITH random_period AS ( SELECT book_date AS start_date, book_date + INTERVAL '30 days' AS end_date FROM bookings ORDER BY RANDOM() LIMIT 1 ) SELECT b.* FROM bookings b CROSS JOIN random_period rp WHERE b.book_date BETWEEN rp.start_date AND rp.end_date; -- Создаем индексы на временной таблице CREATE INDEX tmp_idx_book_ref ON temp_filtered_bookings (book_ref); CREATE INDEX tmp_idx_book_date ON temp_filtered_bookings (book_date);
Тестовый запрос - TEMP TABLE
SELECT b.book_ref, b.book_date, t.ticket_no, t.passenger_name, s.flight_id, f.status, f.scheduled_departure, f.actual_departure, bp.seat_no, bp.boarding_no FROM temp_filtered_bookings b LEFT JOIN tickets t ON b.book_ref = t.book_ref LEFT JOIN segments s ON t.ticket_no = s.ticket_no LEFT JOIN flights f ON s.flight_id = f.flight_id LEFT JOIN boarding_passes bp ON s.ticket_no = bp.ticket_no AND s.flight_id = bp.flight_id ORDER BY b.book_date, t.ticket_no;
План выполнения тестового запроса -TEMP TABLE
Sort (cost=2211998.73..2215844.96 rows=1538491 width=98) (actual time=34246.862..34542.140 rows=1211093 loops=1) Sort Key: b.book_date, t.ticket_no Sort Method: external merge Disk: 109672kB -> Hash Left Join (cost=579129.28..1925563.26 rows=1538491 width=98) (actual time=23831.991..32643.918 rows=1211093 loops=1) Hash Cond: (s.flight_id = f.flight_id) -> Merge Right Join (cost=575526.49..1917921.78 rows=1538491 width=74) (actual time=23774.473..31882.934 rows=1211093 loops=1) Merge Cond: (s.ticket_no = t.ticket_no) -> Merge Left Join (cost=1.12..1264914.56 rows=20598448 width=25) (actual time=2.411..22735.924 rows=3430362 loops=1) Merge Cond: ((s.ticket_no = bp.ticket_no) AND (s.flight_id = bp.flight_id)) -> Index Only Scan using segments_pkey on segments s (cost=0.56..424624.50 rows=20598448 width=18) (actual time=1.704..1121.887 rows=3430362 loops=1) Heap Fetches: 0 -> Index Scan using boarding_passes_pkey on boarding_passes bp (cost=0.56..637941.32 rows=19870556 width=25) (actual time=0.698..18861.672 rows=3430362 loops=1) -> Materialize (cost=575525.37..581342.10 rows=1163347 width=63) (actual time=6939.314..7652.509 rows=1211093 loops=1) -> Sort (cost=575525.37..578433.73 rows=1163347 width=63) (actual time=6939.289..7437.005 rows=902132 loops=1) Sort Key: t.ticket_no Sort Method: external merge Disk: 47192kB -> Nested Loop Left Join (cost=0.43..432700.18 rows=1163347 width=63) (actual time=0.175..4432.691 rows=902132 loops=1) -> Seq Scan on temp_filtered_bookings b (cost=0.00..6791.41 rows=410341 width=36) (actual time=0.096..71.373 rows=410341 loops=1) -> Index Scan using idx_tickets_book_ref on tickets t (cost=0.43..1.01 rows=3 width=34) (actual time=0.009..0.010 rows=2 loops=410341) Index Cond: (book_ref = b.book_ref) -> Hash (cost=2247.35..2247.35 rows=108435 width=28) (actual time=57.236..57.238 rows=108435 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 7377kB -> Seq Scan on flights f (cost=0.00..2247.35 rows=108435 width=28) (actual time=0.055..24.930 rows=108435 loops=1) Planning Time: 42.697 ms Execution Time: 34655.641 ms
Дополнение
Временная таблица temp_filtered_bookings пересоздается перед выполнением тестового запроса.
Тестовый сценарий scenario1.sql
-- scenario1.sql -- TEMP TABLES CREATE OR REPLACE FUNCTION scenario1() RETURNS integer AS $$ DECLARE test_rec record; original_app_name text; temp_table_name text; BEGIN -- Сохраняем оригинальное application_name original_app_name := current_setting('application_name'); -- Генерируем уникальное имя временной таблицы temp_table_name := 'temp_filtered_bookings_' || pg_backend_pid(); -- Устанавливаем уникальное имя приложения для сессии PERFORM set_config('application_name', 'scenario1_' || pg_backend_pid(), false); -- Создаем временную таблицу с уникальным именем EXECUTE format(' CREATE TEMP TABLE %I ON COMMIT DROP AS WITH random_period AS ( SELECT book_date AS start_date, book_date + INTERVAL ''30 days'' AS end_date FROM bookings ORDER BY RANDOM() LIMIT 1 ) SELECT b.* FROM bookings b CROSS JOIN random_period rp WHERE b.book_date BETWEEN rp.start_date AND rp.end_date ', temp_table_name); -- Создаем индексы EXECUTE format('CREATE INDEX ON %I (book_ref)', temp_table_name); EXECUTE format('CREATE INDEX ON %I (book_date)', temp_table_name); EXECUTE format(' SELECT b.book_ref, b.book_date, t.ticket_no, t.passenger_name, s.flight_id, f.status, f.scheduled_departure, f.actual_departure, bp.seat_no, bp.boarding_no FROM %I b LEFT JOIN tickets t ON b.book_ref = t.book_ref LEFT JOIN segments s ON t.ticket_no = s.ticket_no LEFT JOIN flights f ON s.flight_id = f.flight_id LEFT JOIN boarding_passes bp ON s.ticket_no = bp.ticket_no AND s.flight_id = bp.flight_id ORDER BY b.book_date, t.ticket_no ', temp_table_name) INTO test_rec; -- Восстанавливаем оригинальное application_name PERFORM set_config('application_name', original_app_name, false); RETURN 0; EXCEPTION WHEN OTHERS THEN -- Восстанавливаем application_name в случае ошибки PERFORM set_config('application_name', original_app_name, false); RAISE; END; $$ LANGUAGE plpgsql;
Сравнительный анализ планов выполнения запросов
Ключевые различия:
1.Подход к работе с данными:
TEMP TABLE: Использует временную таблицу с предварительной фильтрацией и индексами
2.Использование ресурсов
TEMP TABLE: Меньше дисковых операций сортировки (109,672 kB)
Оба запроса используют external merge сортировку из-за большого объема данных
3.Операции в плане
TEMP TABLE: Разделяет логику - сначала фильтрация во временную таблицу, затем JOIN
4.Чтение данных
TEMP TABLE: Работает с уже отфильтрованными строками во временной таблице
Итоговый вывод:
TEMP TABLE показывает лучшую производительность благодаря:
Разделению ответственности: Фильтрация данных выполняется один раз и сохраняется
Эффективному использованию индексов: Индексы на временной таблице ускоряют JOIN операции
Уменьшению сложности запроса: Основной запрос становится проще для оптимизатора
Снижению стоимости случайного выбора: ORDER BY RANDOM() выполняется один раз при создании временной таблицы
Подход TEMP TABLE более масштабируем и предсказуем по производительности, особенно при работе с большими объемами данных.
Производительность и ожидания СУБД
Операционная скорость
Среднее увеличение операционной скорости при использовании запроса "TEMP TABLE" составило 14.62%
Ожидания СУБД
Сравнительный анализ wait_event_type
Ключевые наблюдения:
Ожидания ввода-вывода (IO):
В обоих типах запросов наблюдаются высокие и растущие значения IO.
Для TEMP TABLE максимальное значение IO достигает 22882, для LEFT JOIN — 15183.
Это указывает на значительную нагрузку на систему ввода-вывода в обоих случаях.Межпроцессное взаимодействие (IPC):
В LEFT JOIN значения IPC значительно выше (до 24107) и демонстрируют устойчивый рост.
В TEMP TABLE - IPC ниже (до 5870) и растет медленнее.
Это говорит о том, что LEFT JOIN сильнее нагружает механизмы IPC.Легковесные блокировки (LWLOCK):
В TEMP TABLE значения LWLOCK значительно выше (до 311) и растут быстрее.
В LEFT JOIN LWLOCK ниже (до 106) и увеличиваются умеренно.
Использование временных таблиц приводит к более активному использованию легковесных блокировок.Обычные блокировки (LOCK):
Присутствуют только в TEMP TABLE, но после определенного момента становятся нулевыми.
Это может означать, что начальные этапы работы с временными таблицами требуют блокировок, которые затем отпускаются.Таймауты (TIMEOUT):
В LEFT JOIN таймауты выше (до 19) и растут постепенно.
В TEMP TABLE таймауты ниже (до 8) и остаются стабильными.
Это может указывать на более предсказуемое выполнение временных таблиц.
Выводы:
Запросы LEFT JOIN создают высокую нагрузку на IPC и IO, с умеренным ростом легковесных блокировок и таймаутов.
Запросы TEMP TABLE вызывают значительный рост легковесных блокировок (LWLOCK), но при этом IPC и таймауты ниже. Наличие LOCK только на начальном этапе может свидетельствовать об особенностях аллокации временных объектов.
Рекомендации:
При оптимизации запросов с LEFT JOIN стоит обратить внимание на уменьшение межпроцессного взаимодействия и оптимизацию ввода-вывода.
При использовании временных таблиц важно контролировать легковесные блокировки, возможно, за счет сокращения времени жизни временных объектов или оптимизации их структуры.
Метрики iostat для дискового устройства, используемого файловой системой /data
Дисковая подсистема не участвует в ограничении производительности данных запросов.
Метрики vmstat
1. Процессы:
"LEFT JOIN": Количество работающих процессов (procs_r) растёт до 9, есть процессы в ожидании (procs_b до 2).
"TEMP TABLE": procs_r доходит до 13, что указывает на более высокую конкуренцию за CPU.
2. Память:
Оба запроса работают в условиях достаточного объёма свободной памяти.
"LEFT JOIN" активнее использует кэш (выше memory_cache), что может быть связано с обработкой больших объёмов данных.
3. Ввод-вывод:
"LEFT JOIN" создаёт значительную нагрузку на диск (высокие io_bi и io_bo), что согласуется с высокой долей cpu_wa.
"TEMP TABLE" меньше нагружает диск, но при этом сильнее нагружает CPU.
4. Системная активность:
"LEFT JOIN" вызывает больше прерываний и контекстных переключений, что может быть связано с активной работой с данными и блокировками.
"TEMP TABLE" демонстрирует более низкую системную активность, но выше нагрузку на CPU в пользовательском режиме.
5. Использование CPU:
"LEFT JOIN": Высокое ожидание ввода-вывода (cpu_wa), что указывает на узкое место в дисковой подсистеме.
"TEMP TABLE": Низкий cpu_id (простой CPU), высокий cpu_us — запрос активно использует процессор для вычислений.
Итог:
"LEFT JOIN" — I/O-зависимый запрос, создаёт высокую нагрузку на диск и вызывает ожидание ввода-вывода. Подходит для систем с быстрыми дисками и достаточным объёмом оперативной памяти для кэширования.
"TEMP TABLE" — CPU-зависимый запрос, активно использует процессор, но меньше нагружает диск. Может быть эффективнее в системах с быстрыми CPU и достаточным количеством ядер.
Общий итог : Часть-4 "TEMP TABLE"
Для условий высокой параллельной нагрузки и конкуренции за вычислительные ресурсы, с точки зрения производительности СУБД - использование временной таблицы оказывает существенное влияние на увеличение производительности СУБД .
Производительность СУБД:
Среднее увеличение операционной скорости при использовании запроса "TEMP TABLE" составило 14.62%💥
📊 Ключевые выводы о производительности
1. Операционная скорость
TEMP TABLE демонстрирует значительное преимущество - среднее увеличение операционной скорости составляет +14.62% по сравнению с LEFT JOIN
Подход с временными таблицами обеспечивает более предсказуемую и масштабируемую производительность
2. Характер нагрузки на систему
LEFT JOIN (I/O-зависимый запрос):
Высокая нагрузка на дисковую подсистему (io_bi до 87 тыс., io_bo до 7 тыс.)
Значительное межпроцессное взаимодействие (IPC до 24 107)
Высокое время ожидания ввода-вывода (cpu_wa 10-17%)
Больше прерываний и контекстных переключений
TEMP TABLE (CPU-зависимый запрос):
Интенсивное использование процессора (cpu_us до 81%)
Меньше дисковых операций (сортировка 109,672 kB против большего у JOIN)
Высокие легковесные блокировки (LWLOCK до 311)
Более стабильные таймауты (до 8)
🔧 Архитектурные преимущества TEMP TABLE
1. Разделение ответственности:
Фильтрация данных выполняется один раз и сохраняется
Основной запрос упрощается для оптимизатора СУБД
2. Эффективное использование индексов:
Индексы на временной таблице ускоряют JOIN операции
Снижение стоимости случайного выбора (ORDER BY RANDOM())
3. Оптимизация работы с данными:
Уменьшение сложности запроса
Предсказуемое использование памяти
Более эффективное кэширование
📈 Инфраструктурные выводы
1. Для LEFT JOIN требуется:
Быстрые диски (SSD/NVMe)
Достаточный объем оперативной памяти для кэширования
Оптимизация индексов и запросов
2. Для TEMP TABLE требуется:
Мощные многоядерные процессоры
Контроль легковесных блокировок
Оптимизация использования временных объектов
🎯 Рекомендации по выбору подхода
Выбирать TEMP TABLE когда:
Система имеет мощные процессоры
Требуется высокая параллельная обработка
Важна предсказуемость производительности
Работа с большими объемами данных
Выбирать LEFT JOIN когда:
Система имеет быстрые диски
Ограниченные вычислительные ресурсы
Простые запросы с хорошей индексацией
Достаточный объем RAM для кэширования
💡 Ключевой вывод
Для условий высокой параллельной нагрузки и конкуренции за вычислительные ресурсы использование временных таблиц (TEMP TABLE) обеспечивает существенное увеличение производительности СУБД (+14.62%) и является более масштабируемым решением.
Подход TEMP TABLE лучше подходит для современных систем с многоядерными процессорами, обеспечивая более эффективное распределение нагрузки и предсказуемую производительность при работе с большими объемами данных.
