По умолчанию утилита pgbench запускает тест "TPC-B (sort of)" и используется для быстрой оценки производительности PostgreSQL. Горизонт удерживается на время выполнения любого запроса или транзакции. В статье рассматривается насколько удержание горизонта базы данных (vacuum cleanup horizon) уменьшает прозводительность (например, значение tps тестов). Также приводится запрос для мониторинга горизонта баз данных и параметры, которые используются для защиты от долгих транзакций и запросов.
Посмотрим как наличие запроса или транзакции, выполняющиеся какое-то время влияют на резулитаты тестов. Запросы в реальных базах могут длиться от секунд до часов. При тестировании же обычно запросв и транзакций в базе нет - в ней работает только тест pgbench. Он показывает tps. Однако, при реальной эксплуатации tps оказываются ниже или колеблются в широких пределах. Колебания метрики tpmC можно наблюдать в более сложном тесте TPROC-C программы тестирования HammerDB и без удержания горизонта, так как горизонт удерживается и сдвигается при работе самого теста.
Подготовим и запустим тест. Перед запуском теста нужно создать или пересоздать тестовые таблицы. Для этого используется команда: 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.
Пример мониторинга проблемных запросов с помощью pg_stat_kcache:
select round(s.total_exec_time::numeric, 0) time, s.calls, pg_size_pretty(exec_minflts*4096) recl, pg_size_pretty(exec_majflts*4096) faults, pg_size_pretty(k.exec_reads) reads, pg_size_pretty(k.exec_writes) writes, round(k.exec_user_time::numeric, 2) user, round(k.exec_system_time::numeric, 2) sys, k.exec_nvcsws vsw, k.exec_nivcsws isw, left(s.query, 27) query from pg_stat_statements s join pg_stat_kcache() k using (userid, dbid, queryid) join pg_database d on s.dbid = d.oid order by total_exec_time desc limit 5;
time | calls | recl | faults | reads | writes | user | sys | vsw | isw | query
--------+-------+---------+---------+---------+---------+--------+------+-----+------+-----------------------------
274598 | 15795 | 560 kB | 0 bytes | 0 bytes | 560 kB | 272.55 | 2.30 | 375 | 3320 | UPDATE pgbench_branches SET
48929 | 15795 | 1049 MB | 0 bytes | 0 bytes | 680 kB | 44.35 | 4.98 | 267 | 527 | UPDATE pgbench_tellers SET
671 | 15794 | 2204 kB | 0 bytes | 0 bytes | 2184 kB | 0.89 | 0.09 | 0 | 21 | UPDATE pgbench_accounts SET
268 | 15795 | 812 kB | 0 bytes | 0 bytes | 808 kB | 0.56 | 0.03 | 0 | 16 | INSERT INTO pgbench_history
268 | 15794 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | 0.49 | 0.06 | 0 | 13 | SELECT abalance FROM pgbenc
(5 rows)Наиболее проблемная команда: UPDATE на pgbench_branches.
isw - планировщик вытеснял процесс
user/sys пропрорции. user намного больше sys и код, выполнявший команду неоптимален
time процессорное время
В параллельных сессиях запустим транзакции, которые будут удерживать горизонт базы данных. Примеры команды для удержания горизонта транзакцией:
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 failedTPS постепенно снижаются. Горизонт стал удерживаться примерно на 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), так и на неявно начатые транзакции, соответствующие отдельной команде.
