Уровень 3.0

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

Изучена лекция «Кеш буферов»

Анализ операций в кеше буферов

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

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

-bash-4.4$ psql

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

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

postgres=# CREATE DATABASE buffercache_db;

CREATE DATABASE

postgres=# \c buffercache_db

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

Создайте расширение pg_buffercache:
```
buffercache_db=# CREATE EXTENSION pg_buffercache;
```
```
CREATE EXTENSION
```
Расширение pg_buffercache позволяет посмотреть содержимое кеша буферов.
Создайте функцию, которая будет выдавать информацию о буферах, занимаемых заданной таблицей:
```
buffercache_db=# CREATE OR REPLACE FUNCTION get_buffers_info(relname text)
RETURNS TABLE(buffer_id integer, file_node integer, fork text, page_number integer,
    pin_count integer, usage_count integer,
    is_dirty boolean)
AS $$
SELECT bufferid, relfilenode,
  CASE relforknumber
         WHEN 0 THEN 'main'
         WHEN 1 THEN 'fsm'
         WHEN 2 THEN 'vm'
       END fork,
  relblocknumber, pinning_backends, usagecount, isdirty
  FROM pg_buffercache
  WHERE reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
    AND relfilenode = pg_relation_filenode($1::regclass);
$$ LANGUAGE SQL;
```
```
CREATE FUNCTION
```
Созданная функция с использованием расширения pg_buffercache позволяет получить информацию по каждому из буферов в кеше, занятому заданной таблицей.

Для каждого буфера функция возвращает следующую информацию:
- buffer_id — номер буфера в кеше
- file_node — идентификатор файла отношения
- fork — слой отношения (main, vm или fsm)
- page_number — номер страницы в файле
- pin_count — количество закреплений буфера
- usage_count — количество обращений к странице в буфере
- is_dirty — является ли страница «грязной»
Создайте простую таблицу:
```
buffercache_db=# CREATE TABLE some_table(value char(3000)) WITH (autovacuum_enabled=false, fillfactor=50, toast_tuple_target=4000);
```
```
CREATE TABLE
```
В таблице всего один столбец value размером 3000 байт.

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

При этом вставлена может быть только одна версия, поскольку задан параметр хранения fillfactor= 50. Вторая версия строки может оказаться в странице только в результате выполнения операции обновления.

Параметр хранения toast_tuple_target=4000 необходим для того, чтобы значение столбца value не сжималось и не перемещалось в TOAST-таблицу.

Дело в том, что в соответствии со стратегией хранения EXTENDED, которая является базовой для типа char, значения столбцов, превышающие 2 kB, либо сжимаются, либо переносятся в TOAST-таблицу. Порог в 2 kB как раз задан значением по умолчанию параметра toast_tuple_target.

Также для созданной таблицы отключена автоочистка параметром хранения autovacuum_enabled.

Операции в кеше буферов

Вставьте в таблицу some_table одну строку и посмотрите содержимое кеша буферов:
```
buffercache_db=# INSERT INTO some_table VALUES('1');
```
```
INSERT 0 1
```
```
buffercache_db=# SELECT * FROM get_buffers_info('some_table');
```
```
 buffer_id | file_node | fork | page_number | pin_count | usage_count | is_dirty
-----------+-----------+------+-------------+-----------+-------------+----------
       442 |     17538 | main |           0 |         0 |           1 | t
(1 строка)
```
В результате вставки новая страница была размещена в 442-м буфере.

Счетчик обращений usage_count увеличился на единицу.

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

Поскольку страница только что создана и она пока что имеется только в кеше буферов, для нее выставлен признак is_dirty.
Начните транзакцию, откройте курсор и получите вставленную строку:
```
buffercache_db=# BEGIN;
```
```
BEGIN
```
```
buffercache_db=*# DECLARE cur CURSOR FOR SELECT value::integer FROM some_table;
```
```
DECLARE CURSOR
```
```
buffercache_db=*# FETCH cur;
```
```
 value
-------
     1
(1 строка)
```
С использованием курсора можно убедиться, что счетчик закреплений действительно используется.

Открытый курсор удерживает прочитанную страницу закрепленной с целью быстрого получения следующей строки из нее командой FETCH.
Посмотрите содержимое кеша буферов:
```
buffercache_db=*# SELECT * FROM get_buffers_info('some_table');
```
```
 buffer_id | file_node | fork | page_number | pin_count | usage_count | is_dirty
-----------+-----------+------+-------------+-----------+-------------+----------
       442 |     17538 | main |           0 |         1 |           2 | t
(1 строка)
```
Теперь благодаря открытому курсору счетчик pin_count равен 1.

