Разбираемся что MySQL пишет на диск и зачем

[GuDron]

Disclaimer: автор этого текста не является разработчиком MySQL, все нижеописанное может не совпадать с реальным положением дел.

Часть 0: Размышления о хранении данных​

Разработчики предъявляют высокие требования к базам данных: максимальная надежность (ничего из того, что было записано не должно быть утеряно ни при каких обстоятельствах), и, одновременно, максимальная производительность при различных видах нагрузки (Запись/Чтение или OLTP/OLAP). Достичь этих требований может быть не просто. Давайте попробуем разобраться, как это делает MySQL.

Размышляя о базе данных, легко представить таблицу базы данных как HashMap/BinaryTree, отображающие первичный ключ (primary key) в структурированные записи с данными. Такое хранилище может работать in memory. Но, как только мы захотим записать данные на диск, придется использовать какие-то алгоритмы во внешней памяти. Просто положить наш HashMap на диск не получится, потому что память и диски слишком разные: чтение/запись диска производится блоками, latency диска больше чем у RAM, а еще нельзя будет воспользоваться обычными указателями и аллокаторами памяти — все это придется заменить самостоятельно.

Почему MMAP не лучший выход: Are You Sure You Want to Use MMAP in Your Database Management System? Для просмотра ссылки Войди или Зарегистрируйся

К счастью, давно уже придуманы структуры данных и алгоритмы, такие как B+Tree и Для просмотра ссылки Войди или Зарегистрируйся, а также бесчисленное количество их вариаций (Подробнее можно прочитать в книге “Database Internals: A Deep Dive into How Distributed Data Systems Work” за авторством Alex Petrov). InnoDB - основной движок хранения MySQL, использует вариацию B+Tree. Данные хранятся в страницах (pages), которые загружаются с диска в buffer pool и при необходимости сохраняются на диск обратно.

Unix-like операционные системы поддерживают разные гарантии записи файла.

• Самым быстрым и заодно ненадежным способом является обычная запись в файл. Операционная система запишет данные в page cache (в память). И уже в фоне запишет данные на диск.
• Если при открытии файла указать флаг O_DIRECT - то запись в файл будет идти мимо page cache - сразу во внутренний буфер диска. Но при отключении питания сервера - мы все еще можем потерять данные.
• fsync — это отдельный системный вызов для сброса данных на диск. На Linux системах fsync ожидает записи на физический носитель, а не только во внутренний буфер диска. Факт записи на диск дает гарантии сохранности данных.
• fdatasync — так же сбрасывает данные на диск, но не дожидается надежной записи обновленных метаданных файловой системы. Если метаданные изменились, но из-за отказов не были записаны на диск, то при следующем старте, Linux не узнает об этих изменениях (например, о том, что файл был увеличен в размере и туда были записаны данные) - данные будут поломаны.

Часть 1: Double Write Buffer​

Страницы с данными в InnoDB по умолчанию занимают 16Кб. Размер страницы — это компромисс. С одной стороны, большие страницы с данными улучшают пропорцию полезных данных к служебным, с другой стороны, большие страницы приводят к бОльшему Для просмотра ссылки Войди или Зарегистрируйся: Например, UPDATE одного числа (4 байта) в одной строке приводит к перезаписи всей страницы (килобайты). Разные базы данных выбирают различные размеры страниц: PostgreSQL использует страницы по 8Кб, а MySQL по-умолчанию по 16Кб, но администраторы баз данных при большом желании могут выбрать размер от 4Кб до 64Кб (Для просмотра ссылки Войди или Зарегистрируйся).

Уже на этом этапе мы сталкиваемся с проблемой атомарности записи данных на диск: современные Linux-based системы не гарантируют атомарность записи блоков размером больше 4Кб.

Детальное описание состояние дел с атомарностью записи на диск можно найти на StackOverflow. [ Для просмотра ссылки Войди или Зарегистрируйся ]. Там же героическая история как инженеры Google патчили ядро, драйвера и файловые системы, чтобы атомарно писать блоками по 16Кб.

Что делает InnoDB, чтобы страницы с данными не побились во время записи? InnoDB пишет их дважды: сначала в doublewrite buffer, и только потом страницы записываются в положенное им место.

Несмотря на название, сам doublewrite buffer не удваивает количество IO операций - страницы в doublewrite buffer пишутся большими блоками и выполняется всего один fsync() (да и то, если не используется IO_DIRECT). Если в процессе crash-recovery InnoDB найдет “битую” страницу - он сможет достать ее целый вариант из doublewrite buffer.

