При инициализации экземпляра MySQL создаются две стандартные справочные таблицы undo. Стандартные справочные таблицы undo создаются на этапе инициализации, чтобы предоставить место для сегментов отката, которые должны существовать перед тем, как будут приняты операторы SQL. Для поддержки автоматической обрезки справочных таблиц undo требуется как минимум две справочные таблицы. См. Обрезку справочных таблиц undo.

Стандартные справочные таблицы undo создаются в расположении, определенном переменной innodb_undo_directory. Если переменная innodb_undo_directory не определена, стандартные справочные таблицы undo создаются в каталоге данных. Файлы данных стандартных справочных таблиц undo имеют имена undo_001 и undo_002. Соответствующие имена справочных таблиц undo, определенные в словаре данных, — innodb_undo_001 и innodb_undo_002.

Дополнительные справочные таблицы undo можно создать во время выполнения с помощью операторов SQL. См. Добавление справочных таблиц undo.

Изначальный размер справочной таблицы undo обычно составляет 16 МБ. Изначальный размер может отличаться, когда новая справочная таблица undo создается операцией обрезки. В этом случае, если размер расширения файла больше 16 МБ, а предыдущее расширение произошло в течение последней секунды, новая справочная таблица undo создается на четверть размера, определенного переменной innodb_max_undo_log_size.

Справочная таблица undo расширяется как минимум на 16 МБ. Для обработки интенсивного роста размер расширения файла удваивается, если предыдущее расширение произошло менее чем за 0,1 секунды. Удвоение размера расширения может происходить несколько раз до максимального значения 256 МБ. Если предыдущее расширение произошло более 0,1 секунды назад, размер расширения уменьшается вдвое, что также может происходить несколько раз, до минимального значения 16 МБ. Если для справочной таблицы undo определен параметр AUTOEXTEND_SIZE, она расширяется до большего из значений AUTOEXTEND_SIZE и размера расширения, определяемого описанной выше логикой. Сведения о параметре AUTOEXTEND_SIZE см. в Разделе 17.6.3.9, «Настройка параметра AUTOEXTEND_SIZE для табличных пространств».

Поскольку журналы undo могут стать большими во время длительных транзакций, создание дополнительных справочных таблиц undo может помочь предотвратить увеличение размера отдельных справочных таблиц undo. Дополнительные справочные таблицы undo можно создать во время выполнения, используя синтаксис CREATE UNDO TABLESPACE.

CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu';

Имя файла справочной таблицы undo должно иметь расширение .ibu. Недопустимо указывать относительный путь при определении имени файла справочной таблицы undo. Допустим полный путь, но путь должен быть известен InnoDB. Известные пути — те, что определены переменной innodb_directories. Рекомендуется использовать уникальные имена файлов справочных таблиц undo, чтобы избежать потенциальных конфликтов имен файлов при перемещении или клонировании данных.

При запуске сканируются каталоги, определенные переменной innodb_directories, на наличие файлов справочных таблиц undo. (Сканирование также проходит по подкаталогам.) Каталоги, определенные переменными innodb_data_home_dir, innodb_undo_directory и datadir, автоматически добавляются к значению innodb_directories, независимо от того, определена ли переменная innodb_directories явно. Поэтому справочная таблица undo может находиться в путях, определенных любыми из этих переменных.

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

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

Чтобы просмотреть имена и пути справочных таблиц undo, запросите таблицу INFORMATION_SCHEMA.FILES:

SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES
  WHERE FILE_TYPE LIKE 'UNDO LOG';

Экземпляр MySQL поддерживает до 127 справочных таблиц undo, включая две стандартные справочные таблицы undo, созданные при инициализации экземпляра MySQL.

Справочные таблицы undo можно удалить, используя синтаксис DROP UNDO TABALESPACE. См. Удаление справочных таблиц undo.

Пространства таблиц отката, созданные с помощью синтаксиса CREATE UNDO TABLESPACE, можно удалить во время работы с помощью синтаксиса DROP UNDO TABALESPACE.

Пространство таблиц отката должно быть пустым перед удалением. Чтобы очистить пространство таблиц отката, необходимо сначала пометить его как неактивное с помощью синтаксиса ALTER UNDO TABLESPACE, чтобы пространство таблиц больше не использовалось для назначения сегментов отката новым транзакциям.

ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;

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

DROP UNDO TABLESPACE tablespace_name;

Состояние пространства таблиц отката можно отслеживать, запрашивая таблицу схемы информации INNODB_TABLESPACES.

SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
  WHERE NAME LIKE 'tablespace_name';

Состояние inactive указывает, что сегменты отката в пространстве таблиц отката больше не используются новыми транзакциями. Состояние empty указывает, что пространство таблиц отката пустое и готово к удалению или готово к повторной активации с помощью оператора ALTER UNDO TABLESPACE tablespace_name SET ACTIVE. Попытка удалить пространство таблиц отката, которое не пустое, приведет к ошибке.

Нельзя удалить пространства таблиц отката по умолчанию (innodb_undo_001 и innodb_undo_002), созданные при инициализации экземпляра MySQL. Однако их можно сделать неактивными с помощью оператора ALTER UNDO TABLESPACE tablespace_name SET INACTIVE. Перед тем как сделать пространство таблиц отката по умолчанию неактивным, должно существовать пространство таблиц отката, которое заменит его. Всегда должно быть как минимум два активных пространства таблиц отката для поддержки автоматического обрезания пространств таблиц отката.

