DBA2-10 Тема 1 «Изоляция»

Транзакция - множество операций, которые переводят базу данных из одного корректного состояни в другое корретное (соглассованость) при условии, что транзакция выполнена полностью (атомарность) и без помех со стороны других транзакций (изоляция)

Атомарность - транзакция либо выполняется полностью, либо не оставляет никаких следов

Согласованность определяет ограничениями целостности и семантикой приложения

Неполная изоляция приводит к нарушению корректности (аномалиям) при конкурентном выполнении

Возможные аномалии (согласно стандарту, но на деле их больше):

“грязное” чтение - запрос видит незафиксированные изменения других транзакций
неповторяющееся чтение - повторное чтение строки вернет другое значение, если оно было изменено (и зафиксировано) другой транзакцией (тут про ячейки)
фантомное чтение - повторный запрос по одниоу и тому же условию вернет иную выборку, если другая транзакция добавила (и зафиксировала) новые строки, удовлетворяюще этому условию (тут про строки)
остутсвие всяких аномалий - результат выполнения транзакций совпадает с результатом последовательного выполнения транзакций

Уровни изоляции (согласно стандарта)

| Грязное чтение | неповторяющееся чтение | фантомное чтение | другие аномалии | | ------------------- | -------------------------- | -------------------- | ------------------- | --- | | Read Uncommited | да | да | да | да | | Read Committed | - | да | да | да | | Repeatable Read | - | - | да | да | | Serializable | - | - | - | - |

на всех уровнях не допускается потеря зафиксированных изменений

Уровни изоляции postgres

| Грязное чтение | неповторяющееся чтение | фантомное чтение | другие аномалии | | --------------------------------- | -------------------------- | -------------------- | ------------------- | --- | | Read Committed (по умолчанию) | - | да | да | да | | Repeatable Read | - | - | - | да | | Serializable | - | - | - | - |

Демо

SHOW transaction_isolation; -- текущий уровень изоляции
SHOW default_transaction_isolation; -- значение по умолчанию

В read committed данные вычитываются согласованно на начало запуска оператора

select pg_sleep(2), * from demo; -- пока ждем 2 сек, табличка поменялась, но мы не увидим этих изменений

Обновляющие транзакции, в случае конфликтов, вынужденны перевычитать данные (перечитываются только измененные строки)

DBA2-10 Тема 2 «Страницы и версии строк»

Страницы (8кб)
- табличная страница
  - заголовок страницы (24 байта)
  - указатели на версии строк (массив)
  - строка
    - заголовок весрии строки
    - версия строки
- страница индекса
  - заголовок страницы (24 байта)
  - указатели на версии строк (массив)
  - специальная область (специфичные данные для разных видов индексов)
  - строка
    - индексируемое значение
    - указатель на версию строки в странице таблички (обратная ссылка)

Благодаря тому что индексы ссылаются только на указатели сами данные внутри страничек можно реорганизовать никак не трогая индекс

Указатели на версии строк хранят:

статус версии строки
указатель на версию строки и ее длина

Версии строк в табличной странице

За это критикуют postgres, т.к. вне зависимости от размера строки будет всегда плюс 24 байта

xmin committerd, xmin aborted - биты позволяющие ответить на статус завершения транзакции xmin
ctid - указатель на следующую версию этой же строки (аля linked list), если следующей версии нет - указатель смотрит сам на себя

Для индексов все в принципе так же, за исключением того что за вместо данных мы храним сам ключ (значения колонок), а ссылка ведет на саму строку

В индексе нет xmin, xmax, по этому смотря в индекс нельзя сказать должна ли быть видна эта строка, из-за чего необходимо идти в табличку за xmin, xmax после чего уже становиться понятно

Страницы читаются в оперативную память “как есть”, данные неперносимы между разными платформами, между полями данных возможны пропуски из-за выравнивания

Вставка

xmax aborted выставился в ture, что говорит о том что еще нет транзакции удалившей эту версию и поле xmax можно игнорить

XACT - журнал транзакций, здесь мы видим что транзакция под номером 100 все еще жужжит

После комита, в строке ничего не происходит (бит как был так и не проставлялся) последующие запросы будут ходить и сверяться с журналом XACT и уже последующая транзакция выполнив эту проверку проставит этот бит

Это может быть читающая транзакция

Интересный пример, после COPY перегона большого объема данных, потом если выполнить select count(*) будет большая дисковая активность, так как этот sleect начнет проставлять битики

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

