Михаил Григорьев Сисадмин - это не профессия, это призвание.

На первый взгляд может показаться, что тема не стоит и выеденного яйца, но при детальном рассмотрении вопросом управления временным табличным пространством (temporary tablespace) в Oracle MySQL нужно вовремя озаботиться — это позволит избежать появления различных проблем в будущем.

Давайте рассмотрим что такое временное табличное пространство (temporary tablespace) в Oracle MySQL, как им управлять и какие проблемы нас могут ждать если мы вовремя не настроим некоторые параметры.

Исходные данные: Oracle MySQL 5.7.25 на Debian Linux

Для начала как всегда ссылка на первоисточник информации — официальная документация по Oracle MySQL

А теперь небольшая предыстория, почему нужно знать и уметь управлять временным табличным пространством (temporary tablespace). В один прекрасный зимний вечер ко мне в скайп постучался клиент с криком: «Михаил, помоги, только что на сервере с MySQL на диске почти моментально закончилось место, база еле работает, мы не можем понять куда исчезли 200GB свободного места». Я подключаюсь к серверу и довольно быстро нахожу источник поедания места, вот так это выглядело:

# ls -rlth /var/lib/mysql | grep ibtmp
-rw-r----- 1 mysql mysql 210G Dec 12 10:50 ibtmp1

Все место на диске занял файл /var/lib/mysql/ibtmp1

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

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

Итак, давайте вначале посмотрим лог mysql на предмет упоминания в нем создания файла временного ТП:

# less /var/log/mysql/error.log
2019-02-20T16:12:53.472749Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-02-20T16:12:53.472836Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-02-20T16:12:53.750085Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

Вот те самые строки в который MySQL при запуске создает файл ibtmp1, все довольно просто, быстро и лаконично.

Теперь посмотрим настройки временного ТП:

# mysql -u root -p
mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+
1 row in set (0.00 sec)

Тут мы видим, что у нас создается 1 файл для временного ТП размером 12 МБ, с авторасширением и без ограничения размера.

Более детальную информация о временном ТП можно посмотреть таким запросом:

# mysql -u root -p
mysql> SELECT FILE_ID, FILE_NAME, ENGINE, INITIAL_SIZE/1024/1024 AS INITIAL_SIZE_MB,\
 AUTOEXTEND_SIZE/1024/1024 AS AUTOEXTEND_SIZE_MB, \
 TOTAL_EXTENTS*EXTENT_SIZE/1024/1024 AS TOTAL_SIZE_MB,\
 (TOTAL_EXTENTS-FREE_EXTENTS)*EXTENT_SIZE/1024/1024 AS TOTAL_USED_MB, \
 DATA_FREE/1024/1024 AS DATA_FREE_MB,\
 MAXIMUM_SIZE/1024/1024 AS MAXIMUM_SIZE_MB FROM INFORMATION_SCHEMA.FILES \
 WHERE TABLESPACE_NAME = 'innodb_temporary'\G

*************************** 1. row ***************************
           FILE_ID: 37
         FILE_NAME: ./ibtmp1
            ENGINE: InnoDB
   INITIAL_SIZE_MB: 12.00000000
AUTOEXTEND_SIZE_MB: 64.00000000
     TOTAL_SIZE_MB: 12.00000000
     TOTAL_USED_MB: 8.00000000
      DATA_FREE_MB: 8.00000000
   MAXIMUM_SIZE_MB: NULL
1 row in set (0.00 sec)

mysql> SELECT @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT @@innodb_data_home_dir;
+------------------------+
| @@innodb_data_home_dir |
+------------------------+
| NULL                   |
+------------------------+
1 row in set (0.00 sec)

