Optimalisering av databasespørringer ved å bruke eksempelet på en B2B-tjeneste for byggherrer

Hvordan øke 10 ganger antallet spørringer til databasen uten å flytte til en mer produktiv server og opprettholde systemfunksjonalitet? Jeg vil fortelle deg hvordan vi taklet nedgangen i ytelsen til databasen vår, hvordan vi optimaliserte SQL-spørringer for å betjene så mange brukere som mulig og ikke øke kostnadene for dataressurser.

Jeg lager en tjeneste for styring av forretningsprosesser i byggefirmaer. Rundt 3 tusen selskaper jobber med oss. Mer enn 10 tusen mennesker jobber med systemet vårt hver dag i 4-10 timer. Det løser ulike problemer med planlegging, varsling, advarsel, validering... Vi bruker PostgreSQL 9.6. Vi har rundt 300 tabeller i databasen og opptil 200 millioner forespørsler (10 tusen forskjellige) mottas hver dag. I gjennomsnitt har vi 3-4 tusen forespørsler per sekund, på de mest aktive øyeblikkene mer enn 10 tusen forespørsler per sekund. De fleste spørsmålene er OLAP. Det er mye færre tillegg, modifikasjoner og slettinger, noe som betyr at OLTP-belastningen er relativt lett. Jeg oppga alle disse tallene slik at du kan vurdere omfanget av prosjektet vårt og forstå hvor nyttig erfaringen vår kan være for deg.

Bilde en. Lyrisk

Da vi startet utviklingen, tenkte vi egentlig ikke på hva slags belastning som ville falle på databasen og hva vi ville gjøre hvis serveren sluttet å trekke. Når vi utformet databasen, fulgte vi generelle anbefalinger og prøvde å ikke skyte oss selv i foten, men gikk utover generelle råd som "ikke bruk mønsteret Entitetsattributtverdier vi gikk ikke inn. Vi designet basert på prinsippene for normalisering, unngår dataredundans og brydde oss ikke om å øke hastigheten på visse spørringer. Så snart de første brukerne ankom, møtte vi et ytelsesproblem. Som vanlig var vi helt uforberedte på dette. De første problemene viste seg å være enkle. Som regel ble alt løst ved å legge til en ny indeks. Men det kom en tid da enkle patcher sluttet å virke. Da vi innså at vi mangler erfaring og det blir stadig vanskeligere for oss å forstå hva som forårsaker problemene, hyret vi inn spesialister som hjalp oss med å sette opp serveren riktig, koble til overvåking og viste oss hvor vi kan se for å få tak i statistikk.

Bilde to. Statistisk

Så vi har omtrent 10 tusen forskjellige spørringer som utføres på databasen vår per dag. Av disse 10 tusen er det monstre som blir henrettet 2-3 millioner ganger med en gjennomsnittlig utførelsestid på 0.1-0.3 ms, og det er spørringer med en gjennomsnittlig utførelsestid på 30 sekunder som kalles 100 ganger om dagen.

Det var ikke mulig å optimalisere alle 10 tusen spørringene, så vi bestemte oss for å finne ut hvor vi skulle rette innsatsen for å forbedre ytelsen til databasen på riktig måte. Etter flere iterasjoner begynte vi å dele opp forespørsler i typer.

TOP forespørsler

Dette er de tyngste spørringene som tar mest tid (total tid). Dette er spørringer som enten kalles veldig ofte eller spørringer som tar svært lang tid å utføre (lange og hyppige spørringer ble optimalisert i de første iterasjonene av kampen om hastighet). Som et resultat bruker serveren mest tid på utførelse. Dessuten er det viktig å skille toppforespørsler etter total utførelsestid og separat etter IO-tid. Metodene for å optimalisere slike søk er litt forskjellige.