Commit дешевый, но плата за это - следующая транзакиция читающая эту строку будет чуть дольше так как будет сверять и обновлять байты

проставляем 101 в xmax, транзакция по все еще бежит (так же ведет себя select for update)

следующая транзакция видит xmax и то что 101 еще не завершилась - как следствие строка заблокированна и надо ждать

так же как и с commit, при вызове rollback обновляется xact, но биты в строке не меняются, бит в строке будет обновлен когда будет обращение от последующей строки

при обновлении мы проставляем 102 в xmax старой версии

содаем новую версию в которой 102 будет в xmin, ссылка старой строки обновляется и смотрит на новую версию

в индексе так же появляется вторая строка ссылающаяся на вторую версию

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

Вложенные транзакции

собственный номер и статус в XACT - конечный статус зависит от статуса основной тразакции
информация о вложенности сохраняется на диске
- каталог $PGDATA/pg_subtrans/
- данные кэшируются в буферах общей памяти (аналогично XACT)
примеры использования
- точка SAVEPOINT
- обработка исключений в PL/pgSQL (EXCEPTION)
- режим psql ON_ERROR_ROLLBACK = on/interactive

Пример

CREATE TABLE t(id serial, s text);
CREATE INDEX t_s ON t(s);
-- что бы изучить структуру страницы и версий строк, есть расширение pageinspect
CREATE EXTENSION pageinspect;

-- вьюха что бы смотреть
-- get_raw_page - читает сырые байтики без понимание что это
-- heap_page_items - парсит именно табличную страничку
CREATE VIEW t_v AS
SELECT '(0,'||lp||')' AS ctid,
       CASE lp_flags
         WHEN 0 THEN 'unused'
         WHEN 1 THEN 'normal'
         WHEN 2 THEN 'redirect to '||lp_off
         WHEN 3 THEN 'dead'
       END AS state,
       t_xmin as xmin,
       t_xmax as xmax,
       CASE WHEN (t_infomask & 256) > 0  THEN 't' END AS xmin_c,
       CASE WHEN (t_infomask & 512) > 0  THEN 't' END AS xmin_a,
       CASE WHEN (t_infomask & 1024) > 0 THEN 't' END AS xmax_c,
       CASE WHEN (t_infomask & 2048) > 0 THEN 't' END AS xmax_a,
       t_ctid
FROM heap_page_items(get_raw_page('t',0))
ORDER BY lp;

-- похожее представление для индекса
-- у индекса первая страничка 0 - занята служебными данными, потому тут мы читаем 1-ю
CREATE VIEW t_s_v AS
SELECT itemoffset,
       ctid
FROM bt_page_items('t_s',1);

В сессии

BEGIN;
INSERT INTO t(s) VALUES('FOO');
SELECT txid_current(); -- 493
SELECT * FROM t_v;
-- похожее но менее информативное
SELECT xmin, xmax, * FROM t;

COMMIT;

SELECT * FROM t_v; -- ничего не поменялось

SELECT * FROM t;

SELECT * FROM t_v; -- а вот теперь будут байтики

Стурктура странички

SELECT lower, upper, special, pagesize FROM page_header(get_raw_page('t',0));

0 - начало заголвка странички и указатели на версии строк
lower - начало свободного места
upper - начало данных (версий строк)
special - начало спец. данных (только для индексов)
pagesize - конец страницы
от 0 до lower - занимает заголовок странички (24 байта) и массив указателей - можно ответить на вопрос сколько указателей
от lower до upper - это свободное место
от upper до special - это занятое место

DBA2-10 Тема 3 «Снимки данных»

Снимок - дает согласованную картину данных на момент времени (видны только зафиксированные на этот момент данные)

Создается

read commited - в начале каждого оператора
repeatable read, serializable - в начале первого оператора транзакции

Видимость версий строк

видимость версии строки ограничена xmin и xmax
версия попадает в снимок, когда
- изменения транзакции xmin видны для снимка
- изменения транзакции xmax не видны для снимка
изменения транзакции видны, когда
- либо это та же самая тразакция, что создала снимок
- либо она звершилась фиксацией до момента создания снимка
отдельные правила для видимости собственных изменений
- учитывается порядковый номер операции в транзакции (cmin/cmax)

Снимок данных

