Mysqltuner centos 7 как пользоваться

Обновлено: 06.07.2024

После установки MySQL на выделенный сервер, базовый файл конфигурации находится в /etc/my.cnf .
В такой конфигурации работать сервер будет, но про оптимальную работу говорить не буду.

Сперва потребуется конфигурационный файл my.cnf , более подходящий для нашего сервера.

По умолчанию примеры расположены в директории /usr/share/mysql .

Выбираем конфигурационный файл исходя из наших потребностей:

my-small.cnf - для систем с объемом памяти менее 64Mb, где MySQL используется редко или в незначительной степени;

medium.cnf - под MySQL может выделяться до 64Мb памяти (для маломощных VDS/VPS);

my-large.cnf - для систем с оперативной памятью от 512Мb;

my-huge.cnf - для систем с оперативной памятью 1-2Gb;

Выбираем одну из наиболее подходящих нам конфигураций и переписываем её вместо текущей my.cnf .

Приступим к оптимизации с помощью утилиты MySQL Tuner

Скачиваем утилиту

Скрипт попросит имя и пароль MySQL администратора, после чего выведет результаты своей работы.
Вывод результатов работы утилиты примерно такой:

Очень важно обратить внимание на строку

Еще одним, наиболее важным параметром увеличения производительности MySQL является key_buffer_size . Параметр определяет размер общего для всех пользовательских процессов буфера индексных блоков MyISAM таблиц. Обычно устанавливается в пределах 30-40% от общей выделенной под MySQL оперативной памяти.

Следует помнить, что рекомендации утилиты MySQL Tuner будут тем более оптимальны, чем дольше работает без перезагрузок сервер MySQL.

Начало

Сервер у нас пусть будет на CentOS. Оптимизировать будем методом правки конфига my.cnf .

Настройка некоторых параметров может повысить
производительность БД сервера в несколько раз!

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

Для этого возьмем htop (как красивый и наглядный инструмент):

Выведем htop :

Получаем нечто такое:
Запишем себе в my.cnf:

Теперь давайте узнаем количество таблиц и их типы.
Для этого возьмем mysql tuner:

Запишем себе в my.cnf:

Типовой конфиг обычно рекомендуют какой-то такой:

Теперь давайте разбираться, что мы будем оптимизировать здесь, зачем, как и почему (особенно почему этих параметров маловато.

Оптимизация и конфиг

Для начала можно пролистать в конец вывода mysql tuner и посмотреть, что же он там рекомендует. В нашем случае это выглядит как-то так:

Не будем заниматься бездумной подстановкой, а пройдемся по параметрам mysql , которые могут нас интересовать в первую очередь. Что к чему:
skip-external-locking, — убирает внешнюю блокировку, что быстрее;
skip-name-resolve , — позволяет MySQL избегать ответа на запрос DNS при проверке подключения клиентов к серверу MySQL .

Таким образом, сервер MySQL будет использовать только
IP -адреса, а не имена хостов, что немного, но быстрее.

binlog_cache _ size, — размер кэша для хранения изменений в двоичном журнале. Задает размер только для кэша транзакций. Сделаем 100M — больше не нужно.

innodb_stats_on_metadata = 0 (OFF), — для ускорения работы с
INFORMATION_SCHEMA, SHOW TABLE STATUS или SHOW INDEX отключим обновление статистики при выполнении таких операций

quer y _cache_size = 128M и query_сache_type
= 1, — кэши запросов. 1 — в принципе включен, 128M ограничение. Не
рекомендуется ставить выше 256M , т.к это может привести к блокировке.

Так как у нас большеInnoDB таблиц, то зануляем cache _ size .
С версии MySQL 5.6 query_cache_size отключен, а с версии 8.0 удален

Стандартно все таблицы и индексы хранятся в одном файле, поэтому используем innodb_file_per_table = 1.

Значение innodb_open_files и table_open_cache — рекомендуется устанавливать обе опции в 4096 или 8192 . А вообще рассчитывается как количество таблиц во всех базах, умноженное на 2 , ориентировочно.

При работе с InnoDB является важнейшим параметр innodb_buffer_pool_size , он устанавливается по принципу «чем больше, тем лучше». Рекомендуется выделять до 70-80% оперативной памяти сервера.

innodb_log_file_size — влияет на скорость записи, устанавливает размер лога операций (операции сначала записываются в лог, а потом применяются к данным на диске). Чем больше этот лог, тем быстрее будут работать записи (т.к. их поместится больше в файл лога). Файлов всегда два, а их размер одинаковый. Значением параметра задается размер одного файла.

ВНИМАНИЕ!️При изменении параметра innodb_log_file_size остановите MySQL, сделайте резервную копию файлов ib_logfile-n (файлы чаще всего лежат в /var/lib/mysql/), измените значение параметра innodb_log_file_size и запустите MySQL. В результате
MySQL создаст новый лог-файл указанного в конфигурации размера.

Установка большого размера innodb_log_file_size может привести к увеличению быстродействия, но при этом увеличится время восстановления данных, выберите от 256M до 1G .

innodb_log _ buffer_size — размер буфера транзакций. Обычно рекомендуется не применять, если не используете BLOB и TEXT больших размеров.

innodb_flush _ method, — определяет логику сброса данных на диск. В современных системах при использовании RAID и резервных узлов, вы будете выбирать между ODSYNC и ODIRECT, — первый параметр быстрее, второй безопаснее.

key_buffer _ size — буфер для работы с ключами и индексами, и sort_buffer — буфер для сортировки. Если Вы не используете MyISAM таблицы, рекомендуется установить размер key_buffer_size в 32Мб для хранения индексов временных
таблиц.

Параметр thread_cache _ size указывает количество тредов (threads), уходящих в кеш при отключении клиента. При новом подключении тред не создается, а берется из кеша, что позволяет экономить ресурсы при больших нагрузках.

innodb_flush_log_attrx_commit, — может повысить пропускную способность записи данных в базу в сотни раз. Он определяет, будет ли Mysql сбрасывать каждую операцию на диск (в файл лога).

innodb_flush_log_at_trx_commit = 1 используется для случаев,
когда сохранность данных — это приоритет номер один.

innodb_flush_log_at_trx_commit = 2 для случаев, когда небольшая потеря данных не критична. Есть еще 0 (ноль) — самый производительный, но небезопасный вариант.

max_connections — если вы получаете ошибки " Too many connections ", эту опцию стоит увеличить. А так большой пользы в оптимизации от неё нет.

Количество потоков ввода/вывода файлов в InnoDB задается опциями innodb_read_io_threads , innodbwrite_io_threads, обычно этому параметру присваивается значение 4 или 8 , на быстрых SSD -дисках установите в 16. Значение innodb_thread_concurrency установите в количество ядер * 2 .

Конфиг получается вот такой:

Ну и напоследок можно посмотреть рекомендации тюнера и последовать им.

Заключение

Вот такой вот интересный конфиг получился. Если Вам сложно, то на первых порах стоит пользоваться mySQL калькулятором, который подскажет основные параметры и позволит не выходить за пределы доступной памяти — как-никак всё упирается в неё:

Исторически в веб-разработке так сложилось, что самой популярной базой данных стала MySQL. В принципе, такой выбор закономерен: она бесплатная, имеет широкие возможности, простая в использовании и, что самое важное для работы сайта, быстрая. Тем не менее, она может оказаться и «узким местом» сайта – без правильных настроек и оптимизации страницы сайта могут грузиться медленно, возможны ошибки соединения с БД и прочее.

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

Кроме того, она полностью бесплатна, имеет открытый исходный код, опубликованный на GitHub и активно развивается.

Установка и первичная настройка MySQLTuner

MySQLTuner представляет собой скрипт, написанный на языке Perl. Поэтому его можно либо установить из репозиториев операционной системы, либо скачать напрямую. Установка производится следующей командой:

Кроме того, скрипт можно загрузить непосредственно с GitHub вручную или с использованием команды

Если вы скачивали файл без установки, перед запуском скрипт mysqltuner.pl нужно перенести на сервер и установить на файл права, разрешающие его запуск (chmod 775). Это можно сделать во встроенном файловом-менеджере или при помощи команды

выполняемой из-под учетной записи администратора.

Всё, установка и настройка закончены, можно переходить к запуску.

Запуск и работа MySQLTuner

Авторы MySQLTuner рекомендуют запускать скрипт после того как ваша база данных MySQL проработала хотя бы 24 часа без перезагрузок и изменений в конфигах. То же самое касается и повторных запусков после смены конфигурации. Если это условие выдержано, можно переходить к запуску MySQLTuner.

Запуск зависит от способа, которым проводилась установка:

если утилита устанавливалась через репозитории, запуск выполняется командой

msqltuner

после чего указываются логин и пароль учётной записи с правами администратора базы данных;

если скрипт скачивался с github, запуск выглядит следующим образом:

perl mysqltuner.pl

а затем также указываются логин и пароль административной учётной записи.

После запуска скрипт выдаст информацию об используемой базе данных (версию MySQL, время работы с момента последнего перезапуска, объем занимаемой памяти, размеры кэша, количество одновременных соединений и другие параметры работы). Здесь особое внимание нужно обратить на строки, начинающиеся на [!!] – они могут влиять на производительность MySQL.

После этого будут показаны рекомендации по оптимизации работы базы данных. Они в выдаче утилиты находятся в разделе Recommendations. Под рекомендациями есть названия конкретных переменных, которые нужно изменить, и рекомендуемые значения для них. За это отвечает секция Variables to adjust.

Измененные значения переменных вносятся в конфигурационный файл my.cnf. Если каких-то параметров в файле нет, их можно туда дописать вместе с рекомендуемым значением. Для применения новых параметров MySQL-сервер необходимо перезапустить. Выполнить это можно командой service mysqld restart (для CentOS 6) или systemctl restart mariadb.service (для CentOS 7).

Когда база проработает без перезагрузок достаточное для сбора статистики время, нужно еще раз запустить MySQLTuner и посмотреть изменившиеся рекомендации. Если все сделано правильно, вы наверняка ощутите прирост производительности MySQL на вашем сервере.

И помните, что как бы хорошо вы ни настроили работу баз данных, ваш сайт будет работать быстро и без сбоев, только если вы позаботились о качественном и надежном хостинге. Смотрите отзывы о нашей работе и обращайтесь за грамотной консультацией.

В сфере разработки современных web-сайтов самой популярной базой данных является СУБД mysql или ее бесплатный аналог MariaDB. Простота установки и высокая скорость работы сделали ее одной из наиболее часто используемых баз данных в мире хостинга. Недаром корпорация Oracle почуяв зарождение серьезного конкурента выкупила MySQL и сделала его коммерческим продуктом. Она может многое, работает быстро, но без должной изначальной настройки может стать очень узким местом вашего веб-сайта. А отсюда может тянуться и медленная загрузка страниц сайта, и ошибки на страницах вида "Too many active connections". Все, описанное в данной статье, верно также и для MariaDB, - mysqltuner прекрасно работает с этой базой данных.

После установки mysql на выделенный сервер, изначально его файл конфигурации размещается в /etc/my.cnf и выглядит весьма скромно. В такой конфигурации работать то он будет, но про какую-либо оптимальную работу говорить не приходиться. Вот и попробуем немного улучшить наши настройки сервера с целью оптимизации работы базы данных mysql. Первым делом нам необходимо взять более менее подходящий нам начальный конфигурационный файл my.cnf. Производитель MySql уже позаботился о нас и подготовил несколько типовых файлов, найти которые можно в директории /usr/share/mysql. Там есть четыре необходимых нам файла: my-small.cnf, my-medium.cnf, my-large.cnf и my-huge.cnf. Выбираем под наш сервер, нашу память, наши запросы наиболее подходящий файл. my-small.cnf - конфигурация для систем с объемом памяти менее 64Mb (это объем памяти именно под сервер mysql, а не всю операционную систему в целом) , где mysql используется время от времени. Это не для нас.medium.cnf - чуть лучше, под mysql может выделяться до 64Мб памяти. В общем то это тоже обычно не для нас, разве что очень маломощные VDS, VPS.my-large.cnf - уже посерьезнее, для систем с оперативной памятью от 512Мб. my-huge.cnf - для систем с оперативной памятью 2Гб. Выбираем одну из наиболее подходящих нам конфигураций и переписываем ее вместо текущей my.cnf . Например:

После чего перезапустим сервер MySQL.

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

Одной из таких утилит, которая может помочь в выявлении проблемных мест конфигурации сервера mysql, является утилита mysqltuner. Утилита представляет собой скрипт написанный на языке perl. Инсталляции не требует. Его просто нужно скачать

Кроме того, во многих системах Linux уже есть подготовленные бинарные пакеты mysqltuner. Так, в Lunux CentOS и RedHat можно просто установить паке и запустить его:

Скрипт попросит имя и пароль MySQL администратора, после чего выведет результаты своей работы. Вывод результатов работы утилиты примерно такой:

Во-первых, сразу бросается в глаза параметр, отвечающий за максимальное кол-во подключений к базе:

Что еще говорит нам оптимизатор mysqltuner? Он сообщает, что если мы не используем подсистему InnoDB, то в конфигурационный файл my.cnf нужно добавить опцию skip-innodb. Тоже самое касается и подсистемы BDB, - если не используем, то отключаем: skip-bdb.

Также оптимизатор сообщает нам, что желательно выполнить оптимизацию всех таблиц базы (команда OPTIMIZE TABLE) для избавления от дефрагментации данных. Выполнить оптимизацию для всех таблиц базы сразу можно командой:

Не обязательно выполнять команду для всех таблиц базы. Можно посмотреть (например в phpmyadmin), какие действительно нуждаются в дефрагментации, и выполнить оптимизацию только для них.

Оптимизатор mysqltuner предупреждает нас также о том, что субд mysql запущена менее чем 24 часа назад, а программе для сбора статистики и выдаче рекомендации требуется как минимум сутки (в идеале 5-7 дней непрерывно работающего сервера).

И на последок нам рекомендует включить логирование медленных запросов (slow query), которые можно будет потом передать разработчикам ПО для их оптимизации (добавлению нужных индексов, например). Задать необходимость протоколирования медленных запросов можно в конфигурационном файле my.cnf следующей командой:

Сохраняемся, выходим и перегружаем сервер mysql. Повторно запустив mysqltuner увидим соответствующие изменения в выводе.

Еще одним, наиболее важным параметром увеличения производительности mysql является key_buffer_size. Параметр определяет размер общего для всех пользовательских процессов буфера индексных блоков MyISAM таблиц. Обычно устанавливается в пределах 30-40% от общей выделенной под Mysql оперативной памяти.

Мы рассмотрели достаточно простенький пример для получения общих сведений об утилите MySqlTuner. В дальнейшем попробуем Вам показать действительно боевой пример на серьезной боевой базе с мощный сервером.

Читайте также: