Стандартні параметри в Mysql розраховані на мікроскопічні бази даних, що працюють під малими навантаженнями на скромному залозі.
Процес оптимального налаштування Mysql складається з двох частин - первісна настройка та коригування параметрів під час роботи. Коригування параметрів в робочому режимі багато в чому залежить від специфіки Вашої системи і її моніторингу. Розберемося з параметрами і рекомендаціями по установці їх значень.
Налаштування потрібно вносити в my.cnf.
innodb_buffer_pool_size
Якщо Ви використовуєте тільки InnoDB таблиці, встановлюйте це значення максимально можливим для Вашої системи. Буфер InnoDB кешує і дані та індекси. Тому значення цього ключа варто встановлювати в 70% ... 80% всієї доступної пам'яті.
# При тому, що на нашому сервері 32Гб оперативної пам'яті
innodb_log_file_size
Ця опція впливає на швидкість запису. Вона встановлює розмір логу операцій (так операції спочатку записуються в лог, а потім застосовуються до даних на диску). Чим більше цей лог, тим швидше будуть працювати записи (тому що їх поміститься більше в файл логу). Файлів завжди два, а їх розмір однаковий. Значенням параметра задається розмір одного файлу:
# Так два файли дадуть розмір логу в 2x512M = 1G
innodb_log_buffer_size
# Значення за замовчуванням в 1М має бути досить для більшості випадків
innodb_file_per_table
Якщо включити цю опцію, Innodb буде зберігати дані всіх таблиць в окремих файлах (замість одного файлу за замовчуванням). Приросту в продуктивності не буде, проте є ряд переваг:
- При видаленні таблиць, диск буде звільнятися. За замовчуванням загальний файл даних може тільки розширюватися, але не зменшуватися.
- Використання компресійного формату таблиць зажадає включити цей параметр.
# З версії 5.6 цей параметр включений за замовчуванням
innodb_flush_method
Цей параметр визначає логіку скидання даних на диск. У сучасних системах при використанні RAID і резервних узов, ви будете вибирати між O_DSYNC і O_DIRECT:
# Пам'ятайте про обов'язкове використання резервних вузлів (наприклад, реплік)
innodb_flush_log_at_trx_commit
Зміна цього параметра може підвищити пропускну здатність запису даних в базу в сотні разів. Він визначає, чи буде Mysql скидати кожну операцію на диск (в файл логу).
Тут слід керуватися такою логікою:
- innodb_flush_log_at_trx_commit = 1 для випадків, коли збереження даних - це пріоритет номер один.
- innodb_flush_log_at_trx_commit = 2 для випадків, коли невелика втрата даних не критична (наприклад, ви використовуєте дублювання і зможете відновити невелику втрату). В цьому випадку транзакції будуть скидатися в лог на диск тільки раз в секунду.
Встановлюйте значення на свій розсуд, проте в більшості випадків підійде другий варіант:
# Значне прискорення записи в базу, проте для цього потрібен механізмів дублювання даних
query_cache_size
Значення цього параметра визначає скільки пам'яті варто використовувати під кеш запитів. Найправильніший підхід - не покладатися на цей механізм. На практиці він працює дуже неефективно. Так, весь кеш запитів для певної таблиці скидається щоразу, коли в таблицю вноситься хоча б одна зміна. Це може привести до того, що включення кешування навіть сповільнить базу даних:
# Однак переконайтеся, що використовуєте індекси для забезпечення високої швидкості роботи запитів
max_connections
Не слід змінювати значення цього параметра на старті. Однак, якщо ви отримуєте помилки «Too many connections». цю опцію варто піднімати. Вона визначає максимальну кількість одночасних з'єднань з базою даних: