Перейти к содержимому

11 Занятие 11: Журналирование / Настройка журнала

Исходная страница в нативном Markdown: Журналирование / Настройка журнала
Примечание

Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. HTML source: dba2_11_wal_tuning.html Archive: DBA2-handouts-16.zip Страниц PDF: 22

Page 1: Журналирование / Настройка журнала

Авторские права

© Postgres Professional, 2016–2025 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)

Использование материалов курса

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru

Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения.

Page 2: Темы

  • Уровни журнала и решаемые задачи
  • Надежность записи
  • Производительность

Page 3: Уровни журнала

Настройка

  • чем выше уровень,
  • тем больше информации
  • wal_level = replica
  • в журнале
  • Minimal
    • восстановление после сбоя
  • Replica
    • восстановление из резервной копии, физическая репликация
      • операции массовой обработки данных, блокировки,
    • номера выполняющихся транзакций
  • Logical
    • логическая репликация
      • информация для логического декодирования

Комментарии преподавателя

Кроме основной задачи — восстановления согласованности после сбоя — журнал можно использовать и для других целей, если добавить в него дополнительную информацию. Для этого существуют несколько уровней журнала, устанавливаемых параметром wal_level.

Уровень minimal содержит только информацию, необходимую для восстановления после сбоя. В журнал не записываются операции массовой обработки данных (такие, как CREATE TABLE AS SELECT, CREATE INDEX и т. п.), их долговечность гарантируется немедленной записью данных на диск.

Уровень replica (используется по умолчанию) позволяет восстанавливать систему из горячих резервных копий, сделанных утилитой pg_basebackup. Для этого в журнал записываются все изменения данных, включая операции массовой обработки. Уровень также позволяет организовать физическую репликацию — передавать поток журнальных записей на другой сервер (рассматривается в курсе DBA3), для чего в журнал включается информация о ряде блокировок.

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

Чем выше уровень, тем больше информации попадает в журнал и, следовательно, тем больше его объем.

Page 4: Уровни журнала

Используем новую базу данных.

=> CREATE DATABASE wal_tuning;
CREATE DATABASE
=> \c wal_tuning
You are now connected to database "wal_tuning" as user "student".
=> CREATE EXTENSION pg_walinspect;
CREATE EXTENSION

Установим для журнала уровень minimal (при этом придется также задать нулевое значение параметра max_wal_senders и перезапустить сервер).

=> ALTER SYSTEM SET wal_level = minimal;
ALTER SYSTEM
=> ALTER SYSTEM SET max_wal_senders = 0;
ALTER SYSTEM
student$ sudo pg_ctlcluster 16 main restart
student$ psql wal_tuning

Посмотрим, как записывается в журнал команда CREATE TABLE AS SELECT.

=> SELECT pg_current_wal_insert_lsn();
 pg_current_wal_insert_lsn
---------------------------
 0/1FEDE90
(1 row)
=> CREATE TABLE t_wal(n) AS SELECT 1 FROM generate_series(1,3000);
SELECT 3000
=> SELECT pg_current_wal_insert_lsn();
 pg_current_wal_insert_lsn
---------------------------
 0/20233B8
(1 row)

Объем журнала:

=> SELECT '0/20233B8'::pg_lsn - '0/1FEDE90'::pg_lsn;
 ?column?
----------
   218408
(1 row)

В журнале нет записей, описывающих вставку строк в таблицу:

=> SELECT resource_manager, record_length, xid, start_lsn, record_type
FROM pg_get_wal_records_info('0/1FEDE90','0/20233B8')
WHERE record_type IN ('CREATE','INSERT+INIT','COMMIT')
ORDER BY start_lsn;
 resource_manager | record_length | xid | start_lsn | record_type
------------------+---------------+-----+-----------+-------------
 Storage          |            42 |   0 | 0/1FF1D78 | CREATE
 Transaction      |            34 | 745 | 0/2023390 | COMMIT
(2 rows)

На уровне minimal изменения, выполненные операторами CREATE TABLE AS SELECT, CREATE INDEX, TRUNCATE и некоторыми другими, не журналируются. Эти операторы всегда сами выполняют синхронизацию, обеспечивая долговечность. А журнал содержит только записи, необходимые для восстановления после сбоя.

Page 5: Page 5

Удалим таблицу, вернем уровень по умолчанию (replica) и повторим эксперимент.

