Налаштування та оптимізація MySQL сервера

У цій статті будуть описані різні параметри MySQL, переважно ті, що впливають на продуктивність. Для зручності всі змінні розділені за розділами (базові налаштування, обмеження, потоки налаштування, кешування запитів, таймінги, буфери, InnoDB). Спочатку уточнимо імена деяких змінних, які змінилися у версії 4 MySQL, а в мережі продовжують зустрічатися і старі та нові варіанти імен, що викликає питання.

Базові налаштування

Обмеження

  • bind-address — інтерфейс, який слухатиме сервер. Для безпеки рекомендується встановити тут 127.0.0.1, якщо ви не використовуєте зовнішні з'єднання з сервером.
  • max_allowed_packet — максимальний розмір даних, які можуть бути надіслані за один запит. Слід збільшити, якщо зіткнетеся з помилкою "Packet too large".
  • max_connections — максимальна кількість паралельних з'єднань до сервера. Збільште його, якщо ви стикаєтеся з проблемою «Too many connections».
  • max_join_size — забороняє SELECT оператори, які, ймовірно, будуть аналізувати більше вказаної кількості рядків або більше вказаної кількості пошуків по диску. Використовується для захисту від кривих запитів, які намагаються рахувати мільйони рядків. Значення за замовчуванням більше 4 мільярдів, тому ви, швидше за все, захочете його значно зменшити.
  • max_sort_length — вказує, скільки байт із початку полів типу BLOB або TEXT використовувати для сортування. Значення за замовчуванням 1024, якщо ви побоюєтеся некоректно спроектованих таблиць або запитів, слід зменшити його.

Налаштування потоків

  • thread_cache_size — вказує кількість потоків, що кешуються. Після обробки запиту сервер не завершуватиме потік, а розміститьйого в кеші, якщо кількість потоків, що знаходяться в кеші менше, ніж зазначене значення. Значення за умовчанням 0, збільште його до 8 або відразу до 16. Якщо спостерігається зростання значення змінної стану Threads_Created , слід ще збільшити thread_cache_size .
  • thread_concurrency є актуальним тільки для Solaris/SunOS всупереч тому, що пишуть у мережі. "Підказує" системі скільки потоків запускати одночасно, виконуючи виклик функції thr_setconcurrency. Рекомендоване значення – подвійне чи потрійне число ядер процесора.

Кешування запитів

  • query_cache_limit — максимальний розмір запиту, що кешується.
  • query_cache_min_res_unit — мінімальний розмір блоку, що зберігається в кеші.
  • query_cache_size - розмір кешу. 0 вимикає використання кешу. Для вибору оптимального значення необхідно спостерігати за змінною станом Qcache_lowmem_prunes і домогтися, щоб її значення збільшувалося незначно. Також потрібно пам'ятати, що надмірно великий кеш створюватиме непотрібне навантаження.
  • query_cache_type - (OFF, DEMAND, ON). OFF відключає кешування, DEMAND – кешування буде здійснюватись лише за наявності директиви SQL_CACHE у запиті, ON включає кешування.
  • query_cache_wlock_invalidate — визначає, чи будуть дані братися з кешу, якщо таблиця, до яких вони відносяться, заблокована на читання.