Счетчик usage_count теперь равен 2, поскольку произошло второе обращение к странице в буфере.

Обратите внимание, что страница по-прежнему «грязная». Однако в вашем эксперименте это могло быть не так.

Дело в том, что с определенной периодичностью (по умолчанию раз в 5 минут) «грязные» страницы сбрасываются на диск процессом checkpointer, речь о котором пойдет в теме 9.

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

Зафиксируйте транзакцию и посмотрите содержимое кеша буферов:

buffercache_db=# COMMIT;

COMMIT

buffercache_db=# SELECT * FROM get_buffers_info('some_table');

 buffer_id | file_node | fork | page_number | pin_count | usage_count | is_dirty
-----------+-----------+------+-------------+-----------+-------------+----------
       442 |     17538 | main |           0 |         0 |           2 | t
(1 строка)

После фиксации транзакции курсор отпустил страницу и счетчик pin_count снова стал равен 0.

Вставьте еще одну строку и посмотрите содержимое кеша буферов:
```
buffercache_db=# INSERT INTO some_table VALUES ('2');
```
```
INSERT 0 1
```
```
buffercache_db=# SELECT * FROM get_buffers_info('some_table');
```
```
 buffer_id | file_node | fork | page_number | pin_count | usage_count | is_dirty
-----------+-----------+------+-------------+-----------+-------------+----------
       442 |     17538 | main |           0 |         0 |           3 | t
       474 |     17538 | fsm  |           2 |         0 |           1 | t
       475 |     17538 | fsm  |           0 |         0 |           1 | f
       476 |     17538 | main |           1 |         0 |           1 | t
(4 строки)
```
Для новой вставленной строки был выделен новый буфер с номером 476. Вспомним, что таблица создана таким образом, что для каждой вставляемой строки создается новая страница данных.

Однако в кеше было задействовано еще два буфера с номерами 474 и 475.

Это произошло потому, что при вставке второй строки потребовалось обратиться к карте свободного пространства (fsm).

Она представляет собой дерево, поэтому потребовалось прочитать две страницы. Нулевая страница (page_number = 0) является корнем дерева, а вторая страница (page_number = 2) является листовой.

При этом при вставке сначала было обращение к нулевой странице данных и счетчик обращений был увеличен на единицу (usage_count = 3).

При обращении стало ясно, что новую версию строки в страницу вставлять нельзя из-за fillfactor = 50 и необходимо создать новую страницу, а также сделать соответствующую отметку в карте свободного пространства.
Посмотрите план выполнения запроса данных из таблицы some_table:
```
EXPLAIN (analyze, buffers, costs off, timing off, summary off) SELECT * FROM some_table;
```
```
                   QUERY PLAN
------------------------------------------------
 Seq Scan on some_table (actual rows=2 loops=1)
   Buffers: shared hit=2
(2 строки)
```
Команда EXPLAIN позволяет посмотреть план, построенный планировщиком для выполнения запроса.

При этом c опцией analyze запрос также выполняется в соответствии с построенным планом.

Опция buffers позволяет посмотреть, сколько при выполнении запроса фактически было прочитано страниц из кеша, а сколько пришлось прочитать из файла данных.

С помощью остальных опций отключен вывод ненужной в настоящий момент информации.

Из плана видно, что для выполнения запроса потребовалось прочитать две страницы, которые были найдены в кеше (shared hit = 2).

Если пришлось обратиться к диску при выполнении запроса, то в плане в разделе Buffers также появилось поле read.

Использование буферного кольца

Посмотрите значение параметра shared_buffers:
```
buffercache_db=# SELECT setting, unit FROM pg_settings WHERE name = 'shared_buffers';
```
```
 setting | unit
---------+------
 16384   | 8kB
(1 строка)
```
Размер кеша буферов измеряется количеством страниц данных.

По умолчанию это значения равно 16384, что соответствует 128 MB.
Вставьте в таблицу some_table 16384/4 строк:
```
buffercache_db=# INSERT INTO some_table (SELECT '100' FROM generate_series(1, 16384/4));
```
```
INSERT 0 4096
```
Таблица была заполнена таким образом, чтобы ее размер немного превосходил четверть размера буферного кеша.

