Adatbázis-lekérdezések optimalizálása egy B2B szolgáltatás példájával az építők számára

Hogyan lehet tízszeresére növelni az adatbázisba irányuló lekérdezések számát anélkül, hogy egy produktívabb kiszolgálóra költöznénk, és fenntartani a rendszer funkcionalitását? Elmondom, hogyan kezeltük adatbázisunk teljesítményének csökkenését, hogyan optimalizáltuk az SQL lekérdezéseket, hogy minél több felhasználót kiszolgáljanak, és ne növeljék a számítási erőforrások költségeit.

Építőipari cégek üzleti folyamatainak menedzselésére vállalok szolgáltatást. Körülbelül 3 ezer cég dolgozik velünk. Naponta több mint 10 ezren dolgoznak rendszerünkkel 4-10 órát. Különféle tervezési, értesítési, figyelmeztetési, érvényesítési problémákat old meg... PostgreSQL 9.6-ot használunk. Körülbelül 300 tábla van az adatbázisban, és naponta akár 200 millió lekérdezés (10 ezer különböző) érkezik. Átlagosan másodpercenként 3-4 ezer, a legaktívabb pillanatokban több mint 10 ezer kérésünk van másodpercenként. A legtöbb lekérdezés OLAP. Sokkal kevesebb a kiegészítés, módosítás és törlés, ami azt jelenti, hogy az OLTP-terhelés viszonylag csekély. Mindezeket a számokat azért adtam meg, hogy felmérhesse projektünk mértékét, és megértse, mennyire hasznosak lehetnek a tapasztalataink az Ön számára.

Kép egyet. Lírai

Amikor elkezdtük a fejlesztést, nem igazán gondolkodtunk azon, hogy milyen terhelés nehezedik az adatbázisra, és mit csinálunk, ha a szerver leállítja a húzást. Az adatbázis kialakítása során általános ajánlásokat követtünk, és igyekeztünk nem lábon lőni magunkat, de túlléptünk az általános tanácsokon, mint például „ne használd a mintát Entitásattribútumértékek nem mentünk be. A normalizálás elve alapján terveztünk, elkerülve az adatredundanciát, és nem törődtünk bizonyos lekérdezések felgyorsításával. Amint az első felhasználók megérkeztek, teljesítményproblémába ütköztünk. Erre szokásunkhoz híven teljesen felkészületlenek voltunk. Az első problémák egyszerűnek bizonyultak. Általában mindent egy új index hozzáadásával oldottak meg. De eljött az idő, amikor az egyszerű javítások leálltak. Felismertük, hogy nincs tapasztalatunk, és egyre nehezebb megértenünk, mi okozza a problémákat, ezért szakembereket fogadtunk fel, akik segítettek a szerver helyes beállításában, a csatlakoztatás megfigyelésében, és megmutatták, hol keressünk. statisztika.

Két kép. Statisztikai

Tehát körülbelül 10 ezer különböző lekérdezésünk van naponta, ami az adatbázisunkban fut le. Ebből a 10 ezerből vannak 2-3 milliószor végrehajtott szörnyek, átlagosan 0.1-0.3 ms-os végrehajtási idővel, és vannak olyan lekérdezések, amelyek átlagos végrehajtási ideje 30 másodperc, amelyeket naponta 100-szor hívnak meg.

Nem sikerült mind a 10 ezer lekérdezést optimalizálni, ezért úgy döntöttünk, hogy kitaláljuk, hová fordítsuk erőfeszítéseinket az adatbázis teljesítményének megfelelő javítása érdekében. Több iteráció után elkezdtük a kéréseket típusokra bontani.

TOP kérések

Ezek a legnehezebb lekérdezések, amelyek a legtöbb időt (teljes időt) veszik igénybe. Ezek olyan lekérdezések, amelyeket vagy nagyon gyakran hívnak meg, vagy olyan lekérdezések, amelyek végrehajtása nagyon hosszú ideig tart (a hosszú és gyakori lekérdezéseket a sebességért folytatott küzdelem első iterációiban optimalizálták). Ennek eredményeként a szerver a legtöbb időt a végrehajtásukra fordítja. Ezenkívül fontos, hogy a legfontosabb kéréseket a teljes végrehajtási idő és az IO idő szerint különítse el. Az ilyen lekérdezések optimalizálásának módszerei kissé eltérnek.

Minden cégnél bevett gyakorlat, hogy TOP kérésekkel dolgoznak. Kevés van belőlük, egyetlen lekérdezés optimalizálásával az erőforrások 5-10%-a szabadul fel. Ahogy azonban a projekt érik, a TOP lekérdezések optimalizálása egyre inkább nem triviális feladattá válik. Az összes egyszerű módszert már kidolgozták, és a „legsúlyosabb” kérés „csak” az erőforrások 3-5%-át veszi igénybe. Ha a TOP lekérdezések összességében az idő 30-40%-ánál kevesebbet vesznek igénybe, akkor valószínűleg már tett erőfeszítéseket a gyors működés érdekében, és ideje továbblépni a következő csoportból származó lekérdezések optimalizálására.
Továbbra is meg kell válaszolni azt a kérdést, hogy hány legnépszerűbb lekérdezés szerepeljen ebben a csoportban. Általában legalább 10-et veszek be, de legfeljebb 20-at. Arra törekszem, hogy a TOP csoport első és utolsó ideje legfeljebb 10-szer térjen el egymástól. Vagyis ha a lekérdezés végrehajtási ideje meredeken leesik az 1. helyről a 10. helyre, akkor a TOP-10-et veszem, ha fokozatosabb a visszaesés, akkor 15-re vagy 20-ra növelem a csoportlétszámot.

Középparasztok

Ezek mind olyan kérések, amelyek közvetlenül a TOP után érkeznek, az utolsó 5-10% kivételével. Általában ezeknek a lekérdezéseknek az optimalizálása rejlik a lehetőség a szerver teljesítményének jelentős növelésére. Ezek a kérések akár 80%-ot is elérhetnek. De még ha arányuk meghaladta az 50%-ot, ideje alaposabban megvizsgálni őket.

Farok

Mint említettük, ezek a lekérdezések a végén jönnek, és az idő 5-10%-át igénybe veszik. Ezeket csak akkor felejtheti el, ha nem használ automatikus lekérdezéselemző eszközöket, akkor az optimalizálásuk is olcsó lehet.

Hogyan értékeljük az egyes csoportokat?

SQL lekérdezést használok, amely segít a PostgreSQL ilyen értékelésében (biztos vagyok benne, hogy sok más DBMS-hez hasonló lekérdezés írható)

SQL lekérdezés a TOP-MEDIUM-TAIL csoportok méretének becsléséhez

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

A lekérdezés eredménye három oszlop, amelyek mindegyike tartalmazza a csoportból származó lekérdezések feldolgozásához szükséges idő százalékos arányát. A kérésben két szám található (az én esetemben 20 és 800), amelyek elválasztják az egyik csoporttól származó kérelmeket a másiktól.

Körülbelül így viszonyul a kérések aránya az optimalizálási munka megkezdésekor és most.

Az ábra azt mutatja, hogy a TOP kérések aránya meredeken csökkent, de a „középparasztok” száma nőtt.
Eleinte a TOP kérések között nyilvánvaló baklövések szerepeltek. Idővel megszűntek a gyermekbetegségek, csökkent a TOP-os kérések aránya, és egyre több erőfeszítést kellett tenni a nehéz kérések felgyorsítására.

A kérések szövegének lekéréséhez a következő kérést használjuk

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

Itt található a leggyakrabban használt technikák listája, amelyek segítettek felgyorsítani a TOP lekérdezéseket:

• A rendszer újratervezése, például az értesítési logika átdolgozása üzenetközvetítő segítségével az adatbázis rendszeres lekérdezése helyett
• Indexek hozzáadása vagy módosítása
• ORM-lekérdezések átírása tiszta SQL-be
• Lusta adatbetöltési logika újraírása
• Gyorsítótár az adatok denormalizálásával. Például van egy táblakapcsolatunk: Szállítás -> Számla -> Kérés -> Alkalmazás. Vagyis minden kézbesítés más táblákon keresztül egy alkalmazáshoz van társítva. Annak érdekében, hogy ne kapcsoljuk össze az összes táblát az egyes kérésekben, megkettőztük a kérelem hivatkozását a kézbesítési táblázatban.
• Statikus táblázatok gyorsítótárazása referenciakönyvekkel és ritkán változó táblázatok a programmemóriában.

A változtatások néha lenyűgöző újratervezést jelentettek, de a rendszerterhelés 5-10%-át biztosították, és indokoltak voltak. Idővel a kipufogó egyre kisebb lett, és egyre komolyabb átalakításra volt szükség.

Ezután figyelmünket a kérések második csoportjára, a középparasztok csoportjára fordítottuk. Sokkal több kérdés van benne, és úgy tűnt, hogy sok időt vesz igénybe a teljes csoport elemzése. A legtöbb lekérdezés azonban nagyon egyszerűen optimalizálhatónak bizonyult, és sok probléma több tucatszor megismétlődött különböző változatokban. Íme néhány példa néhány tipikus optimalizálásra, amelyeket több tucat hasonló lekérdezésre alkalmaztunk, és az optimalizált lekérdezések mindegyik csoportja 3-5%-kal tehermentesítette az adatbázist.

• Ahelyett, hogy a rekordok meglétét ellenőrizték volna a COUNT és a teljes táblázatos vizsgálat segítségével, az EXISTS használatba lépett.
• Megszabadult a DISTINCT-től (nincs általános recept, de néha könnyen megszabadulhatsz tőle a kérés 10-100-szoros gyorsításával).

  Például egy lekérdezés helyett az összes illesztőprogram kiválasztásához egy nagy szállítási táblázatból (DELIVERY)

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

  lekérdezést végzett egy viszonylag kisméretű PERSON táblán

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

  Úgy tűnik, hogy korrelált részlekérdezést használtunk, de ez több mint 10-szeres gyorsulást ad.

• Sok esetben a COUNT-ot teljesen elhagyták és
  felváltja a hozzávetőleges érték számítása
• helyett

  UPPER(s) LIKE JOHN%’ 
  

  használat

  s ILIKE “John%”
  

Az egyes kéréseket néha 3-1000-szeresre gyorsították fel. A lenyűgöző teljesítmény ellenére eleinte úgy tűnt számunkra, hogy nincs értelme optimalizálni egy olyan lekérdezést, amely 10 ms-ig tart, a 3 legnehezebb lekérdezések közé tartozik, és az adatbázis teljes betöltési idejének század százalékát veszi igénybe. De azzal, hogy ugyanazt a receptet alkalmaztuk egy azonos típusú lekérdezések csoportjára, néhány százalékot visszanyertünk. Annak érdekében, hogy ne vesztegessük az időt a több száz lekérdezés manuális áttekintésével, több egyszerű szkriptet írtunk, amelyek reguláris kifejezéseket használtak az azonos típusú lekérdezések megtalálásához. Ennek eredményeként a lekérdezések csoportjaiban végzett automatikus keresés lehetővé tette számunkra, hogy szerény erőfeszítéssel tovább javítsuk teljesítményünket.

Ennek eredményeként már három éve ugyanazon a hardveren dolgozunk. Az átlagos napi terhelés körülbelül 30%, csúcsidőben eléri a 70%-ot. A kérelmek száma, valamint a felhasználók száma hozzávetőleg 10-szeresére nőtt. És mindez a TOP-MEDIUM kérések ugyanezen csoportjainak folyamatos figyelésének köszönhetően. Amint új kérés jelenik meg a TOP csoportban, azonnal elemezzük és megpróbáljuk felgyorsítani. A MEDIUM csoportot hetente egyszer ellenőrizzük lekérdezéselemző szkriptek segítségével. Ha új lekérdezésekkel találkozunk, amelyeket már tudjuk, hogyan kell optimalizálni, gyorsan megváltoztatjuk őket. Néha találunk új optimalizálási módszereket, amelyek egyszerre több lekérdezésre is alkalmazhatók.

Előrejelzéseink szerint a jelenlegi szerver még 3-5-szörös felhasználói szám növekedést bír majd. Igaz, van még egy ászunk – még mindig nem vittük át a SELECT lekérdezéseket a tükörbe, ahogy az ajánlott. De ezt nem tudatosan tesszük, mert először szeretnénk teljesen kimeríteni az „okos” optimalizálás lehetőségeit, mielőtt bekapcsolnánk a „nehéztüzérséget”.
Az elvégzett munka kritikus áttekintése javasolhatja a függőleges skálázás használatát. Vásároljon erősebb szervert ahelyett, hogy a szakemberek idejét vesztegeti. A szerver talán nem kerül ennyibe, főleg, hogy még nem merítettük ki a vertikális skálázás határait. Azonban csak a kérelmek száma 10-szeresére nőtt. Az évek során a rendszer funkcionalitása bővült, és mostanra több fajta kérés érkezik. A gyorsítótárazásnak köszönhetően a meglévő funkciók kevesebb kéréssel és hatékonyabban teljesíthetők. Ez azt jelenti, hogy biztonságosan megszorozhatja további 5-tel, hogy megkapja a valós gyorsulási együtthatót. Tehát a legóvatosabb becslések szerint azt mondhatjuk, hogy a gyorsulás 50-szeres vagy több volt. Egy szerver függőleges lengetése 50-szer többe kerülne. Főleg, ha figyelembe vesszük, hogy ha az optimalizálás megtörtént, az folyamatosan működik, és a bérelt szerverért havonta jön a számla.

Forrás: will.com