Адміністрування MySQL/MariaDB: встановлення, налаштування, оптимізація

Що ламається в адмініструванні MySQL під час зростання навантаження

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

Причина зазвичай не в залізі, а в налаштуваннях — буферний пул InnoDB менший за робочий набір, і читання йде на диск, вставки виконуються по одній транзакції замість пакетів. Симптоми розмазані по метриках, тому проблему шукають уже під час збою.

Другий клас відмов — завислі запити й блокування рядків. Тривала транзакція або зміна схеми на живій таблиці тримають рядки й метадані, черга очікувань зростає, вільні з’єднання закінчуються, і замість даних застосунок отримує тайм-аути.

Повільний журнал і профіль навантаження розбираємо інструментами на кшталт pt-query-digest із набору Percona — видно, який запит тримає блокування і кого він гальмує.

Окремий біль — відставання репліки. Поки ведуча пише пакетно, підпорядкований вузол не встигає застосовувати зміни, і контур читання віддає застарілі дані: звіти, пошук і особистий кабінет розходяться з основною базою.

Нічне резервне копіювання теж перестає вкладатися у вікно — копія знімається довше, а до ранку диск заповнений.

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

Окремо кожен пункт виглядає дрібницею, разом вони перетворюються на нічні інциденти. Розбираємо, як це закривається.

Що змінюється після налаштування: відгук, простій, відмовостійкість

Час відгуку під навантаженням — перша метрика, за якою видно ефект. Поки схему не налаштовано, пікові запити впираються в повне сканування таблиць і блокування рядків: відгук зростає ступенями, а до кінця дня база відповідає в рази повільніше, ніж уранці.

Після налаштування — індекси під фактичні шаблони запитів, вивірений розмір буферного пулу, ліміти з’єднань і розумні тайм-аути — відгук на 95-му процентилі тримається рівно й падає в кілька разів, при цьому навантаження залишається тим самим.

Окремо вимірюємо час відновлення після збою. Налаштована реплікація, регулярні резервні копії з обов’язковою перевіркою відновлення на окремому контурі й автоматичне перемикання на репліку переводять аварію зі сценарію «піднімаємо руками пів дня» у кілька хвилин.

Простій сервісу перестає бути подією тижня і вкладається в цільові показники доступності, про які домовляємося заздалегідь.

Зникає й цілий клас відмов: переповнення диска роздутими журналами, деградація на тривалих міграціях, падіння через необмежені з’єднання. Обсяг даних перестає зростати лінійно — партиціювання, політика зберігання й очищення історичних записів тримають приріст у межах.

У результаті з’являється запас за місткістю: база витримує зростання навантаження й доданий функціонал без термінової заміни сервера, а рішення про масштабування ухвалюються за метриками, а не за фактом чергового падіння.

Режими роботи бази: який варіант підходить вашому завданню

Вибір режиму — це не про потужність сервера, а про профіль навантаження і той клас відмов, який завдання готове пережити. У MySQL і MariaDB набір варіантів той самий, і на початку шляху різниця між ними майже непомітна.

Виявляється вона на піках читання, під час планового обслуговування і в момент відмови вузла — там і видно, чим платять за просту схему.

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

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

Як відбувається впровадження: від аудиту до передачі

Роботи йдуть фіксованим порядком: спочатку знімаємо факти про поточний стан, потім проєктуємо цільову конфігурацію, і лише після цього змінюємо параметри. Кожна зміна спирається на вимірювання, а не на типовий конфіг, тому результат відтворюваний.

1. Аудит бази. Розбираємо параметри сервера, структуру таблиць, швидкість зростання даних, статистику повільних запитів та історію блокувань. На виході — список вузьких місць із пріоритетом: що деградує зараз, а що проявиться при зростанні.
2. Схема розгортання. Визначаємо топологію: один вузол або первинний із репліками, як розподіляється читання, де зберігаються копії. Схему погоджуємо до встановлення — переробити її пізніше дорожче, ніж одразу розвести навантаження.
3. Налаштування під профіль навантаження. Рахуємо буфери й кеші, ліміти з’єднань, режим журналювання й скидання на диск під конкретний профіль: короткі транзакції, довгі аналітичні вибірки або змішаний потік.
4. Перевірка під навантаженням. Проганяємо сценарій, близький до робочого: піки запису, звітні вибірки, одночасні підключення. Дивимося не середні, а хвости — за ними видно, коли користувач помічає гальмування.
5. Перевірка відновлення. Розгортаємо копію на окремому вузлі й заміряємо час до робочого стану з доведенням до точки відмови. Поки відновлення не відпрацьовано наживо, резервна копія вважається неперевіреною.
6. Регламент і передача. Фіксуємо моніторинг ключових метрик, пороги алертів, порядок планових робіт і оновлення конфігурацій, проводимо навчання команди на реальних сценаріях.

Артефакти впровадження: конфігурації, схеми, регламенти

Що залишається у замовника після робіт — не лише працюючий сервер, а й набір документів, за якими його зможе супроводжувати будь-який інженер, а не тільки той, хто налаштовував.

Конфігурації кладемо в систему контролю версій з описом змін, схеми й регламенти — у спільний репозиторій документації поряд із кодом застосунку, щоб розходження між фактом і описом було видно на рев’ю.

• Файли конфігурації — основний конфіг сервера під контролем версій, із профілями під роль вузла (майстер, репліка, вузол лише для читання) і коментарями, чому параметр виставлений саме так.
• Схема розгортання — топологія вузлів і потоки даних: куди пише застосунок, як розходиться реплікація, де точка розділення читання й запису, що станеться при втраті кожного вузла.
• Політики доступу й ролі — матриця привілеїв: сервісні облікові записи з мінімально необхідним набором прав, окремі для реплікації й моніторингу, операторський обліковий запис лише для читання; секрети зберігаються в менеджері паролів, а не в конфігах.
• Регламент резервного копіювання — що і як часто знімається (повна копія плюс журнали змін), строк зберігання, ротація, обов’язкова перевірка відновлення на окремому стенді, а не «бекап є, отже працює».
• Інструкція з відновлення — покроковий порядок: зупинка запису з боку застосунку, розгортання копії, доведення до потрібної точки за журналами, перевірка цілісності та звірка ключових таблиць.
• Типові операції — зміна ведучого вузла, підключення нової репліки, зміна структури таблиць на живій базі, деградація за навантаженням: що робити і в якому порядку.
• Карта спостережуваності — які метрики знімаються, які пороги вважаються тривожними, кому і куди надходить оповіщення.

Плюс журнал змін: хто, коли і навіщо правив конфігурацію або права — без нього за півроку будь-яка правка перетворюється на археологію.

Кейс адміністрування MySQL: стабілізували базу інтернет-магазину

Проєкт — інтернет-магазин із сезонними розпродажами: у пік кошик і оформлення замовлення починали чекати базу. На MySQL крутилися і вітрина, і адмінка, і звіти маркетингу.

Частка повільних запитів до кінця дня трималася на 12-14%, аварійні перезапуски траплялися 5-6 разів на тиждень, відновлення після падіння займало близько 40 хвилин.

Що знайшли

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

Резервні копії робилися, але їх ніхто не розгортав: перевірка відновлення виявила, що частина копій непридатна.

Що змінили

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

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

Результат до і після

Частка повільних запитів — з 12-14% до 0,3%. Час відновлення — з 40 хвилин до 4. Аварійні перезапуски — п’ять-шість на тиждень, а за останній квартал нуль. Пікове навантаження розпродажу витримали двократне до попереднього максимуму без зростання часу відгуку вітрини.

Власна база чи керована: що обирати під ваш профіль

Вибір зводиться не до швидкості рушія, а до того, хто тримає експлуатацію. Власна база на своїх потужностях дає повний контроль: гілка MySQL, параметри буферів і з’єднань, плани запитів, доступ до файлів даних і логів.

Керований контур забирає патчі, резервні копії й відмовостійкість — натомість ви втрачаєте частину доступу до налаштувань і не обираєте час регламентних робіт.

Розходження видно за чотирма осями: оновлення й патчі, масштабування, зона відповідальності, трудовитрати команди.

Практичне правило: якщо навантаження передбачуване і є інженер, готовий чергувати й розбирати повільні запити, власна база окупається свободою налаштування.

Якщо продукт зростає піками, а команда невелика, керований контур знімає чергування й рутину — але масштабування впирається в ліміти провайдера, а не у вашу архітектуру.

Окрема вісь — оновлення. На своїх потужностях мінорне оновлення потребує вікна, перевірки сумісності й плану відкату; у керованому контурі його застосовують за чужим графіком.

Тому критичні до простою сервіси частіше тримають базу окремо, а аналітику й внутрішні системи віддають назовні.

Питання, які ставлять інженери перед початком робіт

Перед початком робіт інженери ставлять ті самі питання — про відмовостійкість, відкат і межі відповідальності. Нижче короткі відповіді на п’ять із них, щоб рішення спиралося на регламент, а не на обіцянки.

Візьметеся за вже розгорнуту базу?

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

Як переконатися, що копія справді відновиться?

Копія без перевірки відновлення копією не вважається. Розгортаємо її на окремому стенді, звіряємо контрольні суми таблиць і заміряємо час відновлення до погодженого цільового показника.

Хто відповідає за оновлення безпеки?

Ми відстежуємо випуск виправлень, тестуємо їх на копії та застосовуємо за погодженим графіком. Критичні патчі ставимо позачергово, але завжди з планом відкату на випадок регресу.

Що входить у передачу знань?

Залишаємо документацію з конфігурації, схеми реплікації, регламент резервного копіювання й розбір типових аварій. Передаємо все команді замовника — систему можна вести й без нас.

Обговоримо завдання: розберемо конфігурацію і повернемося з планом

Щоб узятися за адміністрування MySQL не навмання, а за фактом, нам потрібен доступ до інфраструктури й розуміння, як вона живе зараз. Збір цього списку зазвичай займає один-два дні й не потребує зупинки сервісів.

• Доступи до серверів. Окремий обліковий запис для наших інженерів, доступ до панелі провайдера, права на читання конфігурації — так кожна дія залишається в логах, і ви можете її перевірити.
• Поточна схема. Кількість вузлів, топологія реплікації, змінені параметри конфігурації, розклад резервних копій і коли їх останнього разу перевіряли відновленням.
• Профіль навантаження. Піки за годинами й днями тижня, журнал повільних запитів, найбільші таблиці, кількість одночасних з’єднань, частка операцій запису. За цими даними видно, де вузьке місце — у запитах, у схемі чи на диску.
• Вікно робіт. Коли допустимі перезапуск сервісу й перемикання реплік, який час недоступності критичний, чи є регламентне вікно обслуговування.
• Обмеження. Що заборонено змінювати, які вимоги безпеки врахувати, чи потрібен окремий погоджувач на боці вашої служби ІБ.
• Контакти й приймання. Хто погоджує зміни й залишається на зв’язку під час робіт, за якими показниками вважаємо результат: відгук на піках, відсутність відставання реплік, зростання обсягу даних без втрати швидкості.

Зібравши це, повертаємося з планом робіт за етапами, оцінкою строків і порядком відкату на кожному кроці. Опишіть завдання — почнемо з розбору конфігурації та обмежень.