Тонкая настройка MySQL

Joomla, как и большинство PHP-приложений, предполагает ощутимую нагрузку на MySQL, который используется для хранения данных. В поисках повышения производительности, многие пользователи начинают с выполняемых запросов, которые ускоряют путем индексирования. Нередко, многие на этом удовлетворенно останавливаются. Но если у вас есть выделенный сервер (или виртуальный), то вам предоставился счастливый случай для настройки MySQL относительно ВАШЕЙ нагрузки.

 

Перед тем, как начать..

           Первое, что надо сделать — резервную копию всего сервера. Особенно — баз данных MySQL и my.cnf. Изначально вам следует выяснить, сколько оперативной памяти вы можете использовать. Но сейчас меня абсолютно не волнует количество оперативной памяти вашего сервера. Я имею ввиду количество свободной памяти. Очевидно вы думаете, что чем больше оперативной памяти, тем больше ее останется свободной (но это не всегда так. У меня есть сервер с 2 Гб оперативной памяти, но свободной всего около 2 мегабайт…). Самый простой путь увидеть свободную память, запустить простую команду «free -m». Появится большое количество полезной информации. Но нас интересует только вторая строка (начинающаяся с «Mem:») и четвертая колонка («free»). Как раз здесь мы видим то количество мегабайт оперативной памяти, которое свободно.

           Теперь посмотрите сколько мегабайт оперативной памяти у вас свободно. Запомните его (очень пригодится немного попозже). Теперь запустите команду “top -d 1 -n 2 -u mysql” (вместо mysql укажите пользователя, под которым у вас запущен сервер MySQL).

            Нас интересует значение колонки “VIRT”, в которой указывается количество оперативной памяти, используемой MySQL в данный момент… Добавьте ее к свободной памяти, о которой говорилось ранее, и будем с ним работать…

            Проверяйте эти значения время от времени. В последствии вам может понадобиться поместить что-нибудь в файл подкачки.

Проверяем свой файл my.cnf

            ВАЖНО! СДЕЛАЙТЕ НЕМЕДЛЕННО РЕЗЕРВНУЮ КОПИЮ файла my.cnf перед тем, как что-либо изменить.  Вы можете найти свой my.cnf (на Linux) в директории /etc. Перед тем, как мы начнем что-либо изменять, нам следует знать с чего начать. Ниже приведен конфигурационный файл с настройками по умолчанию. Ваш может немного отличаться, но он все равно будет отправной точкой.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/lib/mysql/mysql.sock

            Заметьте, что ниже никаких переменных нет (если у вас они есть, ничего страшного. Вы можете просто удалить их, но не удаляйте ничего, что связано с соединениями, тайм-аутами или open_files_limit). И так, не забыли количество памяти? Теперь воспользуемся этим числом ниже. Добавьте соответствующие параметры в файл my.cnf, раздел [mysqld] (замените проценты на эквивалентное значение оперативной памяти, о которой говорилось выше).

Query_cache_limit=2M
Query_cache_size=25%
Query_cache_type=1
Thread_cache_size=128
Key_buffer = 25%
Join_buffer = 4M
Table_cache=25%
Sort_buffer=4M
Read_rnd_buffer_size=1%
Tmp_table_size=10% (или 32M, возможно даже лучше)

            Обратите внимание, что мы использовали только 86% свободной памяти плюс около 10 мегабайт. Это важно, потому что мы не хотим использовать много памяти. Теперь перезагрузите MySQL и пускай он поработает несколько дней в своем обычном режиме (или если это тестовый сервер, симулируйте БОЛЬШУЮ нагрузку сетевого трафика). Также соберите повторно информацию об использовании оперативной памяти.

 

Забавная часть.

            И так, мы имеем данные, приближенные к реальной жизни, относительно которых и будем настраивать. Войдите в phpMyAdmin. На панели справа на главной странице находится очень ценная ссылка (но многие пользователи даже никогда не заглядывают туда) «Show MySQL runtime information» (либо «Status», либо «Состояние» прим. переводчика). Страница отображает отсортированную информацию о состоянии сервера MySQL. Далее я опишу действия, которые вам следует сделать, но сначала я кратко опишу увиденное вами (возможно, что-то будет немного отличаться в зависимости от версии вашего phpMyAdmin).

            Первый раздел — сетевой трафик сервера. Здесь подробно описывается количество запросов, отправленных на сервер (и количество соединений). Следующий раздел — статистика запросов. Это обычный список количества запросов по типам. Далее идет уже более интересующее нас. Три колонки («Переменная», «Значение» и «Описание»). Обратите внимание, что все неправильные и неподходящие значения имеют красный цвет. Это очень важно.

 

Как определить, что требуется сделать.

            До этого мы с вами работали с настройками сервера и игнорировали следующие переменные (относящиеся к эффективности запросов): slow_queries, все переменные с префиксом handler_ и все переменные начинающиеся на select_. Начнем с Key Cache («Кеш индекса»). Этот раздел находится практически в самом низу. Показатель параметра Key_reads должен быть НА МНОГО меньше (почти в 1000 раз) чем Key_read_requests. Если это не так, значит вам следует увеличить key_buffer_size. Начните со значения, равное в процентном соотношении как 25% и посмотрите как это все работает. Или если ваш key_buffer_size очень большой, относительно key_blocks_unused и key_blocks_used. Вам потребуется дополнительное дисковое пространство (еще 10%). Запомните, не делайте это значение очень большим, иначе ваш сервер рискует начать использовать файл подкачки. Перезагружать MySQL еще рано, следует посмотреть еще несколько областей.

            Следующая большая область — Tables («Таблицы»).  Сравните переменные Open_tables и Opened_tables. Если значения их практически одинаковые, все нормально. Если opened_tables на много больше, чем open_table (раз в 10), тогда вам следует подкорректировать значение переменной table_cache. Не забываем, что мы увеличиваем значения, поэтому увеличьте до 25%. После этого посмотрите на Query Cache («Кеш запросов»). Обратите внимание на Qcache_free_memory. Там должно быть большое значение (несколько мегабайт). Далее, значение Qcache_lowmem_prunes должно быть максимально приближено к 0 или вообще быть равно 0. Для этого увеличивайте query_cache_size. Следующим шагом будет проверка значения created_tmp_files. Если оно не маленькое, то увеличивайте tmp_table_size.

 

Повторная проверка

            Теперь перезагрузите сервер. Пускай он поработает несколько дней (регулярно проверяйте загрузку памяти с помощью команды free -m) и потом пройдитесь по настройкам еще раз. Когда все описываемое здесь будет достигнуто, вы добились своего! И вам потребуется только время от времени смотреть на изменения в нагрузке сервера и базах данных.

 

Источник: http://www.joomlaperformance.com/articles/server_related/tweaking_mysql_server_23_16.html

Другие публикации по теме:

Сменить префикс таблиц в базе данных mysql... Когда требуется сменить префикс таблиц в mysql, то на помощь придет следующий скрипт, который я нарыл на каком-то форуме:

Поделиться информацией с друзьями!

Чтобы не пропустить обновления, подпишись на RSS или почтовую рассылку (свой выбор сделали уже 128 человек!)

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