Моніторинг сервера бази даних

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

Уяви вебсайт онлайн-магазину. Інформація про товари зберігається у базі даних. Для того, щоб повернути користувачу сторінку з товарами, вебзастосунок повинен зробити запит до бази даних, отримати відповідь та обробити її. Якщо застосунок отримуватиме відповідь від сервера бази даних 10 секунд, то просто не зможе повернути користувачу сторінку з товарами вчасно. З такою швидкістю роботи сайту магазину, більшість користувачів просто піде в інший онлайн-магазин.

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

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

Не залежно від підходу, потрібно знати, на які саме метрики слід звернути увагу при роботі з сервером бази даних. Їх можна розділити на чотири основні категорії:

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

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

Журнал помилок

Перевірити статус сервісу MySQL можна за допомогою наступної команди:

systemctl status mysql

Якщо сервіс не запускається, то дізнатись про причину помилки допоможе журнал помилок (error log). Якщо ти стикаєшся з некоректною або аномальною поведінкою сервера бази даних, то це перше місце, у якому слід шукати додаткову інформацію.

Error log — це файл журналу, що фіксує помилки, які виникають під час роботи сервера MySQL. У цьому файлі часто зустрічаються проблеми, що можуть виникати при запуску сервісу. Ще там можна знайти інформацію про проблеми з конфігурацією, а також попередження та інші діагностичні повідомлення. Регулярний перегляд цього журналу дозволяє проактивно реагувати на потенційні проблеми.

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

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

💡 Шлях до файлу логу помилок вказаний у параметрі log_error.

Максимальна кількість підключень

Коли ти встановлюєш з'єднання з MySQL-сервером, наприклад, з MySQL Workbench, то сервер резервує певну кількість обчислювальних ресурсів для того, щоб обслуговувати це з'єднання. Щоб не залишити без обчислювальних ресурсів компоненти системи керування базами даних, наприклад, storage engines, в MySQL існує обмеження на максимальну кількість підключень. Якщо кількість підключень перевищує максимальну, то сервер перестає приймати нові підключення. Щоб цього уникнути, потрібно знати максимальну кількість підключень до сервера та моніторити активну кількість з'єднань.

Максимальну кількість клієнтських підключень можна встановити в конфігураційному файлі MySQL:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

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

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

sudo mysql -u root 
show status like 'max_used_connections';

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

Журнал повільних запитів

Журнал повільних запитів (від англ. slow query log) потрібний для виявлення неефективних або проблемних запитів, які можуть спричиняти зниження продуктивності бази даних. Те, що запит повільний, може свідчити про те, що база даних неефективно використовує ресурси сервера бази даних. Це, своєю чергою, призводить до зниження продуктивності всіх користувачів. Виявлення та виправлення таких запитів допомагає уникнути подібних проблем. Slow query log якраз дає змогу виявити повільні запити для подальшої оптимізації продуктивності бази даних (переписування повільних запитів, створення індексів або зміну структури бази даних).

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

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

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

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

Для того, щоб більш точно налаштувати логування повільних запитів, слід розглянути ще декілька конфігураційних параметрів. Перш за все, у нас є можливість визначити, що саме ми вважаємо повільним запитом. Адже для різних баз даних це може бути різний час. Задати цей параметр можна в long_query_time в секундах. Якщо час виконання запиту буде більшим за вказане значення, він буде записаний у slow query log. Наприклад, якщо long_query_time встановлено на 2 секунди, будь-який SQL-запит, що виконується довше двох секунд, буде занесений до журналу.

Розглянемо приклад та встановимо для long_query_time значення 0. У реальному житті є сенс використовувати значення 2 або більше, інакше лог буде занадто великим і з нього важко буде зрозуміти, які запити проблемні, а які — ні:

long_query_time = 0

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

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

sudo systemctl restart mysql

Тепер під'єднаємось до сервера бази даних за допомогою MySQL-клієнту:

mysql -u mysqluser -pP@ssw0rd

Виконаємо декілька запитів:

use company; 
select * from Employees; 
exit;

Оскільки ми встановили в long_query_time значення 0, то в наш slow query log потрапили запити, які ми щойно виконали, не зважаючи на те, що виконались вони доволі швидко. Зазирнемо у файл:

sudo tail /var/log/mysql/mysql-slow.log

# Time: 2024-02-02T17:33:25.391117Z
# User@Host: mysqluser[mysqluser] @  [93.175.200.108]  Id:   796
# Query_time: 0.000122  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
select * from Employees; 
set autocommit=1;

Тут нам доступно багато корисної інформації:

Запит, який виконувався.
Час виконання запиту та інформація про користувача, що виконав запит.
Загальний час виконання запиту — query_time.
Час, який запит провів у очікуванні заблокованого об'єкта — lock_time.

Блокування — це механізм, який перевіряє, чи дані оновлюються коректно. Блокування реалізовано по-різному в різних database engines. MyISAM при оновленні рядка бази даних блокує всю таблицю. Тому, якщо хоча б один клієнт оновлює дані в таблиці, то у решти запитів буде великий lock_time відмінний від нуля, оскільки їм прийдеться чекати завершення оновлення даних. Якщо дані в такій таблиці оновлюються відносно часто, то є сенс замінити storage engine на InnoDB, яка при оновленні блокує лише рядок, який оновлюється, і при цьому ще й дозволяє його читання.

Велика кількість занадто складних транзакцій, які використовують більше однієї таблиці, також впливає на lock_time. Якщо твій додаток використовує транзакції, і в середньому lock_time досить великий відносно query_time, то варто переглянути їх необхідність. 5. rows_send та rows_examined — кількість надісланих рядків та кількість рядків, на яких перевіряли умову WHERE-запиту. Якщо ці числа відрізняються та по таблиці часто здійснюється пошук по якомусь зі стовпчиків, то створення індексу на цьому стовпці має покращити продуктивність таких запитів.

Як бачиш, slow query log дозволяє знайти багато підказок для покращення продуктивності бази даних. Проте, коли він увімкнений, error log постійно росте в розмірі. Тому коли користуєшся цим логуванням, уважно слідкуй за використанням місця на диску твого сервера та вимикай slow query log, коли він тобі не потрібен. Щоб вимкнути логування повільних запитів, достатньо закоментувати конфігураційні параметри, які ми додали на початку цього уроку та перезавантажити сервіс MySQL.