Ускоряем MySQL с помощью tmpfs

Tmpfs — временное файловое хранилище во многих Unix подобных операционных системах. Tmpfs пердназначена для монтирования файловой системы, а сама размещается в оперативной памяти вместо физического диска. Использование tmpfs в вашей системе можно увидеть с помощью вывода команды df -h

/dev/sda 30G 925M 28G 4%
tmpfs     502M 0 502M 0% /lib/init/rw
udev      497M 60K 497M 1% /dev
tmpfs     502M 4,0K 502M 1% /dev/shm

В tmpfs могут размещатся любые директории, хранящие временные данные и которые можно удалить при перезагрузке системы: /var/lock, /var/run, /tmp и др. Tmpfs используют для уменьшения количества дисковых операций io c целью повышения производительности системы или экономии ресурса твердотельных накопителей.

В процессе работы базы данных MySQL создаются временные таблицы. Место расположения временных таблиц определяется переменной tmpdir в файле /etc/mysql/my.cnf. Если для таких временных таблиц (файлов) не хватает памяти, то начинается активный i/o, создающий нагрузку на дисковую подсистему сервера. А смонтировав папку временных файлов mysql на tmpfs, мы решим проблему проблему производительности базы данных и системы в целом.

На достаточно новых ядрах к директории /dev/shm по умолчанию подмонтирована файловая система tmpfs размером 0,5 объёма физической памяти, которую можно использовать для своих нужд. Если на вашем сервере дело с tmpfs обстоит именно так, нам остается внести правки файл конфигурации MySQL:

$ mcedit /etc/mysql/my.cnf

Закомментировав предыдущее значение параметра tmpdir, добавляем:

tmpdir = /dev/shm

Перезапускаем базу данных mysql:

$ service mysql restart

Проверяем полученный результат:

$ mysql
mysql> SHOW VARIABLES LIKE 'tmpdir';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| tmpdir        | /dev/shm |
+---------------+----------+
1 row in set (0.00 sec)
Также можем создать отделюную папку /var/lib/mysql/tmp

• Создаем папку

  mkdir /var/lib/mysql/tmp

• Изменяем владельца папки и группу на mysql

  chown mysql:mysql /var/lib/mysql/tmp
  

• Выясняем идентификатор пользователя (uid) и группы (gid) mysql

  id mysql
  

• В файл fstab добавляем запись

  tmpfs /var/lib/mysql/tmp tmpfs rw,gid=107,uid=109,size=1G,nr_inodes=10k,mode=0700 0 0
  

• Монтируем новый tmpfs раздел

  mount /var/lib/mysql/tmp
  

• Редактируем файл конфигурации MySQL /etc/mysql/my.cnf

  tmpdir=/var/lib/mysql/tmp
  

Перезапускаем базу данных mysql:

$ service mysql restart

Проверяем полученный результат:

$ mysql
mysql> SHOW VARIABLES LIKE 'tmpdir';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| tmpdir        | /var/lib/mysql/tmp|
+---------------+-------------------+ 1 row in set (0.00 sec)