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

Как да увеличим обема на заявките към базата данни 10 пъти, без да преминаваме на по-мощен сървър и да запазим работоспособността на системата? Ще споделя как се борихме с намаляването на производителността на нашата база данни и как оптимизирахме SQL заявките, за да обслужваме колкото се може повече потребители, без да увеличаваме разходите за изчислителни ресурси.

Създавам услуга за управление на бизнес процеси в строителните компании. С нас работят около 3000 компании. Над 10 000 души ежедневно работят с нашата система по 4-10 часа. Тя решава различни задачи по планиране, уведомяване, предупреждение, валидиране… Използваме PostgreSQL 9.6. В базата данни имаме около 300 таблици и всеки ден получаваме до 200 милиона заявки (10 000 различни). В средно имаме 3000-4000 заявки в секунда, а по време на пик, повече от 10 000 заявки в секунда. По-голямата част от заявките са OLAP. Добавянията, модификациите и изтриванията са много по-малко, тоест OLTP натоварването е относително малко. Посочих всички тези цифри, за да можете да оцените мащаба на нашия проект и да разберете колко полезен може да бъде нашият опит за вас.

Първа картина. Личен

Когато започвахме разработката, не се замисляхме много за натоварването, което ще се отрази на базата данни и какво ще правим, ако сървърът спре да издържа. При проектирането на базата данни следвахме общите препоръки и се опитвахме да не си правим излишни проблеми, но не се задълбочихме много в съвети като “не използвайте патерни Entity Attribute Values не влизахме. Проектирахме на базата на принципите на нормализация, избягвайки излишъка от данни и не се притеснявахме за ускоряването на определени заявки. Както обикновено, когато първите потребители се появиха, се сблъскахме с проблеми в производителността. Както обикновено, бяхме напълно неподготвени за това. Първоначалните проблеми бяха относително прости. Обикновено се решаваха с добавянето на нов индекс. Но дойде момент, когато простите решения спряха да работят. Осъзнавайки, че опитът ни е недостатъчен и става все по-трудно да разберем в какво се състоят проблемите, наехме специалисти, които ни помогнаха да настроим правилно сървъра, да свържем мониторинг и да ни покажат къде да гледаме, за да получим статистика.

Втора картина. Статистическа

Така имаме около 10 хиляди различни запитвания, които се изпълняват на нашата база данни за ден. От тези 10 хиляди има 'монстри', които се изпълняват по 2-3 милиона пъти със средно време за изпълнение от 0.1-0.3 мс, и запитвания със средно време за изпълнение от 30 секунди, които се извикват 100 пъти на ден.

Оптимизирането на всичките 10 хиляди запитвания не беше възможно, затова решихме да разберем къде да насочим усилията си, за да увеличим производителността на базата данни по правилен начин. След няколко итерации започнахме да разделяме запитванията на типове.

ТОП запитвания

Това са най-тежките запитвания, които отнемат най-много време (общо време). Това са запитвания, които или се извикват много често, или запитвания, които се изпълняват много дълго (дългите и чести запитвания бяха оптимизирани още в ранните итерации на борба за скорост). В крайна сметка сървърът отделя най-много време за тяхното изпълнение. Важно е да се отделят топ запитванията по общо време на изпълнение и отделно по IO време. Начините за оптимизация на такива запитвания са малко различни.

Обичайна практика за всички компании - работа с ТОП запитвания. Те са малко, оптимизацията дори на едно запитване може да освободи 5-10% ресурси. Въпреки това, с „узряването“ на проекта, оптимизацията на ТОП запитванията става все по-нетривиална задача. Всички прости методи вече са били изпълнени, а най-тежкото запитване отнема „само“ 3-5% ресурси. Ако ТОП запитванията заедно заемат по-малко от 30-40% от времето, то вероятно вече сте положили усилия да работят бързо и е време да преминете към оптимизация на запитванията от следващата група.
Остава да отговорим на въпроса колко горни запитвания да включим в тази група. Обикновено вземам не по-малко от 10, но не повече от 20. Опитвам се времето на първото и последното в ТОП групата да се различава не повече от 10 пъти. Тоест, ако времето за изпълнение на запитванията рязко пада от 1-во на 10-то място, вземам ТОП-10; ако падането е по-плавно, увеличавам размера на групата до 15 или 20.

