Уровень 3.0

Анализ HOT-обновлений и внутристраничной очистки

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

• Изучена лекция «Внутристраничная очистка и оптимизация HOT»

Подготовка

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

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

   -bash-4.4$ psql

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

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

   postgres=# CREATE DATABASE mvcc_db;

   CREATE DATABASE

   postgres=# \c mvcc_db

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

3. Создайте расширение pageinspect:

   mvcc_db=# CREATE EXTENSION pageinspect;

   CREATE EXTENSION

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

4. Создайте простую таблицу и индекс типа B-дерево по одному столбцу:

   mvcc_db=# CREATE TABLE some_table(id integer, value char(1000)) WITH (autovacuum_enabled=false, fillfactor=50);

   CREATE TABLE

   mvcc_db=# CREATE INDEX id_idx ON some_table(id);

   CREATE INDEX

   Таблица создана с параметром хранения autovacuum_enabled = false, чтобы автоочистка не мешала проведению эксперимента.

   Также задан параметр хранения fillfactor = 50 для того, чтобы после заполнения табличной страницы на 50 % срабатывала внутристраничная очистка.

   Табличная версия строки без заголовка будет занимать 1004 байта (integer + char(1000)) + 24 байта заголовка.

   Итого 1028 байт на табличную версию строки.

   Страница имеет размер 8192 байт, из них заголовок страницы и специальное пространство — по 24 байта каждый, а также по 4 байта на указатели версий строк. Значение fillfactor, равное 50 %, резервирует не более 4072 байт.

   Таким образом, без превышения значения fillfactor в странице может храниться не более трех версий строк объемом 1028 байт.

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

   mvcc_db=# CREATE OR REPLACE FUNCTION get_tuples_info(relname text, pagenum bigint)
   RETURNS TABLE(ctid text, status text, xmin xid, xmax xid, next_ctid text,
       heap_hot_updated boolean, heap_only_tuple boolean)
   AS $$
   SELECT '(' || $2 || ', ' || lp || ')',
       CASE lp_flags
           WHEN 0 THEN 'unused'
           WHEN 1 THEN 'normal'
           WHEN 2 THEN 'redirect to '||lp_off
           WHEN 3 THEN 'dead'
       END,
       t_xmin, t_xmax, t_ctid,
       'HEAP_HOT_UPDATED' = ANY(raw_flags),
       'HEAP_ONLY_TUPLE' = ANY(raw_flags)
            FROM heap_page_items(get_raw_page($1, 'main', $2)),
              LATERAL heap_tuple_infomask_flags(t_infomask, t_infomask2)
       ORDER BY lp;
   $$ LANGUAGE SQL;

   CREATE FUNCTION

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

   В данной реализации добавлены возвращаемые параметры, необходимые для отслеживания HOT-обновлений:

   • status — статус указателя на версию строки
   • heap_hot_updated — информационный бит, указывающий, что версия строки имеет ссылку на следующую версию строки в HOT-цепочке
   • heap_only_tuple — информационный бит, указывающий, что на версию строки нет ссылок из индексов

   Также из возвращаемых параметров исключены информационные биты со статусами транзакций xmin и xmax.

   На вход функции передаются имя отношения (relname) и номер страницы (pagenum).

HOT-обновления

1. Вставьте одну строку в таблицу some_table и посмотрите содержимое табличной и индексной страниц:

   mvcc_db=# INSERT INTO some_table VALUES (1, '100');

   INSERT 0 1

   mvcc_db=# SELECT * FROM get_tuples_info('some_table', 0);

     ctid  | status |  xmin   | xmax | next_ctid | heap_hot_updated | heap_only_tuple
   --------+--------+---------+------+-----------+------------------+-----------------
    (0, 1) | normal | 2901148 |    0 | (0,1)     | f                | f
   (1 строка)

   mvcc_db=# SELECT itemoffset, ctid, dead FROM bt_page_items('id_idx', 1);

    itemoffset | ctid  | dead
   ------------+-------+------
             1 | (0,1) | f
   (1 строка)

   На данный момент в табличной странице одна «живая» версия строки (0, 1) со статусом normal.

   Информационные биты heap_hot_updated и heap_only_tuple не выставлены, так как строка еще не обновлялась и на нее имеется ссылка из индекса.

   В индексной странице находится соответствующая индексная строка, ссылающаяся на версию строки в табличной странице.

   При этом, как и ожидалось, индексная строка не помечена флагом dead.

