Размещаем временные таблицы MySQL в оперативной памяти


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

В одном проекте, требовалось создать выборку, которая создавалась огромным запросом с подзапросами. Вес временной таблицы был 2ГБ и если одновременно два администратора начинали строить нужную им выборку – сайт “замирал”. Проанализировав данный запрос, сделали вывод, что ускорить эту выборку можно разбитием на несколько мелких и несколько крупных запросов и остальную работу переложить на PHP. Действительно, на некоторое время оптимизация успешно удалась. Но через полгода данные накопились приличные и все вернулось на тоже место.
После недолгих раздумий и анализа самого железа, решили установить на сервер ssd-винчестер специально под базу данных и немного изменить использование памяти. Удалось осободить 14ГБ свободной оперативки и было решено переместить временные таблицы в эти самые 14ГБ неиспользованной оперативной памяти.

Итак, что бы сервер MySQL хранил временные таблицы в оперативной памяти нужно:

  • Создаем папку для хранения временных файлов /var/lib/mysql/tmp_table
    mkdir /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 года его хватит с головой.


Другие публикации:

Написать комментарий через:

 
               
  • Локальный блог
  •  
 

Ваш отзыв

Имя *

Почта (скрыта) *

Сайт

Напишите цифрами двa вoceмь двa *

Сообщение

 
Возврат % от покупок
Статусы для соц.сетей на ArtKiev Design Studio