На днях столкнулся с ошибкой в эксплуатации и настройке MySQL сервера, о которой решил написать, чтобы самому не забыть решение, и с вами поделиться. Тема довольно распространённая, теория по которой будет полезна всем, кто работает с этой СУБД.

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

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

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2G

Тут указано, что создаётся файл изначального размера 12 мегабайт с автоматическим увеличением до 2 гигабайт. Заранее предсказать, какого максимального объёма будет достаточно, трудно. Это зависит от многих факторов. Я такую настройку сделал несколько лет назад на одном сервере, который на днях засбоил.

Проблемы выражались в том, что существенно увеличилась запись на диск. Об этом отрапортовал Zabbix. Посмотрел статистику этой VM на гипервизоре, тоже видно, что нагрузка на диск выросла. В самой VM в логе MySQL примерно в то же время стали появляться ошибки:

[ERROR] /usr/sbin/mysqld: The table '/tmp/#sql_1c6f_1' is full

Меня они сначала сбили с толку, так как подумал, что на сервере не хватает места. Но нет, с местом всё в порядке. Стал разбираться дальше и понял, что подобная ошибка означает не только недостаток места на диске, (а при нём будет такая же ошибка) но и нехватку выделенного места под temporary tablespace. Увеличил значение в innodb_temp_data_file_path и перезапустил MySQL сервер. Ошибка ушла, как и повышенная нагрузка на диск.

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

#mysql