Optimering af databaseforespørgsler ved hjælp af eksemplet med en B2B-tjeneste for bygherrer

Hvordan vokser man 10 gange antallet af forespørgsler til databasen uden at flytte til en mere produktiv server og opretholder systemfunktionalitet? Jeg vil fortælle dig, hvordan vi håndterede nedgangen i vores databases ydeevne, hvordan vi optimerede SQL-forespørgsler for at betjene så mange brugere som muligt og ikke øge omkostningerne ved computerressourcer.

Jeg laver en service til styring af forretningsprocesser i byggevirksomheder. Omkring 3 tusinde virksomheder arbejder med os. Mere end 10 tusinde mennesker arbejder med vores system hver dag i 4-10 timer. Det løser forskellige problemer med planlægning, notifikation, advarsel, validering... Vi bruger PostgreSQL 9.6. Vi har omkring 300 tabeller i databasen, og hver dag modtages op til 200 millioner forespørgsler (10 tusind forskellige) ind i den. I gennemsnit har vi 3-4 tusinde anmodninger i sekundet, på de mest aktive tidspunkter mere end 10 tusinde anmodninger i sekundet. De fleste af forespørgslerne er OLAP. Der er meget færre tilføjelser, ændringer og sletninger, hvilket betyder, at OLTP-belastningen er relativt let. Jeg har givet alle disse tal, så du kan vurdere omfanget af vores projekt og forstå, hvor nyttig vores erfaring kan være for dig.

Billede et. Lyrisk

Da vi startede udviklingen, tænkte vi ikke rigtig over, hvilken slags belastning der ville falde på databasen, og hvad vi ville gøre, hvis serveren holdt op med at trække. Ved design af databasen fulgte vi generelle anbefalinger og forsøgte ikke at skyde os selv i foden, men gik ud over generelle råd som "brug ikke mønsteret Enhedsattributværdier vi gik ikke ind. Vi designet baseret på principperne om normalisering, undgår dataredundans og var ligeglade med at fremskynde visse forespørgsler. Så snart de første brugere ankom, stødte vi på et ydeevneproblem. Som sædvanlig var vi fuldstændig uforberedte på dette. De første problemer viste sig at være enkle. Som regel blev alt løst ved at tilføje et nyt indeks. Men der kom et tidspunkt, hvor simple patches holdt op med at virke. Da vi indså, at vi mangler erfaring, og det bliver stadig sværere for os at forstå, hvad der forårsager problemerne, hyrede vi specialister, som hjalp os med at opsætte serveren korrekt, tilslutte overvågning og viste os, hvor vi skulle søge Statistikker.

Billede to. Statistisk

Så vi har omkring 10 tusinde forskellige forespørgsler, der udføres på vores database om dagen. Af disse 10 tusinde er der monstre, der udføres 2-3 millioner gange med en gennemsnitlig eksekveringstid på 0.1-0.3 ms, og der er forespørgsler med en gennemsnitlig eksekveringstid på 30 sekunder, der kaldes 100 gange om dagen.

Det var ikke muligt at optimere alle 10 tusinde forespørgsler, så vi besluttede at finde ud af, hvor vi skulle rette vores indsats for at forbedre databasens ydeevne korrekt. Efter flere iterationer begyndte vi at opdele anmodninger i typer.

TOP anmodninger

Dette er de tungeste forespørgsler, der tager mest tid (samlet tid). Det er forespørgsler, der enten kaldes meget ofte eller forespørgsler, der tager meget lang tid at udføre (lange og hyppige forespørgsler blev optimeret i de første iterationer af kampen om hastighed). Som et resultat bruger serveren mest tid på deres eksekvering. Desuden er det vigtigt at adskille topforespørgsler efter samlet eksekveringstid og separat efter IO-tid. Metoderne til at optimere sådanne forespørgsler er lidt anderledes.