2. Обновите значение столбца value вставленной строки и посмотрите содержимое табличной и индексной страниц:

   mvcc_db=# UPDATE some_table SET value = '200' WHERE id = 1;

   UPDATE

   mvcc_db=# SELECT * FROM get_tuples_info('some_table', 0);

     ctid  | status |  xmin   |  xmax   | next_ctid | heap_hot_updated | heap_only_tuple
   --------+--------+---------+---------+-----------+------------------+-----------------
    (0, 1) | normal | 2901148 | 2901149 | (0,2)     | t                | f
    (0, 2) | normal | 2901149 |       0 | (0,2)     | f                | t
   (2 строки)

   mvcc_db=# SELECT itemoffset, ctid, dead FROM bt_page_items('id_idx', 1);

    itemoffset | ctid  | dead
   ------------+-------+------
             1 | (0,1) | f
   (1 строка)

   В табличной странице создана вторая версия строки (0, 2).

   В первой версии строки (0, 1) проставлен информационный бит heap_hot_updated, свидетельствующий, что произошло HOT-обновление.

   Также в первой версии строки в поле next_ctid указана ссылка на следующую версию строки (0, 2) в HOT-цепочке.

   Во второй версии строки (0, 2) проставлен информационный бит heap_only_tuple, свидетельствующий, что ссылок из индексов на данную версию строки нет.

   В индексной странице ничего не поменялось. Осталась только одна индексная строка, ссылающаяся на первую версию строки в цепочке.

   Это произошло, потому что обновление не затронуло столбец id, по которому построен индекс, и соответственно сработала оптимизация HOT.

3. Еще дважды обновите значение столбца value и посмотрите содержимое табличной и индексной страниц:

   mvcc_db=# UPDATE some_table SET value = '300' WHERE id = 1;

   UPDATE 1

   mvcc_db=# UPDATE some_table SET value = '400' WHERE id = 1;

   UPDATE 1

   mvcc_db=# SELECT * FROM get_tuples_info('some_table', 0);

     ctid  | status |  xmin   |  xmax   | next_ctid | heap_hot_updated | heap_only_tuple
   --------+--------+---------+---------+-----------+------------------+-----------------
    (0, 1) | normal | 2901148 | 2901149 | (0,2)     | t                | f
    (0, 2) | normal | 2901149 | 2901150 | (0,3)     | t                | t
    (0, 3) | normal | 2901150 | 2901151 | (0,4)     | t                | t
    (0, 4) | normal | 2901151 |       0 | (0,4)     | f                | t
   (4 строки)

   mvcc_db=# SELECT itemoffset, ctid, dead FROM bt_page_items('id_idx', 1);

    itemoffset | ctid  | dead
   ------------+-------+------
             1 | (0,1) | f
   (1 строка)

   Теперь в цепочке 4 версии строки. В индексе по-прежнему одна строка.

   Значение fillfactor должно быть уже превышено. Проверить это можно, обратившись к данной странице и выполнив любой запрос, который должен вызвать внутристраничную очистку в случае превышения fillfactor.

Внутристраничная очистка

1. Выполните запрос к таблице some_table и посмотрите содержимое табличной и индексной страниц:

   mvcc_db=# SELECT id FROM some_table;

    id
   ----
     1
   (1 строка)

   mvcc_db=# SELECT * FROM get_tuples_info('some_table', 0);

     ctid  |    status     |  xmin   | xmax | next_ctid | heap_hot_updated | heap_only_tuple
   --------+---------------+---------+------+-----------+------------------+-----------------
    (0, 1) | redirect to 4 |         |      |           |                  |
    (0, 2) | unused        |         |      |           |                  |
    (0, 3) | unused        |         |      |           |                  |
    (0, 4) | normal        | 2901151 |    0 | (0,4)     | f                | t
   (4 строки)

   mvcc_db=# SELECT itemoffset, ctid, dead FROM bt_page_items('id_idx', 1);

    itemoffset | ctid  | dead
   ------------+-------+------
             1 | (0,1) | f
   (1 строка)

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

   Очищены версии строк (0, 1), (0, 2) и (0, 3).

   Однако указатель на версию строки (0, 1) не освобожден, так как на него по-прежнему имеется ссылка в индексе.

   При этом статус указателя изменен на redirect to 4 для сохранения возможности перехода на версию строки (0, 4) при индексном доступе.

   В индексной странице по-прежнему без изменений.

   Указатели версий строк (0, 2) и (0, 3) освобождены и могут быть переиспользованы, о чем говорит статус unused.