=> DROP TABLE t_wal;
DROP TABLE
=> ALTER SYSTEM RESET wal_level;
ALTER SYSTEM
=> ALTER SYSTEM RESET max_wal_senders;
ALTER SYSTEM
student$ sudo pg_ctlcluster 16 main restart
student$ psql wal_tuning
=> SHOW wal_level;
 wal_level
-----------
 replica
(1 row)

Посмотрим, как теперь записывается в журнал команда CREATE TABLE AS SELECT.

=> SELECT pg_current_wal_insert_lsn();
 pg_current_wal_insert_lsn
---------------------------
 0/202D5E8
(1 row)
=> CREATE TABLE t_wal(n) AS SELECT 1 FROM generate_series(1,3000);
SELECT 3000
=> SELECT pg_current_wal_insert_lsn();
 pg_current_wal_insert_lsn
---------------------------
 0/2070848
(1 row)

Объем журнала увеличился:

=> SELECT '0/2070848'::pg_lsn - '0/202D5E8'::pg_lsn;
 ?column?
----------
   275040
(1 row)

Помимо изменений системного каталога, в журнал попадают записи: CREATE — создание файла отношения; INSERT+INIT — вставка строк в таблицу; COMMIT — фиксация транзакции.

=> SELECT resource_manager, record_length, xid, start_lsn, record_type
FROM pg_get_wal_records_info('0/202D5E8','0/2070848')
WHERE record_type IN ('CREATE','INSERT+INIT','COMMIT')
ORDER BY start_lsn;

Page 6: Page 6

 resource_manager | record_length | xid | start_lsn | record_type
------------------+---------------+-----+-----------+-------------
 Storage          |            42 |   0 | 0/2030B78 | CREATE
 Heap             |            59 | 747 | 0/2041660 | INSERT+INIT
 Heap             |            59 | 747 | 0/2044F10 | INSERT+INIT
 Heap             |            59 | 747 | 0/20487C0 | INSERT+INIT
 Heap             |            59 | 747 | 0/204C070 | INSERT+INIT
 Heap             |            59 | 747 | 0/204F908 | INSERT+INIT
 Heap             |            59 | 747 | 0/20531B8 | INSERT+INIT
 Heap             |            59 | 747 | 0/2056A68 | INSERT+INIT
 Heap             |            59 | 747 | 0/205A318 | INSERT+INIT
 Heap             |            59 | 747 | 0/205DBB0 | INSERT+INIT
 Heap             |            59 | 747 | 0/2061460 | INSERT+INIT
 Heap             |            59 | 747 | 0/2064D10 | INSERT+INIT
 Heap             |            59 | 747 | 0/20685C0 | INSERT+INIT
 Heap             |            59 | 747 | 0/206BE58 | INSERT+INIT
 Heap             |            59 | 747 | 0/206F708 | INSERT+INIT
 Transaction      |           421 | 747 | 0/20706A0 | COMMIT
(16 rows)

На уровне replica журнал содержит все изменения данных, что позволяет применить их к физической резервной копии или к реплике.

Page 7: Надежность записи

Кеширование

  • Повреждение данных
  • Неатомарность записи страниц

Page 8: Кеширование

