pg_pathman
pg_pathman copied to clipboard
postgrespro
Long planning time during autovacuum (to prevent wraparound)
Приветствую снова. Понял из соседнего топика что pathman заморожен. Очень печально. Всё же надеюсь на помощь по следующей проблеме.
Problem description
Если во время "autovacuum (to prevent wraparound)", бегущего на мастере, запустить select с фильтрацией по ключу секционирования (на реплике), то время планирования вырастает до неприличных размеров. То же самое происходит, если запустить vacuum freeze, но не происходит с просто vacuum. Причём вакуум может бежать даже на секции, которой нет в execution плане.
Пример. План выполнения без vacuum:
Limit (cost=0.57..1079.96 rows=1000 width=70) (actual time=0.132..10.285 rows=1000 loops=1)
-> Append (cost=0.57..591016.70 rows=547545 width=70) (actual time=0.131..10.129 rows=1000 loops=1)
-> Index Scan Backward using rounds_history_77_started_at_idx on rounds_history_77 r_1 (cost=0.57..591010.10 rows=547544 width=70) (actual time=0.131..10.015 rows=1000 loops=1)
Index Cond: ((started_at > '2018-05-11 00:00:00'::timestamp without time zone) AND (started_at < '2018-05-11 11:01:00'::timestamp without time zone))
Filter: (brand_id = 73)
Rows Removed by Filter: 21651
-> Index Scan Backward using idx_rounds_history_started_at on rounds_history r (cost=0.58..6.60 rows=1 width=70) (never executed)
Index Cond: ((started_at > '2018-05-11 00:00:00'::timestamp without time zone) AND (started_at < '2018-05-11 11:01:00'::timestamp without time zone))
Filter: (brand_id = 73)
Planning time: 0.723 ms
Execution time: 10.405 ms
При бегущем autovacuum: rounds_history_79 (to prevent wraparound):
Limit (cost=0.57..1079.96 rows=1000 width=70) (actual time=10.475..412.404 rows=1000 loops=1)
-> Append (cost=0.57..591016.70 rows=547545 width=70) (actual time=10.473..412.106 rows=1000 loops=1)
-> Index Scan Backward using rounds_history_77_started_at_idx on rounds_history_77 r_1 (cost=0.57..591010.10 rows=547544 width=70) (actual time=10.472..411.893 rows=1000 loops=1)
Index Cond: ((started_at > '2018-05-11 00:00:00'::timestamp without time zone) AND (started_at < '2018-05-11 11:01:00'::timestamp without time zone))
Filter: (brand_id = 73)
Rows Removed by Filter: 21651
-> Index Scan Backward using idx_rounds_history_started_at on rounds_history r (cost=0.58..6.60 rows=1 width=70) (never executed)
Index Cond: ((started_at > '2018-05-11 00:00:00'::timestamp without time zone) AND (started_at < '2018-05-11 11:01:00'::timestamp without time zone))
Filter: (brand_id = 73)
Planning time: 145640.316 ms
Execution time: 412.652 ms
Секции разбиты по:
"pathman_rounds_history_77_check" CHECK (started_at >= '2018-05-07 00:00:00'::timestamp without time zone AND started_at < '2018-05-14 00:00:00'::timestamp without time zone)
Сам SQL:
SELECT * FROM rounds_history AS r
WHERE brand_id = 73 AND started_at>'2018-05-11 00:00:00' AND started_at<'2018-05-11 11:01:00'
ORDER BY started_at DESC
LIMIT 1000 OFFSET 0;
Есть ли какие-то идеи?
Environment
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------------------+----------+--------------+----------------+------------+-----------------+--------------
plpgsql | 10 | 11 | f | 1.0 | |
pg_stat_statements | 10 | 2200 | t | 1.3 | |
postgres_fdw | 10 | 2200 | t | 1.0 | |
pgcrypto | 10 | 2200 | t | 1.2 | |
pg_cron | 10 | 2200 | f | 1.0 | {350179} | {""}
pg_pathman | 10 | 2200 | f | 1.4 | {350199,350210} | {"",""}
(6 rows)
PostgreSQL 9.5.10 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit
get_pathman_lib_version
10409 (1 row)
Понял из соседнего топика что pathman заморожен. Очень печально.
На самом деле, мы по-прежнему стараемся оперативно отвечать на вопросы. Кроме того, мы собираемся выпустить мигратор pg_pathman <=> vanilla.
Причём вакуум может бежать даже на секции, которой нет в execution плане.
Я думаю, это может быть связано с какими-то блокировками. Пробовали смотреть pg_locks?
Блокировки, отобранные по pid'у:
Первые секунд 50 такое:
obj_name | obj_type | procpid | locktype | mode | granted | page | tuple | transactionid | virtualtransaction
---------------------------------------------------+----------+---------+------------+-----------------+---------+------+-------+---------------+--------------------
rounds_history | table | 17764 | relation | AccessShareLock | t | | | | 16/7163665
idx_rounds_history_finish_at_br_gc_pl | index | 17764 | relation | AccessShareLock | t | | | | 16/7163665
idx_rounds_history_gc_br_start_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163665
idx_rounds_history_start_at_br_pl_gc | index | 17764 | relation | AccessShareLock | t | | | | 16/7163665
idx_rounds_history_pl_br_start_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163665
idx_rounds_history_brand_id_ins_at_finished_false | index | 17764 | relation | AccessShareLock | t | | | | 16/7163665
idx_rounds_history_brand_id_ins_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163665
idx_rounds_history_started_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163665
idx_rounds_history_player_game | index | 17764 | relation | AccessShareLock | t | | | | 16/7163665
idx_rounds_history_brand_id_finished_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163665
idx_rounds_history_finished_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163665
rounds_history_pkey | index | 17764 | relation | AccessShareLock | t | | | | 16/7163665
idx_rounds_history_pl_br_finish_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163665
| | 17764 | virtualxid | ExclusiveLock | t | | | | 16/7163665
(14 rows)
Потом ещё секунд 70 такое:
obj_name | obj_type | procpid | locktype | mode | granted | page | tuple | transactionid | virtualtransaction
------------------------------------------------------+----------+---------+------------+-----------------+---------+------+-------+---------------+--------------------
rounds_history_77 | table | 17764 | relation | AccessShareLock | t | | | | 16/7163666
rounds_history | table | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_pl_br_finish_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_finish_at_br_gc_pl | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_gc_br_start_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_start_at_br_pl_gc | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_pl_br_start_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_brand_id_ins_at_finished_false | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_brand_id_ins_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_started_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_player_game | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_brand_id_finished_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_finished_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
rounds_history_pkey | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_77_finish_at_br_gc_pl | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
rounds_history_77_pkey | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
rounds_history_77_finished_at_idx | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_77_gc_br_start_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
rounds_history_77_player_code_game_code_idx | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
rounds_history_77_brand_id_finished_at_idx | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_77_brand_id_ins_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
rounds_history_77_started_at_idx | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_77_pl_br_finish_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_77_pl_br_start_at | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_77_brand_id_ins_at_finished_false | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
idx_rounds_history_77_start_at_br_pl_gc | index | 17764 | relation | AccessShareLock | t | | | | 16/7163666
| | 17764 | virtualxid | ExclusiveLock | t | | | | 16/7163666
(27 rows)
Причём сейчас нету autovacuum ни на parent ни на children секции, но есть av wraparound на другие таблицы.