И еще одна статья-перевод. Об этой теме я уже раньше писал (но не с такой степенью детальности) — SeqScan из не вакуумированной таблицы.
Ссылка на оригинал.
Я работаю с PostgreSQL уже много лет. Недавно стал в Percona консультантом для компаний которые переходят на Postgres в результате миграции с Oracle или какой-либо другой (возможно — устаревшей) СУБД. Иногда это бывают компании из списка Fortune-100, в которых работает множество талантливых сотрудников. Однако, не все базы данных работают одинаково и, когда я проверяю Postgres окружение клиентов, я очень часто замечаю большое количество распухших таблиц, распухших индексов, непонимание как это влияет на производительность и непонимание как с этим бороться.
Я написал статью на эту тему несколько лет назад и никогда не задумывался об этом после публикации. Однако, с учетом того что большое количество компаний по понятным причинам переходят на Postgres и у них отсутствуют практические навыки управления такими базами данных, необходимые для поддержки довольно больших БД — я подумал, что перепишу эту статью и верну её к жизни с большей чёткостью, чтобы помочь понять распухание и почему оно происходит.
В чем причина распухания?
В PostgreSQL виновником этого является Multi-Version Concurrency Control (многоверсионность), обычно называемый MVCC.
MVCC гарантирует, что транзакция в базе данных вернет только зафиксированные данные (используя снимок), даже если другие процессы пытаются изменить эти данные.
Представьте базу данных с миллионами строк в таблице. Каждый раз, когда вы обновляете или удаляете строку, Postgres должен отслеживать её изменение используя идентификатор транзакции. Например, вы можете запустить длительный запрос с идентификатором транзакции 100, в то время как условный Джон только что обновил ту же таблицу, используя идентификатор транзакции 101. В таком случае, поскольку вы все еще находитесь внутри сотой транзакции, которая старше, чем сто первая, то изменения, внесенные Джоном в транзакцию с идентификатором 101, не имеют отношения к вашему запросу и не видны ему. Вы находитесь в своей личной копии данных пока ваши изменения не будут применены. Новые запросы от вас или кого-либо еще с идентификатором транзакции больше 101 будут видеть изменения, внесенные Джоном в транзакции 101. В конце концов, новые идентификаторы транзакций большие чем 101 означают, что в настоящее время нет других транзакций с идентификаторами меньше 101; данные, которые вы видели в транзакции с идентификатором 100, больше не нужны базе данных и будут считаться мертвыми, но не исчезнувшими [есть тут что-то не то, но не могу пока ухватить что именно]. Вот оно, распухание!
То есть, очистка используется для освобождения мертвых строк в таблице, чтобы их можно было использовать повторно. Это также помогает избежать переполнения счетчика транзакций.
Давайте пройдемся по шагам, чтобы проиллюстрировать, как все это происходит.
Чтобы Postgres знал, какие данные должны быть в вашем результирующем наборе, созданный Postgres’ом снимок содержит необходимую для этого информацию.
По сути, если ваш идентификатор транзакции равен 100, вы увидите данные только из всех [зафиксированных же?] транзакций, номер которых не превышает 100. Как было указано выше, вы не увидите данные с идентификатором транзакций 101 или выше.
Подготовка примера
Давайте начнем с создания простой таблицы для нашего примера, которую назовем «percona»:
percona=# CREATE TABLE percona ( col1 int ); CREATE TABLE percona=# INSERT INTO percona values (1); INSERT 0 1 percona=# INSERT INTO percona values (2); INSERT 0 1 percona=# INSERT INTO percona values (3); INSERT 0 1 percona=# INSERT INTO percona values (4); INSERT 0 1 percona=# INSERT INTO percona values (5); INSERT 0 1
Вы можете использовать транзакцию для вставки нескольких значений используя BEGIN и COMMIT:
percona=# BEGIN; BEGIN percona=*# INSERT INTO percona SELECT generate_series(6,10); INSERT 0 5 percona=*# COMMIT; COMMIT
Следующим запросом мы увидим 10 строк, которые мы вставили в таблицу, а также некоторые скрытые системные столбцы:
percona=# SELECT xmin, xmax, * FROM percona; xmin | xmax | col1 ----------+------+------ 69099597 | 0 | 1 69099609 | 0 | 2 69099627 | 0 | 3 69099655 | 0 | 4 69099662 | 0 | 5 69099778 | 0 | 6 69099778 | 0 | 7 69099778 | 0 | 8 69099778 | 0 | 9 69099778 | 0 | 10 (10 rows)
Как видите, первые пять значений (столбец col1) имеют уникальные идентификаторы транзакций (столбец xmin) — так как они добавлены отдельными операторами INSERT, выполненными один за другим. Строки со значениями от 6 до 10 имеют один и тот же идентификатор транзакции 6909978; потому что они были добавлены в одной транзакции, которую мы создали с помощью операторов BEGIN и COMMIT.
В этот момент вы можете спросить себя, какое это имеет отношение к вакууму или автовакууму. Мы разберемся с этим. Во-первых, вам нужно знать о логике идентификаторов транзакций и визуально увидеть их для лучшего понимания — что и было показано выше.
Как распухает таблица?
В Postgres куча [heap] — это файл, содержащий список записей разного размера в произвольном порядке, который указывает на расположение строки на странице (страница Postgres имеет размер 8 КБ [по умолчанию]). Указатель на её расположение называется CTID.
Чтобы просмотреть кучу и прочитать необработанные данные из файлов, нам нужно создать следующее расширение внутри нашей базы данных:
CREATE EXTENSION pageinspect;
Теперь мы можем проверить кучу и посмотреть на наши только что созданные таблицу и строки:
percona=# SELECT * FROM heap_page_items(get_raw_page('percona',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 | 69099597 | 0 | 0 | (0,1) | 1 | 2304 | 24 | | | \x01000000
2 | 8128 | 1 | 28 | 69099609 | 0 | 0 | (0,2) | 1 | 2304 | 24 | | | \x02000000
3 | 8096 | 1 | 28 | 69099627 | 0 | 0 | (0,3) | 1 | 2304 | 24 | | | \x03000000
4 | 8064 | 1 | 28 | 69099655 | 0 | 0 | (0,4) | 1 | 2304 | 24 | | | \x04000000
5 | 8032 | 1 | 28 | 69099662 | 0 | 0 | (0,5) | 1 | 2304 | 24 | | | \x05000000
6 | 8000 | 1 | 28 | 69099778 | 0 | 0 | (0,6) | 1 | 2304 | 24 | | | \x06000000
7 | 7968 | 1 | 28 | 69099778 | 0 | 0 | (0,7) | 1 | 2304 | 24 | | | \x07000000
8 | 7936 | 1 | 28 | 69099778 | 0 | 0 | (0,8) | 1 | 2304 | 24 | | | \x08000000
9 | 7904 | 1 | 28 | 69099778 | 0 | 0 | (0,9) | 1 | 2304 | 24 | | | \x09000000
10 | 7872 | 1 | 28 | 69099778 | 0 | 0 | (0,10) | 1 | 2304 | 24 | | | \x0a000000
(10 rows)
В этой таблице показаны 10 записей и несколько столбцов:
- lp — идентификатор строки
- t_xmin — идентификатор транзакции
- t_ctid — указатель
- t_data — это данные
Сейчас указатель для каждой строки указывает сам на себя, как это и должно быть (в скобках отображена информация о странице и tupleid — идентификатор строки). Довольно просто.
Давайте теперь несколько раз обновим какую-нибудь строку. Сначала изменим значение 5 на 20, затем на 30 и, наконец, обратно на 5:
percona=# UPDATE percona SET col1 = 20 WHERE col1 = 5; UPDATE 1 percona=# UPDATE percona SET col1 = 30 WHERE col1 = 20; UPDATE 1 percona=# UPDATE percona SET col1 = 5 WHERE col1 = 30; UPDATE 1
Эти три изменения произошли в рамках трех разных транзакций.
Что у нас получилось? Мы изменили значения столбца три раза, но не добавляли и не удаляли строки. Итак, у нас должно быть 10 строк, верно?
percona=# SELECT COUNT(*) FROM percona; count ------- 10 (1 row)
Выглядит как и ожидалось. Но подождите! Теперь посмотрим на кучу. А вот и реальные данные на диске:
percona=# SELECT * FROM heap_page_items(get_raw_page('percona',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 | 69099597 | 0 | 0 | (0,1) | 1 | 2304 | 24 | | | \x01000000
2 | 8128 | 1 | 28 | 69099609 | 0 | 0 | (0,2) | 1 | 2304 | 24 | | | \x02000000
3 | 8096 | 1 | 28 | 69099627 | 0 | 0 | (0,3) | 1 | 2304 | 24 | | | \x03000000
4 | 8064 | 1 | 28 | 69099655 | 0 | 0 | (0,4) | 1 | 2304 | 24 | | | \x04000000
5 | 8032 | 1 | 28 | 69099662 | 69103876 | 0 | (0,11) | 16385 | 1280 | 24 | | | \x05000000
6 | 8000 | 1 | 28 | 69099778 | 0 | 0 | (0,6) | 1 | 2304 | 24 | | | \x06000000
7 | 7968 | 1 | 28 | 69099778 | 0 | 0 | (0,7) | 1 | 2304 | 24 | | | \x07000000
8 | 7936 | 1 | 28 | 69099778 | 0 | 0 | (0,8) | 1 | 2304 | 24 | | | \x08000000
9 | 7904 | 1 | 28 | 69099778 | 0 | 0 | (0,9) | 1 | 2304 | 24 | | | \x09000000
10 | 7872 | 1 | 28 | 69099778 | 0 | 0 | (0,10) | 1 | 2304 | 24 | | | \x0a000000
11 | 7840 | 1 | 28 | 69103876 | 69103916 | 0 | (0,12) | 49153 | 9472 | 24 | | | \x14000000
12 | 7808 | 1 | 28 | 69103916 | 69103962 | 0 | (0,13) | 49153 | 9472 | 24 | | | \x1e000000
13 | 7776 | 1 | 28 | 69103962 | 0 | 0 | (0,13) | 32769 | 10496 | 24 | | | \x05000000
(13 rows)
У нас 13 строк, а не 10. Что же только что произошло?
Давайте подробнее посмотрим на те обновления, которые были сделаны в отдельных транзакциях (69103876, 69103916, 69103962), чтобы увидеть, что происходит с кучей:
t_xmin (691103876)
- UPDATE percona SET col1 = 20 WHERE col1 = 5;
- Логический DELETE записи с ID = 5;
- Физический INSERT записи с ID = 11;
- UPDATE указателя (t_tcid) записи с ID = 5 на ID = 11.
Строка с ID = 5 становится мертвой, когда его t_xmax устанавливается в новое значение, которое туда записывает транзакция 691103876.
t_xmin (69103916)
- UPDATE percona SET col1 = 30 WHERE col1 = 20;
- Логический DELETE записи с ID = 11;
- Физический INSERT записи с ID = 12;
- UPDATE указателя (t_tcid) записи с ID = 11 на ID = 12.
И снова, строка с ID = 11 становится мертвой, когда его t_xmax устанавливается в новое значение, которое туда записывает транзакция 69103916.
t_xmin (69103962)
- UPDATE percona SET col1 = 5 WHERE col1 = 30;
- Логический DELETE записи с ID = 12;
- Физический INSERT записи с ID = 13
- UPDATE указателя (t_tcid) записи с ID = 11 на ID = 13.
Запись с ID = 13 активна и видна другим транзакциям. У неё нет t_xmax, а t_ctid (0,13) указывает сам на себя.
Ключевой вывод из показанного заключается в том, что мы не добавили и не удалили ни одной строки в нашу таблицу. Если посчитать, мы по-прежнему видим 10 строк, но наша куча увеличилась до 13 за счет выполнения еще трех транзакций.
В общих чертах это демонстрирует как PostgreSQL реализует MVCC и почему у нас есть распухание таблицы в куче. По сути, изменения данных приводят к появлению новой строки, в которой и находится последнее состояние данных. Старые [мёртвые] строки необходимо очистить или повторно использовать для повышения эффективности.
Очистка таблицы от мёртвых строк
Способ справиться с распуханием таблицы — очистить её:
percona=# vacuum percona; VACUUM
Теперь давайте снова проверим кучу:
percona=# SELECT * FROM heap_page_items(get_raw_page('percona',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 | 69099597 | 0 | 0 | (0,1) | 1 | 2304 | 24 | | | \x01000000
2 | 8128 | 1 | 28 | 69099609 | 0 | 0 | (0,2) | 1 | 2304 | 24 | | | \x02000000
3 | 8096 | 1 | 28 | 69099627 | 0 | 0 | (0,3) | 1 | 2304 | 24 | | | \x03000000
4 | 8064 | 1 | 28 | 69099655 | 0 | 0 | (0,4) | 1 | 2304 | 24 | | | \x04000000
5 | 13 | 2 | 0 | | | | | | | | | |
6 | 8032 | 1 | 28 | 69099778 | 0 | 0 | (0,6) | 1 | 2304 | 24 | | | \x06000000
7 | 8000 | 1 | 28 | 69099778 | 0 | 0 | (0,7) | 1 | 2304 | 24 | | | \x07000000
8 | 7968 | 1 | 28 | 69099778 | 0 | 0 | (0,8) | 1 | 2304 | 24 | | | \x08000000
9 | 7936 | 1 | 28 | 69099778 | 0 | 0 | (0,9) | 1 | 2304 | 24 | | | \x09000000
10 | 7904 | 1 | 28 | 69099778 | 0 | 0 | (0,10) | 1 | 2304 | 24 | | | \x0a000000
11 | 0 | 0 | 0 | | | | | | | | | |
12 | 0 | 0 | 0 | | | | | | | | | |
13 | 7872 | 1 | 28 | 69103962 | 0 | 0 | (0,13) | 32769 | 10496 | 24 | | | \x05000000
(13 rows)
После очистки таблицы можно снова использовать строки 5, 11 и 12.
Итак, давайте вставим еще одну строку со значением 11 и посмотрим, что произойдет:
percona=# INSERT INTO percona values (11); INSERT 0 1
Давайте еще раз проверим кучу:
percona=# SELECT * FROM heap_page_items(get_raw_page('percona',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 | 69099597 | 0 | 0 | (0,1) | 1 | 2304 | 24 | | | \x01000000
2 | 8128 | 1 | 28 | 69099609 | 0 | 0 | (0,2) | 1 | 2304 | 24 | | | \x02000000
3 | 8096 | 1 | 28 | 69099627 | 0 | 0 | (0,3) | 1 | 2304 | 24 | | | \x03000000
4 | 8064 | 1 | 28 | 69099655 | 0 | 0 | (0,4) | 1 | 2304 | 24 | | | \x04000000
5 | 13 | 2 | 0 | | | | | | | | | |
6 | 8032 | 1 | 28 | 69099778 | 0 | 0 | (0,6) | 1 | 2304 | 24 | | | \x06000000
7 | 8000 | 1 | 28 | 69099778 | 0 | 0 | (0,7) | 1 | 2304 | 24 | | | \x07000000
8 | 7968 | 1 | 28 | 69099778 | 0 | 0 | (0,8) | 1 | 2304 | 24 | | | \x08000000
9 | 7936 | 1 | 28 | 69099778 | 0 | 0 | (0,9) | 1 | 2304 | 24 | | | \x09000000
10 | 7904 | 1 | 28 | 69099778 | 0 | 0 | (0,10) | 1 | 2304 | 24 | | | \x0a000000
11 | 7840 | 1 | 28 | 69750201 | 0 | 0 | (0,11) | 1 | 2048 | 24 | | | \x0b000000
12 | 0 | 0 | 0 | | | | | | | | | |
13 | 7872 | 1 | 28 | 69103962 | 0 | 0 | (0,13) | 32769 | 10496 | 24 | | | \x05000000
(13 rows)
Наша новая строка (с идентификатором 69750201) повторно использовала строку 11, и теперь указатель этой строки (0,11) указывает на себя.
Как можно увидеть, куча не увеличилась при добавлении новой строки. Postgres повторно использовал ранее созданную строку, которая стала доступной после того как мы очистили таблицу, убрав мёртвые строки (строки, которые больше не будут видны в транзакциях).
Вот такая она получилась — пошаговая иллюстрация того, как происходит распухание в PostgreSQL! [я бы еще про VACUUM FULL добавил для полноты картины]
—
Автор статьи — Jorge Torralba, Postgres-консультант в компании Percona.
Еще раз ссылка на оригинал.
Leave a Reply