Оптимизация MySQL с помощью MySQLTuner

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.