Оптимизация на заявките в базата данни на примера на 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 000 различни заявки, които се изпълняват на нашата база данни на ден. От тези 10 000, има монстри, които се изпълняват 2-3 милиона пъти със средно време за изпълнение от 0.1-0.3 мс и има заявки със средно време за изпълнение от 30 секунди, които се извикват 100 пъти на ден.

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

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) 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
• Преписване на логиката за lazy зареждане на данни
• Кеширане чрез денормализация на данните. Например, имаме връзка между таблиците Доставка -> Счет -> Заявка -> Заявка. Тоест всяка доставка е свързана със заявка чрез други таблици. За да не свързваме всички таблици във всяка заявка, дублирахме връзката към заявката в таблицата Доставка.
• Кеширането на статични таблици със справочници и рядко променящи се таблици в паметта на програмата.

Понякога промените изискваха значителен redesign, но предоставяха 5-10% освобождаване на системата и бяха оправдани. С времето резултатите ставаха все по-малко, а необходимостта от redesign - все по-сериозна.

Тогава обърнахме внимание на втората група запитвания - групата на средняците. В нея имаше много повече запитвания и изглеждаше, че анализът на цялата група ще отнеме значително време. Въпреки това, повечето запитвания се оказаха много прости за оптимизация, а много проблеми се повтаряха десетки пъти в различни варианти. Ето примери на някои типични оптимизации, които прилагахме към десетки подобни запитвания и всяка група оптимизирани запитвания освобождаваше базата данни с 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 мс, влиза в третата стотинa на най-тежките запитвания и по време на натоварването на БД заема стотни части от процента. Но прилагането на един и същи рецепт към група еднотипни запитвания ни позволяваше да спечелим няколко процента. За да не губим време за ръчен преглед на всички стотици запитвания, написахме няколко лесни скрипта, които с помощта на регулярни изрази намираха еднотипни запитвания. В резултат на автоматичния търсене на групи запитвания успяхме да подобрим нашата производителност още повече, с минимални усилия.

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

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

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