Уровень 3.0

Предусловия:

Изучена лекция «Заморозка версий строк»

Анализ заморозки версий строк

Откройте терминал и подключитесь в psql к базе данных postgres c ролью postgres:

[student@pkles-gt0040964 ~]$ sudo -iu postgres

-bash-4.4$ psql

psql (15.5)
Введите "help", чтобы получить справку.

Создайте базу данных pgbench_db и подключитесь к ней:

postgres=# CREATE DATABASE pgbench_db;

CREATE DATABASE

postgres=# \c pgbench_db

Вы подключены к базе данных "pgbench_db" как пользователь "postgres".

Создайте расширение pageinspect:
```
pgbench_db=# CREATE EXTENSION pageinspect;
```
```
CREATE EXTENSION
```
Вспомним, что расширение pageinspect позволяет исследовать страницы данных на низком уровне.
Создайте расширение pg_visibility:
```
pgbench_db=# CREATE EXTENSION pg_visibility;
```
```
CREATE EXTENSION
```
Расширение pg_visibility позволяет обратиться к содержимому карты видимости.

В первом сеансе создайте функцию, которая будет выводить информацию о версиях строк в заданной табличной странице:

pgbench_db=# CREATE OR REPLACE FUNCTION get_tuples_info(relname text, pagenum bigint)
RETURNS TABLE(ctid text, xmin xid, xmax xid, next_ctid text,
    xmin_committed boolean, xmin_aborted boolean,
    xmax_committed boolean, xmax_aborted boolean)
AS $$
SELECT '(' || $2 || ', ' || lp || ')', t_xmin, t_xmax, t_ctid,
    'HEAP_XMIN_COMMITTED' = ANY(raw_flags),
    'HEAP_XMIN_INVALID' = ANY(raw_flags),
    'HEAP_XMAX_COMMITTED' = ANY(raw_flags),
    'HEAP_XMAX_INVALID' = ANY(raw_flags)
         FROM heap_page_items(get_raw_page($1, 'main', $2)),
           LATERAL heap_tuple_infomask_flags(t_infomask, t_infomask2)
         WHERE t_infomask IS NOT NULL OR t_infomask2 IS NOT NULL
    ORDER BY lp;
$$ LANGUAGE SQL;

CREATE FUNCTION

Похожая функция уже использовалась в предыдущих лабораторных работах. Она позволяет заглянуть в заголовки версий строк с использованием расширения pageinspect.

Откройте второй терминал и перейдите в режим выполнения команд от имени пользователя postgres:
```
[student@pkles-gt0041054 ~]$ sudo -iu postgres
```

Анализ влияния параметра vacuum_freeze_min_age

В первом сеансе посмотрите значения конфигурационных параметров автоматической заморозки версий строк:

pgbench_db=# SELECT name, setting FROM pg_settings WHERE name IN ('vacuum_freeze_min_age', 'vacuum_freeze_table_age', 'autovacuum_freeze_max_age') ORDER BY setting DESC;

           name            |   setting
---------------------------+-------------
 vacuum_freeze_min_age     | 50000000
 vacuum_freeze_table_age   | 150000000
 autovacuum_freeze_max_age | 10000000000
(3 строки)

Минимальный возраст транзакции (vacuum_freeze_min_age), подлежащей заморозке при выполнении автоочистки, равен 50 000 000.

В первом сеансе уменьшите значение параметра vacuum_freeze_min_age до 1000:
```
pgbench_db=# SET vacuum_freeze_min_age = 1000;
```
```
SET
```

Во втором сеансе инициализируйте и запустите тест pgbench:

-bash-4.4$ pgbench -i pgbench_db

dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.01 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.25 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 0.11 s, vacuum 0.04 s, primary keys 0.08 s).

-bash-4.4$ pgbench -t 10000 pgbench_db

pgbench (15.5)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
number of transactions per client: 10000
number of transactions actually processed: 10000/10000
number of failed transactions: 0 (0.000%)
latency average = 1.220 ms
initial connection time = 4.260 ms
tps = 819.620737 (without initial connection time)

В тесте выполнено 10000 транзакций. Для этого был использован ключ -t.

Второй сеанс больше не понадобится. Все следующие действия выполняются в первом сеансе.

