Оптимизация Mysql (Лог slow query) на сервере

Загрузка...

Как известно неправильное составление sql запросов, а так же не корректная настройка mysql сервера может привести к большой нагрузке на процессор, а так же жёсткий диск. Узнать какой из процессов на данный момент нагружает linux не составляет большого труда, достаточно воспользоваться командой top или atop, и узнать грузит ли ваш сервер процесс mysqld.

На картинке видно, что mysqld кушает 33% процессора, что собственно в моём случае очень хорошо, так как за день до этого он грузил процессор до 150%.

Честно говоря я даже не подозревал, что один из моих сайтов, посещаемость которого колеблется от 10000 до 15000 посещений мог создавать не сказать, что большую, но и не нормальную нагрузку на сервер, а дело было в sql запросе, который выполнялся 4-5 секунд.

Чтобы узнать какой из sql запросов грузит сервер, я зашёл через ssh доступ с помощью программы Putty, и залез в конфигурационный файл mysql сервера my.cnf, включив лог медленных запросов. В моём случае файл my.cnf расположен в папке /etc/my.cnf

Опцию log-slow-queries нужно расскомментировать, убрав «#», и указать путь к файлу, в который будут записаны sql запросы, превышающие 3 секунды.

Если запрос выполняется более чем указано в опции log_query_time, произойдёт логирование запроса, и вы всегда можете узнать, и оптимизировать данный sql запрос.

Теперь нам необходимо создать лог файл, а так же выставить права на запись этому файлу.

Для соединения по протоколу SSH воспользуйтесь программой PUTTY.

Переходим в папку с логами.
#cd /var/log

Создаём файл slow.log
#nano slow.log

ctrl+O — сохраняем файл
ctrl+x — выходим из редактора

Выставляем права на запись
#chmod 777 slow.log

Перезагружаем mysql
#service mysqld restart

Ну вот в принципе и всё.С этого момента, если sql запрос будет выполняться более 3 секунд, в файл slow.log, будет записан этот медленный запрос.

Настройка конфигурационного файла

Теперь поговорим об оптимизации настроек. Для начала нам всё-таки необходимо узнать, что с состоянием mysql сервера. Для этого я использую phpmyadmin. Где он расположен и как его открыть зависит от настроек сервера. Предположим вы его запустили. Нас интересует правая сторона страницы, а именно(Рисунок).

Теперь идём по ссылке состояние, и мониторим опции с красным шрифтом. Я сразу увидел вот такую картину.(Рисунок), что говорит о 3 mysql запросах, выполнявшиеся более 3 секунд.

А так же красным загорелась опция Created_tmp_disk_tables, что говорит о создание временных таблиц на диске.

Значит необходимо увеличить параметр tmp_table_size, в зависимости от установленной оперативной памяти на сервере. В моём случае было установлено 512MB, которой по всей видимости тоже не хватало.

Ну что увеличим параметр до 576MB. Для этого заходим в конфигурационный файл my.cnf

#nano /etc/my.cnf
Изменяем параметр, жмём ctrl+O,затем Enter, и затем выходим из редактора нажав ctrl+x.

Перезапускаем mysql сервер.
#service mysqld restart

Как вы уже поняли благодаря оценки состояния mysql сервера средствами phpmyadmin, можно без проблем его оптимизировать. Блага все подсказки у вас перед носом. Не забывайте создавать индексы для полей в таблицах, по которым производится соединение нескольких таблиц, проверяйте свои sql запросы в том же phpmyadmin на скорость выполнения, и ваши сайты будут летать.