pg_expecto + Демобаза 2.0: тестовый стенд для экспериментов с СУБД PostgreSQL / forpes.ru

Главная
pg_expecto + Демобаза 2.0: тестовый стенд для экспериментов с СУБД PostgreSQL

pg_expecto + Демобаза 2.0: тестовый стенд для экспериментов с СУБД PostgreSQL -1

23.11.2025 06:35

pg_expecto 0 112 Источник

СУБД — не чёрный ящик, а объект для экспериментов.

Нагрузочное тестирование — это не просто «нагрузить систему до падения». Это точный инструмент для поиска причинно-следственных связей. В этой статье описан пример использования связки из Демобазы 2.0 и комплекса pg_expecto, чтобы провести контролируемый эксперимент. Изменим один SQL-запрос, запустим тест и проанализируем, как это изменение отразилось на производительности СУБД и показателях инфраструктуры.

ℹ️ Демобаза 2.0

Демобаза 2.0 для PostgreSQL / Хабр

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

kznalp/PG_EXPECTO: Комплекс статистического анализа производительности СУБД PostgreSQL

pg-expecto/pg_expecto: Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL

Постановка эксперимента

Оценить влияние изменения паттерна отдельного SQL запроса на производительность СУБД и показатели инфраструктуры в ходе нагрузочного тестирования

Тестовая виртуальная машина

CPU = 8

RAM = 8GB

PostgreSQL 17

Генерация Демобазы 2.0

Заполнение данных

Подключитесь в psql к любой базе, кроме demo, убедитесь, что находитесь 
в каталоге репозитория, и выполните установку:
\! pwd
\i install
При необходимости смените текущий каталог командой \cd.
В ходе установки будет заново создана база данных demo. 
Если она существовала, то все данные в ней будут потеряны! 
В этой базе данных будут созданы две схемы: 
gen для объектов генератора и bookings для создаваемой демобазы.
Устанавливаются расширения btree_gist (для реализации темпорального ключа), 
earthdistance и cube (для расчета расстояний на сфере), 
а также dblink (для запуска параллельных процессов). 
Эти расширения входят в стандартный набор, но убедитесь, 
что они присутствуют в вашей установке PostgreSQL.

Генерация запускается процедурой generate, 
которой передаются начальное и конечное модельное время 
(которое будет фигурировать в таблицах демобазы). 

Например:
CALL generate( now(), now() + interval '2 year' );
Такая команда выполнит необходимую инициализацию, 
создаст очередь событий и начнет генерацию демобазы за 2 года.

postgrespro/demodb: Demonstration Database

План нагрузочного тестирования (конфигурационный файл param.conf)

param.conf

# НАСТРОЙКИ НАГРУЗОЧНОГО ТЕСТИРОВАНИЯ
# Максимальная нагрузка
finish_load = 20
# Тестовая БД
testdb = demo
# Веса сценариев
scenario1 = 1.0
scenario2 = 1.0
scenario3 = 1.0
scenario4 = 1.0

Тестовый сценарий-1 : Простой точечный SELECT по первичному ключу.

scenario1.sql

