Ускоряем сайт. Логирование медленных запросов MySQL

Март, 19th 2013Рубрика: Web 29975
Подписаться на комментарии по RSS

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

Один из них, использует коммерческий движок 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 разработчиков:

Изучаем PHP и MySQL, 2-е издание (файл PDF)MySQL. Оптимизация производительности, 2-е издание (файл PDF)

Подписывайтесь на канал Яндекс.Дзен и узнавайте первыми о новых материалах, опубликованных на сайте.

Если считаете статью полезной,
не ленитесь ставить лайки и делиться с друзьями.

Комментариев: 1

  1. 2015-02-21 в 08:38:43 | Вася

    У мея аналогичная проблема с Битриксом. Кто может помочь

ваш комментарий будет опубликован после проверки
на указанный адрес будет отправлено письмо с кодом активации. Вы можете настроить собственный профиль и стать активным участником или автором.