номер самой ранней активной транзакции
номер следующей транзакции (еще не существующей)
список активных транзакций
горизонт - точка до которой проходит autovacuum
пока транзакция висит - автовакуум не может продвигаться вперед
old_snapshot_threshold - время, на которое транзакции разрешено транзакции держать снимок, вакууму разрешено удалять строки, если транзакции понадобяться строки она завершиться с ошибкой и откатиться
idle_in_transaction_session_timeout - прибивать транзакции которые ничего не делают но держат вакуум

Виртуальные транзакции

Только читающая транзакция никак не влияет на видимость

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

select txid_current(); --
select txid_current_if_assigned(); -- более честный\оптимальный способ узнать свой номер
select txid_current_snapshot(); -- xmin:xmax:xip_list
select backend_xmin from pg_stat_activity where pid = pg_backend_pid(); -- горизонт

DBA2-10 Тема 4 «HOT-обновления»

Проблемы обновления

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

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

HOT - heap only tuple
heap - подразумевается табличка
tuple - кортеж, строка
heap only tuple - эта версия строки находиться только в таблице и на нее нет ссылок из индекса
heap hot upd - признак что было обновлено HOT
заскочив в табличку пойдет по heap hot upd - пока не встретит false

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

Обновляемый столбей не долджен входить ни в один индекс - иначе на версию строки будет ссылка из индекса и ее нельзя пометить как heap only
Цепочка обновлений - только в пределах одной страницы
- не требуется обращение к другим страница, обход цепочки не ухудшает производительность
- если в табличной странице не хватает места для новой версии, цепочка обрывается (как если бы оптимизация не работала)
- место в странице можно зарезервировать. уменьшив параметр хранения таблицы fillfactor (100% → 10%)

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

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

Происходит зачистка, но индекс не трогается, т.к. это другая страничка

Примечания:

фактическую очистку проведет вакуум
индекс, впервые сходив и увидев что колонка dead пометит и у себя как мерткую
unused - это как бы по умолчанию, можно использовать
теоретически очистка внутри странички может прям укладываться в страничку и как следствие и вакууму делать ничего не надо будет

DBA2-10 Тема 5 «Очистка»

Обычная очистка

выполняется командой VACUUM - не конфликтует с обычной активностью в системе
обрабатывает таблицу и все ее индексы
- очищает ненужные версии строк в табличных страницах (пропуская страницы, уже отмеченные в карте видимости)
- очищает индексные записи, ссылающиеся на очищенные версии страниц
- освобождает указатели
- обновляет карту свободного пространтсва - спец структура, в которой для таблицы храняться “дырки” куда можно вставить новые строки
- обновляет карту видимости - для каждой странички выставляется бит, являются ли все версии на этой страничке видимыми для всех пользователей (аля можно не чистить)

maintenance_work_mem - сюда складываются указатели которые нужно зачищать

Мониторинг

VACUUM VERBOSE - будет выводить инфу о том что он сейчас делает
представление pg_stat_progress_vacuum
- полный размер таблицы
- число прочитанных страниц и число очищенных страниц
- количество уже завершенных циклов очистки индексов
- число идентификаторов версий строк, помещающихся в память и текущее число идентификаторов в памяти
- текущая фаза очистки

Регулирование нагрузки

процесс чередует работу и ожидание - примерно vacuum_cost_limit условных единиц работы, затем засыпает на vacuum_cost_delay мс
настройки
- vacuum_cost_limit = 200
- vacuum_cost_delay = 0ms
- стоимость обработки
  - vacuum_cost_page_hit = 1 - страницы в кэше
  - vacuum_cost_page_miss = 10 - страницы на диске (нет в памяти, нужно вычитать с диска)
  - vacuum_cost_page_dirty = 20 - грязной страницы (означает что нужно будет записать на диск)

Анализ

выполняется при VACUUM ANALYZE или ANALYZE - не конфликтует с обычной активностью в системе
собирает статистику для планировщика

DBA2-10 Тема 6 «Автоочистка»

Autovacuum launcher

для каждой баззы данных (в которой есть активность) запускать рабочий процесс раз в autovacuum_naptime, количесво рабочих процессов < autovacuum_max_workers

Настройки

autovacuum = on
track_counts = on # required, иначе автовакуум не будет знать куда ходить
autovacuum_naptime = 60s
autovacuum_max_workers=3

Autovacuum worker

выполнять по очереди очистку всех таблиц (включая TOAST), в которых число ненужных версий строк превышает autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factory * число строк в таблице

а так же выполнять анализ всех таблиц, в которых число изменившихся версий стррок превышает autovacuum_analyze_threshold + authovacuum_analyze_scale_factor * число строк в табличке

