Het optimaliseren van databasequery's aan de hand van het voorbeeld van een B2B-service voor bouwers

Hoe kan ik het aantal queries naar de database tien keer vergroten zonder over te stappen naar een productievere server en de systeemfunctionaliteit behouden? Ik zal u vertellen hoe we zijn omgegaan met de afnemende prestaties van onze database, hoe we SQL-query's hebben geoptimaliseerd om zoveel mogelijk gebruikers te bedienen en de kosten van computerbronnen niet te verhogen.

Ik maak een dienst voor het beheren van bedrijfsprocessen bij bouwbedrijven. Ongeveer drieduizend bedrijven werken met ons samen. Meer dan 3 mensen werken elke dag 10-4 uur met ons systeem. Het lost verschillende problemen op met betrekking tot planning, kennisgeving, waarschuwing, validatie... We gebruiken PostgreSQL 10. We hebben ongeveer 9.6 tabellen in de database en dagelijks worden er tot 300 miljoen zoekopdrachten (200 verschillende) ontvangen. Gemiddeld hebben we 10-3 duizend verzoeken per seconde, op de meest actieve momenten meer dan 4 duizend verzoeken per seconde. De meeste zoekopdrachten zijn OLAP. Er zijn veel minder toevoegingen, wijzigingen en verwijderingen, wat betekent dat de OLTP-belasting relatief licht is. Ik heb al deze cijfers verstrekt zodat u de omvang van ons project kunt beoordelen en kunt begrijpen hoe nuttig onze ervaring voor u kan zijn.

Foto één. Lyrisch

Toen we met de ontwikkeling begonnen, dachten we er niet echt over na wat voor soort belasting er op de database zou vallen en wat we zouden doen als de server zou stoppen met trekken. Bij het ontwerpen van de database volgden we algemene aanbevelingen en probeerden we onszelf niet in de voet te schieten, maar gingen we verder dan algemene adviezen zoals ‘gebruik het patroon niet’. Entiteitskenmerkwaarden wij zijn niet naar binnen gegaan. We hebben ontworpen op basis van de principes van normalisatie, waarbij gegevensredundantie werd vermeden en het ons niet uitmaakte om bepaalde zoekopdrachten te versnellen. Zodra de eerste gebruikers arriveerden, stuitten we op een prestatieprobleem. Zoals gewoonlijk waren we hier totaal niet op voorbereid. De eerste problemen bleken eenvoudig. In de regel werd alles opgelost door een nieuwe index toe te voegen. Maar er kwam een ​​tijd dat eenvoudige patches niet meer werkten. Omdat we ons realiseerden dat het ons aan ervaring ontbreekt en het voor ons steeds moeilijker wordt om te begrijpen wat de problemen veroorzaakt, hebben we specialisten ingehuurd die ons hielpen de server correct in te stellen, de monitoring te verbinden en ons lieten zien waar we moesten zoeken. statistieken.

Foto twee. Statistisch

We hebben dus ongeveer 10 verschillende zoekopdrachten die per dag in onze database worden uitgevoerd. Van deze 10 zijn er monsters die 2-3 miljoen keer worden uitgevoerd met een gemiddelde uitvoeringstijd van 0.1-0.3 ms, en er zijn queries met een gemiddelde uitvoeringstijd van 30 seconden die 100 keer per dag worden aangeroepen.

Het was niet mogelijk om alle 10 zoekopdrachten te optimaliseren, dus besloten we uit te zoeken waar we onze inspanningen op moesten richten om de prestaties van de database correct te verbeteren. Na verschillende iteraties begonnen we verzoeken in typen te verdelen.

TOP-verzoeken

Dit zijn de zwaarste zoekopdrachten die de meeste tijd in beslag nemen (totale tijd). Dit zijn queries die heel vaak worden aangeroepen, of queries waarvan de uitvoering erg lang duurt (lange en frequente queries werden geoptimaliseerd in de eerste iteraties van de strijd om snelheid). Als gevolg hiervan besteedt de server de meeste tijd aan de uitvoering ervan. Bovendien is het belangrijk om topverzoeken te scheiden op basis van de totale uitvoeringstijd en afzonderlijk op IO-tijd. De methoden voor het optimaliseren van dergelijke zoekopdrachten zijn enigszins verschillend.

