В работе периодически сталкиваюсь с медленными Drupal сайтами, и тормоза очень часто вызваны медленным выполнением запросов к Mysql. Причины бывают разные, но зачастую медленное выполнение запросов вызвано тем, что при выполнении запроса MySQL вынуждено использовать временные таблицы на диске. Для этого используется каталог заданный в переменной tmpdir файла конфигурации my.cnf. На мой взгляд, правильным решением в таких ситуациях является оптимизация базы данных (использование типов полей наиболее подходящих под содержимое, правильная настройка идексов, и построение корректных запросов к базе, учитывающих индексы в базе). Но также мы можем помочь MySQL если переместим эти временные таблицы в оперативную память (такое решение подходит для серверов с большим количеством оперативной памяти), что позволит более быстро выполнять операции связанные с использованием временных таблиц, за счет экономии на операциях записи и чтения с диска, так как всё будет делаться в оперативной памяти. Для переноса папки tmpdir в оперативную память мы подключим к папке /var/lib/mysql/tmp раздел tmpfs размером 4 Gb выполнив следующие шаги:
Создаем папку для хранения временных файлов, например: /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=27,uid=27,size=4G,nr_inodes=10k,mode=0700 0 0
Монтируем новый tmpfs раздел
mount /var/lib/mysql/tmp
Редактируем файл конфигурации MySQL /etc/mysql/my.cnf
tmpdir=/var/lib/mysql/tmp
Перезапускаем MySQL
/etc/init.d/mysql restart
Перенос директории tmpdir MySQL в оперативную память может помочь решить проблему с kjournald, когда журналирование файловой системы вызывает значительную нагрузку на диск. На практике высокую нагрузку на kjournald создаёт именно MySQL большим количеством чтений и записей во временные файлы.
При этом важно понимать, что MySQL сам решает, когда использовать запись на диск, и это зависит от настроек сервера, размера базы данных и объёма доступной оперативной памяти. Например, при массовых INSERT запросах MySQL может не использовать tmpdir в оперативной памяти, так как для этих операций временные файлы на диске не создаются. Напротив, для крупных SELECT или операций, формирующих большие временные таблицы, использование tmpdir в RAM становится актуальным.
Чтобы убедиться в том, что всё работает корректно, стоит запускать действительно большие и долгие запросы, которые гарантированно создадут временные таблицы. В течение их выполнения можно наблюдать использование смонтированного раздела командой df -h. Инструменты вроде mysqltuner также показывают, что временные таблицы создаются, но фактическое использование tmpdir зависит от конкретного запроса.
Оптимизация работы с tmpdir и временными таблицами особенно важна для сайтов с высокой нагрузкой и частыми выборками данных, чтобы минимизировать влияние на файловую систему и повысить общую производительность MySQL.
Добавить комментарий