Страницы сайта генерируются медленно? Возникают ошибки 502 bad gateway и 504 gateway timeout? Хостер говорит, что сайт создает слишком большую нагрузку на процессор? Скорее всего, проблемы связаны с базой данных. В этой статье рассмотрим вопросы оптимизации производительности MySQL.

Как понять, что дело именно в MySQL

Если сайт работает на популярной CMS, то можно воспользоваться отчетом по SQL-запросам, выполняемым при генерации страницы. Например, в Drupal такой отчет доступен в модуле Devel, в Joomla – в режиме отладки, в Wordpress – в расширении Debug bar. Если специальных инструментов нет, то можно до и после выполнения каждого SQL-запроса вызвать PHP-функцию microtime() и посчитать разность.

Drupal Devel

Если сайт размещается на VPS или выделенном сервере, аналогичные данные можно получить и непосредственно из MySQL. Например, из журнала медленных запросов.

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

С чего начать оптимизацию

Итак, вы определили, какие запросы выполняются при генерации страницы. Дальше возможны варианты:

1. Есть тяжелые запросы, занимающие сотни миллисекунд.
2. Запросов много, но все они выполняются достаточно быстро.

В первом случае можно попробовать оптимизировать отдельные запросы. Здесь поможет SQL-оператор EXPLAIN и знания об индексах. Это решение применимо ко всем сайтам, в том числе размещенным на виртуальном хостинге.

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

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

Кеш запросов

В кеше запросов (query cache) сохраняются пары запрос-ответ. Когда запрос уже есть в кеше, ответ отдается практически мгновенно. Если данные в таблицах меняются не слишком часто, происходит ощутимый прирост производительности (в противном случае кеш быстро сбрасывается).

По умолчанию кеширование выключено. Включить его можно, добавив в конфигурационный файл my.cnf строчку вида query_cache_size = 64M . Через переменную query_cache_size задается размер оперативной памяти, выделяемой под кеш, в данном случае - 64 мегабайта.

Теперь нужно перезапустить MySQL. Сделать это можно из некоторых панелей управления (в ISPmanager: Management tools - Services), либо по SSH из командной строки примерно так:
/usr/local/etc/rc.d/mysql-server stop
/usr/local/etc/rc.d/mysql-server start

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

Есть еще несколько переменных для настройки кеша:

• query_cache_type задает режим работы кеша, когда query_cache_size установлен больше нуля. Допустимые значения query_cache_type: 0 или OFF - кеширование выключено; 1 или ON - кеширование включено для всех выражений, кроме начинающихся с SELECT SQL_NO_CACHE; 2 или DEMAND - кеширование включено только для запросов, начинающихся с SELECT SQL_CACHE.
• query_cache_limit – максимально допустимый размер, при котором результат выполнения запроса будет сохранен в кеше.
• query_cache_min_res_unit – минимальный размер блоков памяти, выделяемых под кеш. По умолчанию 4 Кб. Если у вас много результатов значительно меньшего объема, query_cache_min_res_unit можно понизить, чтобы память использовалась эффективнее. Подходящее значение можно рассчитать по формуле (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache.

Пример my.cnf для небольшого VPS:
query_cache_size = 64M
query_cache_limit = 2M
query_cache_type = 1
query_cache_min_res_unit = 2K

Посмотреть текущее состояние кеша можно в phpMyAdmin на вкладке Status, либо из командной строки:

# mysql -u root -p
Password: ********
mysql> SHOW GLOBAL STATUS LIKE ‘Qcache%’;
+----------------------------+------------+
| Variable_name              | Value      |
+----------------------------+------------+
| Qcache_free_blocks         | 130        |
| Qcache_free_memory         | 56705448   |
| Qcache_hits                | 57092      |
| Qcache_inserts             | 10412      |
| Qcache_lowmem_prunes       | 0          |
| Qcache_not_cached          | 5036       |
| Qcache_queries_in_cache    | 1023       |
| Qcache_total_blocks        | 2409       |
+----------------------------+------------+
8 rows in set (0.01 sec)

• Qcache_free_blocks – количество свободных блоков в кеше.
• Qcache_free_memory – объем свободной ОЗУ, отведенной под кеш.
• Qcache_hits – количество запросов, результаты которых были взяты из кеша.
• Qcache_inserts – количество запросов, которые были добавлены в кеш.
• Qcache_lowmem_prunes – количество запросов, которые были удалены из кеша из-за нехватки памяти.
• Qcache_not_cached – количество запросов, которые не были записаны в кеш (с SQL_NO_CACHE или некешируемые по другим причинам).
• Qcache_queries_in_cache – количество запросов, которые находятся в кеше.
• Qcache_total_blocks – общее количество блоков.

Долю закешированных запросов от их общего числа можно посчитать по формуле Qcache_hits / (Com_select + Qcache_hits). Степень использования кеша - Qcache_hits / Qcache_inserts.

О нюансах работы кеша MySQL можно почитать на mysqlperformanceblog.com (англ.)

Оптимизация отдельных запросов

Чтобы оптимизировать тяжелый запрос, сначала его нужно исследовать. Для этого допишите перед SELECT слово EXPLAIN, и MySQL покажет план выполнения запроса. В первую очередь интерес представляет информация об использовании индексов.

Результат работы оператора EXPLAIN

Индексы – это структуры данных, создаваемые с целью повышения производительности поиска записей в таблицах. Таблицы в базе данных могут иметь большое количество строк, которые хранятся в произвольном порядке, и их поиск по заданному критерию путем последовательного просмотра таблицы строка за строкой может занимать много времени. Индекс формируется из значений одного или нескольких столбцов таблицы и указателей на соответствующие строки таблицы и, таким образом, позволяет искать строки, удовлетворяющие критерию поиска. [источник]

Индексы - ключ к высокой производительности MySQL, их важность увеличивается по мере роста объема данных в базе. Индексы нужно создавать для столбцов, по которым

• производится поиск в части WHERE
• соединяются таблицы при JOIN
• сортируются и группируются записи при ORDER BY и GROUP BY
• производится поиск MIN() и MAX()

Индексы могут быть составными, в этом случае важен порядок столбцов.

Разбирая вывод EXPLAIN, обратите особое внимание на столбцы

• type (значение ALL - плохо)
• key (NULL - плохо)
• ref (NULL - плохо)
• extra (Using filesort, Using temporary, Using where - плохо)

Описание всех значений и пример оптимизации запроса можно посмотреть в документации.

Добавить индексы можно из phpMyAdmin или с помощью запросов вида ALTER TABLE table_name ADD INDEX index_name (column_name)

Журнал медленных запросов

Если определить тяжелые запросы «на глаз» не получается, нужно собрать более обширную статистику. В этом поможет журнал медленных запросов (slow query log).

Для включения журнала в MySQL, начиная с версии 5.1.29, задайте переменной slow_query_log значение 1 или ON; для отключения журнала - 0 или OFF. В более старых версиях используется log-slow-queries = /var/db/mysql/slow_queries.log (путь можно задать другой).

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

Пара полезных дополнительных настроек:

• log-queries-not-using-indexes – запись в журнал запросов, не использующих индексы.
• slow_query_log_file – имя файла журнала. По умолчанию host_name-slow.log

Пример для записи в журнал всех запросов, выполняющихся дольше 50 миллисекунд:
slow_query_log = 1
slow_query_log_file = /var/db/mysql/slow_queries.log
long_query_time = 0.05
log-queries-not-using-indexes = 1

Пример для старых версий MySQL, все запросы дольше 1 секунды:
log-slow-queries = /var/db/mysql/slow_queries.log
long_query_time = 1

Для анализа журнала используются утилиты mysqldumpslow, mysqlsla и mysql_slow_log_filter. Они парсят журнал и выводят агрегированную информацию о медленных запросах.

mysqldumpslow – утилита из состава MySQL. Вызывается таким образом: mysqldumpslow [параметры] [файл_журнала ...] . Пример:

mysqldumpslow

Count: 3 Time=2.13s (6s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t1 select * from t1

Count – сколько раз был выполнен запрос данного типа. Time – среднее время выполнения запроса, дальше в скобках – суммарное время выполнения всех запросов данного типа.

Некоторые параметры mysqldumpslow:

• -t N – отображать только первые N запросов.
• -g pattern - анализировать только запросы, которые соответствуют шаблону (как grep).
• -s sort_type - как сортировать вывод. Значения sort_type: t или at - сортировать по суммарному или среднему времени выполнения запросов, c - по количеству выполненных запросов данного типа.

mysqlsla – еще одна утилита для анализа логов MySQL с аналогичной функциональностью. Пример использования:

mysqlsla -lt slow /tmp/slow_queries.log

Подробности в документации.

mysql_slow_log_filter - perl-скрипт с похожей функциональностью. Пример использования:

tail –f mysql-slow.log | mysql_slow_log_filter –T 0.5 –R 1000

Эта команда в реальном времени покажет запросы, выполняющиеся дольше 0,5 секунды или сканирующие больше 1000 строк.

Выявленные медленные запросы дальше можно оптимизировать, используя EXPLAIN и индексы.

Вторая часть статьи будет посвящена тонкой настройке MySQL. Материал находится в разработке.

---
Евгений Демин, http://unixzen.ru
Дмитрий Сергеев, http://hosting101.ru