Зачастую на виртуальных серверов есть проблема с нагрузкой базы данных или же о медленной работе базы данных, по этому приведём пример оптимизации настройки 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 завершена.
