Влияние удержания горизонта базы данных PostgreSQL на производительность по тесту pgbench

По умолчанию pgbench запускает тест «TPC-B (sort of)» и используется для быстрой оценки производительности PostgreSQL. В статье рассматривается насколько удержание горизонта базы данных (vacuum cleanup horizon) уменьшает прозводительность (значение tps теста). Горизонт удерживается на время выполнения любого запроса или транзакции. Приводится запрос для наблюдения за горизонтом баз данных и параметры, которые используются для защиты от долгих транзакций и запросов.

Перед запуском теста нужно создать или пересоздать тестовые таблицы. Для этого используется команда: pgbench -i

Запустим стандартный тест, указав длительность 10000 секунд  с выдачей промежуточных результатов раз в 120 секунд:

pgbench -T 10000 -P 120  pgbench (17.0 (Ubuntu 17.0-1.pgdg22.04+1))  starting vacuum...end.  progress: 120.0 s, 583.4 tps, lat 1.711 ms stddev 1.877, 0 failed  progress: 240.0 s, 579.9 tps, lat 1.722 ms stddev 1.830, 0 failed  progress: 360.0 s, 597.5 tps, lat 1.671 ms stddev 1.702, 0 failed  progress: 480.0 s, 596.5 tps, lat 1.674 ms stddev 1.730, 0 failed

Результат теста обычный: примерно 597 tps.

В параллельных сессиях запустим транзакции, которые будут удерживать горизонт базы данных. Примеры команды для удержания горизонта транзакцией:

postgres=# select pg_backend_pid();   pg_backend_pid  ----------------            42775  (1 row)  postgres=# begin;  BEGIN  postgres=*# select pg_current_xact_id();  pg_current_xact_id   --------------------              7165718  (1 row)

Вызов функции, выдающий номер транзакции начинает транзакцию. Вместо вызова функции можно использовать команды insert, create и т.п.

Транзакция для удержания горизонта моментальным снимком: 

postgres=# select pg_backend_pid();   pg_backend_pid  ----------------            42791  (1 row)  postgres=# begin transaction isolation level repeatable read;  BEGIN  postgres=*# select 1;   ?column?   ----------          1  (1 row)

Версии строк, не вышедшие за горизонт базы данных не могут очищаться ни вакуумум, ни быстрой очисткой (HOT cleanup).

Запрос, который показывает горизонт базы данных:

postgres=# select pid, extract(epoch from (clock_timestamp()-xact_start)) secs, age(backend_xmin), age(backend_xid), datname database, state from pg_stat_activity where backend_xmin IS NOT NULL OR backend_xid IS NOT NULL order by 2 desc;    pid  |    secs    |  age  |  age  | database |        state  -------+------------+-------+-------+----------+---------------------   42775 | 867.497022 |       | 69101 | postgres | idle in transaction   42791 | 861.716918 | 69101 |       | postgres | idle in transaction   42864 |   0.018711 |       |     1 | postgres | active   42845 |   0.007854 | 69101 |       | postgres | active  (4 rows)

Запрос учитывает и транзакции и снимки, удерживающие горизонт (два столбца с псевдонимами age).

Горизонт удерживается 867 секунд или в номерах транзакций: 69101 номеров транзакций, отстоящих от текущей. Вторая транзакция была запущена на 6 секунд позже. Если первая транзакция завершится, то горизонт немного продвинется горизонт будет удерживать вторая транзакция. Вторая транзакция эквивалентна долгому запросу (одиночному SELECT). Одиночный SELECT будет удерживать моментальный снимок до окончания своего выполнения.

Пока удерживается горизонт в окне с тестом начинают меняться значения TPS:

 progress: 480.0 s, 596.5 tps, lat 1.674 ms stddev 1.730, 0 failed  progress: 600.0 s, 315.3 tps, lat 3.168 ms stddev 2.806, 0 failed  progress: 720.0 s, 135.6 tps, lat 7.369 ms stddev 6.537, 0 failed  ..  progress: 1080.0 s, 128.3 tps, lat 7.790 ms stddev 4.536, 0 failed  progress: 1200.0 s, 126.0 tps, lat 7.933 ms stddev 1.957, 0 failed  progress: 1320.0 s, 113.2 tps, lat 8.830 ms stddev 1.440, 0 failed  progress: 1440.0 s, 101.9 tps, lat 9.813 ms stddev 2.092, 0 failed  progress: 1560.0 s, 93.0 tps, lat 10.746 ms stddev 3.057, 0 failed  progress: 1680.0 s, 86.7 tps, lat 11.535 ms stddev 4.033, 0 failed  progress: 1800.0 s, 80.5 tps, lat 12.421 ms stddev 3.771, 0 failed  ..  progress: 2280.0 s, 68.3 tps, lat 14.633 ms stddev 2.571, 0 failed  progress: 2400.0 s, 66.2 tps, lat 15.104 ms stddev 2.074, 0 failed  progress: 2520.0 s, 63.0 tps, lat 15.861 ms stddev 3.670, 0 failed  ..  progress: 3000.0 s, 56.6 tps, lat 17.661 ms stddev 2.598, 0 failed  progress: 3120.0 s, 54.9 tps, lat 18.203 ms stddev 3.860, 0 failed

 TPS постепенно снижаются. Горизонт стал удерживаться примерно на 500 секунде. На 1440 секунде, то есть примерно через 15 минут TPS снизился примерно в шесть раз: до 101.9 tps. Удержания горизонта базы данных не даёт вычищать старые версии строк и приводит к раздуванию (bloat) файлов отношений.

После завершния транзакций, удерживающих горизонт базы данных, TPS почти сразу восстановился:

progress: 3240.0 s, 54.1 tps, lat 18.496 ms stddev 2.579, 0 failed progress: 3360.0 s, 52.2 tps, lat 19.148 ms stddev 3.184, 0 failed progress: 3480.0 s, 50.3 tps, lat 19.895 ms stddev 4.295, 0 failed progress: 3600.0 s, 251.7 tps, lat 3.971 ms stddev 6.235, 0 failed progress: 3720.0 s, 618.9 tps, lat 1.613 ms stddev 1.124, 0 failed ^C

Удержание горизонта снимками или транзакциями снижает производительность. Если оставить транзакции на 10 часов, то TPS упадёт до четырёх транзакций в секунду. Выгрузка данных утилитой pg_dump удерживает горизонт базы на время работы утилиты, так как pg_dump использует моментальный снимок, чтобы выгрузить данные согласованно. pg_dump можно запускать на физической реплике без обратной связи, тогда работа утилиты не будет влиять на мастер. Горизонт могут удерживать реплики с обратной связью. Долгие запросы стоит переносить на реплики и не включать на реплицах обратную связь. По умолчанию обратная связь не включена (hot_standby_feedback=off). Если обратная свзь включена, то горизонт (удерживается на всех базах) можно найти запросами:

select max(age(xmin)) from pg_replication_slots;  select backend_xmin, application_name from pg_stat_replication order by  age(backend_xmin) desc;

В самих репликах искать процессы, выполняющие команды, удерживающие горизонт можно так же, как и на мастере — запросом к pg_stat_activity.

Для ограничения удержания горизонта в 17 версии используются параметры: idle_in_transaction_session_timeout, idle_session_timeout, statement_timeout, transaction_timeout.

Параметры statement_timeout и idle_session_timeout не защищают от транзакций, состоящих из серии недолгих команд и коротких пауз между ними (например, длинная серия быстрых UPDATE в цикле). Для защиты от долгих команд SELECT до 17 версии может использоваться параметр old_snapshot_threshold. Его не стоит устанавливать на физических репликах. В 17 версии old_snapshot_threshold убран и transaction_timeout позволяет его заменить.

Параметр transaction_timeout позволяет отменить не только простаивающую, но и любую транзакцию или одиночную команду, длительность которой превышает указанный период времени. Действие параметра распространяется как на явные транзакции (начатые с помощью команды BEGIN), так и на неявно начатые транзакции, соответствующие отдельной команде.