SQL-Ex blog

Некоторые вспомогательные детали

PostgreSQL использует уровень изоляции транзакций Read Committed (чтение зафиксированных транзакций) как уровень изоляции по умолчанию, и примеры ниже предполагают использование этого уровня изоляции. Другими уровнями в стандарте SQL являются read uncommitted (чтение незафиксированных транзакций), repeatable read (повторяемое чтение), and serializable (сериализуемый). Вы можете почитать больше о них и их различном возможном поведении (например, неповторяемые чтения и чтение фантомных данных) здесь.

Каждой транзакции, имеющей место в инсталляции PostgreSQL, присваивается уникальный числовой идентификатор. Этот идентификатор является 32-битным целым числом, которое монотонно возрастает по мере выполнения транзакций. Вы можете использовать функцию txid_current() для получения ID текущей транзакции. Обратите внимание, что вызов этой функции увеличивает ID транзакции базы данных.

select txid_current();

Поскольку ID транзакции используется для отслеживания строк, которые видны в системе PostgreSQL MVCC, важно его знать.

Код, представленный ниже, создает таблицу, которая будет использоваться во всех примерах этой статьи. Обратите внимание, что флаг autovacuum_enabled для таблицы выключен явно - об этом чуть позже.

create table mvcctable 

(

	idcol integer,

	valcol char (255)

) with (autovacuum_enabled = off);

create index idx_mvcctable on mvcctable (idcol);

Следующий код вставит 100000 строк в mvcctable, при этом idcol будет содержать возрастающий набор значений, а valcol - случайным образом формируемое строковое значение:

insert into mvcctable (idcol, valcol)

select *, substr (md5 (random ()::text), 0, 255)

from generate_series (1, 100000);

Строки в таблице представляют либо текущую строку (называемую ‘alive’ - живая), либо предыдущую версию, которая больше не нужна (называемая ‘dead’ - мертвая). Можно обратиться к системной таблице pg_stat_user_tables для поиска числа живых и мертвых строк для заданной таблицы. Следующий код запрашивает из системной таблицы количество живых и мертвых строк в таблице mvcctable:

select 

	relname as tablename,

	n_live_tup as livetuples,

	n_dead_tup as deadtuples

from

	pg_stat_user_tables

where

	relname = 'mvcctable';

Таблицы в PostgreSQL содержат несколько скрытых системных столбцов, включая:

• tableoid: идентификатор таблицы как объекта


• xmin: ID транзакции, вызывающей создание строки (с помощью вставки либо обновления)


• xmax: ID транзакции, вызывающей пометку строки для возможного удаления (с помощью удаления или обновления)


• ctid: физическое местоположение строки в таблице (номер страницы и номер индекса строки на странице)

Следующий запрос возвращает все строки из таблицы mvcctable наряду с четырьмя скрытыми системными столбцами:

select

	tableoid,

	xmin,

	xmax,

	ctid,

	*

from

	mvcctable;

Можно увидеть, что идентификатор транзакции был установлен в 104472 для первой транзакции, которая вставила исходные 100000 строк в таблицу. Пока ни одна из этих строк не была модифицирована, поэтому значение xmax установлено в 0.

И последняя вспомогательная информация - вы можете использовать функцию pg_size_pretty и передать в нее имя таблицы для получения размера таблицы. Следующий код сделает это для mvcctable:

select pg_size_pretty(pg_relation_size('mvcctable'));

MVCC в действии

Чтобы показать MVCC в действии, есть простой тестовый сценарий из двух окон подключения; в первом окне запускается явная транзакция и выполняется команда UPDATE для строк без фиксации (commit) транзакции, а во втором окне выполняется оператор SELECT, чтобы посмотреть на происходящее при параллельно выполняющемся UPDATE.

Ниже приведен код для первого окна. Он обновляет первые 100 строк таблицы mvcctable, устанавливая valcol в значение ‘newvalue’ и оставляя транзакцию открытой:

begin;

update mvcctable 

set valcol = 'newvalue'

where idcol <= 100;

Запрос SELECT для второго окна выбирает первые 100 строк:

select * 

from mvcctable 

where idcol <= 100;

Запрос выполняется сразу и возвращает состояние 100 строк ПРЕДШЕСТВУЮЩЕЕ их обновлению другой транзакцией (т.к. чтение зафиксированных данных не допускает "грязного чтения", и фактически Postgresql не допускает его, даже если используется чтение незафиксированных данных). Магия MVCC состоит в том, что сравнивается ID текущей транзакции базы данных со значениями xmin и xmax для запрашиваемых строк в mvcctable и возвращаются корректные значения. Поскольку обновляющая транзакция еще не была зафиксирована, второму запросу не позволено было видеть обновленные строки.

Вернемся к первому окну с открытой транзакцией и выполним следующий код:

select

	tableoid,

	xmin,

	xmax,

	ctid,

	*

from

	mvcctable

order by idcol asc;

Вы можете увидеть, что значения idcol от 1 до 100 должны поменять свое значение value на ‘newvalue’ и получить новое значение xmin, которое представляет ID все еще открытой транзакции, которая вставляет эти новые версии. Значения ctid (номер страницы и индекс строки на странице) должны также обновиться, представляя местоположение новых записей. MVCC гарантирует, что этот запрос вернул новые строки и не видит старых версий.

А теперь, если я выполню тот же код во втором окне:

select

	tableoid,

	xmin,

	xmax,

	ctid,

	*

from

	mvcctable

order by idcol asc 

вы можете увидеть, что исходные 100 строк имеют значение ID транзакции xmax 104511, которое является тем же самым ID транзакции в столбце xmin для 100 новых строк оператора UPDATE.

Наконец, я зафиксирую транзакцию в первом окне:

commit;

И снова повторю запрос select во втором окне:

select

	tableoid,

	xmin,

	xmax,

	ctid,

	*

from

	mvcctable

order by idcol asc;

Можно увидеть, что когда обновляющая транзакция была зафиксирована, MVCC гарантирует видимость только обновленных строк. Старые версии строк, которые теперь помечены как удаленные, больше не видны ни в одной транзакции - они теперь мертвы (‘dead’). Однако эти строки ПО-ПРЕЖНЕМУ находятся в таблице и будут оставаться там, пока не выполнится процесс VACUUM.

Запрос pg_stat_user_tables опять показывает эти, теперь уже мертвые, строки в mvcctable:

select 

	relname as tablename,

	n_live_tup as livetuples,

	n_dead_tup as deadtuples

from

	pg_stat_user_tables

where

	relname = 'mvcctable';

Процесс VACUUM

Когда строка модифицируется в таблице PostgreSQL, она помечается как удаленная. После того, как значение xmax станет старше системного ID транзакции, и транзакция будет зафиксирована, строка становится ненужной и может быть удалена. Однако процесс удаления мертвых строк не является автоматическим. Процесс VACUUM отвечает за освобождение пространства, занимаемого мертвыми строками в таблице. VACUUM имеет также опции для выполнения других задач обслуживания, таких как обновление статистики для индексов, связанных с таблицей, обновление карт видимости (Visibility Maps) для ускорения сканирования только индексов, а также обеспечение защиты, когда значения идентификатора транзакции в экземпляре PostgreSQL исчерпывают 32-битное целое значение и должен выполняться циклический перенос. Вот некоторые варианты этой функции.

Стандартный VACUUM

Стандартный процесс VACUUM сканирует таблицу, помечая ненужные больше строки как свободное пространство. (Заметим, что если имя таблицы не указано, VACUUM удаляет мертвые строки из всех таблиц в базе данных.) Это пространство может затем повторно использоваться для вставляемых/обновляемых данных, но оно не возвращается операционной системе - таблица не ужимается в размере. VACUUM будет обычно генерировать большое число операций ввода/вывода, что может вызывать проблемы с производительностью активных сессий. Структура таблиц не может модифицироваться во время выполнения VACUUM. В большинстве случаев вам не потребуется использовать это в производственной среде, поскольку этим занимается демон автоочистки.

Демон автоочистки

Демон автоочистки (autovacuum daemon) является многопотоковым фоновым процессом, автоматически выполняющим команду VACUUM для поддержания статистики таблиц в актуальном состоянии, что критично для механизма оптимизации запросов (планировщика) при создании точных планов выполнения. Демон автоочистки не является обязательным для использования, но в большинстве случаев это правильный выбор для очистки мертвых строк и обновления статистики. Он включен по умолчанию, и рекомендуется держать его включенным. Этот процесс состоит в нахождении таблиц, испытывающих активность большого числа операторов insert/update/delete, а затем выполнения процессов VACUUM и ANALYZE на этих таблицах. Поскольку этот процесс опирается на сбор информации об активности базы данных, требуется системная установка track_counts для запуска демона автоочистки. Накладные расходы этого процесса обычно низки, и обычно деятельность других сессий не блокируется. Имеется несколько конфигурационных опций для автоочистки, которые описываются здесь.

