Порой системные администраторы сталкиваются с реальной проблемой полного исчерпания дискового пространства в MySQL. Это серьезная проблема из-за постоянных отключений службы, поскольку приходится постоянно перезапускать сервер и ждать следующего отключения.
Что происходит? В этой статье мы объясним это и предложим решения.
Временные таблицы (файлы)
MySQL необходимо создавать временные таблицы для выполнения некоторых типов запросов. Запросы, требующие этапа сортировки, в большинстве случаев должны полагаться на временную таблицу. Например, при использовании GROUP BY, ORDER BY или DISTINCT. Такие запросы выполняются в два этапа: первый — собрать данные и поместить их во временную таблицу, второй — выполнить сортировку по временной таблице.
Временная таблица также необходима в случае оценки некоторых операторов UNION, для VIEW, который не может использовать слияние, для производных таблиц при использовании подзапросов, для UPDATE с несколькими таблицами и в некоторых других случаях.
Если временная таблица небольшая, ее можно создать в памяти, в противном случае она создается на диске. Излишне говорить, что временная таблица в памяти работает быстрее. MySQL создает таблицу в памяти, и если она становится слишком большой, она преобразуется в таблицу на диске. Максимальный размер для временных таблиц в памяти определяется значением tmp_table_size или max_heap_table_size, в зависимости от того, что меньше. Размер по умолчанию в MySQL 5.7 составляет 16 МБ.
Если вы выполняете запросы к большому объему данных или не оптимизировали запросы, вы можете увеличить переменные. При установке порога учитывайте доступную оперативную память и количество одновременных подключений во время пиковых нагрузок. Вы не можете бесконечно увеличивать переменные, так как в какой-то момент вам нужно будет разрешить MySQL использовать временные таблицы на диске.
Примечание: временная таблица создается на диске, если задействованные таблицы имеют столбцы TEXT или BLOB, даже если размер меньше заданного порога.
Механизм хранения временных таблиц
До версии MySQL 5.6 все временные таблицы на диске создавались как MyISAM. Временная таблица создается в памяти или на диске, в зависимости от конфигурации, и сразу же удаляется в конце запроса. Начиная с MySQL 5.7, они по умолчанию создаются как InnoDB.
Новое значение по умолчанию — лучший вариант для общей производительности, и его следует использовать в большинстве случаев.
Доступна новая переменная конфигурации для установки механизма хранения для временных таблиц: internal_tmp_disk_storage_engine. Для переменной можно установить значение innodb (по умолчанию) или myisam.
Потенциальная проблема с временными таблицами InnoDB
Хотя использование InnoDB лучше всего подходит для повышения производительности, может возникнуть новая потенциальная проблема. В некоторых частных случаях у вас может быть исчерпание диска и сбой сервера.
Как и любая другая таблица InnoDB в базе данных, временные таблицы имеют свой собственный файл табличного пространства. Новый файл находится в каталоге данных вместе с общим табличным пространством с именем ibtmp1. Он хранит все таблицы tmp. Файл табличного пространства нельзя уменьшить, и он постоянно увеличивается, пока вы не перезапустите службу mysql или не ограничите размер этого файла. Единственный способ уменьшить размер ibtmp1 до нуля — перезапустить сервер.
Давайте теперь подумаем о следующем:
- у вас есть неоптимизированные запросы, требующие создания очень больших таблиц tmp на диске
- у вас есть оптимизированные запросы, но они создают очень большие таблицы tmp на диске, потому что вы выполняете целевые вычисления с очень большим набором данных (статистика, аналитика)
- у вас есть много одновременных подключений, выполняющих одни и те же запросы с созданием таблицы tmp
- у вас мало свободного места в томе
В такой ситуации легко понять, что размер файла ibtmp1 может значительно увеличиться, и файл может легко исчерпать свободное место. Это может происходить несколько раз в день, и сервер приходится перезапускать, чтобы полностью сжать табличное пространство ibtmp1.
Не обязательно, чтобы параллельные запросы запускались точно в одно и то же время. Поскольку выполнение запроса с большой временной таблицей займет несколько секунд или минут, достаточно запускать запросы в разное время, пока предыдущие еще выполняются. Кроме того, вы должны учитывать, что любое соединение создает свою собственную временную таблицу, поэтому точно такой же запрос создаст еще одну точную копию той же временной таблицы в табличном пространстве. Исчерпать место на диске очень легко с помощью не уменьшающихся файлов!
Итак, что же делать, чтобы избежать истощения диска и простоев?
Тривиальное решение: использовать диск большего размера
Это действительно просто и может решить проблему, но это не оптимальное решение. На самом деле не так просто определить, каким должен быть ваш новый размер диска. Вы можете догадаться, увеличивая размер диска шаг за шагом, что довольно легко сделать, если ваша среда находится в облаке или у вас есть виртуальные устройства на очень большой платформе. Но это непросто сделать в локальной среде.
Но с этим решением вы рискуете понести ненужные расходы, так что имейте это в виду.
Вы также можете переместить файл ibtmp1 на выделенный большой диск, установив следующую переменную конфигурации:
[mysqld]
innodb_temp_data_file_path = ../../tmp/ibtmp1:12M:autoextend
Требуется перезапуск MySQL. Обратите внимание, что путь должен быть указан относительно каталога данных.
Установите верхний предел размера ibtmp1
Например:
[mysqld]
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G
В этом случае размер файла не может превышать 10 ГБ. Вы можете легко устранить сбои, но это опасное решение. Когда файл данных достигает максимального размера, запросы завершаются ошибкой, указывающей, что таблица заполнена. Это, вероятно, плохо для ваших приложений.
Вернитесь к MyISAM для временных таблиц на диске
Это решение кажется нелогичным, но оно может быть лучшим способом избежать простоев за считанные секунды и гарантированно использовать все необходимые временные таблицы.
Вы можете установить следующую переменную в my.cnf:
internal_tmp_disk_storage_engine = MYISAM
Поскольку переменная является динамической, вы можете установить ее также во время выполнения:
SET GLOBAL internal_tmp_disk_storage_engine = MYISAM;
Вернувшись к MyISAM, вы значительно уменьшите возможность полного заполнения дискового пространства. На самом деле временные таблицы будут созданы в разных файлах и сразу же удалены в конце запроса. Больше никаких проблем с постоянно увеличивающимся файлом.
И хотя всегда есть возможность увидеть одну и ту же проблему, на всякий случай вы можете попробовать запускать запросы в одно и то же время или очень близко. В некоторых случаях это будет решение, позволяющее избежать отключений.
Оптимизируйте свои запросы
Это самое важное, что нужно сделать. После возврата механизма хранения к MyISAM, чтобы смягчить случаи сбоев, вы должны обязательно потратить время на анализ запросов.
Цель состоит в том, чтобы уменьшить размер временных таблиц на диске. Цель этой статьи не в том, чтобы объяснить, как исследовать запросы, но вы можете положиться на журнал медленных запросов и использовать специальные инструменты.
Некоторые советы:
- создать отсутствующие индексы в таблицах
- добавьте больше фильтров в запросы, чтобы собирать меньше данных, если они вам действительно не нужны
- если у вас есть очень большие запросы, вы можете использовать диспетчер очередей в своих приложениях для сериализации их выполнения или уменьшения параллелизма
- переписать запросы для оптимизации выполнения
Это будет самое продолжительное действие, но, возможно, после всех оптимизаций вы сможете вернуться, чтобы установить механизм временного хранения на InnoDB для повышения производительности.
Вывод
Иногда улучшения могут иметь неожиданные побочные эффекты. Механизм хранения InnoDB для временных таблиц на диске является хорошим улучшением, но в некоторых частных случаях, например, если у вас неоптимизированные запросы и мало свободного места, у вас могут быть простои из-за ошибки «диск заполнен». Переход от механизма хранения tmp к MyISAM — это самый быстрый способ избежать простоев, но оптимизация запросов — это более важная вещь, которую нужно сделать как можно скорее, чтобы вернуться к InnoDB. И да, даже большой или выделенный диск может помочь. Это тривиальное предложение, возможно, но оно определенно может очень помочь.
Какова бы ни была причина, по которой страница сайта не открывается, например проблемы с базой данных, DDoS-атаки или вирусы, важно контролировать доступность сайта для посетителя. Ситуация, при которой пользователь не может открыть страницу вашего сайта, отрицательно влияет на поднятие сайта в поиске (поисковой выдаче) и оставляет негативное впечатление о вашем сайте у посетителя. Вы теряете потенциальных клиентов, а значит и деньги. Используйте хороший сервис, например BAILRY для постоянного контроля (проверки) доступности сайта. Сервис предоставляет как бесплатную регулярную (периодическую) проверку доступности сайта, так и платную услугу - для постоянного контроля доступности сайта.
Компания Mainton - разработка программного обеспечения под заказ, SEO и реклама в интернете с 2004 года.