Vanlig praksis for alle selskaper er å jobbe med TOP-forespørsler. Det er få av dem; optimalisering av selv ett søk kan frigjøre 5-10 % av ressursene. Men etter hvert som prosjektet modnes, blir optimalisering av TOP-spørringer en stadig mer ikke-triviell oppgave. Alle enkle metoder er allerede utarbeidet, og den mest "tunge" forespørselen tar "bare" 3-5% av ressursene. Hvis TOP-søk totalt tar mindre enn 30-40 % av tiden, har du sannsynligvis allerede gjort en innsats for å få dem til å fungere raskt, og det er på tide å gå videre til å optimalisere søk fra neste gruppe.
Det gjenstår å svare på spørsmålet om hvor mange toppsøk som bør inkluderes i denne gruppen. Jeg tar vanligvis minst 10, men ikke mer enn 20. Jeg prøver å sørge for at tiden for første og siste i TOP-gruppen ikke avviker mer enn 10 ganger. Det vil si at hvis utførelsestiden for spørringen synker kraftig fra 1. plass til 10., tar jeg TOP-10, hvis fallet er mer gradvis, øker jeg gruppestørrelsen til 15 eller 20.

Mellombønder

Dette er alle forespørsler som kommer rett etter TOP, med unntak av de siste 5-10 %. Vanligvis, i å optimalisere disse spørringene ligger muligheten til å øke serverytelsen betraktelig. Disse forespørslene kan veie opptil 80 %. Men selv om andelen deres har overskredet 50 %, er det på tide å se nærmere på dem.

Hale

Som nevnt kommer disse spørringene på slutten og tar 5-10 % av tiden. Du kan bare glemme dem hvis du ikke bruker automatiske spørringsanalyseverktøy, da kan det også være billig å optimalisere dem.

Hvordan evaluere hver gruppe?

Jeg bruker en SQL-spørring som hjelper til med å gjøre en slik vurdering for PostgreSQL (jeg er sikker på at en lignende spørring kan skrives for mange andre DBMS-er)

SQL-spørring for å anslå størrelsen 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 spørringen er tre kolonner, som hver inneholder prosentandelen av tiden det tar å behandle spørringer fra denne gruppen. Inne i forespørselen er det to tall (i mitt tilfelle er det 20 og 800) som skiller forespørsler fra en gruppe fra en annen.

Dette er hvordan andelene av forespørsler omtrent sammenlignes på det tidspunktet optimaliseringsarbeidet startet og nå.

Diagrammet viser at andelen TOP-forespørsler har gått kraftig ned, men "mellombøndene" har økt.
Til å begynne med inkluderte TOP-forespørslene åpenbare tabber. Over tid forsvant barnesykdommer, andelen TOP-forespørsler gikk ned, og det måtte gjøres mer og mer innsats for å få fart på vanskelige forespørsler.

For å få teksten til forespørsler bruker vi følgende forespørsel

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 brukte teknikkene som hjalp oss med å øke hastigheten på TOP-søk:

• Redesign av systemet, for eksempel omarbeiding av varslingslogikken ved å bruke en meldingsmegler i stedet for periodiske spørringer til databasen
• Legge til eller endre indekser
• Omskriving av ORM-spørringer til ren SQL
• Omskriving av lat datainnlastingslogikk
• Bufring gjennom datadenormalisering. For eksempel har vi en tabelltilkobling Levering -> Faktura -> Forespørsel -> Søknad. Det vil si at hver leveranse er knyttet til en applikasjon gjennom andre tabeller. For ikke å koble sammen alle tabellene i hver forespørsel, dupliserte vi lenken til forespørselen i Leveringstabellen.
• Bufre statiske tabeller med oppslagsverk og sjelden endre tabeller i programminnet.

Noen ganger utgjorde endringene en imponerende redesign, men de ga 5-10% av systembelastningen og var berettiget. Med tiden ble eksosen mindre og mindre, og mer og mer seriøs redesign var nødvendig.

Så vendte vi oppmerksomheten mot den andre gruppen av forespørsler - gruppen mellombønder. Det er mange flere spørsmål i den, og det så ut til at det ville ta mye tid å analysere hele gruppen. De fleste søk viste seg imidlertid å være svært enkle å optimalisere, og mange problemer ble gjentatt dusinvis av ganger i forskjellige varianter. Her er eksempler på noen typiske optimaliseringer som vi brukte på dusinvis av lignende spørringer, og hver gruppe med optimaliserte spørringer lastet ut databasen med 3-5 %.

