Про налаштування та оптимізацію MySQL сервера
Бази даних MySQL
Прямий ефір
- Промислові термінали збору даних4.54
- Мова програмування C#2.28
- Бази даних MySQL1.14
- Автоматизація1.14
- MC 2 - Софт1.14
- Розробка на .NET CF1.14
- IT події Красноярська та Красноярського краю1.14
- MC2 - Інструкції Посібника Опис1.14
- Мобільні системи1.14
- XNA Game Studio0.00
У цій статті будуть описані різні параметри MySQL, переважно ті, що впливають на продуктивність. Для зручності всі змінні розділені за розділами (базові налаштування, обмеження, потоки налаштування, кешування запитів, таймінги, буфери, InnoDB). Спочатку уточнимо імена деяких змінних, які змінилися у версії 4 MySQL, а в мережі продовжують зустрічатися і старі та нові варіанти імен, що викликає питання.
Отже, в 4 версії ряду змінних з'явилося закінчення _size. Це стосується змінної thread_cache_size та змінних із розділу Буфери. А змінна read_buffer_size до версії 4 називалася record_buffer. Також змінна skip_external_locking із розділу Базові налаштування до версії 4 називалася skip_locking.
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 має бути якнайменше (