Оптимизация MySQL

Зачастую на виртуальных серверов есть проблема с нагрузкой базы данных или же о медленной работе базы данных, по этому приведём пример оптимизации настройки MySQL. Конфигурационный файл базы данных расположен по пути:

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

Помним, что параметры настраиваются как для MyISAM таблиц, так и для InnoDB. Основные параметры, которые использует конфиг для настройки под сервер:
max_allowed_packet — так как данные между сервером и клиентом передаются пакетами, данный параметр указывает максимальный его размер.
key_buffer_size и sort_buffer_size — данные параметры настраиваются для работы с ключами и сортировками буфера. Её нужно устанавливать в 30-40% от доступной оперативной памяти. Например, если на сервере используется 16Гб памяти, значит данный параметр установим в 128M-256M.
max_connections — параметр указывает максимальное число соединений с базой, то есть, количество клиентов, которые могут одновременно работать с сервером.
innodb_buffer_pool_size — параметр для InnoDB, который обычно устанавливают до 70-80% оперативной памяти сервера для размера памяти буфера.
query_cache_size — параметр для кэша, рекомендуемое значение указывается до 512 МБ, так как большое обслуживание кэша может создавать нагрузку.
table_open_cache — количество таблиц, которые кэшируются.
innodb_log_file_size — максимальный размер лог-файла, которые стоит указывать в 25% от оперативной памяти.
innodb_log_buffer_size — размер самого лог-буфера.
join_buffer_size — праметр, который используется при операциях объединения таблиц и указывает размер буфера при этой операции.
open_files_limit — количество открытых файлов, по умолчанию устанавливается в 1024

Наведём пример более оптимальных конфигов при разном объёме памяти.
Для 1Гб:

max_allowed_packet = 16M
key_buffer_size = 16M
innodb_buffer_pool_size = 512M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
max_connections = 132
query_cache_size = 0
long_query_time = 1
expire_logs_days = 10
max_binlog_size = 100M

Для 2Гб:

max_allowed_packet = 16M
key_buffer_size = 16M
innodb_buffer_pool_size = 1024M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
max_connections = 136
query_cache_size = 0
long_query_time = 1
expire_logs_days = 10
max_binlog_size = 100M

Для 4Гб:

max_allowed_packet = 16M
key_buffer_size = 16M
innodb_buffer_pool_size = 2048M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
max_connections = 144
query_cache_size = 0
long_query_time = 1
expire_logs_days = 10
max_binlog_size = 100M

 

Так же можно использовать утилиту MySQLTuner для оптимизации базы: https://github.com/major/MySQLTuner-perl

Для скачивания используем команду:

wget http://mysqltuner.pl -O tuner.pl --no-check-certificate

Запускаем для сканирования и проверки:

perl tuner.pl

Вывод покажет, что можно увеличить и подправить, например:

Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    performance_schema = ON enable PFS
    innodb_log_file_size should be (=128M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances (=1)

Обратите внимание на раздел вывода:

[!!] Maximum possible memory usage:

Здесь указано сколько ОЗУ может брать Mysql, если это 90% — это не нормально (конечно, если это не сервер с одним mysql). Нужно добиться показателя в 40-60%, в зависимости от потребления озу веб-сервера. Для этого нужно уменьшать параметры для буферов: key_buffer_size , innodb_buffer_pool_size , innodb_buffer_pool_instances
При каждом изменении нужно перезапустить mysql для применения настроек.

Важным моментом является проверка базы на целостность, так как таблицы могу быть повреждены из за некоторых неверных запросов и операциях. По этому можем методом mysqlcheck проверить, починить и оптимизировать таблицы в базе.
Чтобы выполнить процедуру проверки всех таблиц в базе данных, выполняем команду:

mysqlcheck -u root -p --check --all-databases

Чтобы починить таблицы, выполним команду:

mysqlcheck -uroot -p --repair --all-databases

Если нужно провести оптимизацию таблиц:

mysqlcheck -u root -p --optimize --all-databases

Для восстановления одной базы необходимо выполнить команду:

mysqlcheck -r --databases NAME_BASE -u root -p

 

Вы так же можете выполнить проверку и восстановление таблиц через myisamchk. В отличии от mysqlcheck, не требует пароль к базе.
Чтобы определить все повреждённые таблицы, выполним команду:

myisamchk /var/lib/mysql/{база}/*

Чтобы починить все таблицы в базе, выполняем:

myisamchk -ri /var/lib/mysql/{база}/*

На этом простая оптимизация MySQL завершена.

Оставить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *