Оптимізація запитів бази даних на прикладі B2B сервісу для будівельників

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

Я роблю сервіс для управління бізнес-процесами в будівельних компаніях. З нами працює близько 3 тисяч компаній. Понад 10 тисяч людей щодня працюють із нашою системою по 4-10 годин. Вона вирішує різні завдання планування, оповіщення, попередження, валідації… Ми використовуємо PostgreSQL 9.6. У базі даних у нас близько 300 таблиць і кожної доби до неї надходить до 200 млн запитів (10 тисяч різних). У середньому у нас 3-4 тисячі запитів на секунду, в найактивніші моменти понад 10 тисяч запитів на секунду. Більшість запитів — OLAP. Додавань, модифікацій та видалень набагато менше, тобто навантаження OLTP відносно невелике. Всі ці цифри я навів, щоб ви могли оцінити масштаб нашого проекту і зрозуміти, наскільки наш досвід може бути корисним для вас.

Картина перша. Лірична

Коли ми починали розробку, то особливо не замислювалися про те, яке навантаження ляже на БД і що ми робитимемо, якщо сервер перестане витягувати. При проектуванні БД ми наслідували загальні рекомендації і намагалися не стріляти собі в ногу, але далі загальних порад на кшталт “не використовуйте патерн Entity Attribute Values ми не заходили. Проектували виходячи з принципів нормалізації уникаючи надмірності даних та не дбали про прискорення тих чи інших запитів. Як тільки прийшли перші користувачі, ми зіткнулися з проблемою продуктивності. Як завжди, ми виявилися абсолютно не готові до цього. Перші проблеми виявилися простими. Як правило, все вирішувалося додаванням нового індексу. Але настав момент, коли прості латки перестали працювати. Усвідомивши, що досвіду не вистачає і нам все складніше зрозуміти у чому причина проблем, ми найняли фахівців, які допомогли нам правильно налаштувати сервер, підключити моніторинг, показали, куди дивитися, щоб отримати статистика.

Картина друга. Статистична

Отже, у нас є близько 10 тисяч різних запитів, які виконуються на нашій БД за добу. З цих 10 тисяч є монстри, які виконуються по 2-3 млн разів із середнім часом виконання 0.1-0.3 мс і є запити із середнім часом виконання 30 секунд, які викликаються 100 разів на добу.

Оптимізувати всі 10 тисяч запитів було неможливо, тому ми вирішили розібратися з тим, куди спрямовувати зусилля, щоб підвищувати продуктивність БД правильно. Після кількох ітерацій ми почали поділяти запити на типи.

TOP запити

Це найважчі запити, які займають найбільше часу (total time). Це запити, які або дуже часто викликаються або запити, які дуже довго виконуються (довгі та часті запити оптимізували ще на перших ітераціях боротьби за швидкість). У результаті сумарно з їхньої виконання сервер витрачає найбільше часу. Причому важливо відокремлювати топ запити за загальним часом виконання та окремо за IO time. Способи оптимізації таких запитів дещо різні.

Звичайна практика всіх компаній-працювати з TOP запитами. Їх трохи, оптимізація навіть одного запиту може звільнити 5-10% ресурсів. Однак, у міру "дорослішання" проекту оптимізація TOP запитів стає все більш нетривіальним завданням. Всі прості методи вже відпрацьовані, та й "найважчий" запит забирає "всього" 3-5% ресурсів. Якщо TOP запити в сумі займають менше 30-40% часу, то швидше за все ви доклали зусиль, щоб вони працювали швидко і настав час переходити до оптимізації запитів з наступної групи.
Залишається відповісти на запитання, скільки верхніх запитів включити до цієї групи. Я зазвичай беру не менше 10, але не більше 20. Намагаюся, щоб час першого та останнього у TOP групі відрізнявся не більше ніж у 10 разів. Тобто, якщо час виконання запитів різко падає з 1 місця до 10, то беру TOP-10, якщо падіння більш плавне, то збільшую розмір групи до 15 або 20.

Середняки (medium)

Це всі запити, які йдуть одразу за TOP, за винятком останніх 5-10%. Зазвичай в оптимізації цих запитів криється можливість сильно підняти продуктивність сервера. Ці запити можуть важити до 80%. Але навіть якщо їхня частка перевалила за 50%, значить час на них поглянути більш уважно.

Хвіст (tail)

Як було сказано, ці запити йдуть наприкінці і на них йде 5-10% часу. Про них можна забути тільки якщо ви не використовуєте автоматичні засоби аналізу запитів, тоді їх оптимізація теж може дешево обійтися.

Як оцінити кожну групу?

Я використовую SQL запит, який допомагає зробити таку оцінку для PostgreSQL (впевнений, що для багатьох інших СУБД можна написати схожий запит)

SQL запит для оцінки розміру TOP-MEDIUM-TAIL груп

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
  SELECT CASE WHEN rn <= 20              THEN tt_percent ELSE 0 END AS time_top,
         CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
         CASE WHEN rn > 800              THEN tt_percent ELSE 0 END AS time_tail
  FROM (
    SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
    FROM pg_stat_statements
    ORDER BY total_time DESC
  ) AS t
)
AS ts

Результат запиту-три стовпці, кожен із яких містить відсоток часу, що йде на обробку запитів із цієї групи. Усередині запиту є два числа (у моєму випадку це 20 та 800), які відокремлює запити однієї групи від іншої.

Ось так приблизно співвідносяться частки запитів на момент початку оптимізації робіт і зараз.

З діаграми видно, частка TOP запитів різко знизилася, зате зросли “середняки”.
Спочатку в TOP запити потрапляли відверті ляпи. Згодом дитячі хвороби зникли, частка TOP запитів скорочувалася, доводилося докладати зусиль, щоб прискорити важкі запити.

Щоб отримати текст запитів, використовуємо такий запит

SELECT * FROM (
  SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
  FROM pg_stat_statements
  ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800  -- TAIL

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

• Redesign системи, наприклад, переробка логіки повідомлень на message broker замість періодичних запитів до БД
• Додавання або зміна індексів
• Переписування ORM запитів на чистий SQL
• Переписування логіки lazy підвантаження даних
• Кешування через денормалізацію даних. Наприклад, у нас є зв'язок таблиць Доставка -> Рахунок -> Запит -> Заявка. Тобто, кожна доставка пов'язана із заявкою через інші таблиці. Щоб не пов'язувати у кожному запиті всі таблиці, ми продублювали посилання заявку в таблиці Доставка.
• Кешування статичних таблиць з довідниками та рідко змінних таблиць у пам'яті програми.

Іноді зміни тягнули на значний редизайн, але давали 5-10% розвантаження системи і були виправдані. Згодом вихлоп ставав дедалі менше, а редизайн був дедалі серйозніший.

Тоді ми звернули увагу на другу групу запитів – групу середняків. У ній набагато більше запитів і здавалося, що на аналіз усієї групи піде дуже багато часу. Проте більшість запитів виявилися дуже простими для оптимізації, а багато проблем повторювалися десятки разів у різних варіаціях. Ось приклади деяких типових оптимізацій, які ми застосовували до десятків схожих запитів, і кожна група оптимізованих запитів розвантажувала БД на 3-5%.

• Замість перевірки наявності записів за допомогою COUNT та повного сканування таблиці почали використовувати EXISTS
• Позбулися DISTINCT (немає загального рецепту, але іноді можна легко його позбутися прискорюючи запит у 10-100 разів).

  Наприклад, замість запиту для вибірки всіх водіїв великою таблицею доставок (DELIVERY)

  SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
  

  зробили запит щодо порівняно невеликої таблиці PERSON

  SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM PERSON
  WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
  

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

• У багатьох випадках взагалі відмовилися від COUNT та
  замінили на розрахунок наближеного значення
• замість

  UPPER(s) LIKE JOHN%’ 
  

  використовуємо

  s ILIKE “John%”
  

Кожен конкретний запит вдавалося прискорити часом у 3-1000 разів. Незважаючи на вражаючі показники, спочатку нам здавалося, що немає сенсу в оптимізації запиту, який виконується 10 мс, входить до третьої сотні найважчих запитів і загалом навантаження на БД займає соті частки відсотка. Але застосовуючи той самий рецепт до групи однотипних запитів ми відігравали по кілька відсотків. Щоб не витрачати час на ручний перегляд усіх сотень запитів, ми написали кілька простих скриптів, які за допомогою регулярних виразів знаходили однотипні запити. У результаті автоматичний пошук груп запитів дозволив нам ще більше покращити нашу продуктивність, витративши скромні зусилля.

У результаті ми вже три роки працюємо на тому самому залозі. Середньодобове навантаження близько 30%, у піках сягає 70%. Кількість запитів, як і кількість користувачів, зросла приблизно в 10 разів. І все це завдяки постійному моніторингу цих груп запитів TOP-MEDIUM. Як тільки якийсь новий запит з'являється у групі TOP, ми його одразу аналізуємо та намагаємось прискорити. Групу MEDIUM ми щотижня переглядаємо за допомогою скриптів аналізу запитів. Якщо трапляються нові запити, які ми вже знаємо як оптимізувати, ми швидко їх змінюємо. Іноді знаходимо нові способи оптимізації, які можна застосувати одразу до кількох запитів.

За нашими прогнозами, поточний сервер витримає збільшення кількості користувачів ще в 3-5 разів. Щоправда, у нас є ще один козир у рукаві- ми досі не перевели SELECT- запити на дзеркало, як рекомендується робити. Але ми цього не робимо усвідомлено, тому що хочемо спочатку до кінця вичерпати можливості розумної оптимізації, перш ніж включати важку артилерію.
Критичний погляд на виконану роботу може підказати використати вертикальне масштабування. Купити більш потужний сервер, замість витрачати час фахівців. Сервер може коштувати не так дорого, тим більше, що ліміти вертикального масштабування у нас ще не вичерпані. Однак у 10 разів зросла лише кількість запитів. За кілька років збільшився функціонал системи і зараз різновидів запитів побільшало. Той функціонал, який був, за рахунок кешування виконується меншою кількістю запитів, до того ж ефективніших запитів. Значить, можна сміливо помножити ще на 5, щоб отримати реальний коефіцієнт прискорення. Отже, за найскромнішими підрахунками можна сказати, що прискорення склало 50 і більше разів. Вертикально розкачати сервер у 50 разів обійшлося б дорожче. Особливо враховуючи, що одного разу проведена оптимізація працює весь час, а рахунок за орендований сервер надходить щомісяця.

Джерело: habr.com