Страницы сайта генерируются медленно? Возникают ошибки 502 bad gateway и 504 gateway timeout? Хостер говорит, что сайт создает слишком большую нагрузку на процессор? Скорее всего, проблемы связаны с базой данных. В этой статье рассмотрим вопросы оптимизации производительности MySQL.
Если сайт работает на популярной CMS, то можно воспользоваться отчетом по SQL-запросам, выполняемым при генерации страницы. Например, в Drupal такой отчет доступен в модуле Devel, в Joomla – в режиме отладки, в Wordpress – в расширении Debug bar. Если специальных инструментов нет, то можно до и после выполнения каждого SQL-запроса вызвать PHP-функцию microtime() и посчитать разность.
Если сайт размещается на VPS или выделенном сервере, аналогичные данные можно получить и непосредственно из MySQL. Например, из журнала медленных запросов.
Заняться оптимизацией однозначно стоит в случаях, когда при генерации страницы суммарное время выполнения запросов к базе данных превышает 1 секунду.
Итак, вы определили, какие запросы выполняются при генерации страницы. Дальше возможны варианты:
В первом случае можно попробовать оптимизировать отдельные запросы. Здесь поможет 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
Всё, кеш включен. Можно попробовать открыть страницу сайта и потом обновить. Во второй раз должна загрузиться быстрее.
Есть еще несколько переменных для настройки кеша:
Пример 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_hits / (Com_select + Qcache_hits). Степень использования кеша - Qcache_hits / Qcache_inserts.
О нюансах работы кеша MySQL можно почитать на mysqlperformanceblog.com (англ.)
Чтобы оптимизировать тяжелый запрос, сначала его нужно исследовать. Для этого допишите перед SELECT слово EXPLAIN, и MySQL покажет план выполнения запроса. В первую очередь интерес представляет информация об использовании индексов.
Результат работы оператора EXPLAIN
Индексы – это структуры данных, создаваемые с целью повышения производительности поиска записей в таблицах. Таблицы в базе данных могут иметь большое количество строк, которые хранятся в произвольном порядке, и их поиск по заданному критерию путем последовательного просмотра таблицы строка за строкой может занимать много времени. Индекс формируется из значений одного или нескольких столбцов таблицы и указателей на соответствующие строки таблицы и, таким образом, позволяет искать строки, удовлетворяющие критерию поиска. [источник]
Индексы - ключ к высокой производительности MySQL, их важность увеличивается по мере роста объема данных в базе. Индексы нужно создавать для столбцов, по которым
Индексы могут быть составными, в этом случае важен порядок столбцов.
Разбирая вывод EXPLAIN, обратите особое внимание на столбцы
Описание всех значений и пример оптимизации запроса можно посмотреть в документации.
Добавить индексы можно из 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 может задаваться в микросекундах и может быть равен нулю.
Пара полезных дополнительных настроек:
Пример для записи в журнал всех запросов, выполняющихся дольше 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:
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/, всегда использую пару плагинов для оптимизации базы данных. Очень много лишних опций каждый раз при чистке находит, может эти ненужные осиротелые опции и грузят хостинг?
Отправить комментарий