Настройка и оптимизация 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 и запускаем скачивание скрипта
wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
Если что-то не качается, то заходим на http://mysqltuner.pl/ и смотрим, куда переадресовывает нас сайт
После того, как скрипт скачался, запускаем его
perl mysqltuner.pl
Скрипт просит логин и пароль администратора MySQL, нужно их ввести. Далее проводит анализ и выводит результаты.
Так как сервера у всех индивидуальны, то разбирать отчет я не вижу смысла. Читайте и следуйте рекомендациям. Критические позиции выделены красными восклицательными знаками [!!]
Вторая утилита, которую стоит использовать — tuning primer
Качаем ее и запускаем
- wget http://www.day32.com/MySQL/tuning-primer.sh
- sh tuning-primer.sh
На все запросы нажимаем 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 не забывайте ставить в конце точку с запятой)
- mysql
- SHOW VARIABLES;
- или
- SHOW GLOBAL VARIABLES LIKE 'read_buffer_size';
- или
- SELECT @@global.read_buffer_size;
Установить новое значение глобальной переменной можно так
- SET GLOBAL read_buffer_size=8192;
- или
- SET @@global.read_buffer_size=8192;
Переменную для текущей сессии можно установить так
- SET SESSION read_buffer_size=8192;
- или
- SET @@session.read_buffer_size=8192;
- или
- SET LOCAL read_buffer_size=8192;
- или
- SET read_buffer_size=8192;
Из калькулятора видно, что больше всего поджирает память key_buffer_size и innodb_buffer_pool_size, а так же параметр max_connections, который не просто суммируется, но и умножается на память, выделяемую одному соединению. Впрочем, значение этого параметра с легкостью подскажет вам tuning-primer, проанализировав ваши логи.
На этом пока всё, желаю удачи в ускорении вашего MySQL сервера :-)