MySqlTuner - настраиваем базу данных MySQL на оптимальную производительность

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 . Например:

cp /usr/share/mysql/my-large.cnf /etc/my.cnf
 

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

service mysqld restart

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

 

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

wget http://blog.mysqltuner.com/ 

и запустить

perl mysqltuner.pl

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

yum install mysqltuner 
mysqltuner

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

[root@host 1]# perl mysqltuner.pl

>> MySQLTuner 1.0.1 - Major Hayden <Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.82sp1-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 19M (Tables: 90)
[!!] InnoDB is enabled but isn't being used
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 18

-------- Performance Metrics -------------------------------------------------
[--] Up for: 16m 37s (6K q [6.059 qps], 146 conn, TX: 54M, RX: 665K)
[--] Reads / Writes: 62% / 38%
[--] Total buffers: 298.0M global + 6.3M per thread (100 max threads)
[OK] Maximum possible memory usage: 929.2M (26% of installed RAM)
[OK] Slow queries: 0% (0/6K)
[OK] Highest usage of available connections: 5% (5/100)
[OK] Key buffer size / total MyISAM indexes: 256.0M/2.3M
[!!] Key buffer hit rate: 91.3% (1K cached / 101 reads)
[OK] Query cache efficiency: 97.6% (5K cached / 5K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8 sorts)
[OK] Temporary tables created on disk: 5% (1 on disk / 17 total)
[OK] Thread cache hit rate: 96% (5 created / 146 connections)
[OK] Table cache hit rate: 95% (115 open / 121 opened)
[OK] Open file limit used: 21% (222/1K)
[OK] Table locks acquired immediately: 100% (286 immediate / 286 locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
     Add skip-innodb to MySQL configuration to disable InnoDB
     Add skip-bdb to MySQL configuration to disable BDB
     Run OPTIMIZE TABLE to defragment tables for better performance
     MySQL started within last 24 hours - recommendations may be inaccurate
     Enable the slow query log to troubleshoot bad queries

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

Highest usage of available connections: 5% (5/100)

MySQLTuner настроит и оптимизирует базу данных MySQL

Количество текущих соединений - 5, количество возможных соединений - 100. Параметр 100 - это параметр использующийся по умолчанию. Обычно этого значения маловато, именно из-за него появляются сообщения вида "Too many active connections". Увеличим этот параметр до 300. Открываем файл /etc/my.cnf в любимом редакторе и в секции [mysqld] добавляем строчку:

max_connections = 300

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

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

mysqlcheck --user=root --password=parole --optimize database_name

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

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

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

slow_query_log = /var/log/mysql/mysql_slow.log

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

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

 

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

Вас заинтересует / Intresting for you:

Модель развития базы данных My...
Модель развития базы данных My... 787 просмотров Ирина Светлова Thu, 10 Jan 2019, 12:29:03
Выбор оптимальных типов данных...
Выбор оптимальных типов данных... 3595 просмотров Валерий Павлюков Sun, 27 Oct 2019, 15:24:19
Транзакции в базе данных MySQL
Транзакции в базе данных MySQL 8443 просмотров Ирина Светлова Mon, 07 Jan 2019, 05:18:23
Обзор версий MySQL - какой рел...
Обзор версий MySQL - какой рел... 4953 просмотров Ирина Светлова Fri, 05 Feb 2021, 17:19:41
Войдите чтобы комментировать