Анализ сборки мусора

Запрс выводит статистическую информацию по сборке мусора упорядочивая таблицы по доле мёртвых кортежей в обратном порядке. Наверху оказываются таблицы у которых ситуация со сборкой мусора хуже всего, но тут необходимо помнить про scale factor и другие настройки, т.к. в "топе" скорее всего появятся ещё и таблицы, которые, например, не требуют сборки мусора из-за малого кол-ва записей или не достаточного увеличения таблицы для запуска autovacuum. Также необходимо помнить и учитывать, что отображаемая статистика это данные, накопленные с момента последней очистки статистики.

Read more »

В ожидании 8.4 - Карты видимости

Перевод Waiting for 8.4 - Visibility maps с select * from depesz;

(от автора) О-да, детка! ;)

Да. Только ради этого патча будет смысл обновиться до 8.4.

Его принял Heikki Linnakangas 3-го декабря. Сообщение:

Добавляет карту видимости. Карта видимости - битовая карта с одним битом на страницу 
данных, где установленный бит указывает, что все записи на странице видимы для всех 
транзакций и поэтому страница не нуждается в очистке (vacuuming). Карта хранится в 
дополнительном отношении.

Пассивная сборка мусора (lazy vacuum) использует карты видимости для того, чтобы 
пропускать страницы, не требующие очистки. Сборка мусора также ответственна за 
установку битов видимости. В будущем это даёт возможность реализации index-only 
сканирований, но в данный момент нельзя гарантировать, что карты видимости всегда 
будут актуальны.

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

Ранее был ещё один патч, позволяющий autovacuum-у использовать эти возможности.

Даже сейчас 8.4 можно назвать великим. В нём реализовано множество новых возможностей. Кто-то назовёт CTE более важным. Другие скажут, что это локаль на уровне базы.

Но CTE не будет использоваться повсеместно. То же можно сказать о локали уровня базы и практически всех других новых возможностях.

Но vacuum будет использоваться всеми. Это, конечно, ни в коем случае не означает, что другие патчи не важны - они важны. Но этот поприветствуют больше пользователей.

Что же мы получили? В кратце - сборка мусора стала быстрее. Возможно намного.

Давайте посмотрим.

Во первых, я запустил версию PG с этим патчем, выключил autovacuum и запустил сборку мусора (vacuum) по всей базе (для того, чтобы избежать неожиданных запусков autovacuum).

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

CREATE TABLE test_1 (i INT4);
CREATE TABLE test_2 (i INT4);
CREATE TABLE test_3 (i INT4);
CREATE TABLE test_4 (i INT4);

добавим данных:

INSERT INTO test_1 SELECT generate_series(1, 100000000);
INSERT INTO test_2 SELECT generate_series(1, 100000000);
INSERT INTO test_3 SELECT generate_series(1, 100000000);
INSERT INTO test_4 SELECT generate_series(1, 100000000);

(знаю, что это не много, но я на ноуте и не хочу ждать всю ночь :)

Теперь обновим таблицы:

UPDATE test_2 SET i = i + 1 WHERE i < 10000000;
UPDATE test_3 SET i = i + 1 WHERE i < 50000000;
UPDATE test_4 SET i = i + 1 WHERE i < 90000000;

Для проверки нового функционала надо запустить vacuum несколько раз, т.к. каждый vacuum использует информацию полученную предыдущим. Т.ч. я сделал:

VACUUM test_1;
VACUUM test_2;
VACUUM test_3;
VACUUM test_4;

после INSERT-ов и после UPDATE-ов. Для большей информативности я ещё раз сделал UPDATE и после него ещё раз vacuum.

Результат:

		Visibility maps available		Change
		No	Yes
Vacuum #1 post-insert	test_1	233.34 s	310.53 s	+33.1 %
	test_2	243.45 s	262.20 s	+7.7 %
	test_3	237.86 s	260.26 s	+9.4 %
	test_4	198.72 s	200.60 s	+0.9 %
Vacuum #2 post-update #1	test_1	96.71 s	0.38 s	-99.6 %
	test_2	150.91 s	59.81 s	-60.4 %
	test_3	283.22 s	234.06 s	-17.4 %
	test_4	418.41 s	503.25 s	+20.3 %
Vacuum #3 post-update #2	test_1	98.11 s	0.54 s	-99.4 %
	test_2	142.92 s	27.10 s	-81.0 %
	test_3	283.91 s	297.43 s	+4.8 %
	test_4	416.35 s	507.77 s	+22.0 %

Что получается? На самом деле PostgreSQL с картами видимости отработал медленнее в ситуациях с большим кол-вом "новых" записей, не зависимо от того были ли они добавлены или изменены. Но если ваш autovacuum настроен правильно - этого не случится. Очевидно ему желательно видеть не более ~10% изменений строк, и тогда производительность просто взлетит.

В дополнение к этому, если ваши таблицы (не зависимо от размера) в основном неизменны, то их vacuum будет практически моментален - очень полезное свойство.

Я был бы очень рад, если бы производительность в случае первого теста была улучшена, но в подавляющем своём большинстве это всё просто великолепно.

Хочу заострить внимание на выдержке из сообщения патча:

В будущем это даёт возможность реализации index-only 
сканирований...

Это очень многообещающе. Другие СУБД (включая открытые) реализуют такое через так называемые covering indexes (когда запрос оперирует только полями содержащимися в индексе, и не приходится лезть в таблицу), но PostgreSQL всё ещё удаётся их избегать. Стремления очевидны, и карты видимости - 1-й шаг к окончательной победе :)

Как получить OID таблицы

Перевод How to get a table's OID с BERND'S BLOG

Мои клиенты, пытающиеся работать с OID-ами, часто задают мне вопросы об отношениях, которые надо куда-нибудь добавить (3 раза только на этой неделе). Например, кто-то хочет добавить специальную конфигурацию autovacuum-а для определённой таблицы. Ему надо выяснить OID этой таблицы. Обычно люди используют такой запрос:

SELECT oid FROM pg_class WHERE relname = 'foo' AND relkind = 'r';
oid
-------
16385
(1 row)

Возвращается текущий OID таблицы по её имени, который может в дальнейшем быть использован для ваших нужд. Однако, есть более простой способ получения такой информации, просто приведением имени отношения к типу regclass:

SELECT 'foo'::regclass::oid;
oid
-------
16385
(1 row)

Вернёмся к примеру с autovacuum-ом:

INSERT INTO pg_autovacuum 
VALUES('foo'::regclass, 't', -1, 0.05, -1, -1, -1, -1, -1, -1);

Тут не используется прямое приведение к OID, т.к. будет использовано неявное.

Доступны также другие reg* приведения, например:

SELECT 'now'::regproc::oid;
oid
------
1299
(1 row)

SELECT 'int4'::regtype::oid;
oid
-----
23