Оптимізація 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 завершена.

Goodhoster.NET
Додати коментар