Настройка и оптимизация MySQL сервера

Если вы, как и я, не бум-бум в тонких настройках MySQL сервера, то эта информация может вам быть полезна :-)

Стоит сразу отметить, что из коробки MySQL сервер настроен как-то не очень… Хотя тут уж как повезет. Но я выбрал сложный путь оптимизации сервера, поскольку его работа меня не устраивала.

Прежде всего заходим в папку /usr/share/mysql и выбираем из предложеных файлов максимально подходящий нам и от него пляшем.

Нам предлагают на выбор:

• my-small.cnf — для серверов с малым кол-вом памяти (64 мб), где MySQL используется редко
• my-medium.cnf — для серверов с малым кол-вом памяти (32-64 мб), где MySQL играет главную роль или серверов со 128 мб памяти, где MySQL используется совместно с другими программами (вроди web server)
• my-large.cnf — для систем с большим кол-вом памяти (512 мб), где в основном используется MySQL
• my-huge.cnf — для больших систем (1G-2G памяти), где в основном используется MySQL
• my-innodb-heavy-4G.cnf — 4GB памяти, только InnoDB таблицы, ACID, много соединений, тяжелые запросы

Выбираем один из них и копируем поверх etc/my.cnf

Не забываем перезапускать сервер командой service mysqld restart

Обратите внимание, что если вы используете InnoDB, то в конфиг файлах нужно раскомментировать соответствующие строки. Делайте это с осторожностью, у меня некоторые из них не давали запускаться серверу.

Для дальнейшей настройки нужно дать поработать серверу хотя бы 24 часа, а лучше 48 и более.

После этого используем утилиты для оптимизации MySQL сервера.

Первой будет mysqltuner

Переходим куда-нибудь во временную директорию cd /tmp и запускаем скачивание скрипта

Если что-то не качается, то заходим на http://mysqltuner.pl/ и смотрим, куда переадресовывает нас сайт

После того, как скрипт скачался, запускаем его

Скрипт просит логин и пароль администратора MySQL, нужно их ввести. Далее проводит анализ и выводит результаты.

Так как сервера у всех индивидуальны, то разбирать отчет я не вижу смысла. Читайте и следуйте рекомендациям. Критические позиции выделены красными восклицательными знаками [!!]

Вторая утилита, которую стоит использовать — tuning primer

Качаем ее и запускаем

На все запросы нажимаем Enter и вводим логин и пароль администратора MySQL

Сиотрим рекомендации и выполняем.

Тут я могу сказать, что делать рекомендации нужно поочередно, а не все сразу. После каждого изменения перезапускаем MySQL сервер. Если что-то пошло не так и он не запускается — откатываем изменения. Так вы съэкономите себе кучу времени и нервов :-)

И еще один важный момент. Обе утилиты могут вам написать такое замечание:

Maximum possible memory usage xxx G (>100% of installed RAM) или Max memory limit exceeds 90% of physical memory

Это значит, что теоретически ваш сервер может зависнуть, при активной работе MySQL. Если говорить точнее, то при нехватке памяти процесс mysqld будет убит OOM Killer'ом и сайты будут недоступны, пока вы не заметите проблему.

Так вот, из отчета совершенно не понятно, как этот Max memory limit посчитать. Не ищите его в настройках my.cnf — его там нет. Рассчитывается он по формуле, в которой сам черт ногу сломит, поэтому проще воспользоваться калькулятором mysqlcalculator.com, который все сделает за вас. В него включены и настройки MySQL по умолчанию, которых в вашем my.cnf может и не быть. Чтобы узнать настройки mysql по умолчанию, нужно зайти в mysql в консоли и набрать команду (оперируя с mysql не забывайте ставить в конце точку с запятой)

Установить новое значение глобальной переменной можно так

Переменную для текущей сессии можно установить так

Из калькулятора видно, что больше всего поджирает память key_buffer_size и innodb_buffer_pool_size, а так же параметр max_connections, который не просто суммируется, но и умножается на память, выделяемую одному соединению. Впрочем, значение этого параметра с легкостью подскажет вам tuning-primer, проанализировав ваши логи.

На этом пока всё, желаю удачи в ускорении вашего MySQL сервера :-)