Оптимізація сервера MySQL за допомогою утиліти MySQL Tuner - Geek Notes - Roman Bogachev

Оптимізація сервера MySQL за допомогою спеціальної утиліти mysqltuner.

Після встановленняMySQL на виділений сервер, базовий файл конфігурації знаходиться в /etc/my.cnf . У такій конфігурації працювати сервер буде, але про оптимальну роботу не говоритиму.

Спочатку потрібно конфігураційний файл my.cnf , найбільш підходящий для нашого сервера.

За замовчуванням приклади розташовані в директорії /usr/share/mysql.

Вибираємо конфігураційний файл, виходячи з наших потреб:

my-small.cnf - для систем з об'ємом пам'яті менше 64Mb, де MySQL використовується рідко або в незначній мірі;

medium.cnf - підMySQL може виділятися до 64Мb пам'яті (для малопотужнихVDS/VPS );

my-large.cnf – для систем з оперативною пам'яттю від 512Мb;

my-huge.cnf – для систем з оперативною пам'яттю 1-2Gb;

Вибираємо одну з найбільш відповідних нам конфігурацій і переписуємо її замість поточної my.cnf.

Приступимо до оптимізації за допомогою утилітиMySQL Tuner

Завантажуємо утиліту

Скрипт попросить ім'я та парольMySQL адміністратора, після чого виведе результати своєї роботи. Виведення результатів роботи утиліти приблизно таке:

Дуже важливо звернути увагу на рядок

Кількість поточних з'єднань -16, кількість можливих з'єднань -151. Параметр151 - це параметр, який використовується за замовчуванням. Зазвичай цього значення замало, саме через нього з'являються повідомлення "Too many active connections". Збільшимо цей параметр до300. У секції [mysqld] у конфігураційному файлі /etc/my.cnf

Ще одним, найважливішим параметром збільшення продуктивностіMySQL єkey_buffer_size. Параметр визначає розмір загального для всіх процесів користувача буфера індексних блоківMyISAM таблиць. Зазвичай встановлюється в межах 30-40% від загальної виділеної підMySQL оперативної пам'яті.

Слід пам'ятати, що рекомендації утилітиMySQL Tuner будуть тим більш оптимальні, чим довше працює без перезавантажень серверMySQL.

Рекомендації

1) СтавимоMyISAM за замовчуванням і відключаємо непотрібні нам речі:

2) Прив'язуємо базу для використання лише на нашому сервері:

3) Виставляємо необхідні максимальні обмеження:

4) Встановлюємо загальний буфер, кеш для запитів та максимальний розмір для тимчасових таблиць:

5) Максимальна кількість з'єднань до бази та очікуваних процесів:

6) Буфери за кожен процес (великі значення типу 1Mb і вище нам це явно перебір).

7) Службовий сортувальний буфер (пам'ять постійно не займає) та кеш відкритих таблиць.

8) Необхідні таймаути, щоб відсікати довгі очікування.

9) Встановлення кодування UTF-8:

10) За бажанням можна додати логи повільних запитів або індексів, що не використовуються (створіть доступним на запис файл для лога).

Зверніть увагу на “1.7M per thread (151 max threads)”. Збільшивши кількість max_connections втричі, сильно збільшиться використання оперативної пам'яті. Тому не варто ставити max_connections про запас. Подивіться свої поточні метрики, скільки з'єднань буває у піку, додайте до цього 10-20%.

---