От автора: один знакомый преподаватель ВУЗа рассказал, как один из его студентов, отвечая на поставленный вопрос, вывел новую аксиому. Правильная оптимизация – это сесть на теплую батарею, неправильная – на раскаленную печку. Данный постулат применим во всех сферах человеческой деятельности. Ему должна соответствовать даже проводимая оптимизация MySQL.
Зачем все упрощать?
От «тонкой» настройки СУБД часто зависит влияние многих факторов, «утяжеляющих» всю систему MySQL и негативно влияющие на ее производительность. Кроме этого плохо «подогнанная» система является причиной потребления большего количества серверных мощностей. Что может привести к замедлению скорости работы всего ресурса. Особенно, если он развернут на CMS, работающей на основе PHP и MySQL. Несколько «симптомов» того, что пора оптимизировать MySQL:
Если «вылетают» 504 и 502 ошибки («gateway timeout» и «bad gateway»).
Хостер сообщает о превышении сайтом лимитов нагрузки на сервер.
Медленная загрузка страниц сайта даже при быстром сетевом подключении.
Увеличение времени выполнения запросов к БД.
Чаще всего эти признаки начинают проявляться у ресурсов, обладающих высокой посещаемостью. А также у крупных порталов с большим количеством страниц и разделов.
У новых, еще «неокрепших» сайтов подобных проблем поначалу не возникает. Но со временем, когда ресурс «раскручивается», начинает что-то работать не так. Причем проявление всех описанных выше огрех негативно влияют на авторитетность ресурса и его посещаемость. В общем, оптимизацию MySQL лучше проводить на ранних стадиях развития «болезни».
Средства для постановки диагноза
Если ресурс развернут на основе одной из популярных CMS, тогда инструменты для диагностики производительности СУБД вам будет найти намного легче. Для распространенных движков написано множество расширений, позволяющих правильно поставить диагноз «заболевшему» экземпляру MySQL.
Чаще всего симптомами «расстройства» СУБД проявляются и вызваны плохой оптимизацией исполняемого кода SQL. В качестве примера мы рассмотрим процесс установки и работы с плагином Debug Bar, предназначенного для WordPress.
Установка плагина
Чтобы установить плагин Debug Bar, который используется при оптимизации MySQL, заходим в административную панель CMS. Затем в основном меню справа переходим в раздел «Плагины», «Добавить новый». И жмем на «Загрузить плагин», если вы уже скачали инсталляционный пакет расширения к себе на сервер.
Иначе справа находим поле «Поиск плагинов» и вводим туда название расширения. Затем в появившемся перечне выбираем нужный нам (Debug Bar), и жмем на ссылку «Установить».
После успешной установки (с помощью любого из вариантов) расширение нужно активировать. Это можно сделать здесь или со страницы плагинов.
Но это еще не все. Для активации отладочного режима в WordPress придется прописать несколько строк в конфигурационном файле движка (wp-config.php). У меня в установленной на локальном сервере CMS этот файл находится здесь: Z:\home\localhost\www\wordpress
То есть в корневой папке движка. Открываем файл любым удобным для вас редактором, поддерживающим синтаксис PHP. Например, я использовал Dreamweaver. Далее находим строку:
|
1 |
define('WP_DEBUG', false); |
В ней меняем второй аргумент на true. И рядом вставляем еще одну строку:
|
1 |
define( 'SAVEQUERIES', true ); |
Плагин в действии
После настройки расширения, позволяющего оптимизировать MySQL, посмотрим, какие полезные данные отображает плагин. Для этого снова перейдите в админку WordPress и в правом углу на верхней панели нажмите «Debug». После этого появится меню со списком всех запросов, направленных к MySQL. А также время выполнения каждого из них и общее время. Эти данные доступны в разделе «Queries».
Стоит начинать волноваться о «здоровье» вашего экземпляра СУБД, если общее время, затраченное на выполнение всех запросов, превышает 1 секунду.
Тяжелыми считаются те запросы, время которых превышает сотни мс. Если они присутствуют, то нужно заняться их оптимизацией с помощью индексом и использования в запросах команды EXPLAIN.
В ситуации, когда тяжелые «составляющие» отсутствуют, но количество исполняемых запросов огромно, оптимизировать MySQL нужно путем изучения файлов логов. Но имеется более быстрый способ «лечения».
По умолчанию в СУБД кеширование запросов отключено. Для его активации придется редактировать конфигурационный файл сервера БД. У меня my.cnf находится здесь:
Теперь откройте файл с помощью любого редактора (можно через Блокнот), добавьте в него переменную query_cache_size и установите для нее оптимальное значение. Чаще всего хватает 64 Мб.
Перезапустите сервер СУБД. Теперь давайте убедимся в эффективности проделанной оптимизации MySQL. Снова перейдем в административную панель WordPress, и с помощью Debug Bar посмотрим, как изменилось общее время выполнения всех запросов.
Мы видим, что количество запросов увеличилось. Это связано с перезапуском сервера СУБД, во время которого все части сайта «извлекаются» из базы повторно. Но при этом общее время на выполнения всех запросов значительно уменьшилось.
Благодаря включению режима кэширования все выполняемые запросы записываются в выделенную оперативную память. И при их повторном вызове их результат повторно не обрабатывается, а извлекается из кэша. Таким образом повышается скорость загрузки всех страниц сайта и затрачивается меньше серверных мощностей.
Получается, что оптимизировать MySQL у нас получилось. Это значит (согласно теории оптимизации) мы сели с вами попой не на раскаленную печь, а на теплую батарею :).
