Оптимізація MySQL для великої БД і великого навантаження

14 січня 2016

16 січня 2017

* Під великим навантаженням мається на увазі >2GB та >2K користувачів/хв

Врахуйте, що самі творці сервісу для роботи з базами даних наголошують на тому, що, як показує досвід, шкідливо читати різні мануали, тюнінги і так далі, адже інформація в основному неправильна, автори погано розуміють що саме вони змінюють і як це впливає на роботу системи. Також свідчать що для більшості випадків найоптимальнішими будуть стандартні налаштування! Опишу власний процес налаштування і що вдалося зрозуміти власне мені. Буду вдячний, якщо хтось поправить де що не так. Почну з вихідних даних.

Ситуація

БД розміром 1980 М
Кількість одночасних користувачів на сайті > 2000 (прогнозовано ~ 8000),
з них всі проходять через головну сторінку, що є конкурентним запитом до кількох таблиць одночасно
Кількість оперативної пам'яті сервера - 32GB
Кількість процесорів (ядер) - 8

Підготовка та план дій

Думаю, що не залишуся один в припущенні, що мала ймовірність того, що стандартна конфігурація розрахована на бази з гігабайтами даних та десятками гігабайт ресурсів.
Файл /usr/share/doc/mysql-server-5.5/examples/my-innodb-heavy-4G.cnf.gz — це для найпотужніших машин, однак, як видно з назви, розмір розрахованого RAM там всього 4GB. Попри це в основному будемо спиратися на цей файл + в ньому є хоч якісь пояснення. Додатково використаємо ще 2 джерела порад: звірятимемо з згенерованою конфігурацією на сайті Percona (https://tools.percona.com/wizard - потрібна реєстрація).
Після застосування кожного параметру (рекомендується), кількох чи всіх перевірятимемо роботу сайту і запускатимемо утиліту mysqltuner (доступна через стандартний інсталятор) саме ←тією командою. Наостанок звіримо все з рекомендаціями CMS.

Основний файл, з яким ми будемо займатися, як згадувалося під час опису встановлення MySQL, знаходиться за шляхом:

nano /etc/mysql/my.cnf

Щоб не перезапускати кожного разу MySQL, так як сайт на даний момент працює, більшість змін можна вносити через консоль mysql в phpmyadmin, або запущену з терміналу за допомогою команди:

mysql -u root -p

Для цього нам потрібна команда наступного типу (значення вказуються в байтах, в файлі конфігурації можна вказувати M - MB, G - GB, тощо):

set global max_allowed_packet=16777216

Якщо все зрозуміло, то переходимо до параметрів.

Підбираємо налаштування

Варто підмітити, що ці значення будуть коригуватися, опираючись на довгострокову реакцію системи і теорію.

default-storage-engine

Щоб точно визначити обробник зберігання, після * Fine Tuning вставимо:

default-storage-engine = InnoDB

key_buffer_size

Змінна «key_buffer_size» категорії MyISAM впливає на виділення оперативної пам'яті для MySQL. Це може істотно прискорити бази даних, якщо у вас є вільна пам'ять на сервері. Розмір «key_buffer» як правило, повинний бути не більше 25% від доступної оперативної пам'яті при використанні таблиці з MyISAM, проте немає сенсу встановлювати більше, ніж загальний розмір усіх .MYI файлів. Якщо значення занадто високе, то ресурси витрачаються даремно. За замовчуванням група не виділена, мені захотілося це виправити для наочності. Значення 32М рекомендували всі джерела одноголосно. Видаляємо key_buffer, додаємо:

#
# * MyISAM
#
key_buffer_size         = 32M

myisam-recover

Percona рекомендує встановити цей параметр в значення "Буде старатися відновити пошкоджені внаслідок неправильного закриття таблиці при наступному перезапуску, зберігатиме резервну копію до відновлення, у випадку якщо таке відновлення вже раз відбулося та відновлення не буде скасовано у випадку невдалого відновлення рядка". Здається так, можете перевірити. Додамо ще число потоків відновлення (може сягати кількості процесорів у випадку коли є більше 1 індексного стовпця в таблиці.

myisam-recover   = FORCE,BACKUP
myisam_repair_threads = 1

max-allowed-packet

З міркувань безпеки зазначимо:

max-allowed-packet             = 16M
max-connect-errors             = 1000000

max_connections

Фактично мало відноситься до продуктивності, а впливає тільки на виведення помилки "занадто багато підключень". Майте на увазі, що це конкурентні запити, а не кількість користувачів в системі. Можна протестувати введену кількість утилітою siege, виконавши команду з іншої машини

siege -i -b -c 1000 -t 1M -f /path/to/list/of/urls.txt

Раджу розібратися в її роботі і проводити подібні тестування. можна відслідковувати постійно максимальну кількість з'єднань в консолі mysql: SHOW STATUS LIKE 'Max_used_connections';, щоб визначити оптимальне число. Для початку в тестових цілях поставимо відносно високе значення:

max_connections        = 1000

Тестування показало, що вже при 500 одночасних підключеннях час відповіді сервера значно зменшується. Використаємо саме таке значення

max_connections        = 500

thread_cache_size

Скільки потоків має зберігатися в кеші для повторного використання. Коли клієнт від'єднується, потоки клієнта поміщаються в кеш, якщо там не більше потоків, ніж встановлене значення thread_cache_size. Це значно зменшує кількість необхідних випадків створення потоків, якщо у вас є багато нових підключень. (Як правило, це не дасть істотного виграшу в продуктивності, якщо хороша реалізація потоків). Дехто рекомендує встановити на один більше ніж max_connections, та я не думаю що це прекрасна ідея для великих значень. Рекомендована Percona кількість 50 мені теж не сподобалася, адже краще щоб показник використання кеш-пам'яті (threads_created / Max_used_connections ) * 100 наближався до 100%. Щоб дізнатися в консолі mysql: SHOW STATUS LIKE 'threads_created';. В мене воно навіть при тестуванні не перевищувала 400, тому думаю що такого може вистачити:

thread_cache_size       = 501

table_open_cache

Кількість таблиць в кеші. Може наближатися до кількості таблиць в базі. Змінна стану Opened_tables дозволяє відстежувати число таблиць, відкритих в обхід кешу, добре коли її значення наближається до 0. CMS рекомендує наступне значення:

table_open_cache        = 512

thread_concurrency

Кількість одночасно запущених потоків. Рекомендується подвійне число ядер процесора:

thread_concurrency      = 16

query_cache_limit

максимальний розмір кешованого запиту

query_cache_limit       = 16M

query_cache_size

Тут я заплутався. Не пам'ятаю з якого дива в мене з'явилося значення 3G (згодом зрозумів, mysqltuer все радив його збільшити), my-heavy.cnf рекомендує 64М, Percona взагалі вказує вимкнути. Поставивши так, та перевіривши сайту та тюнера, трохи збільшив, що виявилося оптимальним, :

query_cache_size = 128M
query_cache_min_res_unit = 2K

innodb_buffer_pool_size

Розмір пам'яті, що виділяється InnoDB для зберігання і індексів, і даних. Чим більше значення, тим краще. Можна збільшувати аж до загального розміру всіх InnoDB таблиць або до 70% ОЗУ, залежно від того, що менше. 7G by percona - досить серйозна заявка, tuner під час дослідів одобрив 2.3, що наближається до 2G heavy.cnf, для початку десь так і залишимо, з плином часу будемо слідкувати.

innodb_buffer_pool_size = 3G

Як показала практика, коли розмір потрібного innodb_buffer_pool_size (можна опиратися на вказівки mysqltuner) перевищує розмір вказаного, сайт (БД) може зависати. На даний момент цей параметр у мене знаходиться в значенні 5G.

join_buffer_size

Якщо ми маєте джоіни без індексів, можете підняти це значення до такого, наприклад:

join_buffer_size=16M

але краще позбутися таких запитів тимчасово ввімкнувши наступний лог і відслідкувавши джерело проблеми

log-queries-not-using-indexes  = 1

timeouts

І наостанок чудовим тоном буде налаштувати таймаути для того щоб висячі з'єднання розривалися і не займали місце. Перевірте поточні значення за допомогою команди SHOW VARIABLES LIKE '%timeout%';, яка виведе приведений нижче блок і змініть значення, які відрізняються. Основними показниками є wait_timeout та interactive_timeout, які є взаємодоповнюваними і зазвичай змінюються разом. Це в якійсь мірі час життя процесів.

+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 120      |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 120      |
+----------------------------+----------+

Також

Можна спробувати додати:

tmp_table_size                 = 32M
max_heap_table_size            = 32M

[myisamchk]
key_buffer_size         = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqld_safe]
open_files_limit = 60192

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

Лишимо стандартними, вони оптимальні:

thread_stack            = 192K

Будьте уважні, на сайті Percona багато значень вказані через дефіс замість "_", краще виправити. Також через наступні параметри в мене не перезапустився MySQL:

innodb_log_buffer_size = 4M
innodb_log_file_size = 128M