Ну не могу пока с этим вакуумом остановиться. В прошлой статье про него (И еще раз про VACUUM) я написал последний абзац и на некоторое время забыл про него:
А теперь на подумать — что будет с местом в таблице, если произойдет такой же случай, только реальная миллионно-первая строка останется в последней странице таблице, а миллион строк перед ней будут удалены?
Через некоторое время после публикации стал думать — а как точно проверить что произойдет? И решил руками проверить. Что ниже и показываю.
Сначала — всё стандартно. Создаем таблицу и отключаем по ней автовакуум (чтобы он нам не мешал, когда нужно — запустим руками):
CREATE TABLE t_vacuum (id INTEGER) WITH (autovacuum_enabled = off); CREATE TABLE
Вставляем в неё миллион строк:
INSERT INTO t_vacuum SELECT * FROM generate_series(1,1000000); INSERT 0 1000000
Смотрим размер таблицы:
\dt+ t_vacuum
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+----------+-------+-------+-------------+---------------+-------+-------------
public | t_vacuum | table | pavel | permanent | heap | 35 MB |
(1 row)
Проанализируем таблицу:
ANALYZE t_vacuum; ANALYZE
И посмотрим сколько страниц занимает таблица. Для этого обратимся к системному каталогу:
SELECT relpages FROM pg_class WHERE relname = 't_vacuum'; relpages ---------- 4425 (1 row)
Почти четыре с половиной тысячи страниц. Ту же информацию про страницы получим, если заглянем в план выполнения запроса, который будет выбирать всю информацию из таблицы:
EXPLAIN (analyze, buffers) SELECT * FROM t_vacuum;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Seq Scan on t_vacuum (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.031..152.357 rows=1000000 loops=1)
Buffers: shared hit=4425
Planning Time: 0.075 ms
Execution Time: 233.932 ms
(4 rows)
Обратите внимание на buffers — то же значение. Далее. Обновим все строки таблицы банальным запросом:
UPDATE t_vacuum SET id = id+2; UPDATE 1000000
Опять проанализируем таблицу:
ANALYZE t_vacuum; ANALYZE
И снова посмотрим сколько страниц она занимает:
SELECT relpages FROM pg_class WHERE relname = 't_vacuum'; relpages ---------- 8850 (1 row)
В плане запроса будет то же количество страниц. А вот количество строк останется без изменений — миллион «живых» строк. Посмотрим размер таблицы:
\dt+ t_vacuum
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+----------+-------+-------+-------------+---------------+-------+-------------
public | t_vacuum | table | pavel | permanent | heap | 69 MB |
(1 row)
Произведем очистку таблицы:
VACUUM VERBOSE t_vacuum; psql:t_vacuum processing.txt:42: INFO: vacuuming "public.t_vacuum" psql:t_vacuum processing.txt:42: INFO: "t_vacuum": removed 1000000 row versions in 4425 pages psql:t_vacuum processing.txt:42: INFO: "t_vacuum": found 1000000 removable, 1000000 nonremovable row versions in 8850 out of 8850 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1350 There were 0 unused item identifiers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.16 s, system: 0.00 s, elapsed: 0.16 s. VACUUM
Тут всё понятно — найден миллион строк которые можно удалить и миллион — которые нельзя. И количество страниц тоже указано, те же 8850 штук. Информации о том, что что-то было урезано — нет. И опять посмотрим размер таблицы:
\dt+ t_vacuum
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+----------+-------+-------+-------------+---------------+-------+-------------
public | t_vacuum | table | pavel | permanent | heap | 69 MB |
(1 row)
Без изменений. Как я уже писал ранее — место, освобожденное при очистке операционной системе не возвращается, но его можно повторно использовать. То есть, половина таблицы пуста и готова к использованию. И PostgreSQL позволяет заглянуть в эти пустые страницы (конечно, не только в них, можно и в заполненные посмотреть) с помощью расширения pageinspect, оно входит в поставку сервера, поэтому установить его можно очень просто:
CREATE EXTENSION pageinspect; CREATE EXTENSION
В его составе есть несколько полезных функций, к которым ниже и обратимся. Посмотрим что находится в середине таблицы. То есть, я загляну в страницу, которая была заполнена последней после добавления миллиона строк. Выше мы уже выяснили что последней заполненной страницей тогда была 4425-я страница, туда и посмотрим. Нумерация начинается с нулевой страницы, поэтому в запросе укажем (4425-1)ю страницу:
SELECT * FROM heap_page_items(get_raw_page('t_vacuum', 4424));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
-----+--------+----------+--------+--------+--------+----------+------------+-------------+------------+--------+--------+-------+------------
1 | 0 | 0 | 0 | | | | | | | | | |
2 | 0 | 0 | 0 | | | | | | | | | |
3 | 0 | 0 | 0 | | | | | | | | | |
4 | 0 | 0 | 0 | | | | | | | | | |
5 | 0 | 0 | 0 | | | | | | | | | |
6 | 0 | 0 | 0 | | | | | | | | | |
7 | 0 | 0 | 0 | | | | | | | | | |
8 | 0 | 0 | 0 | | | | | | | | | |
9 | 0 | 0 | 0 | | | | | | | | | |
10 | 0 | 0 | 0 | | | | | | | | | |
...
177 | 8160 | 1 | 28 | 1348 | 0 | 0 | (4424,177) | 1 | 10496 | 24 | | | \x03000000
178 | 8128 | 1 | 28 | 1348 | 0 | 0 | (4424,178) | 1 | 10496 | 24 | | | \x04000000
179 | 8096 | 1 | 28 | 1348 | 0 | 0 | (4424,179) | 1 | 10496 | 24 | | | \x05000000
180 | 8064 | 1 | 28 | 1348 | 0 | 0 | (4424,180) | 1 | 10496 | 24 | | | \x06000000
181 | 8032 | 1 | 28 | 1348 | 0 | 0 | (4424,181) | 1 | 10496 | 24 | | | \x07000000
182 | 8000 | 1 | 28 | 1348 | 0 | 0 | (4424,182) | 1 | 10496 | 24 | | | \x08000000
183 | 7968 | 1 | 28 | 1348 | 0 | 0 | (4424,183) | 1 | 10496 | 24 | | | \x09000000
184 | 7936 | 1 | 28 | 1348 | 0 | 0 | (4424,184) | 1 | 10496 | 24 | | | \x0a000000
185 | 7904 | 1 | 28 | 1348 | 0 | 0 | (4424,185) | 1 | 10496 | 24 | | | \x0b000000
186 | 7872 | 1 | 28 | 1348 | 0 | 0 | (4424,186) | 1 | 10496 | 24 | | | \x0c000000
Часть информации я не буду указывать. Но первая половина страницы полезными данными не заполнена (где нули стоят и больше ничего), никакой информации. Но туда можно добавлять новые строки. А вторая половина (у меня — с идентификатора 177 и далее до конца страницы) заполнена реальными данными. Там есть и идентификаторы транзакций, и сами данные (последний столбец).
Теперь, в рамках моего эксперимента, обновим одну строку из середины таблицы. Например, с идентификатором (поле lp) 177. Для этого в запросе укажу физическое расположение данной версии строки в таблице — системный столбец ctid, его мы подсмотрели в выводе расширения pageinspect:
UPDATE t_vacuum SET id = id+2 where ctid = '(4424,177)'; UPDATE 1
Посмотрим что у нас получилось в таблице, в той же странице. Сделаем это снова с помощью pageinspect:
SELECT * FROM heap_page_items(get_raw_page('t_vacuum', 4424));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
-----+--------+----------+--------+--------+--------+----------+------------+-------------+------------+--------+--------+-------+------------
1 | 6560 | 1 | 28 | 1350 | 0 | 0 | (4424,1) | 32769 | 10240 | 24 | | | \x05000000
2 | 0 | 0 | 0 | | | | | | | | | |
3 | 0 | 0 | 0 | | | | | | | | | |
4 | 0 | 0 | 0 | | | | | | | | | |
5 | 0 | 0 | 0 | | | | | | | | | |
6 | 0 | 0 | 0 | | | | | | | | | |
7 | 0 | 0 | 0 | | | | | | | | | |
8 | 0 | 0 | 0 | | | | | | | | | |
9 | 0 | 0 | 0 | | | | | | | | | |
10 | 0 | 0 | 0 | | | | | | | | | |
...
177 | 8160 | 1 | 28 | 1348 | 1350 | 0 | (4424,1) | 16385 | 8448 | 24 | | | \x03000000
178 | 8128 | 1 | 28 | 1348 | 0 | 0 | (4424,178) | 1 | 10496 | 24 | | | \x04000000
179 | 8096 | 1 | 28 | 1348 | 0 | 0 | (4424,179) | 1 | 10496 | 24 | | | \x05000000
180 | 8064 | 1 | 28 | 1348 | 0 | 0 | (4424,180) | 1 | 10496 | 24 | | | \x06000000
181 | 8032 | 1 | 28 | 1348 | 0 | 0 | (4424,181) | 1 | 10496 | 24 | | | \x07000000
182 | 8000 | 1 | 28 | 1348 | 0 | 0 | (4424,182) | 1 | 10496 | 24 | | | \x08000000
183 | 7968 | 1 | 28 | 1348 | 0 | 0 | (4424,183) | 1 | 10496 | 24 | | | \x09000000
184 | 7936 | 1 | 28 | 1348 | 0 | 0 | (4424,184) | 1 | 10496 | 24 | | | \x0a000000
185 | 7904 | 1 | 28 | 1348 | 0 | 0 | (4424,185) | 1 | 10496 | 24 | | | \x0b000000
186 | 7872 | 1 | 28 | 1348 | 0 | 0 | (4424,186) | 1 | 10496 | 24 | | | \x0c000000
Опять же, я вывод сократил. Но самое важное — оставил. Можно заметить что у нас в начало страницы была добавлена реальная строка, причем — добавлена она транзакцией 1350 (столбец t_xmin). А если найти ту строку, которую мы непосредственно обновляли, с lp 177 — она осталась, но немного изменилась. В частности, она помечена как удаленная, и удалила её как раз транзакция с идентификатором 1350 — столбец t_max. И столбец t_ctid обновился, он ведёт на первую строку на этой странице, как раз туда, где хранится актуальная версия строки.
Картина у нас сейчас такая — вторая половина таблицы (физически) заполнена данными, первая — пустая, но туда попадают новые версии строк. Теперь давайте удалим всё из этой таблицы, но (!) — кроме той строки, которую только что добавили, то есть той, у которой ctid равен (4424,1):
DELETE FROM t_vacuum WHERE ctid <> '(4424,1)'; DELETE 999999
Тут тоже всё логично — удалён миллион строк без одной. Опять посмотрим размер таблицы:
\dt+ t_vacuum
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+----------+-------+-------+-------------+---------------+-------+-------------
public | t_vacuum | table | pavel | permanent | heap | 69 MB |
(1 row)
Размер не изменился. Пока всё как обычно — мёртвые строки остались в таблицы, поэтому размер остался как был. Посмотрим сколько страниц занимает таблица:
SELECT relpages FROM pg_class WHERE relname = 't_vacuum';
relpages
----------
8850
(1 row)
То, что и было. А теперь посмотрим что находится в той же страницы 4425 после удаления всех строк:
SELECT * FROM heap_page_items(get_raw_page('t_vacuum', 4424));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
-----+--------+----------+--------+--------+--------+----------+------------+-------------+------------+--------+--------+-------+------------
1 | 6560 | 1 | 28 | 1350 | 0 | 0 | (4424,1) | 32769 | 10496 | 24 | | | \x05000000
2 | 0 | 0 | 0 | | | | | | | | | |
3 | 0 | 0 | 0 | | | | | | | | | |
4 | 0 | 0 | 0 | | | | | | | | | |
5 | 0 | 0 | 0 | | | | | | | | | |
6 | 0 | 0 | 0 | | | | | | | | | |
7 | 0 | 0 | 0 | | | | | | | | | |
8 | 0 | 0 | 0 | | | | | | | | | |
9 | 0 | 0 | 0 | | | | | | | | | |
10 | 0 | 0 | 0 | | | | | | | | | |
...
177 | 8160 | 1 | 28 | 1348 | 1350 | 0 | (4424,1) | 16385 | 9472 | 24 | | | \x03000000
178 | 8128 | 1 | 28 | 1348 | 1351 | 0 | (4424,178) | 8193 | 8448 | 24 | | | \x04000000
179 | 8096 | 1 | 28 | 1348 | 1351 | 0 | (4424,179) | 8193 | 8448 | 24 | | | \x05000000
180 | 8064 | 1 | 28 | 1348 | 1351 | 0 | (4424,180) | 8193 | 8448 | 24 | | | \x06000000
181 | 8032 | 1 | 28 | 1348 | 1351 | 0 | (4424,181) | 8193 | 8448 | 24 | | | \x07000000
182 | 8000 | 1 | 28 | 1348 | 1351 | 0 | (4424,182) | 8193 | 8448 | 24 | | | \x08000000
183 | 7968 | 1 | 28 | 1348 | 1351 | 0 | (4424,183) | 8193 | 8448 | 24 | | | \x09000000
184 | 7936 | 1 | 28 | 1348 | 1351 | 0 | (4424,184) | 8193 | 8448 | 24 | | | \x0a000000
185 | 7904 | 1 | 28 | 1348 | 1351 | 0 | (4424,185) | 8193 | 8448 | 24 | | | \x0b000000
186 | 7872 | 1 | 28 | 1348 | 1351 | 0 | (4424,186) | 8193 | 8448 | 24 | | | \x0c000000
Опять часть информации убрал. Все строки во второй половине страницы (начиная с lp 178) помеченные как удаленные транзакцией с идентификатором 1351. Это как раз наш DELETE выше. Для интереса, посмотрим на версии строк, относящиеся к той строке, которую мы ранее обновили:
SELECT * FROM heap_page_items(get_raw_page('t_vacuum', 4424)) where t_ctid = '(4424,1)';
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
-----+--------+----------+--------+--------+--------+----------+----------+-------------+------------+--------+--------+-------+------------
1 | 6560 | 1 | 28 | 1350 | 0 | 0 | (4424,1) | 32769 | 10496 | 24 | | | \x05000000
177 | 8160 | 1 | 28 | 1348 | 1350 | 0 | (4424,1) | 16385 | 9472 | 24 | | | \x03000000
(2 rows)
Тут можно xmin-xmax увидеть рядом, другие данные посмотреть. А теперь снова выполним очистку:
VACUUM VERBOSE t_vacuum; psql:t_vacuum processing.txt:82: INFO: vacuuming "public.t_vacuum" psql:t_vacuum processing.txt:82: INFO: "t_vacuum": removed 1000000 row versions in 4426 pages psql:t_vacuum processing.txt:82: INFO: "t_vacuum": found 1000000 removable, 1 nonremovable row versions in 4426 out of 8850 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1352 There were 175 unused item identifiers. Skipped 0 pages due to buffer pins, 4424 frozen pages. 0 pages are entirely empty. CPU: user: 0.14 s, system: 0.00 s, elapsed: 0.14 s. psql:t_vacuum processing.txt:82: INFO: "t_vacuum": truncated 8850 to 4425 pages DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s VACUUM
Найдена одна версия строки которую нельзя удалить, про страницы тоже имеется (1 nonremovable row versions in 4426 out of 8850 pages), мол, на 4426 страницах одна версия есть, которая не удалена.
Но появилась вот такая строка в выводе VACUUM’a: INFO: «t_vacuum»: truncated 8850 to 4425 pages — таблица была усечена, с 8850 до 4425 страниц. То есть, во время очистки часть свободного места, которое было в таблице — было отдано операционной системе. Давайте проверим и это. Посмотрим размер таблицы:
\dt+ t_vacuum
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+----------+-------+-------+-------------+---------------+-------+-------------
public | t_vacuum | table | pavel | permanent | heap | 35 MB |
(1 row)
В отличии от всех прочих раз, теперь размер таблицы действительно изменился, уменьшился в два раза. Проанализируем таблицу:
ANALYZE t_vacuum; ANALYZE
И посмотрим сколько страниц таблица занимает:
SELECT relpages FROM pg_class WHERE relname = 't_vacuum';
relpages
----------
4425
(1 row)
Без каких-либо VACUUM FULL и подобных команд таблица сократилась. А что там внутри этой таблицы? Заглянем в какую-нибудь страницу из начала таблицы:
SELECT * FROM heap_page_items(get_raw_page('t_vacuum', 4));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
-----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------
1 | 0 | 0 | 0 | | | | | | | | | |
2 | 0 | 0 | 0 | | | | | | | | | |
3 | 0 | 0 | 0 | | | | | | | | | |
4 | 0 | 0 | 0 | | | | | | | | | |
5 | 0 | 0 | 0 | | | | | | | | | |
6 | 0 | 0 | 0 | | | | | | | | | |
7 | 0 | 0 | 0 | | | | | | | | | |
8 | 0 | 0 | 0 | | | | | | | | | |
9 | 0 | 0 | 0 | | | | | | | | | |
10 | 0 | 0 | 0 | | | | | | | | | |
...
217 | 0 | 0 | 0 | | | | | | | | | |
218 | 0 | 0 | 0 | | | | | | | | | |
219 | 0 | 0 | 0 | | | | | | | | | |
220 | 0 | 0 | 0 | | | | | | | | | |
221 | 0 | 0 | 0 | | | | | | | | | |
222 | 0 | 0 | 0 | | | | | | | | | |
223 | 0 | 0 | 0 | | | | | | | | | |
224 | 0 | 0 | 0 | | | | | | | | | |
225 | 0 | 0 | 0 | | | | | | | | | |
226 | 0 | 0 | 0 | | | | | | | | | |
(226 rows)
Страница полностью пуста, туда можно помещать новые версии строк. А теперь заглянем в ту страницу, в которой у нас находится единственная «живая» версия строки:
SELECT * FROM heap_page_items(get_raw_page('t_vacuum', 4424));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
-----+--------+----------+--------+--------+--------+----------+----------+-------------+------------+--------+--------+-------+------------
1 | 8160 | 1 | 28 | 1350 | 0 | 0 | (4424,1) | 32769 | 10496 | 24 | | | \x05000000
2 | 0 | 0 | 0 | | | | | | | | | |
3 | 0 | 0 | 0 | | | | | | | | | |
4 | 0 | 0 | 0 | | | | | | | | | |
5 | 0 | 0 | 0 | | | | | | | | | |
6 | 0 | 0 | 0 | | | | | | | | | |
7 | 0 | 0 | 0 | | | | | | | | | |
8 | 0 | 0 | 0 | | | | | | | | | |
9 | 0 | 0 | 0 | | | | | | | | | |
10 | 0 | 0 | 0 | | | | | | | | | |
...
215 | 0 | 0 | 0 | | | | | | | | | |
216 | 0 | 0 | 0 | | | | | | | | | |
217 | 0 | 0 | 0 | | | | | | | | | |
218 | 0 | 0 | 0 | | | | | | | | | |
219 | 0 | 0 | 0 | | | | | | | | | |
220 | 0 | 0 | 0 | | | | | | | | | |
221 | 0 | 0 | 0 | | | | | | | | | |
222 | 0 | 0 | 0 | | | | | | | | | |
223 | 0 | 0 | 0 | | | | | | | | | |
224 | 0 | 0 | 0 | | | | | | | | | |
225 | 0 | 0 | 0 | | | | | | | | | |
226 | 0 | 0 | 0 | | | | | | | | | |
(226 rows)
На этой странице у нас эта «живая» строка и есть. И больше — ничего. А все остальные страницы, которые у нас были после этой строки были отрезаны обычным процессом очистки.
Ну и закончим эксперимент выполнив полную очистку таблицы:
VACUUM FULL VERBOSE t_vacuum; psql:t_vacuum processing.txt:104: INFO: vacuuming "public.t_vacuum" psql:t_vacuum processing.txt:104: INFO: "t_vacuum": found 0 removable, 1 nonremovable row versions in 4425 pages DETAIL: 0 dead row versions cannot be removed yet. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
Посмотрим размер:
\dt+ t_vacuum
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+----------+-------+-------+-------------+---------------+------------+-------------
public | t_vacuum | table | pavel | permanent | heap | 8192 bytes |
(1 row)
Размер кардинально изменился. Посмотрим сколько страниц занимает таблица:
SELECT relpages FROM pg_class WHERE relname = 't_vacuum';
relpages
----------
1
(1 row)
Всего одна страница. Заглянем в нее с помощью pageinspect:
SELECT * FROM heap_page_items(get_raw_page('t_vacuum', 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
1 | 8160 | 1 | 28 | 1350 | 0 | 0 | (0,1) | 1 | 11008 | 24 | | | \x05000000
(1 row)
И вот она, наша единственная строка.
Выводы
- Команда VACUUM (обычно) не возвращает освобожденное место операционной системе;
- Если в таблице нет «живых» строк (И еще раз про VACUUM) — тогда VACUUM отрежет все страницы, отдаст их ОС;
- И только что проверили, что VACUUM отрежет и вернёт ОС все «пустые» страницы, которые находятся в дальнем краю таблицы и после которых нет «живых» версий строк;
- Продолжение предыдущего пункта — но может возникнуть ситуация что мы эти «пустые» строки прикроем живой версией строки, тогда VACUUM ничего не отдаст операционной системе.
Leave a Reply