• I stedet for å sjekke tilstedeværelsen av poster ved å bruke COUNT og en full tabellskanning, begynte EXISTS å bli brukt
• Ble kvitt DISTINCT (det er ingen generell oppskrift, men noen ganger kan du enkelt bli kvitt den ved å fremskynde forespørselen med 10-100 ganger).

  For eksempel, i stedet for en spørring for å velge alle sjåfører fra en stor tabell over leveranser (LEVERINGS)

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

  laget en forespørsel på en relativt liten 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 ut til at vi brukte en korrelert underspørring, men den gir en hastighet på mer enn 10 ganger.

• I mange tilfeller ble COUNT helt forlatt og
  erstattet med beregning av omtrentlig verdi
• i stedet for

  UPPER(s) LIKE JOHN%’ 
  

  bruk

  s ILIKE “John%”
  

Hver spesifikk forespørsel ble noen ganger fremskyndet med 3-1000 ganger. Til tross for den imponerende ytelsen, virket det først for oss at det ikke var noen vits i å optimalisere en spørring som tar 10 ms å fullføre, er en av de tredje hundre tyngste spørringene og tar opp hundredeler av en prosent av den totale databasens lastetid. Men ved å bruke samme oppskrift på en gruppe søk av samme type, vant vi tilbake noen prosent. For ikke å kaste bort tid på å manuelt gjennomgå alle hundrevis av spørringer, skrev vi flere enkle skript som brukte regulære uttrykk for å finne spørringer av samme type. Som et resultat gjorde automatisk søk ​​i grupper med søk oss til å forbedre ytelsen ytterligere med beskjeden innsats.

Som et resultat har vi jobbet med den samme maskinvaren i tre år nå. Den gjennomsnittlige daglige belastningen er omtrent 30 %, i topper når den 70 %. Antall forespørsler, samt antall brukere, har økt ca. 10 ganger. Og alt dette takket være konstant overvåking av de samme gruppene av TOP-MEDIUM-forespørsler. Så snart en ny forespørsel dukker opp i TOP-gruppen, analyserer vi den umiddelbart og prøver å øke hastigheten. Vi gjennomgår MEDIUM-gruppen en gang i uken ved hjelp av spørringsanalyseskript. Hvis vi kommer over nye søk som vi allerede vet hvordan vi skal optimalisere, endrer vi dem raskt. Noen ganger finner vi nye optimaliseringsmetoder som kan brukes på flere spørringer samtidig.

I følge våre prognoser vil den nåværende serveren tåle en økning i antall brukere med ytterligere 3-5 ganger. Riktignok har vi ett ess til i ermet - vi har fortsatt ikke overført SELECT-spørringer til speilet, slik det anbefales. Men vi gjør ikke dette bevisst, fordi vi vil først fullstendig uttømme mulighetene for "smart" optimalisering før vi slår på det "tunge artilleriet".
Et kritisk blikk på arbeidet som er utført kan foreslå å bruke vertikal skalering. Kjøp en kraftigere server i stedet for å kaste bort tiden til spesialister. Serveren koster kanskje ikke så mye, spesielt siden vi ennå ikke har brukt grensene for vertikal skalering. Imidlertid økte bare antallet forespørsler 10 ganger. I løpet av flere år har funksjonaliteten i systemet økt og nå er det flere typer forespørsler. Takket være caching utføres funksjonaliteten som fantes i færre forespørsler og mer effektive forespørsler. Dette betyr at du trygt kan multiplisere med ytterligere 5 for å få den virkelige akselerasjonskoeffisienten. Så, ifølge de mest konservative estimatene, kan vi si at akselerasjonen var 50 ganger eller mer. Å svinge en server vertikalt vil koste 50 ganger mer. Spesielt med tanke på at når optimalisering er utført fungerer det hele tiden, og regningen for den leide serveren kommer hver måned.

Kilde: www.habr.com