Den sædvanlige praksis for alle virksomheder er at arbejde med TOP-anmodninger. Der er få af dem; optimering af selv én forespørgsel kan frigøre 5-10 % af ressourcerne. Men efterhånden som projektet modnes, bliver optimering af TOP-forespørgsler en stadig mere ikke-triviel opgave. Alle simple metoder er allerede udarbejdet, og den mest "tunge" anmodning tager "kun" 3-5% af ressourcerne. Hvis TOP-forespørgsler i alt tager mindre end 30-40 % af tiden, så har du højst sandsynligt allerede gjort en indsats for at få dem til at fungere hurtigt, og det er tid til at gå videre til at optimere forespørgsler fra den næste gruppe.
Det er tilbage at besvare spørgsmålet om, hvor mange topforespørgsler der skal inkluderes i denne gruppe. Jeg tager normalt mindst 10, men ikke mere end 20. Jeg forsøger at sikre, at tiden for den første og den sidste i TOP-gruppen ikke afviger mere end 10 gange. Det vil sige, at hvis udførelsestiden for forespørgsler falder kraftigt fra 1. plads til 10., så tager jeg TOP-10, hvis faldet er mere gradvist, så øger jeg gruppestørrelsen til 15 eller 20.

Mellembønder

Det er alle forespørgsler, der kommer umiddelbart efter TOP, med undtagelse af de sidste 5-10%. Normalt ligger i optimering af disse forespørgsler muligheden for i høj grad at øge serverydeevnen. Disse anmodninger kan veje op til 80 %. Men selvom deres andel har oversteget 50%, så er det tid til at se nærmere på dem.

Hale

Som nævnt kommer disse forespørgsler til sidst og tager 5-10% af tiden. Du kan kun glemme dem, hvis du ikke bruger automatiske forespørgselsanalyseværktøjer, så kan det også være billigt at optimere dem.

Hvordan evaluerer man hver gruppe?

Jeg bruger en SQL-forespørgsel, der hjælper med at lave en sådan vurdering for PostgreSQL (jeg er sikker på, at en lignende forespørgsel kan skrives til mange andre DBMS'er)

SQL-forespørgsel til at estimere størrelsen af ​​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 af forespørgslen er tre kolonner, som hver indeholder den procentdel af tid, det tager at behandle forespørgsler fra denne gruppe. Inde i anmodningen er der to numre (i mit tilfælde er det 20 og 800), der adskiller anmodninger fra en gruppe fra en anden.

Sådan er andelen af ​​anmodninger nogenlunde sammenlignet på det tidspunkt, hvor optimeringsarbejdet begyndte og nu.

Diagrammet viser, at andelen af ​​TOP-anmodninger er faldet kraftigt, men "mellembønderne" er steget.
Til at begynde med inkluderede TOP-anmodningerne åbenlyse bommerter. Med tiden forsvandt børnesygdomme, andelen af ​​TOP-anmodninger faldt, og der skulle gøres en større indsats for at fremskynde vanskelige anmodninger.

For at få teksten til anmodninger bruger vi følgende anmodning

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

Her er en liste over de mest almindeligt anvendte teknikker, der hjalp os med at fremskynde TOP-forespørgsler:

• Redesign af systemet, for eksempel omarbejdelse af meddelelseslogikken ved hjælp af en meddelelsesmægler i stedet for periodiske forespørgsler til databasen
• Tilføjelse eller ændring af indekser
• Omskrivning af ORM-forespørgsler til ren SQL
• Omskrivning af doven dataindlæsningslogik
• Caching gennem data denormalisering. For eksempel har vi en bordforbindelse Levering -> Faktura -> Forespørgsel -> Ansøgning. Det vil sige, at hver levering er knyttet til en applikation gennem andre tabeller. For ikke at linke alle tabeller i hver anmodning, duplikerede vi linket til anmodningen i leveringstabellen.
• Caching af statiske tabeller med opslagsbøger og sjældent ændring af tabeller i programhukommelsen.

Nogle gange udgjorde ændringerne et imponerende redesign, men de gav 5-10% af systembelastningen og var berettigede. Med tiden blev udstødningen mindre og mindre, og mere og mere seriøst redesign var påkrævet.

Så vendte vi vores opmærksomhed mod den anden gruppe af anmodninger - gruppen af ​​mellembønder. Der er mange flere forespørgsler i den, og det så ud til, at det ville tage meget tid at analysere hele gruppen. De fleste forespørgsler viste sig dog at være meget enkle at optimere, og mange problemer blev gentaget dusinvis af gange i forskellige variationer. Her er eksempler på nogle typiske optimeringer, som vi anvendte på snesevis af lignende forespørgsler, og hver gruppe af optimerede forespørgsler fjernede databasen med 3-5 %.

• I stedet for at kontrollere tilstedeværelsen af ​​poster ved hjælp af COUNT og en fuld tabelscanning, begyndte EXISTS at blive brugt
• Slip af med DISTINCT (der er ingen generel opskrift, men nogle gange kan du nemt slippe af med det ved at fremskynde anmodningen med 10-100 gange).

  For eksempel, i stedet for en forespørgsel om at vælge alle chauffører fra en stor tabel med leveringer (LEVERINGS)

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

  lavede en forespørgsel på en relativt lille bordPERSON

  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 ser ud til, at vi brugte en korreleret underforespørgsel, men den giver en speedup på mere end 10 gange.

• I mange tilfælde blev COUNT helt opgivet og
  erstattet af beregning af omtrentlig værdi
• i stedet for

  UPPER(s) LIKE JOHN%’ 
  

  brug

  s ILIKE “John%”
  

Hver specifik anmodning blev nogle gange fremskyndet med 3-1000 gange. På trods af den imponerende ydeevne så det først ud for os, at det ikke var nogen mening i at optimere en forespørgsel, der tager 10 ms at fuldføre, er en af ​​de 3. hundrede tungeste forespørgsler og fylder hundrededele af en procent af den samlede databaseindlæsningstid. Men ved at anvende den samme opskrift på en gruppe af forespørgsler af samme type, vandt vi et par procent tilbage. For ikke at spilde tid på manuelt at gennemgå alle hundredvis af forespørgsler, skrev vi flere simple scripts, der brugte regulære udtryk til at finde forespørgsler af samme type. Som følge heraf gav automatisk søgning i grupper af forespørgsler os mulighed for yderligere at forbedre vores ydeevne med en beskeden indsats.

Som følge heraf har vi arbejdet på den samme hardware i tre år nu. Den gennemsnitlige daglige belastning er omkring 30%, i spidsbelastninger når den 70%. Antallet af anmodninger, såvel som antallet af brugere, er steget cirka 10 gange. Og alt dette takket være den konstante overvågning af de samme grupper af TOP-MEDIUM-anmodninger. Så snart en ny anmodning dukker op i TOP-gruppen, analyserer vi den straks og forsøger at fremskynde den. Vi gennemgår MEDIUM-gruppen en gang om ugen ved hjælp af forespørgselsanalysescripts. Hvis vi støder på nye forespørgsler, som vi allerede ved, hvordan man optimerer, ændrer vi dem hurtigt. Nogle gange finder vi nye optimeringsmetoder, der kan anvendes på flere forespørgsler på én gang.

Ifølge vores prognoser vil den nuværende server modstå en stigning i antallet af brugere med yderligere 3-5 gange. Sandt nok har vi et es mere i ærmet - vi har stadig ikke overført SELECT-forespørgsler til spejlet, som det anbefales. Men det gør vi ikke bevidst, for vi vil først fuldstændigt udtømme mulighederne for "smart" optimering, før vi tænder for det "tunge artilleri".
Et kritisk blik på det udførte arbejde kan foreslå at bruge lodret skalering. Køb en mere kraftfuld server i stedet for at spilde tiden på specialister. Serveren koster måske ikke så meget, især da vi endnu ikke har udtømt grænserne for vertikal skalering. Det var dog kun antallet af anmodninger, der steg 10 gange. I løbet af flere år er funktionaliteten i systemet steget, og nu er der flere typer anmodninger. Takket være caching udføres den funktionalitet, der eksisterede, med færre anmodninger og mere effektive anmodninger. Det betyder, at du sikkert kan gange med yderligere 5 for at få den reelle accelerationskoefficient. Så ifølge de mest konservative skøn kan vi sige, at accelerationen var 50 gange eller mere. At svinge en server lodret vil koste 50 gange mere. Især i betragtning af, at når først optimering er gennemført, virker det hele tiden, og regningen for den lejede server kommer hver måned.

Kilde: www.habr.com