CREATE OR REPLACE FUNCTION scenario2() RETURNS integer AS $$
DECLARE
test_rec record ;
test_code text ;
BEGIN
  SET application_name = 'scenario1';

  SELECT
    array_to_string(array(select substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',((random()*(26-1)+1)::integer),1) from generate_series(1,3)),'')
  INTO
    test_code ;

  SELECT
    *
  INTO
    test_rec
  FROM
    airports_data
  WHERE
    airport_code = test_code ;

return 0 ;
END
$$ LANGUAGE plpgsql;

Тестовый сценарий-2 : GROUP BY .

scenario2.sql

CREATE OR REPLACE FUNCTION scenario3() RETURNS integer AS $$
DECLARE
test_rec record ;
BEGIN
  SET application_name = 'scenario2';

  WITH empty_flights AS
  (
    SELECT f.flight_id, count( bp.flight_id )
    FROM bookings.flights f
    LEFT JOIN bookings.boarding_passes bp ON bp.flight_id = f.flight_id
    WHERE f.status IN ( 'Departed', 'Arrived' )
    GROUP BY f.flight_id
    HAVING count( bp.flight_id ) = 0
  )
  SELECT count(*) empty,
    CASE WHEN count(*) > 0 THEN 'WARNING: empty cabin' ELSE 'Ok' END verdict
  INTO test_rec
  FROM empty_flights;

return 0 ;
END
$$ LANGUAGE plpgsql;

Тестовый сценарий-3 : ORDER BY ... LIMIT

scenario3.sql

CREATE OR REPLACE FUNCTION scenario4() RETURNS integer AS $$
DECLARE
test_rec record ;
test_limit integer ;
BEGIN
  SET application_name = 'scenario3';

  SELECT random() * 1000 + 1
  INTO test_limit ;

  SELECT a.airplane_code, a.model->>'en' AS model,
    count(DISTINCT r.route_no) AS no_flights,
    CASE
      WHEN count(DISTINCT r.route_no) > 0 AND a.in_use THEN 'Ok'
      WHEN count(DISTINCT r.route_no) = 0 AND a.in_use THEN 'NOT USED'
      WHEN count(DISTINCT r.route_no) > 0 AND NOT a.in_use THEN 'WRONGLY USED'
      WHEN count(DISTINCT r.route_no) = 0 AND NOT a.in_use THEN 'Ok (not in use)'
    END AS verdict
  INTO test_rec
  FROM bookings.routes r
  RIGHT JOIN gen.airplanes_data a ON a.airplane_code = r.airplane_code
  GROUP BY a.airplane_code, a.model, a.in_use
  ORDER BY a.airplane_code
  LIMIT test_limit ;

return 0 ;
END
$$ LANGUAGE plpgsql;

Тестовый сценарий-4.1 : JOIN

scenario4.sql

CREATE OR REPLACE FUNCTION scenario5() RETURNS integer AS $$
DECLARE
test_rec record ;
BEGIN
  SET application_name = 'scenario4';

  WITH seats_available AS
  ( SELECT airplane_code, fare_conditions, count( * ) AS seats_cnt
    FROM bookings.seats
    GROUP BY airplane_code, fare_conditions
  ), seats_booked AS
  ( SELECT flight_id, fare_conditions, count( * ) AS seats_cnt
    FROM bookings.segments
    GROUP BY flight_id, fare_conditions
  ), overbook AS (
  SELECT f.flight_id, r.route_no, r.airplane_code, sb.fare_conditions,
    sb.seats_cnt AS seats_booked,
    sa.seats_cnt AS seats_available
  FROM bookings.flights AS f
  JOIN bookings.routes AS r ON r.route_no = f.route_no AND r.validity @> f.scheduled_departure
  JOIN seats_booked AS sb ON sb.flight_id = f.flight_id
  JOIN seats_available AS sa ON sa.airplane_code = r.airplane_code
  AND sa.fare_conditions = sb.fare_conditions
  WHERE sb.seats_cnt > sa.seats_cnt
  )
  SELECT count(*) overbookings,
    CASE WHEN count(*) > 0 THEN 'ERROR: overbooking' ELSE 'Ok' END verdict
  INTO test_rec
  FROM overbook;

return 0 ;
END
$$ LANGUAGE plpgsql;

Тестовый сценарий-4.2 : Условный "коррелированный подзапрос"

Создание индексов

demo=# CREATE INDEX CONCURRENTLY idx_seats_airplane_fare ON bookings.seats(airplane_code, fare_conditions);
CREATE INDEX
demo=# CREATE INDEX CONCURRENTLY idx_segments_flight_fare ON bookings.segments(flight_id, fare_conditions);
CREATE INDEX
demo=# CREATE INDEX CONCURRENTLY idx_routes_no_validity ON bookings.routes(route_no, validity);
CREATE INDEX

scenario4.sql

CREATE OR REPLACE FUNCTION scenario5() RETURNS integer AS $$
DECLARE
test_rec record ;
BEGIN
  SET application_name = 'scenario4';

  WITH seats_agg AS MATERIALIZED (
  SELECT
    airplane_code,
    fare_conditions,
    COUNT(*) AS seats_total
  FROM bookings.seats
  GROUP BY airplane_code, fare_conditions
  )
  SELECT
    COUNT(*) AS overbookings,
    CASE WHEN COUNT(*) > 0 THEN 'ERROR: overbooking' ELSE 'Ok' END AS verdict
    INTO test_rec
  FROM (
    SELECT 1
     FROM bookings.flights f
     JOIN bookings.routes r ON r.route_no = f.route_no AND r.validity @> f.scheduled_departure
     JOIN (
     SELECT
      flight_id,
      fare_conditions,
      COUNT(*) AS seats_booked
      FROM bookings.segments
      GROUP BY flight_id, fare_conditions
    ) sb ON sb.flight_id = f.flight_id
    WHERE sb.seats_booked > (
     SELECT sa.seats_total
     FROM seats_agg sa
     WHERE sa.airplane_code = r.airplane_code
     AND sa.fare_conditions = sb.fare_conditions
   )
  ) overbooked;

Планы выполнения тестовых запросов с использованием JOIN и "Коррелированного подзапроса"

План выполнения сценарий-4.1 : JOIN

QUERY PLAN

 Aggregate  (cost=9825.94..9825.95 rows=1 width=40) (actual time=262.702..262.707 rows=1 loops=1)
   ->  Hash Join  (cost=9431.95..9825.94 rows=1 width=0) (actual time=262.696..262.701 rows=0 loops=1)
         Hash Cond: ((f.route_no = r.route_no) AND (seats.airplane_code = r.airplane_code))
         Join Filter: (r.validity @> f.scheduled_departure)
         Rows Removed by Join Filter: 217
         ->  Nested Loop  (cost=9407.50..9796.79 rows=567 width=19) (actual time=218.641..259.306 rows=11355 loops=1)
               ->  Hash Join  (cost=9407.22..9623.25 rows=567 width=8) (actual time=218.539..235.320 rows=11355 loops=1)
                     Hash Cond: (segments.fare_conditions = seats.fare_conditions)
                     Join Filter: ((count(*)) > (count(*)))
                     Rows Removed by Join Filter: 66545
                     ->  HashAggregate  (cost=9366.21..9507.87 rows=14166 width=20) (actual time=217.266..219.770 rows=10888 loops=1)
                           Group Key: segments.flight_id, segments.fare_conditions
                           Batches: 1  Memory Usage: 1425kB
                           ->  Seq Scan on segments  (cost=0.00..6654.55 rows=361555 width=12) (actual time=0.071..90.350 rows=361489 loops=1)
                     ->  Hash  (cost=40.71..40.71 rows=24 width=20) (actual time=1.228..1.230 rows=20 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 10kB
                           ->  HashAggregate  (cost=40.47..40.71 rows=24 width=20) (actual time=1.205..1.211 rows=20 loops=1)
                                 Group Key: seats.airplane_code, seats.fare_conditions
                                 Batches: 1  Memory Usage: 24kB
                                 ->  Seq Scan on seats  (cost=0.00..27.41 rows=1741 width=12) (actual time=0.059..0.420 rows=1741 loops=1)
               ->  Index Scan using flights_pkey on flights f  (cost=0.28..0.31 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=11355)
                     Index Cond: (flight_id = segments.flight_id)
         ->  Hash  (cost=15.78..15.78 rows=578 width=33) (actual time=0.631..0.632 rows=578 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 45kB
               ->  Seq Scan on routes r  (cost=0.00..15.78 rows=578 width=33) (actual time=0.083..0.375 rows=578 loops=1)
 Planning Time: 5.779 ms
 Execution Time: 263.774 ms

План выполнения сценарий-4.2 : "Коррелированный подзапрос"

QUERY PLAN

Aggregate  (cost=334506.18..334506.19 rows=1 width=40) (actual time=12894.579..12899.785 rows=1 loops=1)
   CTE seats_agg
     ->  HashAggregate  (cost=40.47..40.71 rows=24 width=20) (actual time=0.751..0.757 rows=20 loops=1)
           Group Key: seats.airplane_code, seats.fare_conditions
           Batches: 1  Memory Usage: 24kB
           ->  Seq Scan on seats  (cost=0.00..27.41 rows=1741 width=12) (actual time=0.024..0.203 rows=1741 loops=1)
   ->  Hash Join  (cost=326910.78..334463.31 rows=862 width=0) (actual time=12894.575..12899.777 rows=0 loops=1)
         Hash Cond: (segments.flight_id = f.flight_id)
         Join Filter: ((count(*)) > (SubPlan 2))
         Rows Removed by Join Filter: 249660
         ->  Finalize HashAggregate  (cost=315588.67..318101.77 rows=251310 width=20) (actual time=4473.982..4907.592 rows=249660 loops=1)
               Group Key: segments.flight_id, segments.fare_conditions
               Batches: 1  Memory Usage: 28177kB
               ->  Gather  (cost=1000.44..308049.37 rows=1005240 width=20) (actual time=18.306..4125.482 rows=253416 loops=1)
                     Workers Planned: 4
                     Workers Launched: 4
                     ->  Partial GroupAggregate  (cost=0.44..206525.37 rows=251310 width=20) (actual time=0.606..4288.777 rows=50683 loops=5)
                           Group Key: segments.flight_id, segments.fare_conditions
                           ->  Parallel Index Only Scan using idx_segments_flight_fare on segments  (cost=0.44..167521.05 rows=4865495 width=12) (actual time=0.072..2456.531 rows=3892859 loops=5)
                                 Heap Fetches: 271389
         ->  Hash  (cost=11309.28..11309.28 rows=1026 width=8) (actual time=4365.785..4365.789 rows=99609 loops=1)
               Buckets: 131072 (originally 2048)  Batches: 1 (originally 1)  Memory Usage: 4915kB
               ->  Nested Loop  (cost=0.42..11309.28 rows=1026 width=8) (actual time=0.061..4277.802 rows=99609 loops=1)
                     ->  Seq Scan on routes r  (cost=0.00..148.02 rows=5502 width=33) (actual time=0.019..1.508 rows=5502 loops=1)
                     ->  Index Scan using flights_route_no_scheduled_departure_key on flights f  (cost=0.42..2.02 rows=1 width=19) (actual time=0.408..0.772 rows=18 loops=5502)
                           Index Cond: (route_no = r.route_no)
                           Filter: (r.validity @> scheduled_departure)
                           Rows Removed by Filter: 168
         SubPlan 2
           ->  CTE Scan on seats_agg sa  (cost=0.00..0.60 rows=1 width=8) (actual time=0.005..0.011 rows=1 loops=249660)
                 Filter: ((airplane_code = r.airplane_code) AND (fare_conditions = segments.fare_conditions))
                 Rows Removed by Filter: 19
 Planning Time: 5.223 ms
 Execution Time: 12929.075 ms

Анализ результатов нагрузочного тестирования - производительность СУБД

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

График изменения количества параллельных сессий pgbench в ходе нагрузочного тестирования

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

График изменения операционной скорости СУБД для нагрузочного тестирования с использованием Join и Коррелированного подзапроса.

График изменения относительной разницы операционной скорости для нагрузочного тестирования с использованием коррелированного подзапроса по сравнению с использованием JOIN.

Средняя разница операционной скорости СУБД при использовании JOIN и Коррелированного подзапроса составила 0.58%.

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

График изменения операционной скорости тестового сценария с использованием Join и Коррелированного подзапроса.

График изменения относительной разницы операционной скорости тестового сценария тестирования с использованием коррелированного подзапроса по сравнению с использованием JOIN.

Средняя разница операционной скорости тестового запроса при использовании JOIN и "Коррелированного подзапроса" составила 2.44%.

Ожидания типа LWLock

График изменения ожиданий типа LWLock для нагрузочного тестирования с использованием Join и Коррелированного подзапроса.

График изменения относительной разницы ожиданий типа LWLock для нагрузочного тестирования с использованием коррелированного подзапроса по сравнению с использованием JOIN.

С ростом нагрузки, количество ожиданий типа LWLock - снижается, при использовании коррелированного подзапроса, в сравнении с использованием JOIN.

Вывод по результатам анализа метрик производительности СУБД

Использование для тестового запроса JOIN или Коррелированного подзапроса - не оказывает существенного влияния на производительность СУБД в целом и тестового сценария в частности.