Optimalisering van databasisnavrae deur die voorbeeld van 'n B2B-diens vir bouers te gebruik

Hoe om 10 keer die aantal navrae na die databasis te groei sonder om na 'n meer produktiewe bediener te skuif en stelselfunksionaliteit te handhaaf? Ek sal jou vertel hoe ons die afname in die werkverrigting van ons databasis hanteer het, hoe ons SQL-navrae geoptimaliseer het om soveel gebruikers as moontlik te bedien en nie die koste van rekenaarhulpbronne te verhoog nie.

Ek lewer 'n diens vir die bestuur van besigheidsprosesse in konstruksiemaatskappye. Ongeveer 3 duisend maatskappye werk saam met ons. Meer as 10 duisend mense werk elke dag vir 4-10 uur met ons stelsel. Dit los verskeie probleme op van beplanning, kennisgewing, waarskuwing, validering ... Ons gebruik PostgreSQL 9.6. Ons het ongeveer 300 tabelle in die databasis en tot 200 miljoen navrae (10 duisend verskillende) word elke dag ontvang. Ons het gemiddeld 3-4 duisend versoeke per sekonde, op die mees aktiewe oomblikke meer as 10 duisend versoeke per sekonde. Die meeste van die navrae is OLAP. Daar is baie minder byvoegings, wysigings en skrappings, wat beteken dat die OLTP-lading relatief lig is. Ek het al hierdie nommers verskaf sodat jy die skaal van ons projek kan assesseer en verstaan ​​hoe nuttig ons ervaring vir jou kan wees.

Foto een. Liries

Toe ons met ontwikkeling begin het, het ons nie regtig gedink aan watter soort las op die databasis sou val en wat ons sou doen as die bediener ophou trek nie. Toe ons die databasis ontwerp het, het ons algemene aanbevelings gevolg en probeer om nie onsself in die voet te skiet nie, maar het verder gegaan as algemene advies soos "moenie die patroon gebruik nie Entiteiteienskapwaardes ons het nie ingegaan nie. Ons het ontwerp gebaseer op die beginsels van normalisering, om data-oortolligheid te vermy en nie omgegee om sekere navrae te bespoedig nie. Sodra die eerste gebruikers aangekom het, het ons 'n prestasieprobleem ondervind. Soos gewoonlik was ons heeltemal onvoorbereid hiervoor. Die eerste probleme blyk eenvoudig te wees. As 'n reël is alles opgelos deur 'n nuwe indeks by te voeg. Maar daar het 'n tyd gekom toe eenvoudige pleisters ophou werk het. Omdat ons besef het dat ons 'n gebrek aan ondervinding het en dit vir ons al hoe moeiliker word om te verstaan ​​wat die probleme veroorsaak, het ons spesialiste aangestel wat ons gehelp het om die bediener korrek op te stel, monitering te koppel en vir ons gewys het waar om te soek. statistieke.

Foto twee. Statisties

Ons het dus ongeveer 10 duisend verskillende navrae wat per dag op ons databasis uitgevoer word. Van hierdie 10 duisend is daar monsters wat 2-3 miljoen keer uitgevoer word met 'n gemiddelde uitvoeringstyd van 0.1-0.3 ms, en daar is navrae met 'n gemiddelde uitvoeringstyd van 30 sekondes wat 100 keer per dag geroep word.

Dit was nie moontlik om al 10 duisend navrae te optimaliseer nie, daarom het ons besluit om uit te vind waarheen om ons pogings te rig om die werkverrigting van die databasis korrek te verbeter. Na verskeie herhalings het ons versoeke in tipes begin verdeel.

TOP versoeke

Dit is die swaarste navrae wat die meeste tyd neem (totale tyd). Dit is navrae wat óf baie gereeld genoem word óf navrae wat baie lank neem om uit te voer (lang en gereelde navrae is geoptimaliseer in die eerste herhalings van die stryd om spoed). Gevolglik spandeer die bediener die meeste tyd aan die uitvoering daarvan. Boonop is dit belangrik om topversoeke te skei volgens totale uitvoeringstyd en afsonderlik volgens IO-tyd. Die metodes vir die optimalisering van sulke navrae verskil effens.

