Гальмує БД mysql - Адміністрація серверів

Що робити та як вирішити проблеми з продуктивністю бази даних MySQL. В даному випадку ми не говоритимемо про оптимізацію сервера БД, а йтиметься безпосередньо про запити в БД.

Отже, має наступне, mysqltuner показує нам, що всі параметри БД налаштовані коректно і ресурси використовуються ефективно. Але все ж таки відбуваються деякі зависання. Як знайти проблемні запити

Лог повільних запитів

Для початку нам потрібно отримати статистику. Для цього БД включаємо лог повільних запитів. Для цього до файлу /etc/my.cnf додамо рядки

  • log_slow_queries змінна, що задає шлях до файлу лога
  • long_query_time мінімальний час виконання запиту, який вважатиметься повільним. Тобто. всі запити час виконання яких 2 і більше секунд потраплять у лог

Іноді має сенс включити логування запитів, у яких немає індексів

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

Далі чекаємо коли збереться статистика, чим більше часу на збір даних, тим більш актуальні дані ми отримаємо.

Обробка логу повільних запитів

Якщо у вас інтенсивно виконуються запити, і їх досить багато та повільних, то ліг може вирости до великих розмірів у кілька десятків гігабайт. Звичайно ж, у такому лозі розібратися практично не реально. При цьому лог містить запити, що повторюються. Щоб привести лог повільних запитів до читального вигляду, необхідно обробити його утилітою mk-query-digest або pt-query-digest.

Для встановлення Percona Toolkit можна скористатися командою

Далі запускаємо обробку звіту

В результаті ми отримуємо файл згрупованих запитів

У даному файлі формується інформація з топ 10запитів

По першій таблиці видно, що 1,2,3 звіти займають найбільше часу через їх тривалість і кількість запусків.

Далі у файлі слідує інформація по кожному номеру запиту з таблиці та ім'я БД в якій запит був виконаний.

Далі підключаємося до БД із першого запиту

І виконуємо план запиту, запропонований у звіті

В результаті ми отримаємо план виконання запиту.

У Вас висновок вийде абсолютно інший, але буде все теж десять рядків, ось що вони означають:

  • id– номер запиту (їх може бути кілька, якщо є підзапити)
  • select_type– тип запиту SELECT.
  • SIMPLE— Простий запит SELECT без підзапитів або UNION
  • PRIMARY– SELECT – найзовніший запит у JOIN
  • DEPENDENT SUBQUERY – підзапит, який залежить від зовнішнього запиту
  • DERIVED– SELECT є частиною підзапиту всередині FROM
  • SUBQUERY– перший SELECT у підзапиті
  • UNCACHABLE SUBQUERY– підзапит, що не кешується
  • UNION- наступний SELECT в UNION
  • DEPENDENT UNION– наступний SELECT у UNION, залежний від зовнішнього запиту
  • UNION RESULT- результат UNION
  • Table– таблиця, за якою проводиться запит
  • Type— показує, як MySQL пов'язує таблиці. Корисних полів у виведенні, оскільки може показати відсутність індексів. Значення даного поля:

    • System– таблиця має лише один рядок
    • Const– таблиця має лише один відповідний рядок, який проіндексований. Швидкий тип з'єднання.
    • Fulltext– використовується повнотекстовий індекс таблиці
    • Eq_ref- всі частиниіндекси використовуються для зв'язування Також швидкий тип для з'єднання.
    • Ref– усі відповідні рядки індексного стовпця зчитуються для кожної комбінації рядків із попередньої таблиці.
    • Ref_or_null- те саме, що і ref, але також містить рядки зі значенням null для стовпця
    • Index_merge– з'єднання використовує список індексів для отримання результуючого набору. Стовпець key виводу команди EXPLAIN міститиме список використаних індексів.
    • Unique_subquery– підзапит IN повертає лише один результат з таблиці та використовує первинний ключ.
    • Index_subquery- теж, що і Unique_subquery, але повертає більше одного результату.
    • Range– індекс, використаний для знаходження відповідного рядка у певному діапазоні, зазвичай, коли ключовий стовпець порівнюється з константою, використовуючи оператори на кшталт: BETWEEN, IN, >, >=, etc.
    • Index– сканується все дерево індексів для знаходження відповідних рядків.
    • All– Для знаходження відповідних рядків використовується сканування всієї таблиці. Це найгірше значення і говорить про те, що у Вас немає індексу.
  • Possible_keys– показує індекси, які можуть бути використані для знаходження рядків у таблиці.
  • Key– вказує на використаний індекс.
  • Key_len– довжина індексу, яку оптимізатор MySQL вибрав для використання.
  • Ref– вказуються стовпці або константи, які порівнюються з індексом, вказаним у полі key.
  • Rows– відображає кількість записів, оброблених для отримання вихідних даних. Якщо число рядків велике, то запит також потребує оптимізації.
  • Extra- міститьдодаткову інформацію, що стосується плану виконання запиту. Наприклад, значення “Using temporary”, “Using filesort” показують проблему в запиті.

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

    Якщо у запиті є GROUP BY, то MySQL завжди буде сортувати результати. Якщо порядок видаваних результатів вам не важливий, то краще позбавитися цієї операції (сортування).

    Або ж створити індекс по полю по якому відбувається сортування, а так само індекси по вторинним ключам у всіх таблицях із запиту (вторинний ключ - це поле, яке використовується в JOIN).

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

    Оптимізація продуктивності сайту та сервера, звертайтеся [email protected]