В старых версиях MySQL, doublewrite buffer занимал Для просмотра ссылки Войди или Зарегистрируйся в начале system tablespace (файл ibdata1). Запись велась:

• Страницы копировались в doublewrite buffer в памяти.
• Большим блоком записывались на в system tablespace. Если не использовался IO_DIRECT - вызывался fsync().
• Страницы пишутся в нужные места, если не используется IO_DIRECT - вызывается fsync().
• По завершению всех операций, doublewrite buffer считается пустым и готовым к следующей итерации.

Начиная с версии MySQL 8.0.20, алгоритм был изменен - теперь doublewrite buffer пишется в разные файлы (например, в файл #ib_16384_0.dblwr ). Новый подход должен лучше работать на SSD.

 

[GuDron]

Часть 2: Binlogs​

MySQL была спроектирована как база данных, которая может работать с различными движками (storage engines), поэтому MySQL можно разделить на два крупных “слоя” - непосредственно MySQL и различные Storage Engine (на практике это почти всегда InnoDB, реже Memory Engine, но изредка еще встречается MyISAM и MyRocksDB). Из-за этой “двухслойности” у нас есть и явное разделение обязанностей - MySQL занимается обработкой SQL запросов, репликацией (пишет binlogs), а InnoDB отвечает за надежное хранением данных на диске.

Для распространения изменений, записанных на мастере, MySQL использует подход Replicated State Machine (RSM)- все изменения записываются в binlog, и доставляются на реплики. Реплики применяют транзакции к своему текущему состоянию. Если транзакции полностью детерминированы - то в результате на мастере и на репликах получается одинаковое состояние (чего, собственно, мы и ожидаем от базы данных). Как побочный эффект детерминизма - к развернутой из бекапа базе данных можно проигрывать бинлоги и тем самым восстановить базу на любой момент времени (aka Point-in-Time Recovery).

MySQL может писать в binlog как SQL Statements (Statement-based replication), так и просто измененные данные (row-based replication). Для Statment-based replication сложнее гарантировать детерминированность транзакций и совпадение данных, хранящихся на разных хостах.

Binary Log в широком смысле слова - хранилище Binary Log Events (далее “события”). Эти события хранятся в binlog-файлах. Каждый файл начинается с заголовка, содержащего служебную информацию, потом идут события, и в конце пишется rotate event. Кроме этого, MySQL поддерживает Binlog Index, где хранится список всех имеющихся бинлогов.

Binlog cache​

binlog — это файл, который пишется последовательно, целыми транзакциями. Пока одна транзакция не будет записана полностью, нельзя начинать писать вторую транзакцию. Для того, чтобы одни транзакции не блокировали запись других транзакций, все binlog events пишутся сначала в binlog cache (специальный буффер в памяти каждого потока, выполняющего транзакции) и только в момент коммита записываются уже на диск. В случае отката транзакции - binlog cache очищается, как будто ничего и не было записано в него.

Для просмотра ссылки Войди или Зарегистрируйся: Если Для просмотра ссылки Войди или Зарегистрируйся было недостаточно, MySQL начнет сбрасывать кэш на диск (в новый файл, который сразу после создания будет удален (unlink) с файловой системы - т.е. будет “невидим”). Максимальный размер binlog cache на диске настраивается с помощью Для просмотра ссылки Войди или Зарегистрируйся (по-умолчанию 18 эксабайт!). Хотя, документация говорит, что MySQL не может работать с бинлогами больше 4 Гб: при достижении этого порога будет выброшена ошибка.

Group Commit​

Вооружившись знанием о том, что такое binlog, для crash-safe recovery необходимо делать fsync() на каждую запись в бинлоге (настройка sync_binlog = 1). Ведь, с одной стороны, binlog-и не участвуют в непосредственной записи наших данных на диск, используются в репликации (не очень связанной с хранением ваших данных на диске!) и вообще, бинлоги можно отключить, и база продолжит работать!

Если не скидывать бинлоги на диск - велик шанс что упавший MySQL после восстановления будет неконсистентен с другими репликами (и вам повезет, если вы это заметите сразу). В целом жить с sync_binlog отличном от 1 Для просмотра ссылки Войди или Зарегистрируйся, при условии отказа от crash-recovery и переналивкой упавших хостов. Вы же не ожидаете крэша всех хостов MySQL одной транзакцией или retry-ем одной транзакции по всем хостам

Допустим, мы все-таки хотим надежной записи на диск с помощью fsync. Как мы уже знаем, вызов fsync()-а это довольно медленная операция, где мы очень легко можем упереться в IOPS (особенно на HDD дисках). Очевидным решением бутылочного горлышка IOPS-ов является батчинг - на каждый fsync() писать не одну транзакцию, а сразу целую группу транзакций. В MySQL такой батчинг называется Group Commit.

Интересно, что MySQL 5.0 не делал Group Commit, и транзакции ожидали своей очереди для сохранения бинлога на диск. Ни о какой высокой производительности здесь речи идти не может.

В Percona Server 5.5.18-23 Для просмотра ссылки Войди или Зарегистрируйся одну из первых версий group commit:

• Когда поток, выполняющий транзакцию, решит закоммитить транзакцию - он добавляет себя в group commit queue.
• После чего поток пытается понять - является ли он первым в group commit queue. Если он первый - то он становится “group commit leader”.
• Лидер Для просмотра ссылки Войди или Зарегистрируйся (Этот лок может быть занят предыдущим лидером, который все еще пишет на диск). Именно в это время другие потоки могут добавлять транзакции в group commit queue - тем самым собираясь в новую группу.
• Заполучив лок на весь бинлог, лидер Для просмотра ссылки Войди или Зарегистрируйся (следующий лидер создаст себе новую queue)
• Лидер записывает содержимое binlog cache каждого из потоков и делает fsync() (если надо). После чего он “Для просмотра ссылки Войди или Зарегистрируйся” пользовательские потоки, которые заблокировались на записи в бинлог.

Чуть позже, помимо группировки транзакций может быть настроен на небольшое ожидание перед записью в бинлог, пытаясь собрать побольше транзакций в group commit queue. По Для просмотра ссылки Войди или Зарегистрируйся ребят из Percona - количество транзакций в секунду увеличивается на 30%.

В актуальных версиях MySQL group commit сделан чуть по-другому: запись в бинлог разбита на этапы, которые управляются с помощью Commit_stage_manager. MySQL гарантирует, что порядок записи событий в бинлоге совпадает с порядком записи изменений в Storage Engines (Это значительно упрощает работу backup-тулам, таким как xtrabackup или MySQL Clone Plugin).

Все этапы (stages) образуют Для просмотра ссылки Войди или Зарегистрируйся, в котором события берутся из очереди, обрабатываются и складываются в следующую очередь. Каждая очередь защищена своим мьютексом.

Всего используется Для просмотра ссылки Войди или Зарегистрируйся:

• Binlog flush queue - очередь на запись на диск.
• Sync queue - очередь из транзакций, для которых надо вызвать fsync().
• Commit queue - очередь транзакций, которая используется для упорядочивания коммитов транзакций в пределах group commit. (необходима при binlog_order_commit=1).
• Commit order flush queue - очередь из транзакций, которые не пишут в бинлог, но участвуют в group commit - Для просмотра ссылки Войди или Зарегистрируйся для обновления gtid_executed в экзотических ситуациях.

Все stage работают по похожему алгоритму:

1. Когда поток, выполняющий транзакцию, решит закоммитить транзакцию - он добавляет себя во flush queue.
2. После чего поток пытается понять - является ли он первым в очереди или нет. Если он первый - то он становится stage leader.
3. Stage Leader (после небольшого ожидания в binlog_max_flush_queue_time ms) забирает все транзакции из очереди и выполняет свою операцию
   1. binlog flush stage - производит Для просмотра ссылки Войди или Зарегистрируйся в бинлог: данные из binlog cache (binlog_cache_mngr) Для просмотра ссылки Войди или Зарегистрируйся в файл
   2. sync stage - Для просмотра ссылки Войди или Зарегистрируйся fsync()
   3. commit stage - транзакция коммитится в storage engine
4. По завершению операции, Stage Leader добавляет транзакции, которыми он владел, в следующую очередь. Может так оказаться, что очередь, куда пишет stage leader не пуста - это означает что он Для просмотра ссылки Войди или Зарегистрируйся (который ожидает чего-то: блокировки или таймаута). В этот момент наш stage leader теряет свое лидерство. Его события будет обрабатывать “нагнанный” лидер. Такое поведение адаптирует размер group commit-а к самой медленной операции (обычно это fsync()) - долгие операции работают с бОльшим количеством event-ов за раз.

Параллельная репликация​

Дополнительным преимуществом group commit является параллельная репликация - в пределах group-commit-а репликам позволено параллельно выполнять транзакции используя Для просмотра ссылки Войди или Зарегистрируйся потоков, после чего они делают commit в том же порядке что и на мастере (Для просмотра ссылки Войди или Зарегистрируйся), чтобы гарантировать что на реплике не будет состояния, которого никогда не было на мастере (Полезное свойство, если Вы читаете с реплик!).