Die gewone praktyk van alle maatskappye is om met TOP-versoeke te werk. Daar is min van hulle; die optimalisering van selfs een navraag kan 5-10% van hulpbronne bevry. Namate die projek verouder, word die optimalisering van TOP-navrae egter 'n toenemend nie-triviale taak. Alle eenvoudige metodes is reeds uitgewerk, en die mees "swaar" versoek neem "slegs" 3-5% van hulpbronne. As TOP-navrae in totaal minder as 30-40% van die tyd neem, dan het jy heel waarskynlik reeds pogings aangewend om hulle vinnig te laat werk en is dit tyd om aan te beweeg na die optimalisering van navrae van die volgende groep.
Dit bly om die vraag te beantwoord hoeveel topnavrae by hierdie groep ingesluit moet word. Ek neem gewoonlik ten minste 10, maar nie meer as 20 nie. Ek probeer verseker dat die tyd van die eerste en laaste in die TOP-groep met nie meer as 10 keer verskil nie. Dit wil sê, as die uitvoeringstyd van die navraag skerp van die 1ste plek na die 10de daal, neem ek TOP-10, as die daling meer geleidelik is, verhoog ek die groepgrootte na 15 of 20.

Middelboere

Dit is alles versoeke wat onmiddellik na TOP kom, met die uitsondering van die laaste 5-10%. Gewoonlik, in die optimalisering van hierdie navrae lê die geleentheid om bedienerwerkverrigting aansienlik te verhoog. Hierdie versoeke kan tot 80% weeg. Maar selfs al het hul aandeel 50% oorskry, dan is dit tyd om noukeuriger na hulle te kyk.

Stert

Soos genoem, kom hierdie navrae aan die einde en neem 5-10% van die tyd. U kan slegs van hulle vergeet as u nie outomatiese navraaganalise-instrumente gebruik nie, dan kan dit ook goedkoop wees om dit te optimaliseer.

Hoe om elke groep te evalueer?

Ek gebruik 'n SQL-navraag wat help om so 'n assessering vir PostgreSQL te maak (ek is seker dat 'n soortgelyke navraag vir baie ander DBBS'e geskryf kan word)

SQL-navraag om die grootte van TOP-MEDIUM-STERT-groepe te skat

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

Die resultaat van die navraag is drie kolomme, wat elkeen die persentasie tyd bevat wat dit neem om navrae van hierdie groep te verwerk. Binne die versoek is daar twee nommers (in my geval is dit 20 en 800) wat versoeke van een groep van 'n ander skei.

Dit is hoe die aandele van versoeke rofweg vergelyk toe die optimaliseringswerk begin het en nou.

Die diagram toon dat die aandeel TOP-versoeke skerp afgeneem het, maar die "middelboere" het toegeneem.
Aanvanklik het die TOP-versoeke blatante flaters ingesluit. Met verloop van tyd het kindersiektes verdwyn, die aandeel TOP-versoeke het afgeneem en al hoe meer pogings moes aangewend word om moeilike versoeke te bespoedig.

Om die teks van versoeke te kry, gebruik ons ​​die volgende versoek

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

Hier is 'n lys van die mees gebruikte tegnieke wat ons gehelp het om TOP-navrae te bespoedig:

• Herontwerp van die stelsel, byvoorbeeld, die herwerk van die kennisgewinglogika met behulp van 'n boodskapmakelaar in plaas van periodieke navrae na die databasis
• Byvoeging of verandering van indekse
• Herskryf ORM-navrae na suiwer SQL
• Herskryf lui data-laai-logika
• Kas deur data-denormalisering. Ons het byvoorbeeld 'n tabelverbinding Aflewering -> Faktuur -> Versoek -> Aansoek. Dit wil sê, elke aflewering word geassosieer met 'n toepassing deur ander tabelle. Om nie alle tabelle in elke versoek te koppel nie, het ons die skakel na die versoek in die afleweringstabel gedupliseer.
• Kas statiese tabelle met naslaanboeke en verander selde tabelle in programgeheue.

Soms het die veranderinge neergekom op 'n indrukwekkende herontwerp, maar dit het 5-10% van die stelsellading verskaf en was geregverdig. Met verloop van tyd het die uitlaat al hoe kleiner geword, en meer en meer ernstige herontwerp was nodig.

Toe het ons ons aandag gevestig op die tweede groep versoeke – die groep middelboere. Daar is baie meer navrae daarin en dit het gelyk of dit baie tyd sou neem om die hele groep te ontleed. Die meeste navrae blyk egter baie eenvoudig te wees om te optimaliseer, en baie probleme is dosyne kere in verskillende variasies herhaal. Hier is voorbeelde van 'n paar tipiese optimaliserings wat ons op dosyne soortgelyke navrae toegepas het en elke groep geoptimaliseerde navrae het die databasis met 3-5% afgelaai.