2. Обновите столбец id той же строки и посмотрите содержимое табличной и индексной страниц:

   mvcc_db=# UPDATE some_table SET id = 2 WHERE id = 1;

   UPDATE 1

   mvcc_db=# SELECT * FROM get_tuples_info('some_table', 0);

     ctid  |    status     |  xmin   |  xmax   | next_ctid | heap_hot_updated | heap_only_tuple
   --------+---------------+---------+---------+-----------+------------------+-----------------
    (0, 1) | redirect to 4 |         |         |           |                  |
    (0, 2) | normal        | 2901153 |       0 | (0,2)     | f                | f
    (0, 3) | unused        |         |         |           |                  |
    (0, 4) | normal        | 2901151 | 2901153 | (0,2)     | f                | t
   (4 строки)

   mvcc_db=# SELECT itemoffset, ctid, dead FROM bt_page_items('id_idx', 1);

    itemoffset | ctid  | dead
   ------------+-------+------
             1 | (0,1) | f
             2 | (0,2) | f
   (2 строки)

   На этот раз обновление затронуло проиндексированный столбец, поэтому HOT-обновление не сработало и была создана новая индексная строка.

   При этом в табличной странице для новой версии строки был задействован недавно освобожденный указатель (0, 2).

3. Еще дважды обновите значение столбца id и посмотрите содержимое табличной и индексной страниц:

   mvcc_db=# UPDATE some_table SET id = 3 WHERE id = 2;

   UPDATE 1

   mvcc_db=# UPDATE some_table SET id = 4 WHERE id = 3;

   UPDATE 1

   mvcc_db=# SELECT * FROM get_tuples_info('some_table', 0);

     ctid  |    status     |  xmin   |  xmax   | next_ctid | heap_hot_updated | heap_only_tuple
   --------+---------------+---------+---------+-----------+------------------+-----------------
    (0, 1) | redirect to 4 |         |         |           |                  |
    (0, 2) | normal        | 2901153 | 2901154 | (0,3)     | f                | f
    (0, 3) | normal        | 2901154 | 2901155 | (0,5)     | f                | f
    (0, 4) | normal        | 2901151 | 2901153 | (0,2)     | f                | t
    (0, 5) | normal        | 2901155 |       0 | (0,5)     | f                | f
   (5 строк)

   mvcc_db=# SELECT itemoffset, ctid, dead FROM bt_page_items('id_idx', 1);

    itemoffset | ctid  | dead
   ------------+-------+------
             1 | (0,1) | f
             2 | (0,2) | f
             3 | (0,3) | f
             4 | (0,5) | f
   (4 строки)

   Теперь в табличной странице 4 версии строки. Для каждой из них в индексе имеется своя строка, поскольку оптимизация HOT не срабатывает.

   Значение параметра fillfactor должно быть превышено.

4. Выполните запрос к таблице some_table и посмотрите содержимое табличной и индексной страниц:

   mvcc_db=# SELECT id FROM some_table;

    id
   ----
     4
   (1 строка)

   mvcc_db=# SELECT * FROM get_tuples_info('some_table', 0);

     ctid  | status |  xmin   | xmax | next_ctid | heap_hot_updated | heap_only_tuple
   --------+--------+---------+------+-----------+------------------+-----------------
    (0, 1) | dead   |         |      |           |                  |
    (0, 2) | dead   |         |      |           |                  |
    (0, 3) | dead   |         |      |           |                  |
    (0, 4) | unused |         |      |           |                  |
    (0, 5) | normal | 2901155 |    0 | (0,5)     | f                | f
   (5 строк)

   mvcc_db=# SELECT itemoffset, ctid, dead FROM bt_page_items('id_idx', 1);

    itemoffset | ctid  | dead
   ------------+-------+------
             1 | (0,1) | f
             2 | (0,2) | f
             3 | (0,3) | f
             4 | (0,5) | f
   (4 строки)

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

   Однако их статус помечен как dead.

   При следующем обращении к ним через индекс в самом индексе соответствующие строки также будут помечены статусом dead.

   Обратите внимание, что статус указателя на версию строки (0, 1) изменился с redirect to 4 на dead.

   Это произошло в связи с тем, что версия строки (0, 4) была вычищена, но при этом в индексе по-прежнему имеется ссылка на версию строки (0, 1).

5. Отключите возможность использования путей доступа «последовательное сканирование» и «сканирование по битовой карте»:

   mvcc_db=# SET enable_seqscan = off;

   SET

   mvcc_db=# SET enable_bitmapscan = off;

   SET

   Методы «последовательное сканирование» и «сканирование по битовой карте» отключены, чтобы любое обращение к таблице выполнялось через обращение к индексной странице.

   Для таблиц малого объема планировщик выбирает метод «последовательное сканирование» независимо от селективности условия поиска.

