Оптимизация сервера MySQL с помощью утилиты MySQL Tuner

Оптимизация сервера MySQL с помощью специальной утилиты mysqltuner.

После установки MySQL на выделенный сервер, базовый файл конфигурации находится в /etc/my.cnf.
В такой конфигурации работать сервер будет, но про оптимальную работу говорить не буду.

Сперва потребуется конфигурационный файл my.cnf, более подходящий для нашего сервера.

По умолчанию примеры расположены в директории /usr/share/mysql.

Выбираем конфигурационный файл исходя из наших потребностей:

my-small.cnf - для систем с объемом памяти менее 64Mb, где MySQL используется редко или в незначительной степени;

medium.cnf - под MySQL может выделяться до 64Мb памяти (для маломощных VDS/VPS);

my-large.cnf - для систем с оперативной памятью от 512Мb;

my-huge.cnf - для систем с оперативной памятью 1-2Gb;

Выбираем одну из наиболее подходящих нам конфигураций и переписываем её вместо текущей my.cnf.

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

Приступим к оптимизации с помощью утилиты MySQL Tuner

Скачиваем утилиту

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

и запускаем

perl mysqltuner.pl

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

>>  MySQLTuner 1.4.0 - Major Hayden <[email protected]>
 >>  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: 
[!!] Currently running unsupported MySQL version 10.0.17-MariaDB-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM 
[--] Data in MyISAM tables: 200K (Tables: 33)
[--] Data in InnoDB tables: 14M (Tables: 23)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 5

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 21d 9h 47m 3s (707K q [0.383 qps], 38K conn, TX: 519M, RX: 104M)
[--] Reads / Writes: 26% / 74%
[--] Total buffers: 176.0M global + 1.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 426.1M (11% of installed RAM)
[OK] Slow queries: 0% (0/707K)
[OK] Highest usage of available connections: 10% (16/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/285.0K
[OK] Key buffer hit rate: 98.1% (16K cached / 326 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[OK] Temporary tables created on disk: 7% (1K on disk / 23K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 5% (64 open / 1K opened)
[OK] Open file limit used: 8% (87/1K)
[OK] Table locks acquired immediately: 100% (685K immediate / 685K locks)
[!!] Connections aborted: 85%
[OK] InnoDB buffer pool / data size: 128.0M/14.6M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Set thread_cache_size to 4 as a starting value
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_size (>= 8M)
    thread_cache_size (start at 4)
    table_open_cache (> 64)

Очень важно обратить внимание на строку

[OK] Highest usage of available connections: 10% (16/151)

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

max_connections = 300

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

Следует помнить, что рекомендации утилиты MySQL Tuner будут тем более оптимальны, чем дольше работает без перезагрузок сервер MySQL.

Рекомендации

1) Ставим MyISAM по умолчанию и отключаем ненужные нам вещи:

default-storage-engine=MyISAM
innodb=OFF
skip-symbolic-links
skip-networking
skip-name-resolve
skip-federated

2) Привязываем базу для использования только на нашем сервере:

bind-address = 127.0.0.1

3) Выставляем необходимые максимальные ограничения:

max_connect_errors = 1K
max_join_size=200M
max_allowed_packet=16M

4) Устанавливаем общий буфер, кэш для запросов и максимальный размер для временных таблиц:

key_buffer_size=128M
query_cache_size = 256M
max_heap_table_size=256M
tmp_table_size=256M

5) Максимальное число соединений к базе и ожидающих их процессов:

max_connections = 160
thread_cache_size = 160

6) Буферы на каждый процесс (большие значения типа 1Mb и выше для нас это явно перебор).

read_buffer_size = 128K
read_rnd_buffer_size = 256K
join_buffer_size = 256K
sort_buffer_size = 128K

7) Служебный сортировочный буфер (память постоянно не занимает) и кэш открытых таблиц.

myisam_sort_buffer_size = 512M
table_open_cache = 1K

8) Необходимые таймауты, чтобы отсекать долгие ожидания.

interactive_timeout = 60
wait_timeout = 60
connect_timeout =20

9) Установка кодировки UTF-8:

character-set-server=utf8
collation-server=utf8_general_ci

10) По-желанию можно добавить логи медленных запросов или неиспользуемых индексов
(создайте доступным на запись файл для лога).

log_slow_queries = 1
slow_query_log_file = /var/log/mysql-bin.log
long_query_time = 4
log-queries-not-using-indexes

Обратите внимание на “1.7M per thread (151 max threads)”. Увеличив количество max_connections в 3 раза, сильно увеличится использование оперативной памяти. Поэтому не стоит ставить max_connections про запас. Посмотрите свои текущие метрики, сколько соединений бывает в пике, прибавьте к этому 10-20%.

# mysql