Ускоряем MySQL. Перемещение временых таблиц c диска (tmpdir) в оперативную память.

Опубликовано admin - вс, 12/02/2012 - 20:00

В работе периодически сталкиваюсь с медленными Drupal сайтами, и тормоза очень часто вызваны медленным выполнением запросов к Mysql. Причины бывают разные, но зачастую медленное выполнение запросов вызвано тем, что при выполнении запроса MySQL вынуждено использовать временные таблицы на диске. Для этого используется каталог заданный в переменной tmpdir файла конфигурации my.cnf.
На мой взгляд, правильным решением в таких ситуациях является оптимизация базы данных (использование типов полей наиболее подходящих под содержимое, правильная настройка идексов, и построение корректных запросов к базе, учитывающих индексы в базе). Но также мы можем помочь MySQL если переместим эти временные таблицы в оперативную память (такое решение подходит для серверов с большим количеством оперативной памяти), что позволит более быстро выполнять операции связанные с использованием временных таблиц, за счет экономии на операциях записи и чтения с диска, так как всё будет делаться в оперативной памяти.
Для переноса папки tmpdir в оперативную память мы подключим к папке /var/lib/mysql/tmp раздел tmpfs размером 4 Gb выполнив следующие шаги:

  1. Создаем папку для хранения временных файлов, например: /var/lib/mysql/tmp
    mkdir /var/lib/mysql/tmp
  2. Изменяем владельца папки и группу на mysql
    chown mysql:mysql /var/lib/mysql/tmp
    
  3. Выясняем идентификатор пользователя (uid) и группы (gid) mysql
    id mysql
    
  4. В файл fstab добавляем запись
    tmpfs /var/lib/mysql/tmp tmpfs rw,gid=27,uid=27,size=4G,nr_inodes=10k,mode=0700 0 0
    
  5. Монтируем новый tmpfs раздел
    mount /var/lib/mysql/tmp
    
  6. Редактируем файл конфигурации MySQL /etc/mysql/my.cnf
    tmpdir=/var/lib/mysql/tmp
    
  7. Перезапускаем MySQL
    /etc/init.d/mysql restart
    

Теги

Рад, что оказалось полезно. Если честно я сам немного не ожидал. Хотел по-другому делать.

6. Редактируем файл конфигурации MySQL /etc/mysql/my.cnf
tmpdir=/var/lib/mysqltmp опечатка? tmpdir=/var/lib/mysql/tmp

Спасибо за удачный хинт, но есть сомнения...
Попробовал эту идею на CentOS 7.1 с Percona MySQL 5.5, использовал /mysqltmp вместо /var/lib/mysql/tmp как в Вашем примере
Запустил РНР скрипт обработки данных с массовым инсертом в MySQL
Одновременно смотрю по команде
df -h
tmpfs 4.0G 0 4.0G 0% /mysqltmp
То есть не попадает ничего в раздел...а по всей логике - должно ?
Буду признателен за комментарий и мнение.

Возможно Вы правы и для insert это не влияет...просто для меня оптимизация именно массового insert была приоритетом.
Но нет у меня массового select :) чтобы проверить.
Однако несколько Joomla сайтов будут на это завязаны - а там по практике будет частый select.
Обязательно отпишу как увижу на продакшене.
Спасибо за коммент !

Спасибо.
Перенос mysql директории tmpdir в оперативную память позволяет решить проблему с kjournald, когда журналирование файловой системы вешает эту же саму файловую систему.
Как выяснилось, высокую нагрузку на kjournald порождает как раз mysql большим количеством чтений и записи в директорию tmpdir

Здравствуйте,
чуть выше Serge уже поднимал этот вопрос, но все-таки - как убедиться в том, что все работает как положено?
df -h показывает, что место в смонтированном разделе не используется (занято 0), в свою очередь,
mysqltuner показывает, что временные таблицы создаются.

Здравствуйте! Временные таблицы создаются в течение выполнения запроса, для того чтобы убедится в создании временных таблиц Вам необходимо запустить очень долгий и очень большой (при котором точно создастся временная таблицы) запрос и в течение его выполнения посмотреть занятое место.

Добавить комментарий

Filtered HTML

  • Допустимые HTML-теги: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd>
  • Строки и абзацы переносятся автоматически.
  • Web page addresses and email addresses turn into links automatically.