Временные таблицы в MySQL - незаменимая вещь. Конечно, они могут очень загубить производительность всей системы если запросы, которые используют в своей работе временные таблицы, будут очень тяжелыми.
В одном проекте, требовалось создать выборку, которая создавалась огромным запросом с подзапросами. Вес временной таблицы был 2ГБ и если одновременно два администратора начинали строить нужную им выборку - сайт "замирал". Проанализировав данный запрос, сделали вывод, что ускорить эту выборку можно разбитием на несколько мелких и несколько крупных запросов и остальную работу переложить на PHP. Действительно, на некоторое время оптимизация успешно удалась. Но через полгода данные накопились приличные и все вернулось на тоже место.
После недолгих раздумий и анализа самого железа, решили установить на сервер ssd-винчестер специально под базу данных и немного изменить использование памяти. Удалось осободить 14ГБ свободной оперативки и было решено переместить временные таблицы в эти самые 14ГБ неиспользованной оперативной памяти.
Итак, что бы сервер MySQL хранил временные таблицы в оперативной памяти нужно:
- Создаем папку для хранения временных файлов
/var/lib/mysql/tmp_tablemkdir /var/lib/mysql/tmp_table - Делаем mysql владельцем этой папки:
chown mysql:mysql /var/lib/mysql/tmp_table -
Узнаем uid-пользователя и gid-группы mysql:
id mysqlУ нас это 207
-
В файле fstab добавляем следующую запись:
tmpfs /var/lib/mysql/tmp_table tmpfs rw,gid=207,uid=207,size=14G,nr_inodes=10k,mode=0700 0 0 -
После успешного сообщение выполняем монтирование нашего нового раздела:
mount /var/lib/mysql/tmp_table -
Вносим правки в файл конфигурации MySQL. По умолчанию это my.cnf (у вас может быть другой)
tmpdir=/var/lib/mysql/tmp_table
После всех этих манипуляций желательно перезагрузить весь сервер, чтобы убедиться, что все монтируется автоматически после перезагрузки. Таким образом можно получить большой выигрыш в оптимизации системы т.к. файл создаваемый в оперативной памяти имеет более высокую скорость работы чем тот, что копируется на жесткий диск.
Мы после этих манипуляций, добились того, что выборка строилась в 6 раз быстрее, при этом не зажирая все ресурсы под себя и таким образом удалось избавиться от "зависания сайта". Спустя 2 месяца работы - временные таблицы уже достигали почти 8ГБ и нельзя сказать, что скорость заметно упала по сравнению с 6ГБ. В ближайшее время сервер увеличит свою оперативную память и на ближайших 2-3 года его хватит с головой.