Средняки (medium)

Това са всички запитвания, които идват веднага след ТОП, с изключение на последните 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) FROM ( SELECT CASE WHEN rn  20 AND 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 AND rn  800  -- TAIL

Ето списък на най-често използваните техники, които ни помогнаха да ускорим TOP заявките:

• Редизайн на системата, например преработка на логиката на известията към message broker вместо периодични заявки към БД.
• Добавяне или промяна на индекси.
• Преписване на ORM заявки с чист SQL.
• Преписване на логиката за лениво зареждане на данни.
• Кеширане чрез денормализация на данните. Например имаме връзка между таблиците Доставка -> Счет -> Запитване -> Заявка. Тоест всяка доставка е свързана със заявка през други таблици. За да не свързваме всички таблици в всяка заявка, дублирахме връзката към заявката в таблицата Доставка.
• Кеширане на статични таблици със справочници и рядко променящи се таблици в паметта на програмата.

Понякога промените водеха до значителни редизайни, но даваха 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%’ 
  

  VPS KVM

  s ILIKE “John%”
  

Всеки конкретен запит успявало да се ускори между 3-1000 пъти. Въпреки впечатляващите показатели, в началото ни се струваше, че няма смисъл в оптимизацията на запитване, което се изпълнява за 10 ms, попада в третата стотинка на най-тежките запитвания и в общото време на натоварване на БД заема стотни части от процента. Но прилагайки едно и също решение към група еднотипни запитвания, генерираме няколко процента. За да не губим време в ръчно преглеждане на всички стотици запитвания, написахме няколко прости скрипта, които с помощта на регулярни изрази откриваха еднотипни запитвания. В крайна сметка автоматичното търсене на групи запитвания ни позволи да подобрим производителността си още повече, използвайки скромни усилия.

В резултат ние вече три години работим на едно и също оборудване. Среднодневната натовареност е около 30%, а при пикове достига до 70%. Броят на заявките и броят на потребителите нараснаха около десет пъти. И всичко това благодарение на постоянния мониторинг на тези групи заявки TOP-MEDIUM. Веднага щом се появи нова заявка в групата TOP, я анализираме и се опитваме да я ускорим. Групата MEDIUM преглеждаме веднъж седмично с помощта на скриптове за анализ на заявки. Ако попаднат нови заявки, които вече знаем как да оптимизираме, бързо ги променяме. Понякога намираме нови начини за оптимизация, които можем да приложим веднага на няколко заявки.

Според нашите прогнози текущият сървър ще издържи увеличение на броя на потребителите още 3-5 пъти. Вярно е, че имаме още един коз в ръкава - досега не сме преместили SELECT-заявките на огледалото, както е препоръчително. Но не го правим умишлено, тъй като искаме първо да изчерпим възможностите на "умната" оптимизация, преди да включим "тежката артилерия".
Критичният поглед върху извършената работа може да подскаже, че трябва да използваме вертикално мащабиране. Да купим по-мощен сървър вместо да губим времето на специалистите. Сървърът може да не е толкова скъп, особено след като лимитите за вертикално мащабиране у нас все още не са изчерпани. Обаче броят на заявките е нараснал само десет пъти. През последните няколко години функционалността на системата се увеличи и сега разновидностите на заявките станаха повече. Функционалността, която беше, чрез кеширане се изпълнява с по-малък брой заявки и освен това с по-ефективни заявки. Значи, може спокойно да умножим още 5, за да получим реалния коефициент на ускорение. И така, по най-скромните изчисления, можем да кажем, че ускорението е било 50 и повече пъти. Вертикалното надграждане на сървъра 50 пъти би струвало повече. Особено като се има предвид, че веднъж извършената оптимизация работи постоянно, докато сметката за наетия сървър идва всеки месец.

Източник: habr.com