в одной БД может параллельно работать несколько процессов

Настройки автоочистки

autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2 # рекомендуется уменьшать, скажем 5, 3, 2 процента, т.к. 20% на некоторых сетапах это может уже быть очееень много, можно устанавливать отдельно на каждую табличку, см примеры ниже

Параметры хранения таблиц

autovacuum_enabled
toast.autovacuum_enabled
autovacuum_vacuum_threshold
toast.autovacuum_vacuum_threshold
autovacuum_vacuum_scale_factor
toast.autovacuum_vacuum_scale_factor

Мониторинг

log_autovacuum_min_duration
pg_stat_progress_vacuum

Подход к настройке

баланс между разрастанием и накладными расходами - итеративный процесс
основные параметры
- autovacuum_vacuum_scale_factor - частота обработки (понизить на порядок)
- autovacuum_max_workers - параллелизм (по умолчанию 3, стоит увеличить, но надо не забыть увеличить лимиты, иначе нет смысла)
- autovacuum_vacuum_cost_limit - скорость работы (увеличить пропорционально кол-ву рабочих)
- индивидуальная настройка важных таблиц параметрами хранения
мониторинг
- разрастание таблиц
- очередь таблиц, ожидающих очистки
- нагрузка на диски

CREATE DATABASE mvcc_autovacuum;
\c mvcc_autovacuum

CREATE TABLE tvac(id serial, n numeric);
INSERT INTO tvac(n) SELECT 1 FROM generate_series(1,1000);

-- Выставим настройки автоочистки.
-- Небольшое время ожидания, чтобы сразу видеть результат:
ALTER SYSTEM SET autovacuum_naptime = 1;
-- Один процент строк:
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.01;
-- Нулевой порог:
ALTER SYSTEM SET autovacuum_vacuum_threshold = 0;

-- Выставим настройки автоанализа.
-- Два процента строк:
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.02;
-- Нулевой порог:
ALTER SYSTEM SET autovacuum_analyze_threshold = 0;
-- Перечитаем настройки:
SELECT pg_reload_conf();


-- Создадим представление, показывающее, нуждается ли наша таблица в очистке.
CREATE VIEW vacuum_v AS
WITH params AS (
  SELECT (SELECT setting::integer
          FROM   pg_settings
          WHERE  name = 'autovacuum_vacuum_threshold') AS vacuum_threshold,
         (SELECT setting::float
          FROM   pg_settings
          WHERE  name = 'autovacuum_vacuum_scale_factor') AS vacuum_scale_factor
)
SELECT st.relname,
       st.n_dead_tup dead_tup,
       (p.vacuum_threshold + p.vacuum_scale_factor*c.reltuples)::integer max_dead_tup,
       st.n_dead_tup > (p.vacuum_threshold + p.vacuum_scale_factor*c.reltuples)::integer need_vacuum,
       st.last_autovacuum
FROM   pg_stat_all_tables st,
       pg_class c,
       params p
WHERE  c.oid = st.relid
AND    c.relname = 'tvac';


SELECT * FROM vacuum_v;
-- relname | dead_tup | max_dead_tup | need_vacuum | last_autovacuum
-----------+----------+--------------+-------------+-----------------
-- tvac    |        0 |           10 | f           |

-- Таблица не требует очистки (0 ненужных версий) и ни разу не очищалась.

-- Можно создать аналогичное представление и для анализа:

CREATE VIEW analyze_v AS
WITH params AS (
  SELECT (SELECT setting::integer
          FROM   pg_settings
          WHERE  name = 'autovacuum_analyze_threshold') as analyze_threshold,
         (SELECT setting::float
          FROM   pg_settings
          WHERE  name = 'autovacuum_analyze_scale_factor') as analyze_scale_factor
)
SELECT st.relname,
       st.n_mod_since_analyze mod_tup,
       (p.analyze_threshold + p.analyze_scale_factor*c.reltuples)::integer max_mod_tup,
       st.n_mod_since_analyze > (p.analyze_threshold + p.analyze_scale_factor*c.reltuples)::integer need_analyze,
       st.last_autoanalyze
FROM   pg_stat_all_tables st,
       pg_class c,
       params p
WHERE  c.oid = st.relid
AND    c.relname = 'tvac';


-- Таблица не требует анализа; автоанализ уже был выполнен.

