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