Ускоряем сайт. Логирование медленных запросов MySQL
Зиму перезимовали, осталось перезимовать весну... Не смотря на совсем не весеннюю погоду, в плане работы март выдался очень жарким. В данное время занимаюсь оптимизацией работы сразу двух сайтов.
Один из них, использует коммерческий движок Bitrix, где работа сводится к полной переделке шаблона сайта (разработчикам стоило бы для начала ознакомиться с документацией по Bitrix, прежде чем клепать сайт), а также чистке web-страниц от мусора, которым их щедро наделил WISIWIG редактор, этого самого битрикса.
Движок второго сайта самописный, и создавался явно какими-то двоечниками. При работе сайта складывалось впечатление, что ему либо приходится ворочать мегатонны информации, любо у хостера вместо серверов стоят калькуляторы.
Так что продолжим тему оптимизации, начатую в статье Как посчитать время выполнения скрипта и количество запросов к MySQL.
Включаем лог медленных запросов
Если есть подозрение, что приложение работает медленно из-за неэффективных запросов к базе (в моем случае сомневаться и не приходилось), то в первую очередь следует проверить лог медленных запросов. Именно этот лог помог выяснить, что необходимо оптимизировать в первую очередь.
И здесь нас поджидает первая засада - виртуальный хостинг не позволяет менять настройки MySQL сервера. Для отладки скриптов придется установить собственный web-сервер на виртуальную машину, либо воспользоваться готовыми пакетами - LAMP, VertrigoServ, MAMP, Денвер или другим.
Если есть уверенность в собственных силах и у вас имеется выделенный сервер - можете включить отладку на нем, однако оптимизировать скрипты лучше все-таки на тестовых серверах.
В файле настройки MySQL сервера (my.cnf) нам понадобится задействовать опцию slow_query_log (старый вариант log_slow_queries). my.cnf может находиться в разным местах, в зависимости от вашей операционной системы:
- Debian (Ubuntu): /etc/mysql/my.cnf
- CentOS (RedHat): /etc/my.cnf
- FreeBSD: /var/db/mysql/my.cnf (по-умолчанию его нет. можно взять в каталоге /usr/local/share/mysql/)
;включаем лог медленных запросов (путь можно не указывать)
slow_query_log = /var/log/mysql/mysql_slow.log
;время выполнения запроса, после которого запрос считается медленным
;(от 1-10 сек, по умолчанию 10 секунд)
long_query_time = 5
;логирование запросов, которые не используют индексы
log-queries-not-using-indexes
Не забудьте перезапустить сервер БД для применения настроек.
Осталось вдумчиво проанализировать полученный лог, что за запросы мы получили и попытаться это дело ускорить. Таким образом я выяснил основную причину тормознутости сайта - огромное количесто UPDATE запросов к базе данных, которы в сущности оказались не нужны. Отключив соотвествующий кусок кода в скрипте, удалось снизить нагрузку на MySQL почти в 1000 раз (вот такой кривой код содержался в движке).
Оптимизация настроек MySQL сервера
В заключении хочу немного рассказать о оптимизации на стороне сервера. Прежде всего проверьте включено ли кэширование запросов (при выполнении запроса SELECT система MySQL "запоминает" сам запрос и результат). При повторном отправке аналогичного запроса на сервер, система вернет ответ из кэша, вместо того что бы повторно выполнять данный запрос.
Проверяем включено ли кеширование:
mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+------------+
| Variable_name | Value |
+------------------------------+------------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 2147483648 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+------------+
6 rows in set (0.00 sec)
Значение переменной have_query_cache сообщает о включении кэширования.
Переменная query_cache_type описывает какой тип кэширования используется: OFF(0) - кэширвоание отключено, ON(1) - кэширование включено для всех запросов, за исключением использования операторов SELECT с опцией SQL_NO_CACHE, DEMAND (2) - позволяет активировать кэширование запросов по требованию, когда используются операторы SELECT с опцией SQL_CACHE.
Желательно проверить значение объема памяти выделяемое сервером под буффер индексов key_buffer_size в my.cnf (рекомендуется устанавливать это значение в 15-20% от размера оперативной памяти на сервере).
Всем интересующимся данной темой, могу порекомендовать две книжечки, которые сам с удовольствием изучаю, MUST HAVE для всех web разработчиков:
Комментариев: 1
У мея аналогичная проблема с Битриксом. Кто может помочь