Оптимизация 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.

Внимательно изучите строки, отмеченные знаком [!!], и также обратите внимание на сводку с рекомендациями, представленную в последнем блоке Рекомендации.

Параметры, перечисленные в разделе Настройка переменных, должны быть изменены в конфигурационном файле 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 после внесения изменений в настройки.