SQL: що дивитися в лозі повільних запитів, перш ніж перекроювати сервіс

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

Порядок інший. Візьміть очікування, яке людина вже відчуває. Знайдіть запити в цьому вікні. Згрупуйте за текстом. Порівняйте Rows_examined із Rows_sent і порахуйте повтори. Лише тоді вирішуйте: індекс, один агрегувальний запит замість циклу, менша відповідь, чи робота, якій справді не місце в запиті сторінки.

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

1. Що каже один запис, якщо не лякатися шапки

У slow log MySQL шапка і сам запит. У шапці час, користувач, хост, Query_time, Lock_time, Rows_sent, Rows_examined. Потім SQL. Query_time — скільки зайняв цей запит, не скільки чекала людина біля екрана. Lock_time — шматок, який минув в очікуванні блокування. Rows_sent — скільки пішло клієнту. Rows_examined — скільки сервер перебрав, щоб це віддати.

Перший розріз — відношення двох останніх. Запит перебрав сотні тисяч рядків, щоб віддати двадцять: біда в дорозі до даних. Немає індексу, маска з відсотком на початку, функція навколо колонки, джойн, який розмножує рядки до фільтра. Це не доказ, що межа сервісу крива. Розріз лишить запит як був. Те саме відношення ви прочитаєте у двох репозиторіях.

Якщо Lock_time майже дорівнює Query_time, рядок тримає хтось інший. Новий сервіс із тими самими блокуваннями додасть мережевий стрибок. Спершу знайдіть другого писаря, потім перемальовуйте схему.

• Query_time, Lock_time, Rows_sent, Rows_examined записані біля SQL, не «я приблизно пам’ятаю».
• Rows_examined на порядки більший за Rows_sent. Доки EXPLAIN не скаже інакше, база погано добирається до рядків.
• Lock_time — шум округлення чи помітна частка Query_time.
• Таблиця й умова, на які можна показати пальцем. «Запит замовлень» — не запис. Запис — це SQL.

2. Лог мовчить про цикл, і це його властивість

long_query_time — поріг. Запит коротший за поріг у файл не потрапляє. Екран, який ганяє запит на 200 мілісекунд по разу на рядок, може тримати людину багато секунд, а лог лишиться тихим, якщо кожен запуск нижче порога.

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

На навчальній платформі фраза «платформа гальмує» нічого не давала. Цикл запитів зібрали в один агрегувальний: операція пішла з 12,41 секунди на 2,451, пам’яті стало менше на 1,5 ГБ. Повтор і був поломкою. Агрегат, який і далі перебирає не ті рядки, час би зберіг. Рефакторинг, який це пропустить, — перенесення циклу у воркер із підписом «ручка стала швидкою».

Вікно групують за текстом, викидаючи літерали, які міняються від рядка до рядка. П’ятдесят рядків, які відрізняються лише id, — один запит. Біля Query_time пишуть лічильник. Якщо біда в лічильнику, міняють на один агрегувальний запит. Якщо один запуск перебирає непристойно багато рядків, міняють індекс або умову. Розріз сервісу — третя латка, і жодна з перших двох її не вимагає.

• Секунди, які чекала людина, записані біля вікна логу. Лог цю нотатку не замінює.
• Запити згруповані за текстом. Цикл — це лічильник, не п’ятдесят аварій.
• Поріг, із яким писався лог. Не знаєте поріг — не знаєте, чого у файлі немає.
• Копія, де поріг можна опустити або прочитати запити фреймворка, щоб швидкий, але частий запит став видимий.

3. EXPLAIN на копії з обсягом, і лише потім правка

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

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

Пошукових індексів, яких не вистачало, були частиною тієї самої роботи на навчальній платформі. Середнє за тиждень навантаження CPU пішло з 82,2% до 2,75%, коли розібрали вузькі місця, включно з цими індексами. Цифра — середнє за тиждень на тій платформі. EXPLAIN її не друкує, і це не прогноз вашого CPU. Лог і план сказали, куди дивитися. CPU зрушився, бо запити перестали робити зайве, не бо сервіс перейменували.

• EXPLAIN згрупованого запиту на копії з живим обсягом лежить біля SQL.
• Ключ, яким скористались, або його відсутність, одним реченням, яке можна перечитати за тиждень.
• Новий індекс — окрема правка із заміром того самого запиту до і після. Не пачка індексів із чужої статті.
• План ви не вивчаєте, змінюючи живий сервер.

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

4. Чого slow log вам не дозволить

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

Черга доречна, коли ціна — робота, яка не повинна тримати сторінку, і повтор не повинен повторити побічний ефект. Це інше рішення, не спосіб сховати повільний запит. Спершу текст. Межа описана в нотатці коли черга на Node стоїть поруч із Laravel.

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

Лог також не покаже відповідь, яку обробник збирає вже після запиту. На тій навчальній платформі окрема критична операція пішла з 23 937 мс до 48 мс, а одна відповідь API — з 912 КБ до 2,1 КБ. Обрізка відповіді — не рядок slow log. Екрану не потрібен був решта запису. Рефакторинг, який і далі вибирає всі колонки, відвезе той самий обсяг через новий край. Прочитали лог — прочитайте, що дія віддає. Обидві цифри лишаються при своїх операціях. Жодна не означає, що платформа стала швидшою у фіксоване число разів.

І він не дозволить переписати фреймворк. Успадковані системи на продакшені ставили на ноги формою запиту, індексами й розміром відповіді. Якщо пропозиція звучить як «сервіс брудний, давайте перепишемо», спитайте текст, лічильники рядків і секунди. Брудний код, який ганяє один запит за індексом, — супровід. Не сьогоднішній інцидент.

• Чергу, кеш або розріз сервісу не пропонують, доки в згрупованого запиту немає Rows_examined, Rows_sent і лічильника.
• Розмір відповіді обробника — окремий рядок. У slow log його немає.
• Переписування сервісу, якщо воно ще варте роботи, — пізніший проєкт зі своєю причиною, не спосіб полагодити цей запит.

5. Порядок, який можна повторити на наступному інциденті

Екран або задача, на яких людина вже чекає. Секунди записуєте самі. Slow log за це вікно. Якщо секунди не сходяться, дістаєте запити, які поріг сховав. Групування за текстом SQL. У верхнього запиту — Query_time, Lock_time, Rows_sent, Rows_examined і число повторів. EXPLAIN на копії. Міняєте одне: цикл в агрегувальний запит, бракує індексу, або колонки, які були не потрібні. Ту саму операцію міряєте знову. Рядок логу лишається біля цифри.

Немає полів логу — перший крок усе ще назвати екран до будь-якого редизайну. Якщо чекають звіт у CRM, спитайте, чи належать рядки цьому клієнту: звіти без переписування платформи. Швидший запит, який віддає чужі рядки, покращенням не є.

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

Коли має сенс другий погляд

Згрупувати лог і виписати лічильники рядків можна без другого. Кличте, коли лог лежить на сервері, який команда не читає, коли для EXPLAIN потрібна копія обсягу продакшену, а її немає, або коли запит сидить у сервісі, який от-от замінять, і в плані заміни немає Rows_examined.

Надішліть екран, секунди і SQL із цими лічильниками. Сирий лог із клієнтськими значеннями не потрібен. Літерали вичистіть.

Таке читання і правка після нього — робота, яку я роблю на системах, що вже стоять на продакшені. Послуги — швидкість і backend. Роботи тримають цифру біля операції: агрегувальний запит, індекси, розмір відповіді. Якщо текст і лічильники вже є, напишіть у LinkedIn і надішліть їх. Рядки клієнтів залиште в себе.