Выполните очистку и посмотрите возраст старейшей транзакции в базе данных:
```
pgbench_db=# VACUUM;
```
```
VACUUM
```
```
pgbench_db=# SELECT datname, datfrozenxid, age(datfrozenxid) FROM pg_database WHERE datname = 'pgbench_db';
```
```
  datname   | datfrozenxid |  age
------------+--------------+-------
 pgbench_db |       159500 | 10018
(1 строка)
```
Номер старейшей транзакции datfrozenxid в базе данных получен из системного каталога pg_database.

Для определения ее возраста использовалась функция age.

Вспомним, что команда VACCUM, так же как и автоочистка, замораживает только версии строк, у которых возраст транзакции с номером xmin превышает vacuum_freeze_min_age.

Вызов команды VACUUM потребовался, поскольку порог срабатывания автоочистки по количеству измененных или вставленных строк, скорее всего, достигнут не был.

В результате возраст старейшей транзакции в базе данных с номером datfrozenxid превышает 10000, хотя параметр vacuum_freeze_min_age = 1000 и ожидалось, что все версии строк с xmin > vacuum_freeze_min_age будут заморожены.

Так произошло, поскольку очистка не заходила в страницы данных, отмеченные в карте видимости.
Посмотрите возраст старейших транзакции для каждой из таблиц базы данных:
```
pgbench_db=# SELECT relname, relfrozenxid, age(relfrozenxid) FROM pg_class WHERE relname IN ('pgbench_accounts', 'pgbench_branches', 'pgbench_history', 'pgbench_tellers');
```
```
     relname      | relfrozenxid |  age
------------------+--------------+-------
 pgbench_history  |       159515 | 10003
 pgbench_accounts |       168518 |  1000
 pgbench_branches |       169514 |     4
 pgbench_tellers  |       169492 |    26
(4 строки)
```
Старейшая незамороженная версия строки находится в таблице pgbench_history.

Разницу в 15 единиц в возрасте транзакции с номером relfrozenxid в указанной таблице по сравнению с возрастом транзакции datfrozenxid на уровне базы данных можно объяснить тем, что для инициализации pgbench (создание таблиц и т.д.) потребовалось 15 транзакций.

Из этого можно сделать вывод, что в таблице pgbench_history имеется страница, отмеченная в карте видимости и содержащая версию строки с возрастом xmin = 10003.

На самом деле характер таблицы pgbench_history таков, что в нее только вставляются строки и никогда не обновляются. В связи с этим в данной таблице нет устаревших версий строк, и все страницы отмечены в карте видимости.
Посмотрите содержимое карты видимости для нулевой и последней страниц таблицы pgbench_history:
```
pgbench_db=# SELECT relname, relpages FROM pg_class WHERE relname = 'pgbench_history';
```
```
     relname     | relpages
-----------------+----------
 pgbench_history |       65
(1 строка)
```
```
pgbench_db=# SELECT * FROM pg_visibility_map('pgbench_history', 0);
```
```
 all_visible | all_frozen
-------------+------------
 t           | f
(1 строка)
```
```
pgbench_db=# SELECT * FROM pg_visibility_map('pgbench_history', 64);
```
```
 all_visible | all_frozen
-------------+------------
 t           | f
(1 строка)
```
Количество страниц в таблице было получено из системного каталога pg_class.

Далее с использованием расширения pg_visibility было определено, что нулевая и последняя страницы отмечены в карте видимости (all_visible).

По этой причине очистка не заходила в страницы таблицы pgbench_history, а значит, заморозка в них также не выполнялась.

Анализ влияния параметра vacuum_freeze_table_age

Уменьшите значение параметра vacuum_freeze_table_age до 10 000:
```
pgbench_db=# SET vacuum_freeze_table_age = 10000;
```
```
SET
```
Вспомним, что значение параметра vacuum_freeze_table_age определяет порог срабатывания агрессивной очистки, когда в ходе автоочистки или ручной очистки командой VACUUM для заморозки просматриваются все страницы таблицы, у которой возраст старейшей версии строки больше значения vacuum_freeze_table_age.
Выполните очистку, посмотрите возраст старейшей транзакции в базе данных и в таблицах:
```
pgbench_db=# VACUUM;
```
```
VACUUM
```
```
pgbench_db=# SELECT datname, datfrozenxid, age(datfrozenxid) FROM pg_database WHERE datname = 'pgbench_db';
```
```
  datname   | datfrozenxid | age
------------+--------------+------
 pgbench_db |       168518 | 1001
(1 строка)
```
```
pgbench_db=# SELECT relname, relfrozenxid, age(relfrozenxid) FROM pg_class WHERE relname IN ('pgbench_accounts', 'pgbench_branches', 'pgbench_history', 'pgbench_tellers');
```
```
     relname      | relfrozenxid | age
------------------+--------------+------
 pgbench_history  |       168519 | 1000
 pgbench_accounts |       168518 | 1001
 pgbench_branches |       169514 |    5
 pgbench_tellers  |       169492 |   27
(4 строки)
```
Теперь для таблицы pgbench_history сработала агрессивная заморозка, возраст xmin ее старейшей версии строки (10003) был больше значения vacuum_freeze_table_age (10000).