Было вставлено 16384/4 строк, каждая из которых размещена в отдельной странице.

При этом в таблице ранее было создано еще две страницы.

Проверим, что действительно теперь количество страниц в таблице превосходит четверть количества буферов в кеше.
Проанализируйте таблицу и посмотрите, сколько страниц она теперь занимает:
```
buffercache_db=# ANALYZE some_table;
```
```
ANALYZE
```
```
buffercache_db=# SELECT relname, relpages FROM pg_class WHERE relname = 'some_table';
```
```
  relname   | relpages
------------+----------
 some_table |     4098
(1 строка)
```
Все верно, теперь таблица занимает 4098 страниц, а четверть кеша — это 4096 страниц.
Проверьте, что все страницы основного слоя таблицы some_table сейчас размещены в кеше буферов:
```
buffercache_db=# SELECT count(*) FROM get_buffers_info('some_table') WHERE fork = 'main';
```
```
 count
-------
  4098
(1 строка)
```
При этом все вставляемые строки, естественно, сначала были размещены в страницах в кеше.
Перезагрузите экземпляр СУБД и снова подключитесь к базе данных buffercache_db:
```
buffercache_db=# \q
```
```
-bash-4.4$ pg_ctl restart -l logfile
```
```
ожидание завершения работы сервера.... готово
сервер остановлен
ожидание запуска
```
```
-bash-4.4$ psql -d buffercache_db
```
```
psql (15.5)
Введите "help", чтобы получить справку.
```
Можно подумать, что при перезагрузке будут потеряны данные. Ведь все вставленные строки были только в кеше в оперативной памяти.

Но это, конечно, не так. При остановке сервера принудительно вызывается процесс checkpointer, который сбрасывает все «грязные» страницы на диск.

Даже в случае неаккуратной остановки данные все равно не были бы потеряны. Каким образом это обеспечивается, вы узнаете в следующей теме «Журнал предзаписи».
Выполните запрос всех строк в таблице some_table с использованием команды EXPLAIN:
```
buffercache_db=# EXPLAIN (analyze, buffers, costs off, timing off, summary off) SELECT * FROM some_table;
```
```
                    QUERY PLAN
---------------------------------------------------
 Seq Scan on some_table (actual rows=4098 loops=1)
   Buffers: shared read=4098
 Planning:
   Buffers: shared hit=12 read=8 dirtied=1
(4 строки)
```
После перезапуска экземпляра кеш буферов стал пустым.

Поэтому при выполнении запроса всех строк пришлось все страницы прочитать с диска (shared read = 4098).

Информация в разделе Planning плана запроса в рамках данной темы не представляет интереса.
Посмотрите, сколько страниц из прочитанных находятся в буферном кеше:
```
buffercache_db=# SELECT count(*) FROM get_buffers_info('some_table') WHERE fork = 'main';
```
```
 count
-------
    32
(1 строка)
```
Из 4098 страниц, прочитанных с диска через кеш буферов, в настоящий момент в нем осталось только 32.

Это объясняется тем, что при последовательном сканировании таблицы размером более четверти размера кеша буферов задействуется буферное кольцо размером 32 страницы.

Вспомним, что это соответствует первой стратегии использования буферного кольца.
Выполните обновление всех строк в таблице some_table с использованием команды EXPLAIN:
```
buffercache_db=# EXPLAIN (analyze, buffers, costs off, timing off, summary off) UPDATE some_table SET value = '200';
```
```
                       QUERY PLAN
---------------------------------------------------------
 Update on some_table (actual rows=0 loops=1)
   Buffers: shared hit=8228 read=4066 dirtied=4098
   ->  Seq Scan on some_table (actual rows=4098 loops=1)
         Buffers: shared hit=32 read=4066
 Planning:
   Buffers: shared hit=3
(6 строк)
```
Обновление всех строк таблицы также выполнялось последовательным сканированием с использованием буферного кольца.

При этом 32 страницы были найдены в буферном кольце (см. Seq Scan — shared hit = 32), а 4066 пришлось прочитать из файла (см. Seq Scan — read = 4066).

Однако, в соответствии с первой стратегией, буферы, которые становятся «грязными», исключаются из буферного кольца и переносятся в общий кеш.

Поэтому все 4098 страниц таблицы оказались в общем кеше буферов.

В плане запроса этому соответствует поле shared hit = 8228 в узле Update.

