Оптимізація MySQL за допомогою MySQLTuner

by · Published · Updated

MySQLTuner — це скрипт, який аналізує роботу MySQL і виводить рекомендації щодо його оптимізації.

Завантажити/встановити скрипт можна командою:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

Бажано, щоб перед запуском скрипта сервер MySQL повинен пропрацювати близько доби без перезавантажень і зміни конфігурації, щоб MySQLTuner міг отримати достатньо даних для повноцінного аналізу. В іншому разі результати роботи скрипта будуть марні.

Запустіть скрипт:

perl mysqltuner.pl

MySQLTuner виведе результат, який складатиметься з кількох секцій і матиме приблизно такий вигляд (реальний висновок буде докладнішим і довшим):

root@server:~# perl mysqltuner.pl
 >>  MySQLTuner 1.7.19 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
-------- Log file Recommendations ---------------------------------------------
[OK] Log file /var/log/mysql/error.log exists
[--] Log file: /var/log/mysql/error.log(0B)
[OK] Log file /var/log/mysql/error.log is readable.
[!!] Log file /var/log/mysql/error.log is empty
-------- Security Recommendations ---------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'root@localhost' has no password set.
[!!] User 'user1@%' does not specify hostname restrictions.
[!!] User 'user@%' does not specify hostname restrictions.
-------- Performance Metrics --------------------------------------------------
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 243.2M (12.20% of installed RAM)
[!!] Maximum possible memory usage: 2.7G (138.90% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
-------- InnoDB Metrics -------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/416.0K
-------- Recommendations ------------------------------------------------------
General recommendations:
    Set up a Secure Password for root@localhost user: SET PASSWORD FOR 'root'@'SpecificDNSorIp' = PASSWORD('secure_password');
    Restrict Host for 'user1'@% to user1@SpecificDNSorIp
    UPDATE mysql.user SET host ='SpecificDNSorIp' WHERE user='user1' AND host ='%'; FLUSH PRIVILEGES;
    Dedicate this server to your database for highest performance.
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

Уважно вивчіть рядки, позначені знаком [!!!], і підсумкове зведення рекомендованих змін в останньому блоці Recommendations.

Параметри, наведені в пункті Variables to adjust, необхідно змінювати в конфігураційному файлі MySQL. Якщо у файлі такий параметр відсутній, його необхідно вписати.

Конфігураційний файл MySQL може розміщуватися за різними шляхами.

У Debian/Ubuntu це можуть бути:

/etc/mysql/my.cnf 
/etc/mysql/mysql.conf.d/mysqld.cnf

У CentOS:

/etc/my.cnf 
/etc/mysql/my.cnf

Після кожної зміни конфігураційного файлу необхідно перезапускати MySQL:

systemctl restart mysql

Додаткові рекомендації

1. Перед зміною конфігураційного файлу зробіть його бекап (у команді необхідно вказати актуальний для вас шлях):

cp /etc/mysql/my.cnf ~/my.cnf.backup

2. Після внесення правок почекайте, щоб сервер пропрацював близько доби, після чого можна знову запустити MySQLTuner. Так ви зможете проаналізувати роботу MySQL вже з новими налаштуваннями і продовжити оптимізацію.

3. Правки за рекомендаціями MySQLTuner має сенс вносити поступово і після кожної зміни спостерігати за роботою сервера. У цьому разі, якщо виникнуть будь-які проблеми, ви зможете швидко визначити їхню причину і відкотити небажані зміни.

Не забувайте після кожної зміни налаштувань перезапускати службу MySQL.

Tags: