Optimering av databasfrågor med hjälp av exemplet på en B2B-tjänst för byggare

Hur ökar man 10 gånger antalet frågor till databasen utan att flytta till en mer produktiv server och bibehåller systemfunktionalitet? Jag kommer att berätta hur vi hanterade nedgången i vår databas prestanda, hur vi optimerade SQL-frågor för att betjäna så många användare som möjligt och inte öka kostnaderna för datorresurser.

Jag gör en tjänst för att hantera affärsprocesser i byggföretag. Cirka 3 tusen företag arbetar med oss. Mer än 10 tusen människor arbetar med vårt system varje dag i 4-10 timmar. Det löser olika problem med planering, avisering, varning, validering... Vi använder PostgreSQL 9.6. Vi har cirka 300 tabeller i databasen och upp till 200 miljoner förfrågningar (10 tusen olika) tas emot varje dag. I genomsnitt har vi 3-4 tusen förfrågningar per sekund, vid de mest aktiva ögonblicken mer än 10 tusen förfrågningar per sekund. De flesta av frågorna är OLAP. Det finns mycket färre tillägg, ändringar och raderingar, vilket betyder att OLTP-belastningen är relativt lätt. Jag angav alla dessa siffror så att du kan bedöma omfattningen av vårt projekt och förstå hur användbar vår erfarenhet kan vara för dig.

Bild ett. Lyrisk

När vi började utveckla tänkte vi inte riktigt på vilken typ av belastning som skulle falla på databasen och vad vi skulle göra om servern slutade dra. När vi utformade databasen följde vi allmänna rekommendationer och försökte att inte skjuta oss själva i foten, men gick längre än allmänna råd som "använd inte mönstret Entitetsattributvärden vi gick inte in. Vi designade utifrån principerna för normalisering, undviker dataredundans och brydde oss inte om att snabba upp vissa frågor. Så snart de första användarna kom, stötte vi på ett prestandaproblem. Som vanligt var vi helt oförberedda på detta. De första problemen visade sig vara enkla. Som regel löstes allt genom att lägga till ett nytt index. Men det kom en tid då enkla lappar slutade fungera. När vi insåg att vi saknar erfarenhet och att det blir allt svårare för oss att förstå vad som orsakar problemen, anlitade vi specialister som hjälpte oss att ställa in servern korrekt, koppla upp övervakningen och visade oss var vi skulle leta statistik.

Bild två. Statistisk

Så vi har cirka 10 tusen olika frågor som exekveras på vår databas per dag. Av dessa 10 tusen finns det monster som exekveras 2-3 miljoner gånger med en genomsnittlig exekveringstid på 0.1-0.3 ms, och det finns frågor med en genomsnittlig exekveringstid på 30 sekunder som anropas 100 gånger om dagen.

Det var inte möjligt att optimera alla 10 tusen frågor, så vi bestämde oss för att ta reda på vart vi skulle rikta våra ansträngningar för att förbättra databasens prestanda korrekt. Efter flera iterationer började vi dela in förfrågningar i typer.

TOP förfrågningar

Det här är de tyngsta frågorna som tar mest tid (total tid). Det här är frågor som antingen anropas väldigt ofta eller frågor som tar väldigt lång tid att köra (långa och frekventa frågor optimerades i de första iterationerna av kampen om hastighet). Som ett resultat av detta lägger servern mest tid på att köra dem. Dessutom är det viktigt att separera toppförfrågningar efter total exekveringstid och separat efter IO-tid. Metoderna för att optimera sådana frågor är något annorlunda.

Vanlig praxis för alla företag är att arbeta med TOP-förfrågningar. Det finns få av dem; optimering av ens en fråga kan frigöra 5-10 % av resurserna. Men när projektet mognar blir optimering av TOP-frågor en allt mer icke-trivial uppgift. Alla enkla metoder har redan utarbetats, och den mest "tunga" begäran tar "bara" 3-5% av resurserna. Om TOP-frågor totalt tar mindre än 30-40 % av tiden, har du troligen redan ansträngt dig för att få dem att fungera snabbt och det är dags att gå vidare till att optimera frågor från nästa grupp.
Det återstår att svara på frågan om hur många toppfrågor som ska ingå i denna grupp. Jag brukar ta minst 10, men inte mer än 20. Jag försöker se till att tiden för första och sista i TOP-gruppen inte skiljer sig mer än 10 gånger. Det vill säga, om exekveringstiden för frågan sjunker kraftigt från 1:a plats till 10:e, så tar jag TOP-10, om minskningen är mer gradvis ökar jag gruppstorleken till 15 eller 20.

Mellanbönder

Dessa är alla förfrågningar som kommer direkt efter TOP, med undantag för de sista 5-10 %. Vanligtvis, i optimering av dessa frågor ligger möjligheten att kraftigt öka serverns prestanda. Dessa förfrågningar kan väga upp till 80 %. Men även om deras andel har överskridit 50 %, är det dags att titta på dem mer noggrant.

Svans

Som nämnts kommer dessa frågor i slutet och tar 5-10 % av tiden. Du kan bara glömma dem om du inte använder automatiska frågeanalysverktyg, då kan det också vara billigt att optimera dem.

Hur utvärderar man varje grupp?

Jag använder en SQL-fråga som hjälper till att göra en sådan bedömning för PostgreSQL (jag är säker på att en liknande fråga kan skrivas för många andra DBMS)

SQL-fråga för att uppskatta storleken på TOP-MEDIUM-TAIL-grupper

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

Resultatet av frågan är tre kolumner, som var och en innehåller den procentandel av tiden det tar att bearbeta frågor från denna grupp. Inuti förfrågan finns två nummer (i mitt fall är det 20 och 800) som skiljer förfrågningar från en grupp från en annan.

Så här jämförs andelen förfrågningar ungefär vid den tidpunkt då optimeringsarbetet började och nu.

Diagrammet visar att andelen TOP-förfrågningar har minskat kraftigt, men "mellanbönderna" har ökat.
Till en början inkluderade TOP-förfrågningarna uppenbara misstag. Med tiden försvann barnsjukdomarna, andelen TOP-förfrågningar minskade och allt fler ansträngningar måste göras för att snabba på svåra förfrågningar.

För att få texten till förfrågningar använder vi följande begäran

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

Här är en lista över de mest använda teknikerna som hjälpte oss att påskynda TOP-frågorna:

• Omdesign av systemet, till exempel omarbetning av meddelandelogiken med hjälp av en meddelandeförmedlare istället för periodiska frågor till databasen
• Lägga till eller ändra index
• Omskrivning av ORM-frågor till ren SQL
• Omskrivning av lata dataladdningslogik
• Cachning genom datadenormalisering. Vi har till exempel en tabellkoppling Leverans -> Faktura -> Begäran -> Ansökan. Det vill säga att varje leverans är associerad med en applikation genom andra tabeller. För att inte länka alla tabeller i varje förfrågan duplicerade vi länken till förfrågan i leveranstabellen.
• Cacha statiska tabeller med referensböcker och sällan ändra tabeller i programminnet.

Ibland uppgick ändringarna till en imponerande omdesign, men de gav 5-10% av systembelastningen och var motiverade. Med tiden blev avgaserna mindre och mindre, och mer och mer seriös omkonstruktion krävdes.

Sedan vände vi vår uppmärksamhet mot den andra gruppen av förfrågningar - gruppen mellanbönder. Det finns många fler frågor i den och det verkade som att det skulle ta mycket tid att analysera hela gruppen. De flesta frågor visade sig dock vara mycket enkla att optimera, och många problem upprepades dussintals gånger i olika varianter. Här är exempel på några typiska optimeringar som vi tillämpade på dussintals liknande frågor och varje grupp av optimerade frågor lastade av databasen med 3-5 %.

• Istället för att kontrollera förekomsten av poster med COUNT och en fullständig tabellsökning, började EXISTS användas
• Blev av med DISTINCT (det finns inget allmänt recept, men ibland kan du enkelt bli av med det genom att snabba upp förfrågan med 10-100 gånger).

  Till exempel, istället för en fråga för att välja alla förare från en stor tabell över leveranser (DELIVERY)

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

  gjorde en fråga på en relativt liten bordsPERSON

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

  Det verkar som om vi använde en korrelerad underfråga, men den ger en hastighet på mer än 10 gånger.

• I många fall övergavs COUNT helt och hållet och
  ersätts med beräkning av ungefärligt värde
• istället för

  UPPER(s) LIKE JOHN%’ 
  

  använda

  s ILIKE “John%”
  

Varje specifik begäran snabbades ibland upp med 3-1000 gånger. Trots den imponerande prestandan verkade det först för oss som att det inte var någon mening med att optimera en fråga som tar 10 ms att slutföra, är en av de 3:e hundra tyngsta frågorna och tar upp hundradelar av en procent av den totala databasens laddningstid. Men genom att tillämpa samma recept på en grupp frågor av samma typ fick vi tillbaka några procent. För att inte slösa tid på att manuellt granska alla hundratals frågor skrev vi flera enkla skript som använde reguljära uttryck för att hitta frågor av samma typ. Som ett resultat av den automatiska sökningen av sökgrupper kunde vi förbättra vårt resultat ytterligare med blygsam ansträngning.

Som ett resultat har vi arbetat med samma hårdvara i tre år nu. Den genomsnittliga dagliga belastningen är cirka 30%, i toppar når den 70%. Antalet förfrågningar, liksom antalet användare, har ökat cirka 10 gånger. Och allt detta tack vare den ständiga övervakningen av samma grupper av TOP-MEDIUM-förfrågningar. Så fort en ny begäran dyker upp i TOP-gruppen analyserar vi den omedelbart och försöker påskynda den. Vi granskar MEDIUM-gruppen en gång i veckan med hjälp av frågeanalysskript. Om vi ​​stöter på nya frågor som vi redan vet hur vi ska optimera ändrar vi dem snabbt. Ibland hittar vi nya optimeringsmetoder som kan appliceras på flera frågor samtidigt.

Enligt våra prognoser kommer den nuvarande servern att klara en ökning av antalet användare med ytterligare 3-5 gånger. Det är sant att vi har ett ess till i rockärmen - vi har fortfarande inte överfört SELECT-frågor till spegeln, vilket rekommenderas. Men vi gör inte detta medvetet, för vi vill först helt uttömma möjligheterna till "smart" optimering innan vi slår på det "tunga artilleriet".
En kritisk titt på det utförda arbetet kan föreslå att man använder vertikal skalning. Köp en kraftfullare server istället för att slösa tid på specialister. Servern kanske inte kostar så mycket, speciellt eftersom vi ännu inte har uttömt gränserna för vertikal skalning. Men bara antalet förfrågningar ökade 10 gånger. Under flera år har funktionaliteten i systemet ökat och nu finns det fler typer av förfrågningar. Tack vare caching utförs den funktionalitet som fanns i färre förfrågningar och mer effektiva förfrågningar. Det betyder att du säkert kan multiplicera med ytterligare 5 för att få den verkliga accelerationskoefficienten. Så, enligt de mest konservativa uppskattningarna, kan vi säga att accelerationen var 50 gånger eller mer. Att svänga en server vertikalt skulle kosta 50 gånger mer. Speciellt med tanke på att när optimeringen väl är genomförd fungerar den hela tiden, och räkningen för den hyrda servern kommer varje månad.

Källa: will.com