Kuinka kasvattaa tietokannan kyselyjen määrää 10-kertaiseksi siirtämättä tuottavammalle palvelimelle ja ylläpitää järjestelmän toimivuutta? Kerron sinulle, kuinka käsittelimme tietokantamme suorituskyvyn heikkenemistä, kuinka optimoimme SQL-kyselyt palvelemaan mahdollisimman monia käyttäjiä emmekä lisänneet laskentaresurssien kustannuksia.
Teen palvelua rakennusyritysten liiketoimintaprosessien hallintaan. Noin 3 tuhatta yritystä työskentelee kanssamme. Yli 10 tuhatta ihmistä työskentelee järjestelmämme kanssa päivittäin 4-10 tuntia. Se ratkaisee erilaisia suunnittelu-, ilmoitus-, varoitus- ja validointiongelmia... Käytämme PostgreSQL 9.6:ta. Tietokannassamme on noin 300 taulukkoa ja päivittäin vastaanotetaan jopa 200 miljoonaa kyselyä (10 tuhatta erilaista). Meillä on keskimäärin 3-4 tuhatta pyyntöä sekunnissa, aktiivisimmina hetkinä yli 10 tuhatta pyyntöä sekunnissa. Suurin osa kyselyistä on OLAP. Lisäyksiä, muutoksia ja poistoja on paljon vähemmän, mikä tarkoittaa, että OLTP-kuorma on suhteellisen kevyt. Annoin kaikki nämä luvut, jotta voit arvioida projektimme laajuutta ja ymmärtää, kuinka hyödyllinen kokemuksemme voi olla sinulle.
Kuva yksi. Lyyrinen
Kun aloitimme kehityksen, emme oikeastaan ajatellut, millainen kuormitus kohdistuisi tietokantaan ja mitä tekisimme, jos palvelin lopettaisi vedon. Tietokannan suunnittelussa noudatimme yleisiä suosituksia ja yritimme olla ampumatta itseämme jalkaan, mutta menimme pidemmälle kuin yleiset neuvot, kuten "älä käytä kuviota emme menneet sisään. Suunnittelimme normalisoinnin periaatteiden pohjalta välttäen tietojen redundanssia emmekä välittäneet tiettyjen kyselyiden nopeuttamisesta. Heti kun ensimmäiset käyttäjät saapuivat, kohtasimme suorituskykyongelman. Kuten tavallista, olimme täysin valmistautumattomia tähän. Ensimmäiset ongelmat osoittautuivat yksinkertaisiksi. Yleensä kaikki ratkesi lisäämällä uusi indeksi. Mutta tuli aika, jolloin yksinkertaiset korjaustiedostot lakkasivat toimimasta. Ymmärsimme, että meillä ei ole kokemusta ja meidän on yhä vaikeampaa ymmärtää ongelmien syy .
Kuva kaksi. Tilastollinen
Meillä on siis noin 10 tuhatta erilaista kyselyä, jotka suoritetaan tietokannassamme päivässä. Näistä 10 tuhannesta on hirviöitä, jotka suoritetaan 2-3 miljoonaa kertaa keskimääräisellä suoritusajalla 0.1-0.3 ms, ja on kyselyitä, joiden keskimääräinen suoritusaika on 30 sekuntia ja joita kutsutaan 100 kertaa päivässä.
Kaikkia 10 XNUMX kyselyä ei ollut mahdollista optimoida, joten päätimme selvittää, mihin suunnata ponnistelumme parantaaksemme tietokannan suorituskykyä oikein. Useiden iteraatioiden jälkeen aloimme jakaa pyynnöt tyyppeihin.
TOP-pyynnöt
Nämä ovat raskaimmat kyselyt, jotka vievät eniten aikaa (kokonaisaika). Nämä ovat kyselyitä, joita kutsutaan joko hyvin usein, tai kyselyitä, joiden suorittaminen kestää hyvin kauan (pitkät ja toistuvat kyselyt optimoitiin nopeuden taistelun ensimmäisissä iteraatioissa). Tämän seurauksena palvelin käyttää eniten aikaa niiden suorittamiseen. Lisäksi on tärkeää erottaa suosituimmat pyynnöt kokonaissuoritusajan mukaan ja erikseen IO-ajan mukaan. Tällaisten kyselyiden optimointimenetelmät ovat hieman erilaisia.
Kaikkien yritysten tavanomainen käytäntö on työskennellä TOP-pyyntöjen kanssa. Niitä on vähän, vaikka yhden kyselyn optimointi voi vapauttaa 5-10 % resursseista. Projektin kypsyessä TOP-kyselyiden optimoinnista tulee kuitenkin yhä vähemmän triviaalitehtävä. Kaikki yksinkertaiset menetelmät on jo kehitetty, ja "raskain" pyyntö vie "vain" 3-5% resursseista. Jos TOP-kyselyt vievät yhteensä alle 30-40 % ajasta, olet todennäköisesti jo pyrkinyt saamaan ne toimimaan nopeasti ja on aika siirtyä optimoimaan seuraavan ryhmän kyselyt.
Vielä on vastattava kysymykseen, kuinka monta suosituinta kyselyä pitäisi sisällyttää tähän ryhmään. Otan yleensä vähintään 10, mutta enintään 20. Pyrin varmistamaan, että TOP-ryhmän ensimmäisen ja viimeisen aika eroaa enintään 10 kertaa. Eli jos kyselyn suoritusaika putoaa jyrkästi 1. paikasta 10. sijalle, otan TOP-10:n, jos pudotus on asteittaista, lisään ryhmän koon 15:een tai 20:een.
Keskitalonpojat
Nämä ovat kaikki pyyntöjä, jotka tulevat välittömästi TOP:n jälkeen, paitsi viimeiset 5-10%. Yleensä näiden kyselyiden optimoinnissa on mahdollisuus parantaa huomattavasti palvelimen suorituskykyä. Nämä pyynnöt voivat painaa jopa 80 %. Mutta vaikka niiden osuus olisi ylittänyt 50%, on aika tarkastella niitä tarkemmin.
Häntä
Kuten mainittiin, nämä kyselyt tulevat lopussa ja vievät 5-10 % ajasta. Voit unohtaa ne vain, jos et käytä automaattisia kyselyanalyysityökaluja, niin niiden optimointi voi myös olla halpaa.
Miten jokainen ryhmä arvioidaan?
Käytän SQL-kyselyä, joka auttaa tekemään tällaisen arvioinnin PostgreSQL:lle (olen varma, että samanlainen kysely voidaan kirjoittaa monille muille tietokantajärjestelmille)
SQL-kysely TOP-MEDIUM-TAIL-ryhmien koon arvioimiseksi
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
Kyselyn tuloksena on kolme saraketta, joista jokainen sisältää prosenttiosuuden ajasta, joka kuluu tämän ryhmän kyselyjen käsittelyyn. Pyynnön sisällä on kaksi numeroa (minun tapauksessani 20 ja 800), jotka erottavat ryhmän pyynnöt toisesta.
Näin pyyntöjen osuudet karkeasti vertautuvat optimointityön alkaessa ja nyt.
Kaavio osoittaa, että TOP-pyyntöjen osuus on laskenut jyrkästi, mutta "keskitalonpojat" ovat lisääntyneet.
Aluksi TOP-pyynnöt sisälsivät räikeitä virheitä. Ajan myötä lastentaudit katosivat, TOP-pyyntöjen osuus väheni ja vaikeita pyyntöjä jouduttiin nopeuttamaan yhä enemmän.
Käytämme seuraavaa pyyntöä saadaksemme pyyntöjen tekstin
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
Tässä on luettelo yleisimmin käytetyistä tekniikoista, jotka auttoivat meitä nopeuttamaan TOP-kyselyitä:
- Järjestelmän uudelleensuunnittelu, esimerkiksi ilmoituslogiikan uusiminen viestivälittäjällä säännöllisten tietokantakyselyjen sijaan
- Indeksien lisääminen tai muuttaminen
- ORM-kyselyjen uudelleenkirjoittaminen puhtaaksi SQL:ksi
- Uudelleenkirjoitetaan laiska tiedonlatauslogiikka
- Välimuisti tietojen denormalisoinnin avulla. Meillä on esimerkiksi taulukkoyhteys Toimitus -> Lasku -> Pyyntö -> Hakemus. Toisin sanoen jokainen toimitus liittyy sovellukseen muiden taulukoiden kautta. Jotta emme linkittäisi kaikkia taulukoita jokaisessa pyynnössä, kopioimme linkin pyyntöön Toimitustaulukkoon.
- Staattisten taulukoiden välimuistin tallentaminen hakuteoksilla ja harvoin vaihtuvia taulukoita ohjelmamuistissa.
Joskus muutokset olivat vaikuttavaa uudelleensuunnittelua, mutta ne tarjosivat 5-10% järjestelmän kuormituksesta ja olivat perusteltuja. Ajan myötä pakoputki pieneni ja pieneni ja tarvittiin yhä vakavampaa uudelleensuunnittelua.
Sitten käänsimme huomiomme toiseen pyyntöryhmään - keskitalonpoikien ryhmään. Siinä on paljon enemmän kyselyitä ja näytti siltä, että koko ryhmän analysoiminen vie paljon aikaa. Useimmat kyselyt osoittautuivat kuitenkin erittäin yksinkertaisiksi optimoitaviksi, ja monet ongelmat toistettiin kymmeniä kertoja eri muunnelmissa. Tässä on esimerkkejä tyypillisistä optimoinneista, joita käytimme kymmeniin samankaltaisiin kyselyihin ja jokainen optimoitujen kyselyiden ryhmä tyhjensi tietokannan 3–5 %.
- Sen sijaan, että tietueiden olemassaolo olisi tarkistettu käyttämällä COUNT ja koko taulukkoskannaus, alettiin käyttää EXISTS.
- Päästiin eroon DISTINCT:stä (yleistä reseptiä ei ole, mutta joskus siitä pääsee helposti eroon nopeuttamalla pyyntöä 10-100 kertaa).
Esimerkiksi kyselyn sijaan valita kaikki ajurit suuresta toimitustaulukosta (TOIMITUS)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDteki kyselyn suhteellisen pienessä taulukossa 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)Vaikuttaa siltä, että käytimme korreloitua alikyselyä, mutta se antaa yli 10-kertaisen nopeuden.
- Monissa tapauksissa COUNT hylättiin kokonaan ja
- sen sijasta
UPPER(s) LIKE JOHN%’käyttää
s ILIKE “John%”
Jokaista pyyntöä nopeutettiin joskus 3-1000 kertaa. Huolimatta vaikuttavasta suorituskyvystä, meistä aluksi vaikutti siltä, että ei ollut mitään järkeä optimoida kyselyä, joka kestää 10 ms, on yksi 3. sadasta raskaimmista kyselyistä ja vie sadasosat prosentin koko tietokannan latausajasta. Mutta käyttämällä samaa reseptiä samantyyppisten kyselyiden ryhmään, voitimme muutaman prosentin takaisin. Jotta emme tuhlaa aikaa kaikkien satojen kyselyiden manuaaliseen tarkistamiseen, kirjoitimme useita yksinkertaisia komentosarjoja, jotka käyttivät säännöllisiä lausekkeita samantyyppisten kyselyjen etsimiseen. Tämän seurauksena automaattinen haku kyselyryhmistä antoi meille mahdollisuuden parantaa suorituskykyämme vaatimattomalla vaivalla.
Tämän seurauksena olemme työstäneet samaa laitteistoa nyt kolme vuotta. Keskimääräinen päivittäinen kuormitus on noin 30 %, huipuilla 70 %. Pyyntöjen määrä, kuten myös käyttäjien määrä, on kasvanut noin 10-kertaiseksi. Ja kaikki tämä juuri näiden TOP-MEDIUM-pyyntöryhmien jatkuvan seurannan ansiosta. Heti kun uusi pyyntö ilmestyy TOP-ryhmään, analysoimme sen välittömästi ja yritämme nopeuttaa sitä. Tarkistamme MEDIUM-ryhmän kerran viikossa kyselyanalyysiskripteillä. Jos törmäämme uusiin kyselyihin, jotka osaamme jo optimoida, muutamme niitä nopeasti. Joskus löydämme uusia optimointimenetelmiä, joita voidaan soveltaa useisiin kyselyihin kerralla.
Ennustemme mukaan nykyinen palvelin kestää vielä 3-5-kertaisen käyttäjämäärän kasvun. Totta, meillä on vielä yksi ässä hihassamme - emme ole edelleenkään siirtäneet SELECT-kyselyitä peiliin, kuten suositellaan. Mutta emme tee tätä tietoisesti, koska haluamme ensin täysin käyttää "älykkään" optimoinnin mahdollisuudet ennen kuin käynnistämme "raskaan tykistön".
Tehdyn työn kriittinen tarkastelu voi ehdottaa pystyskaalauksen käyttöä. Osta tehokkaampi palvelin sen sijaan, että hukkaa asiantuntijoiden aikaa. Palvelin ei välttämättä maksa niin paljon, varsinkin kun emme ole vielä käyttäneet pystyskaalauksen rajoja. Kuitenkin vain pyyntöjen määrä kasvoi 10-kertaiseksi. Useiden vuosien aikana järjestelmän toimivuus on lisääntynyt ja nyt pyyntötyyppejä on enemmän. Välimuistin ansiosta olemassa olevat toiminnot suoritetaan harvemmilla pyynnöillä ja tehokkaammilla pyynnöillä. Tämä tarkoittaa, että voit turvallisesti kertoa toisella 5:llä saadaksesi todellisen kiihtyvyyskertoimen. Joten varovaisimpien arvioiden mukaan voimme sanoa, että kiihtyvyys oli 50-kertainen tai enemmän. Palvelimen pystysuunnassa heiluminen maksaisi 50 kertaa enemmän. Varsinkin kun ottaa huomioon, että kun optimointi on tehty, se toimii koko ajan ja lasku vuokrapalvelimesta tulee joka kuukausi.
Lähde: will.com