-- Отключим автоочистку на уровне таблицы и изменим 11 строк (больше 1 %):
ALTER TABLE tvac SET (autovacuum_enabled = off);
UPDATE tvac SET n = n + 1 WHERE id <= 11;

SELECT * FROM vacuum_v;
-- relname | dead_tup | max_dead_tup | need_vacuum | last_autovacuum
-----------+----------+--------------+-------------+-----------------
-- tvac    |       11 |           10 | t           |

-- Как видно, таблице требуется автоочистка.
-- Включим автоочистку для таблицы и подождем несколько секунд...
ALTER TABLE tvac SET (autovacuum_enabled = on);

SELECT * FROM vacuum_v;
-- relname | dead_tup | max_dead_tup | need_vacuum |        last_autovacuum
-----------+----------+--------------+-------------+-------------------------------
-- tvac    |        0 |           10 | f           | 2019-08-12 17:15:32.258085+03

DBA2-10 Тема 7 «Заморозка»

Это история про второй круг счетчика транзакций

Настройка

vacuum_freeze_min_age - минимальный возраст, с которого начинается заморозка
vacuum_freeze_table_age - при достижении такого возраста замораживаются версии строк на всех страницах, используется карта заморозки
autovacuum_freeze_max_age - при достижении такого возраста заморозка запускается принудительно, определяет размер XACT

DBA2-10 Тема 8 «Буферный кэш»

Диск - большой но медленный

ОЗУ - быстрая но мало

Отводим в памяти кусок, в котором кэшируем данные которые читаем с диска

Буферный кэш - это общая для всех процессов область памяти, в которой находиться массив буфферов.

Каждый буфекр это место под страничку данных (8кб) плюс заголовок

shared_buffers - определяет кол-во этих буферов, требует перезапуска, захватывается сразу

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

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

Так же в памяти храниться хэш таблица для быстрого перехода к нужному буферу

Несколько процессов могут “закрепить” один и тот же буффер (pincount), эдакая мягкая блокировка, не разрешает страничку выкинуть из буфера, но при этом позволяет ее менять

Настройка размера кэша

настройка shared_buffers = 128MB - очень мало
буферный кэш должен содержать “активные” данные
- при меньшем размере постоянно вытеняются полезные страницы
- при большем размере бессмысленно расхуд накладные расходы
- начальное приближение - 0.25 ОЗУ (рекомендация с потолка, с чего начать)
нужно учитывать двойное кэширование
- если страницы нет в кэше СУБД, она может оказаться в кэше ОС
- алгоритм вытеснения ОС не учитывает специфики базы данных

Временные таблицы

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

постгрес 11 умеет запоминать что было в буферном кэше и после перезапуска “прогревать” его

DBA2-10 Тема 9 «Журнал предзаписи»

Логическое устройство

последовательность записей
номер записи - 64 битный LSN (log sequence number)
специальный тип pg_lsn

Физическое йстройство

В памяти - кольцевой буферный кэш wal_buffers = -1 - означает 1/32 от shared_buffers
На диске - файлы (сегменты) по 16мб, $PGDATA/pgwal/000100010001 - веткавремени:start_lsn:end_lsn?

Упреждающая запись

Когда мы хотим что либо поменять в буферном кэше - мы помещаем ее в wal, получаем адрес этой записи pg_current_wal_insert_lsn(), полученный lsn выставляем в странице
При фиксации транзакции ставим бит в XACT, после чего создаем новую запись в WAL и снова получаем lsn, который прописываем в xact
При вытеснении страницы из кэша, если у странички есть lsn который еще не на диске, вначале сбрасываем на диск wal

pg_current_wal_lsn() -- то что меньше, уже на диске pg_current_wal_insert_lsn() - голова

Упрощенный алгоритм востановления

при старте сервера после сбоя (состояние кластера в pg_control отличается от shutdown)

для каждой журнальной записи
- определить страницу, к которой относиться эта запись
- применить запись, если ее lsn больше, чем lsn страницы
перезаписаать нежурналируемые табличцы init-файлами

DBA2-10 Тема 10 «Контрольная точка»

checkpoint_completion_target = 0.5

checkpoint_timeout = 5min – обычно делают пореже, 30-60 мин

max_wal_size = 1Gb

DBA2-10 Тема 11 «Настройка журнала»

Уровни журнала

minimal - воставновление после сбоя
replica - по умолчанию, восстановление из резервной копии, репликация плюс операции массовой обработки данных, блокировки
logical - логическая репликация плюс информация для логического декодирования

Чем выше уровень, тем больше размер журнала