6. Выполните запрос к таблице some_table и посмотрите содержимое индексной страницы:

   mvcc_db=# SELECT id FROM some_table;

    id
   ----
     4
   (1 строка)

   mvcc_db=# SELECT itemoffset, ctid, dead FROM bt_page_items('id_idx', 1);

    itemoffset | ctid  | dead
   ------------+-------+------
             1 | (0,1) | t
             2 | (0,2) | t
             3 | (0,3) | t
             4 | (0,5) | f
   (4 строки)

   Обращение к таблице выполнено посредством индекса.

   При обращении было определено, что три версии строки имеют статус dead и такие же статусы проставлены в соответствующих индексных строках.

   Далее, если при очередной вставке новой индексной строки в странице не окажется места для нее, будет вызвана внутристраничная очистка индексной страницы и строки со статусом dead будут очищены.

   Вспомним, что внутристраничная очистка индексных страниц предотвращает их расщепление.

7. Посмотрите статистику обновлений таблицы some_table с помощью представления pg_stat_all_tables:

   mvcc_db=# SELECT relname, n_tup_upd, n_tup_hot_upd FROM pg_stat_all_tables WHERE relname = 'some_table';

     relname   | n_tup_upd | n_tup_hot_upd
   ------------+-----------+---------------
    some_table |         6 |             3
   (1 строка)

   Всего было выполнено 6 обновлений, из которых 3 с использованием оптимизации HOT.

Разрыв HOT-цепочки

1. Очистите таблицу some_table и установите параметр fillfactor в 100 %:

   mvcc_db=# TRUNCATE some_table;

   TRUNCATE TABLE

   mvcc_db=# ALTER TABLE some_table SET (fillfactor=100);

   ALTER TABLE

2. Вставьте одну строку, 5 раз ее обновите и вставьте еще одну строку:

   mvcc_db=# INSERT INTO some_table VALUES(1, '100');

   INSERT 0 1

   mvcc_db=# UPDATE some_table SET value = '200';

   UPDATE 1

   mvcc_db=# UPDATE some_table SET value = '300';

   UPDATE 1

   mvcc_db=# UPDATE some_table SET value = '400';

   UPDATE 1

   mvcc_db=# UPDATE some_table SET value = '500';

   UPDATE 1

   mvcc_db=# UPDATE some_table SET value = '600';

   UPDATE 1

   mvcc_db=# INSERT INTO some_table VALUES(2, '200');

   INSERT 0 1

3. Посмотрите содержимое табличной и индексной страниц:

   mvcc_db=# SELECT * FROM get_tuples_info('some_table', 0);

     ctid  | status |  xmin  |  xmax  | next_ctid | heap_hot_updated | heap_only_tuple
   --------+--------+--------+--------+-----------+------------------+-----------------
    (0, 1) | normal | 139407 | 139408 | (0,2)     | t                | f
    (0, 2) | normal | 139408 | 139409 | (0,3)     | t                | t
    (0, 3) | normal | 139409 | 139410 | (0,4)     | t                | t
    (0, 4) | normal | 139410 | 139411 | (0,5)     | t                | t
    (0, 5) | normal | 139411 | 139412 | (0,6)     | t                | t
    (0, 6) | normal | 139412 |      0 | (0,6)     | f                | t
    (0, 7) | normal | 139413 |      0 | (0,7)     | f                | f
   (7 строк)

   mvcc_db=# SELECT itemoffset, ctid, dead FROM bt_page_items('id_idx', 1);

    itemoffset | ctid  | dead
   ------------+-------+------
             1 | (0,1) | f
             2 | (0,7) | f
   (2 строки)

   Как и ожидалось, в индексе имеется всего две записи — по одной для каждой вставленной.

   При этом для первой вставленной строки в табличной странице организована HOT-цепочка.

4. Еще выполните обновление первой вставленной строки и посмотрите содержимое табличной и индексной страниц:

   mvcc_db=# UPDATE some_table SET value = 700 WHERE id = 1;

   UPDATE 1

   mvcc_db=# SELECT * FROM get_tuples_info('some_table', 0);

     ctid  | status |  xmin  |  xmax  | next_ctid | heap_hot_updated | heap_only_tuple
   --------+--------+--------+--------+-----------+------------------+-----------------
    (0, 1) | normal | 139407 | 139408 | (0,2)     | t                | f
    (0, 2) | normal | 139408 | 139409 | (0,3)     | t                | t
    (0, 3) | normal | 139409 | 139410 | (0,4)     | t                | t
    (0, 4) | normal | 139410 | 139411 | (0,5)     | t                | t
    (0, 5) | normal | 139411 | 139412 | (0,6)     | t                | t
    (0, 6) | normal | 139412 | 139414 | (1,1)     | f                | t
    (0, 7) | normal | 139413 |      0 | (0,7)     | f                | f
   (7 строк)

   mvcc_db=# SELECT itemoffset, ctid, dead FROM bt_page_items('id_idx', 1);

    itemoffset | ctid  | dead
   ------------+-------+------
             1 | (0,1) | f
             2 | (1,1) | f
             3 | (0,7) | f
   (3 строки)

   Количество версий строк в табличной странице не изменилось, поскольку она полностью заполнена.

   Новая версия строки была размещена на новой странице, о чем свидетельствует значение next_ctid = (1, 1) в заголовке версии строки (0, 6).

   При этом в индексной странице появилась новая строка, ссылающаяся на версию строки (1, 1).

   Таким образом, произошел разрыв цепочки.

   Проверим, что новая версия строки действительно попала на новую страницу.