Кеш запитів можна уявити як хеш-масив, ключами якого є запити, а значеннями — результати запитів. Крім результатів MySQL зберігає в кеші список таблиць, вибірка з яких закешована. Якщо в будь-якій таблиці, вибірка з якої є в кеші, відбуваються зміни, то MySQL видаляє з кешу такі вибірки. Також MySQL не кешує запити, результати якихможуть змінитися. При запуску MySQL виділяє блок пам'яті розміром query_cache_size . При виконанні запиту, як тільки отримані перші рядки результату, сервер починає кешувати їх: він виділяє в кеші блок пам'яті, рівний query_cache_min_res_unit, записує в нього результат вибірки. Якщо не вся вибірка помістилася в блок, сервер виділяє наступний блок і так далі. У момент початку запису MySQL не знає про розмір вибірки, тому якщо записаний в кеш розмір вибірки більше, ніж query_cache_limit , то запис припиняється і зайняте місце звільняється, отже, якщо ви знаєте наперед, що результат вибірки буде більшим, варто виконувати його з директивою SQL_NO_CACHE.

  • interactive_timeout — час у секундах, протягом якого сервер очікує активності з боку інтерактивного з'єднання (що використовує прапор CLIENT_INTERACTIVE), перш ніж закрити його.
  • log_slow_queries - вказує серверу логувати довгі («повільні») запити (виконуються довше long_query_time). Як значення передається повне ім'я файлу (наприклад /var/log/slow_queries).
  • long_query_time — якщо запит виконується довше за вказаний час (у секундах), то він вважатиметься «повільним».
  • net_read_timeout — час у секундах, протягом якого сервер очікуватиме отримання даних, перш ніж з'єднання буде перервано. Якщо сервер не обслуговує клієнтів із дуже повільними чи нестабільними каналами, то 15 секунд тут буде достатньо.
  • net_write_timeout — час у секундах, протягом якого сервер очікуватиме отримання даних, перш ніж з'єднання буде перервано. Якщо сервер не обслуговує клієнтів із дуже повільними чи нестабільними каналами, то 15 секунд тут буде достатньо.
  • wait_timeout — час у секундах, протягом якого сервер очікує активності з'єднання, перш ніж перерве його. Загалом 30 секунд буде достатньо.

