# Занятие 11: Журналирование / Настройка журнала > Исходная страница в нативном Markdown: Журналирование / Настройка журнала --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [dba2_11_wal_tuning.html](https://edu.postgrespro.ru/16/dba2-16/dba2_11_wal_tuning.html) > **Archive:** [DBA2-handouts-16.zip](https://edu.postgrespro.ru/16/DBA2-handouts-16.zip) > **Страниц PDF:** 22 ## Page 1: Журналирование / Настройка журнала {#source-page-001} ### Авторские права © Postgres Professional, 2016–2025 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал) ### Использование материалов курса Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса. ### Обратная связь Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru ### Отказ от ответственности Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения. ## Page 2: Темы {#source-page-002} - Уровни журнала и решаемые задачи - Надежность записи - Производительность ## Page 3: Уровни журнала {#source-page-003} **Настройка** - чем выше уровень, - тем больше информации - wal\_level = replica - в журнале - Minimal - восстановление после сбоя - Replica - восстановление из резервной копии, физическая репликация - + операции массовой обработки данных, блокировки, - номера выполняющихся транзакций - Logical - логическая репликация - + информация для логического декодирования ### Комментарии преподавателя Кроме основной задачи — восстановления согласованности после сбоя — журнал можно использовать и для других целей, если добавить в него дополнительную информацию. Для этого существуют несколько уровней журнала, устанавливаемых параметром wal\_level. Уровень minimal содержит только информацию, необходимую для восстановления после сбоя. В журнал не записываются операции массовой обработки данных (такие, как CREATE TABLE AS SELECT, CREATE INDEX и т. п.), их долговечность гарантируется немедленной записью данных на диск. Уровень replica (используется по умолчанию) позволяет восстанавливать систему из горячих резервных копий, сделанных утилитой pg\_basebackup. Для этого в журнал записываются все изменения данных, включая операции массовой обработки. Уровень также позволяет организовать физическую репликацию — передавать поток журнальных записей на другой сервер (рассматривается в курсе DBA3), для чего в журнал включается информация о ряде блокировок. Уровень logical используется для логической репликации — он должен быть включен на сервере, публикующем изменения. Для правильной работы логического декодирования в журнал записывается дополнительная информация, позволяющая корректно расшифровать смысл операций по журнальным записям. Чем выше уровень, тем больше информации попадает в журнал и, следовательно, тем больше его объем. ## Page 4: Уровни журнала {#source-page-004} Используем новую базу данных. ```sql => CREATE DATABASE wal_tuning; ``` ```sql CREATE DATABASE ``` ```sql => \c wal_tuning ``` ```text You are now connected to database "wal_tuning" as user "student". ``` ```sql => CREATE EXTENSION pg_walinspect; ``` ```sql CREATE EXTENSION ``` Установим для журнала уровень minimal (при этом придется также задать нулевое значение параметра max\_wal\_senders и перезапустить сервер). ```sql => ALTER SYSTEM SET wal_level = minimal; ``` ```sql ALTER SYSTEM ``` ```sql => ALTER SYSTEM SET max_wal_senders = 0; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo pg_ctlcluster 16 main restart ``` ```sh student$ psql wal_tuning ``` Посмотрим, как записывается в журнал команда CREATE TABLE AS SELECT. ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/1FEDE90 (1 row) ``` ```sql => CREATE TABLE t_wal(n) AS SELECT 1 FROM generate_series(1,3000); ``` ```sql SELECT 3000 ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/20233B8 (1 row) ``` Объем журнала: ```sql => SELECT '0/20233B8'::pg_lsn - '0/1FEDE90'::pg_lsn; ``` ```text ?column? ---------- 218408 (1 row) ``` В журнале нет записей, описывающих вставку строк в таблицу: ```sql => 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; ``` ```sql 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 {#source-page-005} Удалим таблицу, вернем уровень по умолчанию (replica) и повторим эксперимент. ```sql => DROP TABLE t_wal; ``` ```sql DROP TABLE ``` ```sql => ALTER SYSTEM RESET wal_level; ``` ```sql ALTER SYSTEM ``` ```sql => ALTER SYSTEM RESET max_wal_senders; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo pg_ctlcluster 16 main restart ``` ```sh student$ psql wal_tuning ``` ```text => SHOW wal_level; ``` ```text wal_level ----------- replica (1 row) ``` Посмотрим, как теперь записывается в журнал команда CREATE TABLE AS SELECT. ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/202D5E8 (1 row) ``` ```sql => CREATE TABLE t_wal(n) AS SELECT 1 FROM generate_series(1,3000); ``` ```sql SELECT 3000 ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/2070848 (1 row) ``` Объем журнала увеличился: ```sql => SELECT '0/2070848'::pg_lsn - '0/202D5E8'::pg_lsn; ``` ```text ?column? ---------- 275040 (1 row) ``` Помимо изменений системного каталога, в журнал попадают записи: CREATE — создание файла отношения; INSERT+INIT — вставка строк в таблицу; COMMIT — фиксация транзакции. ```sql => 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 {#source-page-006} ```sql 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: Надежность записи {#source-page-007} Кеширование - Повреждение данных - Неатомарность записи страниц ## Page 8: Кеширование {#source-page-008} ![Source slide page 8: Кеширование](images/page-008.png){#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), но в этом случае про надежность хранения следует забыть. Единственный разумный вариант отключения этого параметра — временное увеличение производительности в случае, если данные можно легко восстановить (например, при начальной миграции). ## Page 9: Повреждение данных {#source-page-009} **Контрольные суммы журнальных записей** - включены всегда, CRC-32 - Контрольные суммы страниц - нужно включить при инициализации кластера - накладные - расходы, увеличение ```text initdb -k ``` - размера журнала - Настройки - data\_checksums - ignore\_checksum\_failure = off ### Комментарии преподавателя Во-вторых, данные могут быть повреждены на носителе, при передаче данных по интерфейсным кабелям и т. п. Часть таких ошибок обрабатывается на аппаратном уровне, но часть — нет. Чтобы вовремя обнаружить возникшую проблему, журнальные записи всегда снабжаются контрольными суммами. Страницы данных также можно защитить контрольными суммами. Это лучше сделать сразу при инициализации кластера, но можно включить утилитой pg\_checksums и потом, остановив сервер. В производственной среде контрольные суммы должны быть включены обязательно, несмотря на накладные расходы на их вычисление и контроль. Иначе можно получить ситуацию, когда возникший сбой не будет вовремя обнаружен. Проверить, включены ли контрольные суммы, можно с помощью параметра data\_checksums (только для чтения). Параметр ignore\_checksum\_failure позволяет не прерывать транзакцию, прочитавшую сбойную страницу, но обычно его не следует включать. ## Page 10: Неатомарность записи {#source-page-010} **Образ страницы** - при сбое страница может быть записана не полностью - в журнал записывается образ страницы - при первом ее изменении после контрольной точки - при восстановлении журнальные записи - применяются к записанному образу - Настройки - увеличивает - размер журнала - 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: Контрольные суммы {#source-page-011} Создадим еще одну таблицу: ```sql => CREATE TABLE t(id integer); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO t VALUES (1),(2),(3); ``` ```sql INSERT 0 3 ``` Вот файл, в котором находятся данные: ```sql => SELECT pg_relation_filepath('t'); ``` ```text pg_relation_filepath ---------------------- base/16390/16404 (1 row) ``` Остановим сервер и поменяем несколько байтов в странице (сотрем из заголовка LSN последней журнальной записи). ```sh student$ sudo pg_ctlcluster 16 main stop ``` ```sh student$ sudo dd if=/dev/zero of=/var/lib/postgresql/16/main/base/16390/16404 oflag=dsync conv=notrunc bs=1 count=8 ``` ```text 8+0 records in 8+0 records out 8 bytes copied, 0,0645192 s, 0,1 kB/s ``` Можно было бы и не останавливать сервер. Достаточно, чтобы: страница записалась на диск и была вытеснена из кеша; произошло повреждение; страница была прочитана с диска. Теперь запускаем сервер. ```sh student$ sudo pg_ctlcluster 16 main start ``` ```sh student$ psql wal_tuning ``` Попробуем прочитать таблицу: ```sql => SELECT * FROM t; ``` ```text WARNING: page verification failed, calculated checksum 40449 but expected 33456 ERROR: invalid page in block 0 of relation base/16390/16404 ``` Параметр ignore\_checksum\_failure позволяет попытаться все-таки прочитать таблицу, хоть и с риском получить искаженные данные (например, если нет резервной копии): ```text => SET ignore_checksum_failure = on; ``` ```text SET ``` ```sql => SELECT * FROM t; ``` ```text WARNING: page verification failed, calculated checksum 40449 but expected 33456 id ---- 1 2 3 (3 rows) ``` ## Page 12: Производительность {#source-page-012} Характер нагрузки - Синхронная запись - Асинхронная запись ## Page 13: Характер нагрузки {#source-page-013} **Постоянный поток записи** - последовательная запись, отсутствие случайного доступа - характер нагрузки отличается от остальной системы - при высокой нагрузке — размещение на отдельных физических дисках - (символическая ссылка из PGDATA/pg\_wal) - Редкое чтение - при восстановлении - при работе процессов walsender, если реплика не успевает быстро - получать записи ### Комментарии преподавателя При обычной работе происходит постоянная и последовательная запись журнальных файлов. Поскольку отсутствует случайный доступ, с такой нагрузкой отлично справляются и обычные диски HDD. Но такой характер нагрузки существенно отличается от того, как происходит доступ к файлам данных. Поэтому обычно выгодно размещать журнал на отдельном физическом диске (дисках), примонтированных к ФС сервера; вместо каталога PGDATA/pg\_wal нужно создать символическую ссылку на соответствующий каталог. Однако есть ситуация, при которой журнальные файлы необходимо читать (кроме понятного случая восстановления после сбоя). Она возникает, если используется потоковая репликация и реплика не успевает получать журнальные записи, пока они еще находятся в буферах оперативной памяти основного сервера. Тогда процессу walsender приходится читать нужные данные с диска. Взаимодействие с репликой подробно рассматривается в курсе DBA3 в модуле «Репликация». Для мониторинга и оценки производительности работы WAL в PostgreSQL версии 14 было добавлено представление pg\_stat\_wal. ## Page 14: Синхронная запись {#source-page-014} **Алгоритм** - при фиксации изменений сбрасываются все накопившиеся записи, - включая запись о фиксации - ожидание commit\_delay, если активно не менее commit\_siblings - транзакций - Свойства - гарантируется долговечность - увеличивается время отклика - Настройки - включать - при большом потоке - коротких транзакций - synchronous\_commit = on - commit\_delay - = 0 - commit\_siblings - = 5 ### Комментарии преподавателя Запись журнала происходит в одном из двух режимов: синхронном (когда при фиксации транзакции продолжение работы невозможно до тех пор, пока все журнальные записи о транзакции не окажутся на диске) и асинхронном (когда журнал записывается частями в фоне). Синхронный режим включается параметром synchronous\_commit. Поскольку синхронизация работает долго, выгодно выполнять ее как можно реже. Для этого процесс, записывающий журнал, делает паузу, определяемую параметром commit\_delay, но только в том случае, если имеется не менее commit\_siblings активных транзакций — в расчете на то, что за время ожидания часть транзакций успеет завершиться и можно будет синхронизировать их записи за один раз. Затем процесс выполняет сброс журнала на диск до необходимого LSN (или несколько больше, если за время ожидания добавились новые записи). При синхронной записи гарантируется долговечность — если транзакция зафиксирована, то все ее журнальные записи уже есть на диске и не будут потеряны. Обратная сторона состоит в том, что синхронная запись увеличивает время отклика (команда COMMIT не возвращает управление до окончания синхронизации) и снижает производительность системы. ## Page 15: Асинхронная запись {#source-page-015} **Алгоритм** - циклы записи через 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 можно устанавливать в рамках транзакций. Это позволяет увеличивать производительность, жертвуя надежностью только части транзакций. В реальности оба режима работают совместно. Журнальные записи долгой транзакции будут записываться асинхронно (чтобы освободить буферы WAL). А если при сбросе грязного буфера окажется, что соответствующая журнальная запись еще не на диске, она тут же будет сброшена в синхронном режиме. ## Page 16: Влияние синхронной фиксации на производительность {#source-page-016} Режим, включенный по умолчанию, — синхронная фиксация. ```text => SHOW synchronous_commit; ``` ```text synchronous_commit -------------------- on (1 row) ``` Запустим простой тест производительности с помощью утилиты pgbench. Для этого сначала инициализируем необходимые таблицы... ```sh student$ pgbench -i wal_tuning ``` ```sql 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). ``` ...а также сбросим статистику о работе журнала предзаписи: ```sql => SELECT pg_stat_reset_shared('wal'); ``` ```text pg_stat_reset_shared ---------------------- (1 row) ``` Запускаем тест на 10 секунд. ```sh student$ pgbench -P 1 -T 10 wal_tuning ``` ```sql 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: 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 — количество операций записи и синхронизации журнала: ```sql => SELECT wal_records, wal_bytes, wal_write, wal_sync FROM pg_stat_wal; ``` ## Page 17: Page 17 {#source-page-017} ```text wal_records | wal_bytes | wal_write | wal_sync -------------+-----------+-----------+---------- 7625 | 581954 | 1131 | 1131 (1 row) ``` Теперь установим асинхронный режим. ```sql => ALTER SYSTEM SET synchronous_commit = off; ``` ```sql ALTER SYSTEM ``` ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` Сбросим накопленные данные. ```sql => SELECT pg_stat_reset_shared('wal'); ``` ```text pg_stat_reset_shared ---------------------- (1 row) ``` И снова запускаем тест. ```sh student$ pgbench -P 1 -T 10 wal_tuning ``` ```sql 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: 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) ``` ```sql => SELECT wal_records, wal_bytes, wal_write, wal_sync FROM pg_stat_wal; ``` ```text wal_records | wal_bytes | wal_write | wal_sync -------------+-----------+-----------+---------- 69171 | 4606168 | 613 | 53 (1 row) ``` Кроме повышения tps, мы видим и саму причину повышения производительности: количество операций синхронизации журнала значительно уменьшилось. Разумеется, на реальной системе соотношение может быть другим, но видно, что в асинхронном режиме производительность существенно выше. ## Page 18: Итоги {#source-page-018} **Журнал позволяет восстановить согласованность после сбоев, но может использоваться и для других задач** - Надежность записи требует настройки не только СУБД, но и на уровне ОС, ФС и аппаратуры - Настройка позволяет достичь баланса между долговечностью и производительностью ## Page 19: Практика {#source-page-019} 1. Изучите, как влияет на размер журнальных записей значение параметра full\_page\_writes. - Для этого повторите простой тест pgbench, показанный - в демонстрации, с разными настройками журнала. Перед - запуском каждого теста выполняйте контрольную точку. - Объясните полученный результат. - 2. Во сколько раз уменьшается размер журнальных записей при включении параметра wal\_compression? ### Комментарии преподавателя Для детального анализа может пригодиться функция pg\_get\_wal\_stats из расширения pg\_walinspect. ## Page 20: 1a. Full page writes = on {#source-page-020} ```sql => CREATE DATABASE wal_tuning; ``` ```sql CREATE DATABASE ``` ```sql => \c wal_tuning ``` ```text You are now connected to database "wal_tuning" as user "student". ``` ```sql => CREATE EXTENSION pg_walinspect; ``` ```sql CREATE EXTENSION ``` ```sh student$ pgbench -i wal_tuning ``` ```sql 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). ``` ```text => SHOW full_page_writes; ``` ```text full_page_writes ------------------ on (1 row) ``` ```text => CHECKPOINT; ``` ```text CHECKPOINT ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/2C5C160 (1 row) ``` Запускаем тест на 10 секунд. ```sh student$ pgbench -T 10 wal_tuning ``` ```sql pgbench (16.2 (Ubuntu 16.2-2.pgdg24.04+1)) starting vacuum...end. transaction type: 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) ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/352EDA0 (1 row) ``` Смотрим статистику журнальных записей: ## Page 21: Page 21 {#source-page-021} ```sql => SELECT sum(count) count, pg_size_pretty(sum(record_size)) record_size, ``` ```sql 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'); ``` ```text count | record_size | fpi_size | combined_size -------+-------------+----------+--------------- 7535 | 555 kB | 8437 kB | 8992 kB (1 row) ``` Значительную часть объема журнала составляют полные образы страниц (FPI). ### 1b. Full page writes = off ```sql => ALTER SYSTEM SET full_page_writes = off; ``` ```sql ALTER SYSTEM ``` ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` ```text => CHECKPOINT; ``` ```text CHECKPOINT ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/353AC20 (1 row) ``` Запускаем тест на 10 секунд. ```sh student$ pgbench -T 10 wal_tuning ``` ```sql pgbench (16.2 (Ubuntu 16.2-2.pgdg24.04+1)) starting vacuum...end. transaction type: 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) ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/35BE030 (1 row) ``` Смотрим статистику журнальных записей: ```sql => 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'); ``` ```text count | record_size | fpi_size | combined_size -------+-------------+----------+--------------- 7474 | 505 kB | 0 bytes | 505 kB (1 row) ``` Объем заметно сократился, полные образы страниц в журнал теперь не записываются. ## Page 22: 2. Сжатие {#source-page-022} ```sql => ALTER SYSTEM SET full_page_writes = on; ``` ```sql ALTER SYSTEM ``` ```sql => ALTER SYSTEM SET wal_compression = on; ``` ```sql ALTER SYSTEM ``` ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` ```text => CHECKPOINT; ``` ```text CHECKPOINT ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/35BE100 (1 row) ``` Запускаем тест на 10 секунд. ```sh student$ pgbench -T 10 wal_tuning ``` ```sql pgbench (16.2 (Ubuntu 16.2-2.pgdg24.04+1)) starting vacuum...end. transaction type: 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) ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/3770508 (1 row) ``` Смотрим статистику журнальных записей: ```sql => 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'); ``` ```text count | record_size | fpi_size | combined_size -------+-------------+----------+--------------- 7497 | 488 kB | 1225 kB | 1713 kB (1 row) ``` В данном случае — при наличии большого числа полных образов страниц — размер журнальных записей уменьшился в несколько раз. Хотя сжатие и нагружает процессор, практически наверняка им стоит воспользоваться при включенных полных образах страниц.