При этом даже в случае агрессивной заморозки версии строк с возрастом xmin, меньшим значения vacuum_freeze_min_age, заморожены не были.

На уровне базы данных возраст старейшей транзакции теперь определяется возрастом старейшей версии строки в таблице pgbench_accounts (1001).

Как видно, в таблице pgbench_accounts не все версии строк были заморожены. Как минимум одна строка оказалась в странице, отмеченной в карте видимости.

Так произошло, поскольку для данной таблицы не был превышен порог агрессивной заморозки.

Интересно, что возраст старейшей транзакции для таблицы pgbench_accounts увеличился на 1 по сравнению с предыдущим пунктом.

Так произошло из-за выполнения команды SET vacuum_freeze_table_age = 10000; под которую был выделен новый номер транзакции.

Заморозка командой VACUUM FREEZE

Выполните ручную заморозку командой VACUUM FREEZE:
```
pgbench_db=# VACUUM FREEZE;
```
```
VACUUM
```
Вспомним, что команда VACUUM FREEZE при выполнении очистки замораживает все версии строк, находящиеся за горизонтом очистки, независимо от их возраста.

Посмотрите возраст старейшей транзакции в базе данных и в таблицах:

pgbench_db=# SELECT datname, datfrozenxid, age(datfrozenxid) FROM pg_database WHERE datname = 'pgbench_db';

  datname   | datfrozenxid | age
------------+--------------+-----
 pgbench_db |       169519 |   0
(1 строка)

pgbench_db=# SELECT relname, relfrozenxid, age(relfrozenxid) FROM pg_class WHERE relname IN ('pgbench_accounts', 'pgbench_branches', 'pgbench_history', 'pgbench_tellers');

     relname      | relfrozenxid | age
------------------+--------------+-----
 pgbench_history  |       169519 |   0
 pgbench_accounts |       169519 |   0
 pgbench_branches |       169519 |   0
 pgbench_tellers  |       169519 |   0
(4 строки)

В результате незамороженных версий строк в базе данных не осталось.

Номер транзакции и признак заморозки в табличной странице

Посмотрите содержимое заголовков версий строк в нулевой странице таблицы pgbench_branches:
```
pgbench_db=# SELECT * FROM get_tuples_info('pgbench_branches', 0);
```
```
  ctid   |  xmin  | xmax | next_ctid | xmin_committed | xmin_aborted | xmax_committed | xmax_aborted
---------+--------+------+-----------+----------------+--------------+----------------+--------------
 (0, 54) |      2 |    0 | (0,54)    | t              | t            | f              | t
(1 строка)
```
Строка с ctid = (0, 54) отмечена одновременно двумя информационными битами xmin_committed и xmin_aborted, что является признаком ее заморозки.

Помимо этого, в поле xmin указанной версии строки проставлено значение 2, которое зарезервировано под замороженные версии строк.

Вставьте еще одну строку в таблицу pgbench_branches и посмотрите содержимое заголовков версий строк в нулевой странице таблицы pgbench_branches:

pgbench_db=# INSERT INTO pgbench_branches VALUES (2, 2000, null);

INSERT 0 1

pgbench_db=# SELECT * FROM get_tuples_info('pgbench_branches', 0);

  ctid   |  xmin  | xmax | next_ctid | xmin_committed | xmin_aborted | xmax_committed | xmax_aborted
---------+--------+------+-----------+----------------+--------------+----------------+--------------
 (0, 2)  | 169522 |    0 | (0,2)     | f              | f            | f              | t
 (0, 54) |      2 |    0 | (0,54)    | t              | t            | f              | t
(2 строки)