Source slide page 8: Кеширование{#source-figure-008 caption=“Source slide page 8: Кеширование”}

Синхронизация с диском

  • данные должны дойти до энергонезависимого
  • хранилища через многочисленные кеши
  • СУБД сообщает о синхронизации операционной системе
  • способом, указанным в wal_sync_method
  • надо учитывать аппаратное кеширование
  • Настройки
    • fsync = on
    • wal_sync_method
    • (утилита pg_test_fsync)

Комментарии преподавателя

Есть несколько моментов, которые влияют на надежность. Во-первых, всевозможные кеши, находящиеся на пути к энергонезависимому хранилищу (такому как пластина жесткого диска).

Когда PostgreSQL требуется надежно записать данные, он пользуется способом, указанным в параметре wal_sync_method. Вариантов несколько, но основных — два: либо после записи операционной системе дается команда синхронизации (fsync, fdatasync), либо файл открывается или записывается со специальным флагом, который говорит о необходимости синхронизации, а по возможности — прямой записи, минуя кеш ядра ОС. Утилита pg_test_fsync позволяет выбрать способ, наиболее подходящий для конкретной ОС и конкретной файловой системы. Но в любом случае это дорогостоящая операция.

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

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

https://postgrespro.ru/docs/postgresql/16/wal-reliability

Page 9: Повреждение данных

Контрольные суммы журнальных записей

  • включены всегда, CRC-32
  • Контрольные суммы страниц
    • нужно включить при инициализации кластера
    • накладные
    • расходы, увеличение
initdb -k
  • размера журнала
  • Настройки
    • data_checksums
    • ignore_checksum_failure = off

Комментарии преподавателя

Во-вторых, данные могут быть повреждены на носителе, при передаче данных по интерфейсным кабелям и т. п. Часть таких ошибок обрабатывается на аппаратном уровне, но часть — нет.

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

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

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

Page 10: Неатомарность записи

Образ страницы

  • при сбое страница может быть записана не полностью
  • в журнал записывается образ страницы
  • при первом ее изменении после контрольной точки
  • при восстановлении журнальные записи
  • применяются к записанному образу
  • Настройки
    • увеличивает
    • размер журнала
    • full_page_writes = on
    • wal_compression = off

Комментарии преподавателя

В-третьих, есть проблема атомарности записи.

Страница данных занимает 8 КБ (или больше: 16 КБ, 32 КБ), а на низком уровне запись происходит блоками, которые обычно имеют меньший размер (512 байт, 4 КБ, хотя бывают и другие размеры). Поэтому при сбое питания страница данных может записаться частично. Понятно, что при восстановлении бессмысленно применять к такой странице обычные журнальные записи.

Для защиты PostgreSQL позволяет записывать в журнал образ всей страницы при первом ее изменении после контрольной точки — этим управляет параметр full_page_writes. Отключать его имеет смысл, только если используемая файловая система и аппаратура сами по себе гарантируют атомарность записи.

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

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

Размер журнала можно уменьшить за счет сжатия полных образов, задав метод сжатия параметром wal_compression. Поддерживаются методы pglz, lz4, zstd. Значение on соответствует выбору pglz, off отключает сжатие.

Page 11: Контрольные суммы

Создадим еще одну таблицу:

=> CREATE TABLE t(id integer);
CREATE TABLE
=> INSERT INTO t VALUES (1),(2),(3);
INSERT 0 3

Вот файл, в котором находятся данные:

=> SELECT pg_relation_filepath('t');
 pg_relation_filepath
----------------------
 base/16390/16404
(1 row)

Остановим сервер и поменяем несколько байтов в странице (сотрем из заголовка LSN последней журнальной записи).

student$ sudo pg_ctlcluster 16 main stop
student$ sudo dd if=/dev/zero of=/var/lib/postgresql/16/main/base/16390/16404 oflag=dsync conv=notrunc
bs=1 count=8
8+0 records in
8+0 records out
8 bytes copied, 0,0645192 s, 0,1 kB/s

Можно было бы и не останавливать сервер. Достаточно, чтобы: страница записалась на диск и была вытеснена из кеша; произошло повреждение; страница была прочитана с диска. Теперь запускаем сервер.

student$ sudo pg_ctlcluster 16 main start
student$ psql wal_tuning

Попробуем прочитать таблицу:

=> SELECT * FROM t;
WARNING:  page verification failed, calculated checksum 40449 but expected 33456
ERROR:  invalid page in block 0 of relation base/16390/16404

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

=> SET ignore_checksum_failure = on;
SET
=> SELECT * FROM t;
WARNING:  page verification failed, calculated checksum 40449 but expected 33456
 id
----
  1
  2
  3
(3 rows)

Page 12: Производительность

Характер нагрузки

  • Синхронная запись
  • Асинхронная запись

Page 13: Характер нагрузки

Постоянный поток записи

  • последовательная запись, отсутствие случайного доступа
  • характер нагрузки отличается от остальной системы
  • при высокой нагрузке — размещение на отдельных физических дисках
  • (символическая ссылка из PGDATA/pg_wal)
  • Редкое чтение
    • при восстановлении
    • при работе процессов walsender, если реплика не успевает быстро
    • получать записи

Комментарии преподавателя

При обычной работе происходит постоянная и последовательная запись журнальных файлов. Поскольку отсутствует случайный доступ, с такой нагрузкой отлично справляются и обычные диски HDD.

Но такой характер нагрузки существенно отличается от того, как происходит доступ к файлам данных. Поэтому обычно выгодно размещать журнал на отдельном физическом диске (дисках), примонтированных к ФС сервера; вместо каталога PGDATA/pg_wal нужно создать символическую ссылку на соответствующий каталог.

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

Для мониторинга и оценки производительности работы WAL в PostgreSQL версии 14 было добавлено представление pg_stat_wal.

Page 14: Синхронная запись

Алгоритм

  • при фиксации изменений сбрасываются все накопившиеся записи,
  • включая запись о фиксации
  • ожидание commit_delay, если активно не менее commit_siblings
  • транзакций
  • Свойства
    • гарантируется долговечность
    • увеличивается время отклика
  • Настройки
    • включать
    • при большом потоке
    • коротких транзакций
    • synchronous_commit = on
    • commit_delay
    • = 0
    • commit_siblings
    • = 5

Комментарии преподавателя

Запись журнала происходит в одном из двух режимов: синхронном (когда при фиксации транзакции продолжение работы невозможно до тех пор, пока все журнальные записи о транзакции не окажутся на диске) и асинхронном (когда журнал записывается частями в фоне).

Синхронный режим включается параметром synchronous_commit.

Поскольку синхронизация работает долго, выгодно выполнять ее как можно реже. Для этого процесс, записывающий журнал, делает паузу, определяемую параметром commit_delay, но только в том случае, если имеется не менее commit_siblings активных транзакций — в расчете на то, что за время ожидания часть транзакций успеет завершиться и можно будет синхронизировать их записи за один раз.

Затем процесс выполняет сброс журнала на диск до необходимого LSN (или несколько больше, если за время ожидания добавились новые записи).

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

Page 15: Асинхронная запись

Алгоритм

  • циклы записи через wal_writer_delay
  • записываются только целиком заполненные страницы;
  • но если новых полных страниц нет, то записывается текущая
  • Свойства
    • гарантируется согласованность, но не долговечность
    • зафиксированные изменения могут пропасть (3 × wal_writer_delay)
  • Настройки
    • можно
    • изменять на уровне
    • synchronous_commit
    • wal_writer_delay = 200ms
    • wal_writer_flush_after = 1MB
    • транзакции

Комментарии преподавателя

При асинхронной записи работает процесс wal writer, сбрасывая накопившиеся журнальные записи либо через wal_writer_delay единиц времени, либо по достижении объема wal_writer_flush_after:

● Если с прошлого раза в буферах была целиком заполнена одна или несколько страниц, сбрасываются только такие, полностью заполненные, страницы (или часть таких страниц; вспомним, что журнальный кеш представляет собой кольцевой буфер — записывается только непрерывная последовательность страниц). При большом потоке изменений это позволяет не синхронизировать одну и ту же страницу несколько раз.

● Если же заполненные страницы не появились, записывается текущая (не до конца заполненная) страница журнала.

Асинхронная запись эффективнее синхронной — фиксация изменений не ждет записи. Однако надежность уменьшается: зафиксированные данные могут пропасть в случае сбоя, если между фиксацией и сбоем прошло менее 3 × wal_writer_delay единиц времени.

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

https://postgrespro.ru/docs/postgresql/16/wal-async-commit

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

Page 16: Влияние синхронной фиксации на производительность

Режим, включенный по умолчанию, — синхронная фиксация.

=> SHOW synchronous_commit;
 synchronous_commit
--------------------
 on
(1 row)

Запустим простой тест производительности с помощью утилиты pgbench. Для этого сначала инициализируем необходимые таблицы…

student$ pgbench -i wal_tuning
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.16 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.51 s (drop tables 0.00 s, create tables 0.03 s, client-side generate 0.20 s,
vacuum 0.12 s, primary keys 0.16 s).

…а также сбросим статистику о работе журнала предзаписи:

=> SELECT pg_stat_reset_shared('wal');
 pg_stat_reset_shared
----------------------
(1 row)

Запускаем тест на 10 секунд.

student$ pgbench -P 1 -T 10 wal_tuning
pgbench (16.2 (Ubuntu 16.2-2.pgdg24.04+1))
starting vacuum...end.
progress: 1.0 s, 107.0 tps, lat 9.192 ms stddev 0.985, 0 failed
progress: 2.0 s, 111.0 tps, lat 9.064 ms stddev 1.785, 0 failed
progress: 3.0 s, 113.0 tps, lat 8.818 ms stddev 0.773, 0 failed
progress: 4.0 s, 113.0 tps, lat 8.852 ms stddev 0.624, 0 failed
progress: 5.0 s, 108.0 tps, lat 9.224 ms stddev 2.463, 0 failed
progress: 6.0 s, 112.0 tps, lat 8.978 ms stddev 0.981, 0 failed
progress: 7.0 s, 114.0 tps, lat 8.768 ms stddev 0.824, 0 failed
progress: 8.0 s, 114.0 tps, lat 8.725 ms stddev 1.281, 0 failed
progress: 9.0 s, 119.0 tps, lat 8.449 ms stddev 1.061, 0 failed
progress: 10.0 s, 117.0 tps, lat 8.537 ms stddev 1.298, 0 failed
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
duration: 10 s
number of transactions actually processed: 1129
number of failed transactions: 0 (0.000%)
latency average = 8.853 ms
latency stddev = 1.329 ms
initial connection time = 8.955 ms
tps = 112.939337 (without initial connection time)

В результатах pgbench нас интересует число транзакций или скорость (tps), а в данных представления pg_stat_wal — количество операций записи и синхронизации журнала:

=> SELECT wal_records, wal_bytes, wal_write, wal_sync FROM pg_stat_wal;

Page 17: Page 17

 wal_records | wal_bytes | wal_write | wal_sync
-------------+-----------+-----------+----------
        7625 |    581954 |      1131 |     1131
(1 row)

Теперь установим асинхронный режим.

=> ALTER SYSTEM SET synchronous_commit = off;
ALTER SYSTEM
=> SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

Сбросим накопленные данные.

=> SELECT pg_stat_reset_shared('wal');
 pg_stat_reset_shared
----------------------
(1 row)

И снова запускаем тест.

student$ pgbench -P 1 -T 10 wal_tuning
pgbench (16.2 (Ubuntu 16.2-2.pgdg24.04+1))
starting vacuum...end.
progress: 1.0 s, 1145.9 tps, lat 0.867 ms stddev 0.159, 0 failed
progress: 2.0 s, 1167.0 tps, lat 0.856 ms stddev 0.142, 0 failed
progress: 3.0 s, 1094.0 tps, lat 0.913 ms stddev 0.169, 0 failed
progress: 4.0 s, 1045.9 tps, lat 0.955 ms stddev 0.168, 0 failed
progress: 5.0 s, 1127.1 tps, lat 0.887 ms stddev 0.133, 0 failed
progress: 6.0 s, 1159.2 tps, lat 0.862 ms stddev 0.124, 0 failed
progress: 7.0 s, 1138.0 tps, lat 0.878 ms stddev 0.122, 0 failed
progress: 8.0 s, 1178.9 tps, lat 0.848 ms stddev 0.128, 0 failed
progress: 9.0 s, 1153.1 tps, lat 0.867 ms stddev 0.121, 0 failed
progress: 10.0 s, 1151.0 tps, lat 0.868 ms stddev 0.122, 0 failed
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
duration: 10 s
number of transactions actually processed: 11361
number of failed transactions: 0 (0.000%)
latency average = 0.879 ms
latency stddev = 0.143 ms
initial connection time = 5.023 ms
tps = 1136.558829 (without initial connection time)
=> SELECT wal_records, wal_bytes, wal_write, wal_sync FROM pg_stat_wal;
 wal_records | wal_bytes | wal_write | wal_sync
-------------+-----------+-----------+----------
       69171 |   4606168 |       613 |       53
(1 row)

Кроме повышения tps, мы видим и саму причину повышения производительности: количество операций синхронизации журнала значительно уменьшилось. Разумеется, на реальной системе соотношение может быть другим, но видно, что в асинхронном режиме производительность существенно выше.

Page 18: Итоги

Журнал позволяет восстановить согласованность после сбоев, но может использоваться и для других задач

  • Надежность записи требует настройки не только СУБД, но и на уровне ОС, ФС и аппаратуры
  • Настройка позволяет достичь баланса между долговечностью и производительностью

Page 19: Практика

  1. Изучите, как влияет на размер журнальных записей значение параметра full_page_writes.
  • Для этого повторите простой тест pgbench, показанный
  • в демонстрации, с разными настройками журнала. Перед
  • запуском каждого теста выполняйте контрольную точку.
  • Объясните полученный результат.
    1. Во сколько раз уменьшается размер журнальных записей при включении параметра wal_compression?

Комментарии преподавателя

Для детального анализа может пригодиться функция pg_get_wal_stats из расширения pg_walinspect.

Page 20: 1a. Full page writes = on

=> CREATE DATABASE wal_tuning;
CREATE DATABASE
=> \c wal_tuning
You are now connected to database "wal_tuning" as user "student".
=> CREATE EXTENSION pg_walinspect;
CREATE EXTENSION
student$ pgbench -i wal_tuning
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.23 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.60 s (drop tables 0.00 s, create tables 0.03 s, client-side generate 0.27 s,
vacuum 0.12 s, primary keys 0.18 s).
=> SHOW full_page_writes;
 full_page_writes
------------------
 on
(1 row)
=> CHECKPOINT;
CHECKPOINT
=> SELECT pg_current_wal_insert_lsn();
 pg_current_wal_insert_lsn
---------------------------
 0/2C5C160
(1 row)

Запускаем тест на 10 секунд.

student$ pgbench -T 10 wal_tuning
pgbench (16.2 (Ubuntu 16.2-2.pgdg24.04+1))
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
duration: 10 s
number of transactions actually processed: 1114
number of failed transactions: 0 (0.000%)
latency average = 8.977 ms
initial connection time = 6.883 ms
tps = 111.396781 (without initial connection time)
=> SELECT pg_current_wal_insert_lsn();
 pg_current_wal_insert_lsn
---------------------------
 0/352EDA0
(1 row)

Смотрим статистику журнальных записей:

Page 21: Page 21

=> SELECT sum(count) count,
  pg_size_pretty(sum(record_size)) record_size,
  pg_size_pretty(sum(fpi_size)) fpi_size,
  pg_size_pretty(sum(combined_size)) combined_size
FROM pg_get_wal_stats('0/2C5C160', '0/352EDA0');
 count | record_size | fpi_size | combined_size
-------+-------------+----------+---------------
  7535 | 555 kB      | 8437 kB  | 8992 kB
(1 row)

Значительную часть объема журнала составляют полные образы страниц (FPI).

1b. Full page writes = off

=> ALTER SYSTEM SET full_page_writes = off;
ALTER SYSTEM
=> SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)
=> CHECKPOINT;
CHECKPOINT
=> SELECT pg_current_wal_insert_lsn();
 pg_current_wal_insert_lsn