• In plaas daarvan om te kyk vir die teenwoordigheid van rekords deur COUNT en 'n volledige tabelskandering te gebruik, het EXISTS begin gebruik word
• Het ontslae geraak van DISTINCT (daar is geen algemene resep nie, maar soms kan jy maklik daarvan ontslae raak deur die versoek met 10-100 keer te versnel).

  Byvoorbeeld, in plaas van 'n navraag om alle bestuurders uit 'n groot tabel van aflewerings te kies (LEVERING)

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

  het 'n navraag oor 'n relatief klein tafelPERSOON gemaak

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

  Dit wil voorkom asof ons 'n gekorreleerde subnavraag gebruik het, maar dit gee 'n versnelling van meer as 10 keer.

• In baie gevalle is COUNT heeltemal laat vaar en
  vervang deur berekening van benaderde waarde
• in plaas van

  UPPER(s) LIKE JOHN%’ 
  

  gebruik

  s ILIKE “John%”
  

Elke spesifieke versoek is soms met 3-1000 keer bespoedig. Ten spyte van die indrukwekkende prestasie, het dit aanvanklik vir ons gelyk of daar geen sin was om 'n navraag te optimaliseer wat 10 ms neem om te voltooi, een van die 3de honderd swaarste navrae is en honderdstes van 'n persent van die totale databasislaaityd in beslag neem. Maar deur dieselfde resep op 'n groep navrae van dieselfde tipe toe te pas, het ons 'n paar persent terug gewen. Om nie tyd te mors om al honderde navrae handmatig te hersien nie, het ons verskeie eenvoudige skrifte geskryf wat gereelde uitdrukkings gebruik het om navrae van dieselfde tipe te vind. Gevolglik het die outomatiese soek van groepe navrae ons in staat gestel om ons prestasie met beskeie moeite verder te verbeter.

Gevolglik werk ons ​​nou al drie jaar aan dieselfde hardeware. Die gemiddelde daaglikse vrag is ongeveer 30%, in pieke bereik dit 70%. Die aantal versoeke, sowel as die aantal gebruikers, het ongeveer 10 keer toegeneem. En dit alles te danke aan die konstante monitering van hierdie einste groepe TOP-MEDIUM versoeke. Sodra 'n nuwe versoek in die TOP-groep verskyn, ontleed ons dit dadelik en probeer dit bespoedig. Ons hersien die MEDIUM-groep een keer per week deur navraaganalise-skrifte te gebruik. As ons nuwe navrae teëkom wat ons reeds weet hoe om te optimaliseer, verander ons dit vinnig. Soms vind ons nuwe optimaliseringsmetodes wat op verskeie navrae gelyktydig toegepas kan word.

Volgens ons voorspellings sal die huidige bediener 'n toename in die aantal gebruikers met nog 3-5 keer weerstaan. Weliswaar het ons nog een aas in die mou - ons het steeds nie SELECT-navrae na die spieël oorgedra nie, soos aanbeveel word. Maar ons doen dit nie bewustelik nie, want ons wil eers die moontlikhede van "slim" optimalisering heeltemal uitput voordat ons die "swaar artillerie" aanskakel.
'n Kritiese blik op die werk wat gedoen is, kan voorstel dat vertikale skaal gebruik word. Koop 'n kragtiger bediener in plaas daarvan om die tyd van spesialiste te mors. Die bediener kos dalk nie soveel nie, veral omdat ons nog nie die grense van vertikale skaal uitgeput het nie. Slegs die aantal versoeke het egter 10 keer toegeneem. Deur die loop van 'n paar jaar het die funksionaliteit van die stelsel toegeneem en nou is daar meer soorte versoeke. Danksy caching word die funksionaliteit wat bestaan ​​het in minder versoeke en doeltreffender versoeke uitgevoer. Dit beteken jy kan veilig met nog 5 vermenigvuldig om die werklike versnellingskoëffisiënt te kry. Dus, volgens die mees konserwatiewe skattings, kan ons sê dat die versnelling 50 keer of meer was. Om 'n bediener vertikaal te swaai sal 50 keer meer kos. Veral as in ag geneem word dat sodra optimalisering uitgevoer is, dit heeltyd werk, en die rekening vir die gehuurde bediener kom elke maand.

Bron: will.com