Использование MySQL на оптимальных настройках для отдельных ресурсов помогает справляться с высокими серверными нагрузками и предотвращает замедление работы сервера.
Perl-скрипт MySQLTuner анализирует статистику работы, оценивает конфигурацию MySQL и выводит пользователю предложения о внесении изменений параметров для повышения производительности и стабильности работы сервера.
Установка
Скачайте MySQLTuner:
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
Для удобства можно воспользоваться штатными репозиториями и установить MySQLTuner.
Для Debian/Ubuntu:
apt-get -y install mysqltuner
Для CentOS:
yum -y install mysqltuner
Разрешите выполнение скрипта (Если производилось скачивание скрипта):
chmod +x mysqltuner.pl
Запустите скрипт mysqltuner.pl. Вам будет предложено ввести имя пользователя и пароль администратора MySQL:
Если производилось скачивание скрипта:
perl mysqltuner.pl --user root --pass rootpassword
или
perl mysqltuner.pl
Если производилась установка:
mysqltuner --user root --pass rootpassword
или
mysqltuner
Если возникла ошибка:
ERROR: cannot verify raw.githubusercontent.com's certificate, issued by '/C=US/O=DigiCert Inc/OU=www.digicert.com/CN=DigiCert SHA2 High Assurance Server CA':
Unable to locally verify the issuer's authority.
To connect to raw.githubusercontent.com insecurely, use `--no-check-certificate'.
Запустите с с ключом --no-check-certificate:
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl --no-check-certificate
Скрипт вернет результаты анализа, аналогичные представленным ниже:
>> MySQLTuner 1.4.0 - Major Hayden
>> 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:
[OK] Currently running supported MySQL version 5.5.41-0+wheezy1
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 1M (Tables: 11)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 11
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 47s (113 q [2.404 qps], 42 conn, TX: 19K, RX: 7K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 597.8M (60% of installed RAM)
[OK] Slow queries: 0% (0/113)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/99.0K
[!!] Query cache efficiency: 0.0% (0 cached / 71 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 25% (54 on disk / 213 total)
[OK] Thread cache hit rate: 97% (1 created / 42 connections)
[OK] Table cache hit rate: 24% (52 open / 215 opened)
[OK] Open file limit used: 4% (48/1K)
[OK] Table locks acquired immediately: 100% (62 immediate / 62 locks)
[OK] InnoDB buffer pool / data size: 128.0M/1.2M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Variables to adjust:
query_cache_limit (> 1M, or use smaller result sets)
MySQLTuner предлагает способы повышения производительности базы данных. Если самостоятельно обновить конфигурацию базы данных затруднительно, то выполнение рекомендаций MySQLTuner является одним из безопасных способов повышения производительности.
Настройка MySQL
При изменении конфигурации MySQL следует внимательно относиться к изменениям и их влиянию на базу данных. Даже при выполнении инструкций таких программ, как MySQLTuner, лучше иметь некоторое понимание процесса.
С порядком анализа можно ознакомиться здесь.
Конфигурационный файл MySQL хранится в директории:
/etc/mysql/my.cnf
Для CentOS:
/etc/my.cnf
В этот файл могут быть внесены изменения, основанные на рекомендациях MySQLTuner в пункте Variables to adjust секции Recommendations. Если какого-либо параметра нет в файле my.cnf, допишите его.
После внесения изменений в my.cnf перезагрузите MySQL-сервер:
Debian/Ubuntu и CentOS 6:
/etc/init.d/mysqld restart
CentOS 7:
systemctl restart mariadb
Для наиболее эффективного использования возможностей MySQLTuner желательно производить небольшие изменения за раз, а затем проводить повторный анализ. Таким итеративным способом можно будет добиться наилучших результатов при настройке MySQL.
При этом, для того чтобы данные были корректны, необходимо, чтобы сервер MySQL проработал не менее 24 часов без перезагрузок и смены параметров конфигурации перед следующим анализом.
Параметры MySQL
key_buffer
Изменение key_buffer выделяет больше памяти MySQL, что существенно ускоряет работу базы данных при условии наличия свободной памяти. Размер key_buffer обычно должен занимать не более 25% системной памяти при использовании MyISAM и до 70% для InnoDB. Если значение установлено слишком высоко, ресурсы расходуются впустую.
Согласно документации MySQL, для серверов с 256MB (или более) ОЗУ с множеством таблиц рекомендуется настройка 64M. Серверы с 128MB ОЗУ и меньшим количеством таблиц могут быть настроены на 16M — это значение по умолчанию. Веб-сайты с еще меньшим числом ресурсов и таблиц могут быть ограничены даже меньшим объемом.
max_allowed_packet
Этот параметр позволяет задать максимальный размер отправляемого пакета. Если известно, что MySQL-сервер будет обрабатывать большие пакеты, лучше увеличить их до размера самого большого пакета. Если это значение будет слишком маленьким, в журнале ошибок появится ошибка.
thread_stack
Это значение содержит размер стека для каждого треда. MySQL считает значение переменной thread_stack по умолчанию достаточным для нормального использования. Однако в случае регистрации ошибки, связанной с thread_stack, оно может быть увеличено.
thread_cache_size
Этот параметр указывает количество тредов, которые уходят в кеш при отключении клиента. При новом подключении тред используется из кеша, что позволяет экономить ресурсы при значительных нагрузках.
max_connections
Этот параметр задает максимальное количество одновременных подключений. Прежде чем задавать это число, лучше всего учитывать максимальное количество подключений, которые были в прошлом, следовательно у вас будет буфер между верхним числом подключений и значением max_connections. Следует обратить внимание на то, что этот параметр не указывает максимальное количество пользователей на сайте. Вместо этого отображается максимальное количество пользователей, одновременно делающих запросы.
table_cache
Это значение должно быть больше, чем значение open_tables. Для определения этого значения используйте:
SHOW STATUS LIKE 'open%';
С полным перечнем анализируемых параметров можно ознакомиться здесь.