De gebruikelijke praktijk van alle bedrijven is om met TOP-aanvragen te werken. Er zijn er maar weinig; het optimaliseren van zelfs één zoekopdracht kan 5-10% van de bronnen vrijmaken. Naarmate het project volwassener wordt, wordt het optimaliseren van TOP-query's echter een steeds minder triviale taak. Alle eenvoudige methoden zijn al uitgewerkt en het meest “zware” verzoek kost “slechts” 3-5% van de middelen. Als TOP-query's in totaal minder dan 30-40% van de tijd in beslag nemen, dan heeft u hoogstwaarschijnlijk al inspanningen geleverd om ze snel te laten werken en is het tijd om verder te gaan met het optimaliseren van de query's van de volgende groep.
Rest ons nog de vraag te beantwoorden hoeveel topquery's in deze groep moeten worden opgenomen. Meestal neem ik er minimaal 10, maar niet meer dan 20. Ik probeer ervoor te zorgen dat de tijd van de eerste en de laatste in de TOP-groep maximaal 10 keer verschilt. Dat wil zeggen, als de uitvoeringstijd van de zoekopdracht scherp daalt van de 1e naar de 10e plaats, dan neem ik TOP-10, als de daling geleidelijker verloopt, dan verhoog ik de groepsgrootte naar 15 of 20.

Middelste boeren

Dit zijn allemaal verzoeken die direct na TOP komen, met uitzondering van de laatste 5-10%. Meestal ligt bij het optimaliseren van deze zoekopdrachten de mogelijkheid om de serverprestaties aanzienlijk te verbeteren. Deze verzoeken kunnen tot 80% wegen. Maar zelfs als hun aandeel de 50% heeft overschreden, is het tijd om er zorgvuldiger naar te kijken.

Staart

Zoals gezegd komen deze vragen aan het einde en nemen ze 5-10% van de tijd in beslag. U kunt ze alleen vergeten als u geen automatische hulpprogramma's voor queryanalyse gebruikt. Het optimaliseren ervan kan ook goedkoop zijn.

Hoe elke groep evalueren?