Здесь мы видим:
FILE_ID — ID файла, он будет меняться при перезапуске MySQL;
FILE_NAME — имя файла и путь до файла (указание ./ibtmp1 означает, что файл временного ТП создается в каталоге определенном настрокой datadir, то есть в /var/lib/mysql, но только при не установленной настроке innodb_data_home_dir, о ней я поясню ниже);
ENGINE — Движок временного ТП;
INITIAL_SIZE_MB — Начальный размер временного ТП в МБ;
AUTOEXTEND_SIZE_MB — Шаг приращения размера временного ТП в МБ;
TOTAL_SIZE_MB — Текущий размер временного ТП на диске в МБ;
TOTAL_USED_MB — Сколько сейчас занято пространства во временном ТП в МБ;
DATA_FREE_MB — Сколько сейчас свободного пространства во временном ТП в МБ;
MAXIMUM_SIZE_MB — Максимальный размер временного ТП в МБ (если указано NULL, то размер не ограничен);

Результаты запросов SELECT @@datadir; и SELECT @@innodb_data_home_dir; нам тоже важны и нужны, но о них мы поговорим чуть ниже.

Как Вы уже поняли, для управления настройками временного ТП используется настройка innodb_temp_data_file_path (по ссылке Вы можете прочитать детальную информацию)

Давайте ограничим размер роста временного ТП на уровне 10 GB, для этого в файле конфигурации /etc/mysql/mysql.conf.d/mysqld.cnf в секции [mysqld] пропишем:

[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:10G

Максимальный размер временного ТП указывается последним параметром с добавлением буквы K, M или G (KB, MB или GB (1024MB)).

Для применения настроек нужно перезапустить MySQL, после этого проверим состояние и параметры временного ТП с помощью моего запроса, результат:

*************************** 1. row ***************************
           FILE_ID: 38
         FILE_NAME: ./ibtmp1
            ENGINE: InnoDB
   INITIAL_SIZE_MB: 12.00000000
AUTOEXTEND_SIZE_MB: 64.00000000
     TOTAL_SIZE_MB: 12.00000000
     TOTAL_USED_MB: 8.00000000
      DATA_FREE_MB: 8.00000000
   MAXIMUM_SIZE_MB: 10240.00000000
1 row in set (0.00 sec)

Мы видим, что FILE_ID стал уже 38 и MAXIMUM_SIZE_MB стал теперь 10240 МБ.

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

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

Давайте создадим 2 файла для временного ТП: 1 файл фиксированного размером 512 MB и разместим его на отдельном разделе смонтированном к каталогу /var/lib/mysql_tmp, а второй как обычно начальным размером 12 MB и с авторасширением до 10 GB, второй файл оставим в каталоге /var/lib/mysql:

1. Смонтируем новый диск и установим владельца на каталог /var/lib/mysql_tmp:

mkdir /var/lib/mysql_tmp
chown -R mysql:mysql /var/lib/mysql_tmp
mount /dev/sda2 /var/lib/mysql_tmp

2. Внесем настройки в файл конфигурации /etc/mysql/mysql.conf.d/mysqld.cnf в секцию [mysqld]:

[mysqld]
innodb_temp_data_file_path=../mysql_tmp/ibtmp1:512M;ibtmp2:12M:autoextend:max:10G

Я не спроста смонтировал новый диск к каталогу /var/lib/mysql_tmp, т.к. в настройке innodb_temp_data_file_path путь до файла ibtmp1 нужно указывать относительно каталога datadir (по умолчанию /var/lib/mysql).

P.S. Если мы используем настройку innodb_data_home_dir, то тогда путь к файлам временного ТП будет указываться относительно пути из innodb_data_home_dir. По умолчанию innodb_data_home_dir = NULL (в самом начале статьи мы выводили эту настройку), то есть все системные табличные пространства хранятся в datadir (по умолчанию в /var/lib/mysql).

Для применения настроек нужно перезапустить MySQL, после этого проверим состояние и параметры временного ТП:

# ls -rlth /var/lib/mysql_tmp
total 513M
-rw-r----- 1 mysql mysql 512M Feb 20 22:26 ibtmp1

# ls -rlth /var/lib/mysql | grep ibtmp
-rw-r----- 1 mysql mysql  12M Feb 20 22:26 ibtmp2

# mysql -u root -p
mysql> SELECT FILE_ID, FILE_NAME, ENGINE, INITIAL_SIZE/1024/1024 AS INITIAL_SIZE_MB,\
 AUTOEXTEND_SIZE/1024/1024 AS AUTOEXTEND_SIZE_MB, \
 TOTAL_EXTENTS*EXTENT_SIZE/1024/1024 AS TOTAL_SIZE_MB,\
 (TOTAL_EXTENTS-FREE_EXTENTS)*EXTENT_SIZE/1024/1024 AS TOTAL_USED_MB, \
 DATA_FREE/1024/1024 AS DATA_FREE_MB,\
 MAXIMUM_SIZE/1024/1024 AS MAXIMUM_SIZE_MB FROM INFORMATION_SCHEMA.FILES \
 WHERE TABLESPACE_NAME = 'innodb_temporary'\G

*************************** 1. row ***************************
           FILE_ID: 39
         FILE_NAME: ../mysql_tmp/ibtmp1
            ENGINE: InnoDB
   INITIAL_SIZE_MB: 512.00000000
AUTOEXTEND_SIZE_MB: 64.00000000
     TOTAL_SIZE_MB: 524.00000000
     TOTAL_USED_MB: 520.00000000
      DATA_FREE_MB: 513.00000000
   MAXIMUM_SIZE_MB: 512.00000000
*************************** 2. row ***************************
           FILE_ID: 39
         FILE_NAME: ./ibtmp2
            ENGINE: InnoDB
   INITIAL_SIZE_MB: 12.00000000
AUTOEXTEND_SIZE_MB: 64.00000000
     TOTAL_SIZE_MB: 524.00000000
     TOTAL_USED_MB: 520.00000000
      DATA_FREE_MB: 513.00000000
   MAXIMUM_SIZE_MB: 10240.00000000
2 rows in set (0.00 sec)

У нас все получилось, создалось 2 файла временного ТП, один из них /var/lib/mysql_tmp/ibtmp1 фиксированного размера 512 MB без возможности дальнейшего роста, а второй /var/lib/mysql/ibtmp2 размером 12 MB с авторасширением до 10 GB.

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

Еще одна немаловажная настройка — это инкремент увеличения размера временного ТП (колонка AUTOEXTEND_SIZE_MB в моем запросе), по умолчанию он равен 64 МБ, если Вам нужно увеличить это значение, то воспользуйтесь настройкой innodb_autoextend_increment

Пример:

[mysqld]
innodb_autoextend_increment=128
innodb_temp_data_file_path=../mysql_tmp/ibtmp1:512M;ibtmp2:12M:autoextend:max:10G

Для применения настроек нужно перезапустить MySQL, после этого проверим состояние и параметры временного ТП с помощью моего запроса, результат:

*************************** 1. row ***************************
           FILE_ID: 40
         FILE_NAME: ../mysql_tmp/ibtmp1
            ENGINE: InnoDB
   INITIAL_SIZE_MB: 512.00000000
AUTOEXTEND_SIZE_MB: 128.00000000
     TOTAL_SIZE_MB: 524.00000000
     TOTAL_USED_MB: 520.00000000
      DATA_FREE_MB: 513.00000000
   MAXIMUM_SIZE_MB: 512.00000000
*************************** 2. row ***************************
           FILE_ID: 40
         FILE_NAME: ./ibtmp2
            ENGINE: InnoDB
   INITIAL_SIZE_MB: 12.00000000
AUTOEXTEND_SIZE_MB: 128.00000000
     TOTAL_SIZE_MB: 524.00000000
     TOTAL_USED_MB: 520.00000000
      DATA_FREE_MB: 513.00000000
   MAXIMUM_SIZE_MB: 10240.00000000
2 rows in set (0.00 sec)

Мы видим, что AUTOEXTEND_SIZE_MB стал 128 МБ.

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