Пространства таблиц отката, созданные с помощью синтаксиса CREATE UNDO TABLESPACE, можно переместить в любой известный каталог, когда сервер выключен. Известные каталоги определяются переменной innodb_directories. Каталоги, определенные переменными innodb_data_home_dir, innodb_undo_directory и datadir, автоматически добавляются к значению innodb_directories, независимо от того, определена ли переменная innodb_directories явно. Эти каталоги и их подкаталоги сканируются при запуске для поиска файлов пространств таблиц отката. Файл пространства таблиц отката, перемещенный в любой из этих каталогов, обнаруживается при запуске и предполагается, что это перемещенное пространство таблиц отката.

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

Паттерны ввода-вывода для журналов отката делают пространства таблиц отката хорошими кандидатами для хранения.

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

Значение по умолчанию для innodb_rollback_segments составляет 128, что также является максимальным значением. Подробную информацию о количестве транзакций, которые поддерживает один сегмент отката, см. в разделе 17.6.6, «Журналы отката».

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

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

Автоматическая обрезка

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

Чтобы обеспечить автоматическую обрезку системных таблиц обратной записи, включите переменную innodb_undo_log_truncate. Например:

mysql> SET GLOBAL innodb_undo_log_truncate=ON;

Когда переменная innodb_undo_log_truncate включена, системные таблицы обратной записи, размер которых превышает предел, определенный переменной innodb_max_undo_log_size, подлежат обрезке. Переменная innodb_max_undo_log_size динамическая и имеет значение по умолчанию 1073741824 байта (1024 МБ).

mysql> SELECT @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
|                 1073741824 |
+----------------------------+

Когда переменная innodb_undo_log_truncate включена:

Ручная обрезка

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

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

ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;

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

Для проверки состояния таблицы обратной записи обратитесь к таблице схемы информации INNODB_TABLESPACES.

SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
  WHERE NAME LIKE 'tablespace_name';

После того, как системная таблица обратной записи перейдет в состояние empty, ее можно повторно активировать, выполнив следующее утверждение:

ALTER UNDO TABLESPACE tablespace_name SET ACTIVE;

Системную таблицу обратной записи в состоянии empty также можно удалить. См. Удаление системных таблиц обратной записи.

Ускорение автоматической обрезки системных таблиц обратной записи

Поток очистки отвечает за опустошение и обрезку системных таблиц обратной записи. По умолчанию поток очистки ищет системные таблицы обратной записи для обрезки один раз каждые 128 вызовов очистки. Частота, с которой поток очистки ищет системные таблицы обратной записи для обрезки, контролируется переменной innodb_purge_rseg_truncate_frequency, которая имеет значение по умолчанию 128.

mysql> SELECT @@innodb_purge_rseg_truncate_frequency;
+----------------------------------------+
| @@innodb_purge_rseg_truncate_frequency |
+----------------------------------------+
|                                    128 |
+----------------------------------------+

Для повышения частоты уменьшите значение innodb_purge_rseg_truncate_frequency. Например, чтобы поток очистки искал системные таблицы обратной записи один раз каждые 32 вызова очистки, установите innodb_purge_rseg_truncate_frequency в значение 32.

mysql> SET GLOBAL innodb_purge_rseg_truncate_frequency=32;

Воздействие на производительность обрезки файлов системных таблиц обратной записи

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

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

Мониторинг обрезки системных таблиц обратной записи

undo и purge подсистемные счетчики предоставляются для мониторинга фоновой активности, связанной с обрезкой журнала обратной записи. Для получения имен и описаний счетчиков запросите таблицу схемы информации INNODB_METRICS.

SELECT NAME, SUBSYSTEM, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%truncate%';

Сведения об активации счетчиков и запросе данных счетчиков см. в Раздел 17.15.6, «Таблица метрик InnoDB INFORMATION_SCHEMA».

Предел обрезки системных таблиц обратной записи

Количество операций обрезки одной и той же системной таблицы обратной записи между контрольными точками ограничено 64. Этот предел предотвращает возможные проблемы, вызванные чрезмерным количеством операций обрезки системных таблиц обратной записи, которые могут возникнуть, если innodb_max_undo_log_size установлено слишком низким на загруженной системе, например. Если предел превышен, системная таблица обратной записи все еще может быть помечена как неактивная, но не обрезается до следующей контрольной точки. В MySQL 8.4 предел составляет 50000.

Восстановление обрезки системных таблиц обратной записи

Операция обрезки системной таблицы обратной записи создает временный undo_space_number_trunc.log файл в каталоге журнала сервера. Этот каталог определяется с помощью innodb_log_group_home_dir. В случае сбоя системы во время операции обрезки временный файл журнала позволяет процессу запуска определить системные таблицы обратной записи, которые обрабатывались в процессе обрезки, и продолжить операцию.

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

mysql> SHOW STATUS LIKE 'Innodb_undo_tablespaces%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_undo_tablespaces_total    | 2     |
| Innodb_undo_tablespaces_implicit | 2     |
| Innodb_undo_tablespaces_explicit | 0     |
| Innodb_undo_tablespaces_active   | 2     |
+----------------------------------+-------+

Описание переменных состояния см. в Раздел 7.1.10, «Переменные состояния сервера».