---------------------------
 0/353AC20
(1 row)

Запускаем тест на 10 секунд.

student$ pgbench -T 10 wal_tuning
pgbench (16.2 (Ubuntu 16.2-2.pgdg24.04+1))
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
duration: 10 s
number of transactions actually processed: 1100
number of failed transactions: 0 (0.000%)
latency average = 9.087 ms
initial connection time = 6.250 ms
tps = 110.043654 (without initial connection time)
=> SELECT pg_current_wal_insert_lsn();
 pg_current_wal_insert_lsn
---------------------------
 0/35BE030
(1 row)

Смотрим статистику журнальных записей:

=> SELECT sum(count) count,
  pg_size_pretty(sum(record_size)) record_size,
  pg_size_pretty(sum(fpi_size)) fpi_size,
  pg_size_pretty(sum(combined_size)) combined_size
FROM pg_get_wal_stats('0/353AC20', '0/35BE030');
 count | record_size | fpi_size | combined_size
-------+-------------+----------+---------------
  7474 | 505 kB      | 0 bytes  | 505 kB
(1 row)

Объем заметно сократился, полные образы страниц в журнал теперь не записываются.

Page 22: 2. Сжатие

=> ALTER SYSTEM SET full_page_writes = on;
ALTER SYSTEM
=> ALTER SYSTEM SET wal_compression = on;
ALTER SYSTEM
=> SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)
=> CHECKPOINT;
CHECKPOINT
=> SELECT pg_current_wal_insert_lsn();
 pg_current_wal_insert_lsn
---------------------------
 0/35BE100
(1 row)

Запускаем тест на 10 секунд.

student$ pgbench -T 10 wal_tuning
pgbench (16.2 (Ubuntu 16.2-2.pgdg24.04+1))
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
duration: 10 s
number of transactions actually processed: 1104
number of failed transactions: 0 (0.000%)
latency average = 9.056 ms
initial connection time = 9.304 ms
tps = 110.428943 (without initial connection time)
=> SELECT pg_current_wal_insert_lsn();
 pg_current_wal_insert_lsn
---------------------------
 0/3770508
(1 row)

Смотрим статистику журнальных записей:

=> SELECT sum(count) count,
  pg_size_pretty(sum(record_size)) record_size,
  pg_size_pretty(sum(fpi_size)) fpi_size,
  pg_size_pretty(sum(combined_size)) combined_size
FROM pg_get_wal_stats('0/35BE100', '0/3770508');
 count | record_size | fpi_size | combined_size
-------+-------------+----------+---------------
  7497 | 488 kB      | 1225 kB  | 1713 kB
(1 row)

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