Ik gebruik een SQL-query die helpt bij het maken van een dergelijke beoordeling voor PostgreSQL (ik ben er zeker van dat een soortgelijke query voor veel andere DBMS'en kan worden geschreven)

SQL-query om de grootte van TOP-MEDIUM-TAIL-groepen te schatten

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

Het resultaat van de query bestaat uit drie kolommen, die elk het percentage tijd bevatten dat nodig is om query's uit deze groep te verwerken. Binnen het verzoek staan ​​twee cijfers (in mijn geval is dit 20 en 800) die verzoeken van de ene groep van de andere scheiden.

Dit is hoe het aandeel verzoeken grofweg vergeleken kan worden op het moment dat de optimalisatiewerkzaamheden begonnen en nu.

Het diagram laat zien dat het aandeel TOP-verzoeken scherp is afgenomen, maar dat de “middenboeren” zijn toegenomen.
Aanvankelijk bevatten de TOP-verzoeken flagrante blunders. In de loop van de tijd verdwenen kinderziektes, nam het aandeel TOP-aanvragen af ​​en moesten er steeds meer inspanningen worden geleverd om moeilijke aanvragen te bespoedigen.

Om de tekst van verzoeken te verkrijgen, gebruiken wij het volgende verzoek

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 een lijst met de meest gebruikte technieken die ons hebben geholpen om TOP-zoekopdrachten te versnellen:

• Herontwerp van het systeem, bijvoorbeeld door de meldingslogica te herwerken met behulp van een berichtenmakelaar in plaats van periodieke zoekopdrachten naar de database
• Indexen toevoegen of wijzigen
• ORM-query's herschrijven naar pure SQL
• Herschrijven van logica voor het laden van luie gegevens
• Caching via denormalisatie van gegevens. Zo hebben wij bijvoorbeeld een tabelkoppeling Levering -> Factuur -> Aanvraag -> Aanvraag. Dat wil zeggen dat elke levering via andere tabellen aan een applicatie wordt gekoppeld. Om niet bij elke aanvraag alle tabellen te koppelen, hebben we de link naar de aanvraag gedupliceerd in de Leveringstabel.
• Het cachen van statische tabellen met naslagwerken en het zelden wijzigen van tabellen in het programmageheugen.

Soms kwamen de veranderingen neer op een indrukwekkend herontwerp, maar ze zorgden voor 5-10% van de systeembelasting en waren gerechtvaardigd. In de loop van de tijd werd de uitlaat steeds kleiner en was er een steeds serieuzer herontwerp nodig.

Vervolgens richtten we onze aandacht op de tweede groep verzoeken: de groep middenboeren. Er zitten veel meer vragen in en het leek erop dat het veel tijd zou kosten om de hele groep te analyseren. De meeste queries bleken echter heel eenvoudig te optimaliseren en veel problemen herhaalden zich tientallen keren in verschillende variaties. Hier zijn voorbeelden van enkele typische optimalisaties die we hebben toegepast op tientallen vergelijkbare zoekopdrachten en elke groep geoptimaliseerde zoekopdrachten heeft de database met 3-5% leeggemaakt.

• In plaats van te controleren op de aanwezigheid van records met behulp van COUNT en een volledige tabelscan, werd EXISTS gebruikt
• DISTINCT verwijderd (er is geen algemeen recept, maar soms kun je er gemakkelijk vanaf komen door het verzoek 10-100 keer te versnellen).

  Bijvoorbeeld in plaats van een vraag om alle chauffeurs te selecteren uit een grote tabel met leveringen (DELIVERY)

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

  heeft een zoekopdracht uitgevoerd op een relatief kleine tafel PERSON

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

  Het lijkt erop dat we een gecorreleerde subquery hebben gebruikt, maar deze levert een versnelling van meer dan tien keer op.

• In veel gevallen werd COUNT helemaal verlaten
  vervangen door berekening van de geschatte waarde
• in plaats van

  UPPER(s) LIKE JOHN%’ 
  

  gebruik

  s ILIKE “John%”
  

Elk specifiek verzoek werd soms 3 tot 1000 keer versneld. Ondanks de indrukwekkende prestaties leek het ons in eerste instantie dat het geen zin had om een ​​query te optimaliseren die 10 ms nodig heeft om te voltooien, een van de derde honderd zwaarste queries is en een honderdste van een procent van de totale laadtijd van de database in beslag neemt. Maar door hetzelfde recept toe te passen op een groep zoekopdrachten van hetzelfde type, hebben we een paar procent teruggewonnen. Om geen tijd te verspillen aan het handmatig beoordelen van alle honderden zoekopdrachten, hebben we verschillende eenvoudige scripts geschreven die reguliere expressies gebruiken om zoekopdrachten van hetzelfde type te vinden. Als gevolg hiervan konden we door het automatisch doorzoeken van groepen zoekopdrachten onze prestaties met bescheiden inspanning verder verbeteren.

Hierdoor werken we nu al drie jaar aan dezelfde hardware. De gemiddelde dagelijkse belasting bedraagt ​​ongeveer 30%, in pieken bereikt deze 70%. Het aantal verzoeken, evenals het aantal gebruikers, is ongeveer 10 keer toegenomen. En dit alles dankzij de constante monitoring van dezelfde groepen TOP-MEDIUM-aanvragen. Zodra er een nieuwe aanvraag in de TOP-groep verschijnt, analyseren wij deze direct en proberen deze te versnellen. We beoordelen de MEDIUM-groep één keer per week met behulp van query-analysescripts. Als we nieuwe zoekopdrachten tegenkomen waarvan we al weten hoe we deze moeten optimaliseren, passen we deze snel aan. Soms vinden we nieuwe optimalisatiemethoden die op meerdere zoekopdrachten tegelijk kunnen worden toegepast.

Volgens onze voorspellingen zal de huidige server een toename van het aantal gebruikers met nog eens 3-5 keer kunnen weerstaan. Het is waar dat we nog een troef achter de hand hebben: we hebben nog steeds geen SELECT-query's naar de mirror overgebracht, zoals aanbevolen. Maar dat doen we niet bewust, omdat we eerst de mogelijkheden van ‘slimme’ optimalisatie volledig willen uitputten voordat we de ‘zware artillerie’ inschakelen.
Een kritische blik op het verrichte werk zou erop kunnen duiden dat er gebruik wordt gemaakt van verticale schaling. Koop een krachtigere server in plaats van de tijd van specialisten te verspillen. De server kost misschien niet zoveel, vooral omdat we de grenzen van verticaal schalen nog niet hebben bereikt. Alleen het aantal verzoeken is echter vertienvoudigd. In de loop van een aantal jaren is de functionaliteit van het systeem toegenomen en zijn er nu meer soorten verzoeken. Dankzij caching wordt de bestaande functionaliteit uitgevoerd met minder verzoeken en efficiëntere verzoeken. Dit betekent dat je veilig met nog eens 10 kunt vermenigvuldigen om de echte versnellingscoëfficiënt te krijgen. Volgens de meest conservatieve schattingen kunnen we dus zeggen dat de versnelling 5 keer of meer was. Het verticaal zwaaien van een server zou 50 keer meer kosten. Vooral als je bedenkt dat zodra de optimalisatie is uitgevoerd, het altijd werkt en dat de rekening voor de gehuurde server elke maand komt.

Bron: www.habr.com