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

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

Правя услуга за управление на бизнес процеси в строителни фирми. Около 3 хиляди компании работят с нас. Повече от 10 хиляди души работят с нашата система всеки ден в продължение на 4-10 часа. Решава различни проблеми на планиране, уведомяване, предупреждение, валидиране... Използваме PostgreSQL 9.6. Имаме около 300 таблици в базата данни и всеки ден се получават до 200 милиона заявки (10 хиляди различни). Средно имаме 3-4 хиляди заявки в секунда, в най-активните моменти над 10 хиляди заявки в секунда. Повечето от заявките са OLAP. Има много по-малко допълнения, модификации и изтривания, което означава, че OLTP натоварването е сравнително малко. Предоставих всички тези числа, за да можете да оцените мащаба на нашия проект и да разберете колко полезен може да бъде нашият опит за вас.

Снимка едно. лиричен

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

Снимка две. Статистически

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

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

ТОП заявки

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

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

Средни селяни

Това са всички заявки, които идват веднага след ТОП, с изключение на последните 5-10%. Обикновено в оптимизирането на тези заявки се крие възможността за значително увеличаване на производителността на сървъра. Тези искания могат да тежат до 80%. Но дори ако техният дял е надхвърлил 50%, тогава е време да ги разгледаме по-внимателно.

Опашка

Както споменахме, тези заявки идват в края и отнемат 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), които разделят заявките от една група от друга.

Ето как грубо се сравняват дяловете на заявките към момента на започване на работата по оптимизацията и сега.

Диаграмата показва, че делът на ТОП заявките рязко е намалял, но „средните селяни“ са се увеличили.
Първоначално ТОП заявките включваха груби гафове. С течение на времето детските болести изчезнаха, делът на ТОП заявките намаля и трябваше да се положат все повече усилия за ускоряване на трудните заявки.

За да получим текста на заявките, използваме следната заявка

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

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

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

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

След това обърнахме внимание на втората група искания - групата на средните селяни. В него има много повече заявки и изглеждаше, че анализът на цялата група ще отнеме много време. Повечето заявки обаче се оказаха много лесни за оптимизиране и много проблеми бяха повторени десетки пъти в различни варианти. Ето примери за някои типични оптимизации, които приложихме към десетки подобни заявки и всяка група оптимизирани заявки разтовари базата данни с 3-5%.

• Вместо проверка за наличие на записи с помощта на COUNT и пълно сканиране на таблица, започна да се използва EXISTS
• Отървах се от DISTINCT (няма обща рецепта, но понякога можете лесно да се отървете от него, като ускорите заявката с 10-100 пъти).

  Например, вместо заявка за избор на всички драйвери от голяма таблица с доставки (ДОСТАВКА)

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

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

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

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