Добавилась новая версия строки (0, 2) с xmin = 169522.

Посмотрите в специальное пространство нулевой страницы таблицы pgbench_branches:
```
pgbench_db=# SELECT special, pagesize, xid_base, multi_base FROM page_header(get_raw_page('pgbench_branches', 0));
```
```
 special | pagesize | xid_base | multi_base
---------+----------+----------+------------
    8168 |     8192 |   159503 |          0
(1 строка)
```
Для этого была использована функция page_header расширения pageinspect, которая, несмотря на свое название, помимо получения информации из заголовка страницы также позволяет получить информацию из специального пространства.

Как видно, в специальном пространстве хранятся два значения: xid_base и multi_base, необходимые для реализации 64-битных счетчиков транзакций и мультитранзакций соответственно.

В частности, значение xmin вставленной в предыдущем пункте версии строки, равное 169522, получается путем сложения 64-битного значения xid_base = 159503 и 32-битного значения xmin, хранимого непосредственно в табличной странице.

Расширение pageinspect показывает уже вычисленное значение xmin, а не физически хранимое в табличной странице.

Завершение

Подключитесь к базе данных postgres, удалите базу данных pgbench_db и выйдите из сеанса:

pgbench_db=# \c postgres

Вы подключены к базе данных "postgres" как пользователь "postgres".

postgres=# DROP DATABASE pgbench_db;

DROP DATABASE

postgres=# \q

Самопроверка

Вопрос 1

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

| ctid | xmin | xmax | xmin committed | xmin aborted | xmax committed | xmax aborted | | --- | --- | --- | --- | --- | --- | --- | | (0,1) | 500 | 501 | 1 | | 1 | | | (0,2) | 2 | 0 | 1 | 1 | | 1 | | (0,3) | 502 | 0 | | 1 | | 1 | | (0,4) | 2 | 504 | 1 | 1 | | |

Какие версии строк являются замороженными? Выберите все верные варианты ответа:

(0,1)

(0,2)

(0,3)

(0,4)

Вопрос 2

В сеансе psql выполнена следующая последовательность команд:

mvcc_db=# SELECT name, setting FROM pg_settings WHERE name IN ('vacuum_freeze_min_age', 'vacuum_freeze_table_age', 'autovacuum_freeze_max_age') ORDER BY setting DESC;
name            |   setting
---------------------------+-------------
vacuum_freeze_min_age     | 100000000
vacuum_freeze_table_age   | 200000000
autovacuum_freeze_max_age | 10000000000
(3 строки)

mvcc_db=# SELECT relname, relfrozenxid, age(relfrozenxid) FROM pg_class WHERE relname = 'some_table';
relname      | relfrozenxid | age
------------------+--------------+------
some_table       |     5000     | 25000
(1 строка)

mvcc_db=# VACUUM FREEZE;
VACUUM

mvcc_db=# SELECT age(relfrozenxid) FROM pg_class WHERE relname = 'some_table';

При выполнении команд параллельных транзакций не проводилось. Автоочистка в экземпляре Pangolin отключена.

Какое число будет получено в результате выполнения последнего запроса?

Вопрос 3

В сеансе psql последовательно выполнены следующие команды:

mvcc_db=# SELECT * FROM generate_series(0,4) pages(page_no),
pg_visibility_map('some_table',pages.page_no)
ORDER BY pages.page_no;
page_no | all_visible | all_frozen
---------+-------------+------------
0       | t           | f
1       | f           | f
2       | t           | t
3       | f           | f
4       | f           | t
(4 строки)

mvcc_db=# VACUUM some_table;
VACUUM

При выполнении команд параллельных транзакций не проводилось. Автоочистка в экземпляре Pangolin отключена.

В каких табличных страницах выполнялась заморозка версий строк при вызове последней команды? Выберите все верные варианты ответа:

Вопрос 4

В специальном пространстве табличной страницы физически хранится значения xid_base = 1500.

В заголовке версии строки в табличной странице физически хранится значение xmin = 1000.

Какое значение xmin заголовка версии строки используется при определении ее видимости?

Анализ заморозки версий строк​

Анализ влияния параметра vacuum_freeze_min_age​

Анализ влияния параметра vacuum_freeze_table_age​

Заморозка командой VACUUM FREEZE​

Номер транзакции и признак заморозки в табличной странице​

Завершение​

Самопроверка​