5. Посмотрите содержимое первой табличной страницы:

   mvcc_db=# SELECT * FROM get_tuples_info('some_table', 1);

     ctid  | status |  xmin  |  xmax  | next_ctid | heap_hot_updated | heap_only_tuple
   --------+--------+--------+--------+-----------+------------------+-----------------
    (1, 1) | normal | 139414 | 0      | (1,1)     | f                | f
   (1 строка)

   Разрыва HOT-цепочки можно было бы избежать, если бы значение fillfactor было бы меньше 100 %.

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

   Соответственно, в индексной странице не пришлось бы создавать лишней индексной строки.

Завершение

1. Подключитесь к базе данных postgres:

   mvcc_db=# \c postgres

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

2. Удалите базу данных mvcc_db и завершите сеанс psql:

   postgres=# DROP DATABASE mvcc_db;

   DROP DATABASE

   postgres=# \q

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

Вопрос 1

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

| ctid | next ctid | heap hot updated | heap only tuple | | --- | --- | --- | --- | | (0, 1) | (0, 2) | 1 | 0 | | (0, 2) | (0, 4) | 1 | 1 | | (0, 3) | (0, 3) | 0 | 0 | | (0, 4) | (1, 1) | 0 | 1 |

Для таблицы создан индекс типа B-дерево, покрывающий все множество строк.

На какие версии строк имеются ссылки из индекса? Выберите все верные варианты ответа

(0, 1)

(0, 2)

(0, 3)

(0, 4)

(1, 1)

Вопрос 2

Состояние указателей на версии строк в табличной странице представлено ниже:

| ctid | status | | --- | --- | | (0, 1) | dead | | (0, 2) | normal | | (0, 3) | unused | | (0, 4) | redirect to 2|

Сколько версий строк имеется в табличной странице?

Вопрос 3

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

mvcc_db=# \d+ some_table
Таблица "public.some_table"
Столбец |   Тип   | Правило сортировки | Допустимость NULL | По умолчанию | Хранилище | Сжатие | Цель для статистики | Описание
---------+---------+--------------------+-------------------+--------------+-----------+--------+---------------------+----------
id      | integer |                    |                   |              | plain     |        |                     |
value   | integer |                    |                   |              | plain     |        |                     |
Индексы:
"id_idx" btree (id)
Метод доступа: heap
Параметры: autovacuum_enabled=off

mvcc_db=# SELECT itemoffset, ctid, dead FROM bt_page_items('id_idx', 1);
itemoffset | ctid  | dead
------------+-------+-----
1 | (0,1) | t
2 | (0,2) | t
3 | (0,3) | f
4 | (0,4) | f
(1 строка)

mvcc_db=# INSERT INTO some_table VALUES (100, 1000);

Индекс "id_idx" содержит только одну листовую страницу.

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

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

Вопрос 4

Состояние указателей на версии строк в табличной странице представлено ниже:

| ctid | status | | --- | --- | | (0, 1) | dead | | (0, 2) | normal | | (0, 3) | unused | | (0, 4) | redirect to 2|

Сколько версий строк имеется в табличной странице?

Вопрос 5

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

mvcc_db=# CREATE TABLE some_table(id integer, value char(3000)) WITH (fillfactor = 50, autovacuum_enabled = off);
CREATE TABLE

mvcc_db=# CREATE INDEX id_idx ON some_table(id);
CREATE INDEX

mvcc_db=# INSERT INTO some_table VALUES(1, '100');
INSERT 0 1

mvcc_db=# INSERT INTO some_table VALUES(2, '200');
INSERT 0 1

mvcc_db=# UPDATE some_table SET value = '150' WHERE id = 1;
UPDATE 1

mvcc_db=# SELECT count(*) FROM bt_page_items('id_idx', 1);

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

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