Пришла такая мысль — проверить как отразится на обращение к таблице с помощью seqscan наличией в ней большого количества «мертвых» строк. Ответ у меня есть, но решил его практически првоерить.
Кратко получилось вот так:
Кол-во строк (т.ч. мертвых) |
BUFFERS |
Размер табл. (МБ) |
Время запроса (МС) |
1 000 000 |
6 370 |
50 |
168,232 |
2 000 000 |
12 739 |
100 |
199,192 |
3 000 000 |
19 109 |
149 |
225,320 |
Подготовка:
Создаем таблицу сразу с отключенным для неё процессом автоочистки:
CREATE TABLE tabv (txt text) WITH (autovacuum_enabled = off);
Про автоочистку можно почитать, например, тут: «Хабр. MVCC-7. Автоочистка».
Вставляем туда миллион строк следующим образом:
INSERT INTO tabv SELECT (999^99*id)::text FROM generate_series(1,1000000) id;
На метод хранения столбца в этот раз обращать внимания не будем, всё по умолчанию.
SET max_parallel_workers_per_gather = 0;
Уберем этой настройкой процесс создания параллельных исполнителей — в данном случае они только отвелкать будут.
Эксперимент:
Выполняем запрос, который приведет к последовательному сканированию всей таблицы:
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) from tabv;
Смотрим план, обращаем внимание на BUFFERS — это число страниц, которое пришлось прочитать при работе. Очень важный показатель работы сервера:
QUERY PLAN ----------------------------------------------------------------------------------------------------------- Aggregate (cost=17199.00..17199.01 rows=1 width=8) (actual time=167.463..167.465 rows=1 loops=1) Buffers: shared hit=6370 -> Seq Scan on tabv (cost=0.00..15033.20 rows=866320 width=0) (actual time=0.048..97.770 rows=1000000 loops=1) Buffers: shared hit=6370 Planning Time: 0.135 ms Execution Time: 167.538 ms (6 rows) Time: 168,232 ms
Можно несколько раз запрос выполнить чтобы значение BUFFERS стабилизировалось на одном значении — 6370.
Посмотрим размер таблицы:
\dt+ tabv List of relations Schema | Name | Type | Owner | Persistence | Size | Description ----------+------+-------+----------+-------------+-------+------------- bookings | tabv | table | postgres | permanent | 50 MB |
А теперь обновим весь этот миллион строк, но сделаем это в отдельной транзакции:
BEGIN; UPDATE tabv set txt = (999^99*id)::text FROM generate_series(1,1) id;
И в той же транзакции повторим EXPLAIN который был выше:
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) from tabv; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Aggregate (cost=34395.30..34395.31 rows=1 width=8) (actual time=198.620..198.622 rows=1 loops=1) Buffers: shared hit=12739 -> Seq Scan on tabv (cost=0.00..30064.04 rows=1732504 width=0) (actual time=67.911..148.882 rows=1000000 loops=1) Buffers: shared hit=12739 Planning Time: 0.097 ms Execution Time: 198.677 ms (6 rows) Time: 199,192 ms
И снова можно несколько раз выполнить, чтобы BUFFERS стал постоянным. Смотрим на значением — 12739. Очень интересное значение, оно (почти) ровно в два раза выше чем предыдущее.
Обратите внимание на значение rows — миллион. Так как миллион видимых строк, а еще миллион мертвых в плане не отображается.
Посмотрим размер таблицы:
\dt+ tabv List of relations Schema | Name | Type | Owner | Persistence | Size | Description ----------+------+-------+----------+-------------+--------+------------- bookings | tabv | table | postgres | permanent | 100 MB |
Опять обновим миллион строк:
UPDATE tabv set txt = (999^99*id)::text FROM generate_series(1,1) id;
И выполним тот же запрос:
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) from tabv; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Aggregate (cost=51594.30..51594.31 rows=1 width=8) (actual time=224.956..224.957 rows=1 loops=1) Buffers: shared hit=16352 read=2757 -> Seq Scan on tabv (cost=0.00..45097.24 rows=2598824 width=0) (actual time=112.414..181.315 rows=1000000 loops=1) Buffers: shared hit=16352 read=2757 Planning Time: 0.052 ms Execution Time: 224.992 ms (6 rows) Time: 225,320 ms
Снова смотрим размер таблицы:
\dt+ tabv List of relations Schema | Name | Type | Owner | Persistence | Size | Description ----------+------+-------+----------+-------------+--------+------------- bookings | tabv | table | postgres | permanent | 149 MB |
В этот раз не вот такое вот получилось с BUFFERS. Но ничего страшного, суммируем и получаем 19109 — в три раза больше, чем наше первоначальное значение BUFFERS. И, раз автовакуум не чистит нашу таблицу — тогда у нас стало в три раза больше строк в таблице (хотя актуальных, по прежнему, один миллион).
А теперь отменяем транзакцию:
ROLLBACK;
И снова запускаем тот же запрос выше:
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) from tabv; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Aggregate (cost=51594.30..51594.31 rows=1 width=8) (actual time=187.201..187.203 rows=1 loops=1) Buffers: shared hit=16280 read=2829 -> Seq Scan on tabv (cost=0.00..45097.24 rows=2598824 width=0) (actual time=0.033..112.328 rows=1000000 loops=1) Buffers: shared hit=16280 read=2829 Planning Time: 0.095 ms Execution Time: 187.259 ms (6 rows) Time: 187,873 ms
И снова в BUFFERS прошлое (максимальное) значение 19109. Как так? Транзакцию же отменили, всё успешно откатилось?
Кстати, размер таблицы — тот же, максимальный:
\dt+ tabv List of relations Schema | Name | Type | Owner | Persistence | Size | Description ----------+------+-------+----------+-------------+--------+------------- bookings | tabv | table | postgres | permanent | 149 MB |
Ответ кратко — потому что PostgreSQL использует версионирование (MVCC): во время обновления строк (UPDATE) помечает их как удаленные и создает новые, с новыми значениями. А помеченные как удаленные строки не удаляются сразу — их удаляет специальный процесс (AUTO)VACUUM, который мы для этой таблицы отключили.
Ответ подробнее — в курсе DBA2. Настройка и мониторинг.
Добьем эксперимент — запустим процесс очистки руками и повторим запрос:
VACUUM tabv; EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) from tabv; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Aggregate (cost=18870.00..18870.01 rows=1 width=8) (actual time=153.674..153.675 rows=1 loops=1) Buffers: shared hit=3850 read=2520 -> Seq Scan on tabv (cost=0.00..16370.00 rows=1000000 width=0) (actual time=0.019..90.680 rows=1000000 loops=1) Buffers: shared hit=3850 read=2520 Planning Time: 0.068 ms Execution Time: 153.718 ms (6 rows) Time: 154,229 ms
В BUFFERS опять те же 6370, которые были изначально.
Глянем размер таблицы напоследок:
\dt+ tabv List of relations Schema | Name | Type | Owner | Persistence | Size | Description ----------+------+-------+----------+-------------+-------+------------- bookings | tabv | table | postgres | permanent | 50 MB |
Опять в три раза меньше, как и было в самом начале. Пусть будет так.
Еще разок таблица с результатами:
Кол-во строк (т.ч. мертвых) |
BUFFERS |
Размер табл. (МБ) |
Время запроса (МС) |
1 000 000 |
6 370 |
50 |
168,232 |
2 000 000 |
12 739 |
100 |
199,192 |
3 000 000 |
19 109 |
149 |
225,320 |
Тут и на время можно обратить внимание, оно тоже увеличивается.
Так что не забывайте про правильные настройки автовакуума.
Leave a Reply