Это значение складывается из:
- 32 буферов, прочитанных из кеша при последовательном сканировании
- 4098 буферов, прочитанных в буферном кольце при обновлении строк
- И тех же 4098 буферов, прочитанных при их переносе из кольца в общий кеш
Также в разделе Buffers появилось поле dirtied, показывающее, сколько страниц в кеше стали «грязными».
Посмотрите, сколько страниц таблицы some_table находятся в буферном кеше:
```
buffercache_db=# SELECT count(*) FROM get_buffers_info('some_table') WHERE fork = 'main';
```
```
 count
-------
  4098
(1 строка)
```
Как видно, в результате выполнения операции массового обновления кеш засорен одноразовыми данными.

Буферное кольцо при этом никак не помогло.

Из этого можно сделать вывод, что обновление большого количества строк следует выполнять небольшими порциями. Это позволит избежать вытеснения горячих данных из кеша буферов.

Мониторинг использования кеша буферов

Посмотрите статистику использования кеша буферов при обращении к таблице some_table:
```
buffercache_db=# SELECT relname, heap_blks_read, heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'some_table';
```
```
  relname   | heap_blks_read | heap_blks_hit
------------+----------------+---------------
 some_table |          12265 |         24617
(1 строка)
```
В результате проведенных экспериментов при работе с таблицей some_table из кеша было прочитано 24617 страниц, а к файлам пришлось обратиться 12265 раз.
Посмотрите общую статистику использования кеша буферов:
```
buffercache_db=# SELECT usagecount, count(*) FROM pg_buffercache GROUP BY usagecount ORDER BY usagecount;
```
```
 usagecount | count
------------+-------
          1 |  4151
          2 |    45
          3 |     7
          4 |     8
          5 |   319
            | 11854
(6 строк)
```
Сгруппировав буферы по значениям usagecount, можно сделать выводы об эффективности использования кеша буферов и достаточности его размера.

В данном случае, несмотря на очень небольшой размер кеша буферов, большая часть буферов (11854) осталась пустой.

При этом 319 буферов использовались очень активно (usagecount = 5). Вероятно, это страницы системного каталога.

В случае, если бы все буферы имели значение usagecount, близкое к 5, стоило бы задуматься об увеличении размера кеша буферов.

Однако оценку необходимого размера кеша буферов нельзя делать на основе одного среза его состояния.

Для получения состоятельной оценки может потребоваться длительное наблюдение за состоянием кеша буферов.

Завершение

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

buffercache_db=# \c postgres

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

postgres=# DROP DATABASE buffercache_db;

DROP DATABASE

postgres=# \q

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

Вопрос 1

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

Seq Scan on test_table (actual rows=500000 loops=1)
Buffers: shared hit=364 read=56
Planning:
Buffers: shared hit=76 read=9

Какое количество страниц данных было прочитано при выполнении запроса в соответствии с указанным планом?

Вопрос 2

Состояние буферов в кеше представлено ниже:

buffer_id | file_node | fork | page_number | pin_count | usage_count | is_dirty
-----------+-----------+------+-------------+-----------+-------------+----------
501 |     17538 | main |           0 |         1 |           0 | f
502 |     30945 | fsm  |           2 |         0 |           0 | t
503 |     30945 | fsm  |           0 |         0 |           1 | f
504 |     30945 | main |          12 |         0 |           3 | f

Какие из буферов соответствуют критерию вытеснения страницы данных?

501

502

503

504

Вопрос 3

Данные в таблице some_table хранятся в 10000 страницах основного слоя. Таблица не имеет индексов.

Размер кеша буферов составляет 32768 буферов.

Выполнен следующий запрос:

SELECT * FROM some_table;

Сколько буферов было задействовано в кеше буферов при выполнении указанного запроса?

Вопрос 4

В psql выполнен следующий запрос:

SELECT usagecount, count(*) FROM pg_buffercache GROUP BY usagecount ORDER BY usagecount;
usagecount | count
------------+-------
1 |  2700
2 |   206
3 |   100
4 |   100
5 |   100
| 13178

Выберите все верные утверждения относительно полученного состояния кеша буферов:

Размер кеша буферов составляет 16384 буферов

Кеш буферов полностью заполнен

В кеше буферов 3206 свободных буферов

В кеше буферов 13178 свободных буферов

Анализ операций в кеше буферов​

Операции в кеше буферов​

Использование буферного кольца​

Мониторинг использования кеша буферов​

Завершение​

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