У всіх буферів є спільна риса - якщо через установку великого розміру буфера дані будуть йти у файл підкачки, то від буфера буде більше шкоди, ніж користі. Тому завжди орієнтуйтеся на доступний вам обсяг фізичної ОЗП.

  • key_buffer_size - розмір буфера, що виділяється під індекси та доступного всім потокам. Дуже важливе налаштування, що впливає на продуктивність. Значення за замовчуванням 8 МБ його однозначно варто збільшити. Рекомендується 15-30% від загального обсягу ОЗУ, проте немає сенсу встановлювати більше, ніж загальний розмір всіх файлів .MYI. Спостерігайте за змінними станами Key_reads і Key_read_requests , відношення Key_reads/Key_read_requests повинно бути якомога менше (max_heap_table_size — максимальний допустимий розмір таблиці, що зберігається в пам'яті (типу MEMORY). таблиць, то встановіть це значення рівним tmp_table_size.
  • myisam_sort_buffer_size — розмір буфера, що виділяється MyISAM для сортування індексів при REPAIR TABLE або створення індексів при CREATE INDEX, ALTER TABLE. Значення за замовчуванням 8 МБ його варто збільшити аж до 30-40% ОЗУ. Виграш у продуктивності відповідно буде лише при виконанні вищезазначених запитів.
  • net_buffer_length - обсяг пам'яті, що виділяється для буфера з'єднання та для буфера результатів на кожен потік. Буфер з'єднання буде вказаного розміру і буфер результатів такого ж розміру, тобто. на кожен потік буде виділено подвійний розмір net_buffer_length. Вказане значення є початковим і за потреби буфери будутьзбільшуватися аж до max_allowed_packet. Типовий розмір 16 КБ. У разі обмеженої пам'яті або використання невеликих запитів значення можна зменшити. У разі постійного використання великих запитів і достатнього обсягу пам'яті, значення варто збільшити до передбачуваного середнього розміру запиту.
  • read_buffer_size - кожен потік при послідовному скануванні таблиць виділяє вказаний обсяг пам'яті кожної таблиці. Як свідчать тести, це значення не слід особливо збільшувати. За замовчуванням 128 КБ, спробуйте збільшити його до 256 КБ, а потім до 512 КБ і поспостерігайте за швидкістю виконання запитів типу SELECT COUNT(*) FROM table WHERE expr LIKE "a%"; великих таблицях.
  • read_rnd_buffer_size — на часі для запитів з «ORDER BY«, тобто. для запитів, результат яких має бути відсортований та які звертаються до таблиці, що має індекси. Значення за замовчуванням 256 КБ, збільште його до 1 МБ або вище, якщо пам'ять дозволяє. Зверніть увагу, що вказане значення пам'яті також виділяється на кожен потік.
  • sort_buffer_size — кожен потік, що здійснює операції сортування (ORDER BY) або групування (GROUP BY), виділяє буфер зазначеного розміру. Значення за замовчуванням 2 МБ, якщо ви використовуєте вказані типи запитів і якщо дозволяє пам'ять, значення варто збільшити. Велике значення змінної стану Sort_merge_passes вказує на необхідність збільшення sort_buffer_size. Також варто перевірити швидкість виконання запитів виду SELECT * FROM table ORDER BY name DESC на великих таблицях, можливо збільшення буфера лише уповільнить роботу (у деяких тестах це так).
  • table_cache (table_open_cache з версії 5.1.3) — кількість відкритих кешованих таблиць для всіх потоків. Відкриттяфайл таблиці може бути досить ресурсомісткою операцією, тому краще тримати відкриті таблиці в кеші. Слід врахувати, що кожен запис у цьому кеші використовує системний дескриптор, тому, можливо, доведеться збільшувати обмеження на кількість дескрипторів (ulimit). Значення за умовчанням 64, його найкраще збільшити до загальної кількості таблиць, якщо їх кількість у допустимих межах. Змінна стан Opened_tables дозволяє відстежувати число таблиць, відкритих в обхід кешу, бажано, щоб її значення було якомога нижче.
  • tmp_table_size - максимальний розмір пам'яті, що виділяється для тимчасових таблиць, створюваних MySQL для внутрішніх потреб. Це значення також обмежується змінною max_heap_table_size , у результаті буде обрано мінімальне значення з max_heap_table_size і tmp_table_size , а інші часові таблиці будуть створюватися на диску. Значення за замовчуванням залежить від системи, спробуйте встановити його рівним 32 МБ і спостерігати за змінним станом Created_tmp_disk_tables , її значення має бути якнайменше.

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

  • innodb_additional_mem_pool_size — розмір пам'яті, що виділяється InnoDB для зберігання різних внутрішніх структур. Якщо InnoDB буде недостатньо цієї пам'яті, буде запитана пам'ять у ОС і записано попередження в лог помилок MySQL.
  • innodb_buffer_pool_size — розмір пам'яті, що виділяється InnoDB для зберігання та індексів та даних. Значення — що більше, то краще. Можна збільшувати аж до загального розміру всіх таблиць InnoDB або до 80% ОЗУ, залежно від того, що менше.
  • innodb_flush_log_at_trx_commit — має три допустимі значення: 0, 1,2. При значенні, що дорівнює0, лог скидається на диск один раз на секунду, незалежно від транзакцій, що відбуваються. При значенні, що дорівнює1, лог скидається на диск при кожній транзакції. При значенні, що дорівнює2, лог пишеться при кожній транзакції, але не скидається на диск ніколи, залишаючи це на совісті ОС. За замовчуванням використовується 1, що є найнадійнішим налаштуванням, але не найшвидшим. У загальному випадку ви можете сміливо використовувати 2, дані можуть бути втрачені лише у разі краху ОС і лише за кілька секунд (залежить від налаштувань ОС). 0 - найшвидший режим, але дані можуть бути втрачені як при краху ОС, так і при краху самого сервера MySQL (втім, дані лише за 1-2 секунди).
  • innodb_log_buffer_size - розмір буфера лога. Значення за замовчуванням 1 МБ, збільшувати його варто, якщо ви знаєте, що буде велика кількість транзакцій InnoDB або значення змінної стану Innodb_log_waits зростає. Вам навряд чи доведеться збільшувати його понад 8 МБ.
  • innodb_log_file_size - максимальний розмір одного лог-файлу. При досягненні цього розміру InnoDB буде створювати новий файл. Значення за промовчанням 5 МБ, збільшення розміру покращить продуктивність, але збільшить час відновлення даних. Встановіть це значення в діапазоні 32 МБ - 512 МБ залежно від розміру сервера (оцінивши його суб'єктивно).

Також для моніторингу роботи сервера зручно використовувати phpMyAdmin, інтерес представляють вкладки Стан і Змінні. Додатково phpMyAdmin дає поради щодо тюнінгу тих чи інших змінних залежно від параметрів роботи сервера. Під час підготовки статті крім офіційної документації та власної голови були використані такі матеріали: