Производительность MySQL. Часть 1. Анализ и оптимизация запросов

Страницы сайта генерируются медленно? Возникают ошибки 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

Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.log
Count: 1 Time=4.32s (4s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1

Count: 3 Time=2.53s (7s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1 limit N

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

Смотрите также

Комментарии

Длинный текст

Работа с запросами достаточно сложный процесс. Мне помогла сильно увеличить производительность MySQL оптимизация my.cnf.

В свое время после нескольких дней оптимизации по каждому параметру для MyISAM на средненагруженном сервере в итоге вышел такой конфиг (по возможности убрано всё лишнее и оставлены лишь необходимые мне настройки):

[mysqld]
default-storage-engine=MyISAM
innodb=OFF
skip-symbolic-links
skip-networking
skip-name-resolve
skip-federated
bind-address = 127.0.0.1

max_connect_errors = 1K
max_join_size=200M
max_allowed_packet=16M

key_buffer_size=128M
query_cache_size = 256M
max_heap_table_size=256M
tmp_table_size=256M

max_connections = 160
thread_cache_size = 160

read_buffer_size = 128K
read_rnd_buffer_size = 256K
join_buffer_size = 256K
sort_buffer_size = 128K

myisam_sort_buffer_size = 512M
table_open_cache = 1K

interactive_timeout = 60
wait_timeout = 60
connect_timeout =20

character-set-server=utf8
collation-server=utf8_general_ci

log_slow_queries = 1
slow_query_log_file = /var/log/mysql-bin.log
long_query_time = 4
#log-queries-not-using-indexes

Подробности:
1) Ставим MyISAM по умолчанию и отключаем ненужные нам вещи:
default-storage-engine=MyISAM
innodb=OFF
skip-symbolic-links
skip-networking
skip-name-resolve
skip-federated

2) Привязываем базу для использования только на нашем сервере:
bind-address = 127.0.0.1

3) Выставляем необходимые максимальные ограничения:
max_connect_errors = 1K
max_join_size=200M
max_allowed_packet=16M

4) Устанавливаем общий буфер, кэш для запросов и max размер для временных таблиц:
key_buffer_size=128M
query_cache_size = 256M
max_heap_table_size=256M
tmp_table_size=256M

5) Максимальное число соединений к базе и ожидающих их процессов:
max_connections = 160
thread_cache_size = 160

6) Буферы на каждый процесс (большие значения типа 1Mb и выше для нас это явно перебор).
read_buffer_size = 128K
read_rnd_buffer_size = 256K
join_buffer_size = 256K
sort_buffer_size = 128K

7) Служебный сортировочный буфер (память постоянно не занимает) и кэш открытых таблиц.
myisam_sort_buffer_size = 512M
table_open_cache = 1K

8) Необходимые таймауты, чтобы отсекать долгие ожидания.
interactive_timeout = 60
wait_timeout = 60
connect_timeout =20

9) Установка кодировки UTF8:
character-set-server=utf8
collation-server=utf8_general_ci

10) По желанию можно добавить логи медленных запросов или неиспользуемых индексов (создайте доступным на запись файл для лога).
log_slow_queries = 1
slow_query_log_file = /var/log/mysql-bin.log
long_query_time = 4
log-queries-not-using-indexes

Быстрой Вам базы данных!

Я так понимаю речь идет об оптимизации базы данных. А ведь можно же очищать и оптимизировать базу данных с помощью разных плагинов. У меня вот сайт на WordPress http://pc-reanimator.ru/, всегда использую пару плагинов для оптимизации базы данных. Очень много лишних опций каждый раз при чистке находит, может эти ненужные осиротелые опции и грузят хостинг?

Отправить комментарий

Если вы укажете номера тикетов или имя пользователя, отзыв будет выглядеть убедительнее, а провайдеру будет проще разобраться с вашей проблемой

Подробнее о форматировании

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
5 + 7 10 + 8 плюс 3 6