Полная очистка (VACUUM FULL)

Процесс VACUUM FULL используется для сжатия таблицы к ее минимальному размеру и возвращения дискового пространства операционной системе. Обычно эта версия VACUUM необходима только для конкретных таблиц, содержащих в основном мертвые строки - таких как постановочные таблицы ETL. По ряду причин это не тот процесс, который должен использоваться регулярно. Во-первых, при нормальной транзакционной обработке таблиц обычно не требуется высвобождать место, занимаемое мертвыми строками, для передачи его ОС. Это пространство снова потребуется так или иначе. Во-вторых, этот процесс всегда создает копию таблицы и новые индексы для нее, что всегда затронет все данные в таблице, а не только мертвые строки. Из-за копирования данных в новое множество кучи и индексных структур таблица блокируется на время выполнения операции. Это может стать значительной проблемой для таблиц, предполагающих параллельный доступ во время обычной обработки транзакций. Много лучше иметь стандартную очистку, выполняемую по расписанию, или полагаться на демон автоочистки.

Пример VACUUM

Теперь на предыдущем примере я покажу, как VACUUM может повлиять на размер таблицы и число мертвых строк. Запрос к pg_stat_user_tables и pg_size_pretty покажет, что mvcctable содержит 100000 строк, при этом 100 из них помечены как мертвые, и таблица имеет размер 29Мб:

select 

	relname as tablename,

	n_live_tup as livetuples,

	n_dead_tup as deadtuples

from

	pg_stat_user_tables

where

	relname = 'mvcctable';

select pg_size_pretty(pg_relation_size('mvcctable'));

Следующий оператор UPDATE создает дополнительно 50000 мертвых строк в таблице:

update	mvcctable

set	valcol = 'MassiveUpdate'

where	idcol <= 50000;

Повторный запрос к pg_size_pretty показывает, что размер таблицы mvcctable вырос до 43Мб:

select pg_size_pretty(pg_relation_size('mvcctable'));

Обычно тут должен начаться процесс автоочистки и очистить мертвые строки, но, когда я создавал таблицу, я выключил работу демона на этой таблице, установив опцию autovacuum_enabled в off.

Следующий код запускает процесс VACUUM для mvcctable:

vacuum mvcctable;

Запрос к pg_stat_user_tables показывает, что все мертвые строк был удалены:

select 

	relname as tablename,

	n_live_tup as livetuples,

	n_dead_tup as deadtuples

from

	pg_stat_user_tables

where

	relname = 'mvcctable';

Однако таблица все еще имеет тот же самый размер:

select pg_size_pretty(pg_relation_size('mvcctable'));

Это ожидалось, поскольку стандартный VACUUM удаляет только мертвые строки таблицы, поэтому это пространство может быть снова использоваться таблицей - никакое пространство не отдается обратно операционной системе. Чтобы это сделать, вам должно это РЕДКО потребоваться, вы можете использовать команду VACUUM FULL, как показано ниже:

vacuum full mvcctable;

Теперь таблица была сжата до своего исходного размера:

select pg_size_pretty(pg_relation_size('mvcctable'));

Резюме об управлении параллелизмом в PostgreSQL

PostgreSQL использует многоверсионное управление параллелизмом оптимистичной изоляции, гарантирующее, что записи не блокируют чтения, а чтения не блокируют записи. Для достижения этого модифицированные строки помечаются идентификатором транзакции, которая их изменила. Затем транзакции сравнивают свои ID с ID задействованных строк, чтобы увидеть, какие строки могут быть прочитаны. Процесс VACUUM используется для удаленя мертвых строк из таблицы, поэтому занимаемое ими место может быть повторно использовано.

Базы данных PostgreSQL продолжают увеличиваться в размерах и сложности, поэтому необходимо отслеживать все, что просходит в их среде. Встроенный SolarWinds Database Mapper помогает автоматизровать и поддерживать обновление документации базы данных, создание словарей данных и отслеживать источник данных с помощью анализа происхождения данных и их влияния, чтобы упростить соблюдение бизнес-правил и правил конфиденциальности данных.