Optimiziranje upita baze podataka na primjeru B2B usluge za građevinare

Kako povećati 10 puta broj upita u bazu podataka bez prelaska na produktivniji poslužitelj i održati funkcionalnost sustava? Reći ću vam kako smo se nosili s padom performansi naše baze podataka, kako smo optimizirali SQL upite da posluže što većem broju korisnika i da ne povećavaju troškove računalnih resursa.

Izrađujem uslugu vođenja poslovnih procesa u građevinskim tvrtkama. S nama radi oko 3 tisuće tvrtki. Više od 10 tisuća ljudi radi s našim sustavom svaki dan 4-10 sati. Rješava razne probleme planiranja, obavijesti, upozorenja, validacije... Koristimo PostgreSQL 9.6. U bazi imamo oko 300 tablica i svaki dan zaprimimo do 200 milijuna upita (10 tisuća različitih). U prosjeku imamo 3-4 tisuće zahtjeva u sekundi, u najaktivnijim trenucima više od 10 tisuća zahtjeva u sekundi. Većina upita je OLAP. Mnogo je manje dodavanja, izmjena i brisanja, što znači da je OLTP opterećenje relativno malo. Naveo sam sve ove brojke kako biste mogli procijeniti razmjere našeg projekta i shvatiti koliko vam naše iskustvo može biti korisno.

Slika jedna. Lirski

Kad smo započeli razvoj, nismo baš razmišljali o tome kakvo će opterećenje pasti na bazu podataka i što ćemo učiniti ako poslužitelj prestane povlačiti. Prilikom dizajniranja baze podataka slijedili smo općenite preporuke i nastojali ne pucati sami sebi u nogu, ali smo išli dalje od općih savjeta poput "nemojte koristiti uzorak Vrijednosti atributa entiteta nismo ušli. Dizajnirali smo na principima normalizacije, izbjegavajući redundanciju podataka i nismo brinuli o ubrzanju pojedinih upita. Čim su stigli prvi korisnici, naišli smo na problem s performansama. Kao i obično, bili smo potpuno nespremni za ovo. Pokazalo se da su prvi problemi jednostavni. U pravilu se sve rješavalo dodavanjem novog indeksa. Ali došlo je vrijeme kada su jednostavni zakrpe prestali djelovati. Uvidjevši da nam nedostaje iskustva i da nam je sve teže shvatiti što je uzrok problema, angažirali smo stručnjake koji su nam pomogli pravilno postaviti server, spojiti nadzor i pokazali nam gdje tražiti statistika.

Slika druga. Statistički

Dakle, dnevno imamo oko 10 tisuća različitih upita koji se izvršavaju na našoj bazi podataka. Od tih 10 tisuća postoje monsteri koji se izvrše 2-3 milijuna puta s prosječnim vremenom izvršenja od 0.1-0.3 ms, a tu su i upiti s prosječnim vremenom izvršenja od 30 sekundi koji se pozivaju 100 puta dnevno.

Nije bilo moguće optimizirati svih 10 tisuća upita, pa smo odlučili smisliti kamo usmjeriti svoje napore kako bismo ispravno poboljšali performanse baze podataka. Nakon nekoliko iteracija, počeli smo dijeliti zahtjeve na vrste.

TOP zahtjeva

Ovo su najteži upiti koji oduzimaju najviše vremena (ukupno vrijeme). To su upiti koji se ili vrlo često pozivaju ili upiti za čije izvršenje treba jako dugo (dugi i česti upiti optimizirani su u prvim iteracijama borbe za brzinu). Kao rezultat toga, poslužitelj troši najviše vremena na njihovo izvršenje. Štoviše, važno je razdvojiti vrhunske zahtjeve prema ukupnom vremenu izvršenja i zasebno prema IO vremenu. Metode za optimizaciju takvih upita malo su drugačije.

Uobičajena praksa svih tvrtki je rad s TOP zahtjevima. Malo ih je, optimizacija čak i jednog upita može osloboditi 5-10% resursa. Međutim, kako projekt sazrijeva, optimizacija TOP upita postaje sve netrivijalniji zadatak. Sve jednostavne metode već su razrađene, a "najteži" zahtjev uzima "samo" 3-5% resursa. Ako TOP upiti ukupno zauzimaju manje od 30-40% vremena, onda ste se najvjerojatnije već potrudili da brzo rade i vrijeme je da prijeđete na optimizaciju upita iz sljedeće grupe.
Ostaje odgovoriti na pitanje koliko top upita treba uvrstiti u ovu grupu. Obično uzmem najmanje 10, ali ne više od 20. Trudim se da se vrijeme prvog i zadnjeg u TOP skupini razlikuje najviše 10 puta. Odnosno, ako vrijeme izvršenja upita naglo padne s 1. mjesta na 10. mjesto, tada uzimam TOP-10, ako je pad postupniji, tada povećavam veličinu grupe na 15 ili 20.

Srednji seljaci

To su svi zahtjevi koji dolaze odmah nakon TOP-a, s izuzetkom zadnjih 5-10%. Obično u optimizaciji ovih upita leži prilika za značajno povećanje performansi poslužitelja. Ovi zahtjevi mogu težiti i do 80%. Ali čak i ako je njihov udio premašio 50%, vrijeme je da ih pažljivije pogledate.

Rep

Kao što je spomenuto, ovi upiti dolaze na kraju i zauzimaju 5-10% vremena. Na njih možete zaboraviti samo ako ne koristite alate za automatsku analizu upita, tada njihova optimizacija također može biti jeftina.

Kako ocijeniti svaku grupu?

Koristim SQL upit koji pomaže napraviti takvu procjenu za PostgreSQL (siguran sam da se sličan upit može napisati za mnoge druge DBMS-ove)

SQL upit za procjenu veličine grupa TOP-MEDIUM-TAIL

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

Rezultat upita su tri stupca, od kojih svaki sadrži postotak vremena potrebnog za obradu upita iz ove grupe. Unutar zahtjeva nalaze se dva broja (u mom slučaju 20 i 800) koji odvajaju zahtjeve iz jedne grupe od druge.

Ovako se otprilike uspoređuju udjeli zahtjeva u trenutku početka rada na optimizaciji i sada.

Dijagram pokazuje da je udio TOP zahtjeva naglo smanjen, ali su se "srednji seljaci" povećali.
U početku su TOP zahtjevi uključivali očigledne pogreške. S vremenom su dječje bolesti nestale, udio TOP zahtjeva se smanjio, a trebalo je ulagati sve više napora kako bi se ubrzali teški zahtjevi.

Za dobivanje teksta zahtjeva koristimo sljedeći zahtjev

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

Evo popisa najčešće korištenih tehnika koje su nam pomogle da ubrzamo TOP upite:

• Redizajn sustava, na primjer, prerada logike obavijesti korištenjem brokera poruka umjesto periodičnih upita bazi podataka
• Dodavanje ili mijenjanje indeksa
• Prepisivanje ORM upita u čisti SQL
• Prepisivanje logike odgođenog učitavanja podataka
• Predmemoriranje kroz denormalizaciju podataka. Na primjer, imamo vezu tablice Isporuka -> Račun -> Zahtjev -> Prijava. To jest, svaka isporuka povezana je s aplikacijom putem drugih tablica. Kako ne bismo povezivali sve tablice u svakom zahtjevu, duplicirali smo vezu na zahtjev u tablici Isporuka.
• Predmemoriranje statičkih tablica s referentnim knjigama i tablicama koje se rijetko mijenjaju u programskoj memoriji.

Ponekad su promjene dovele do impresivnog redizajna, ali su pružale 5-10% opterećenja sustava i bile su opravdane. S vremenom je ispuh postajao sve manji i zahtijevao se sve ozbiljniji redizajn.

Zatim smo obratili pozornost na drugu skupinu zahtjeva - skupinu srednjih seljaka. U njoj ima još mnogo upita i činilo se da će trebati dosta vremena da se analizira cijela grupa. Međutim, pokazalo se da je većinu upita vrlo jednostavno optimizirati, a mnogi problemi ponovljeni su desetke puta u različitim varijantama. Evo primjera nekih tipičnih optimizacija koje smo primijenili na desetke sličnih upita i svaka grupa optimiziranih upita rasteretila je bazu podataka za 3-5%.

• Umjesto provjere prisutnosti zapisa pomoću COUNT i skeniranja cijele tablice, počeli su se koristiti EXISTS
• Riješio se DISTINCT-a (nema općeg recepta, ali ponekad ga se lako možeš riješiti tako da zahtjev ubrzaš 10-100 puta).

  Na primjer, umjesto upita za odabir svih vozača iz velike tablice isporuka (ISPORUKA)

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

  napravio je upit na relativno maloj tablici 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)
  

  Čini se da smo upotrijebili korelirani podupit, ali daje ubrzanje više od 10 puta.

• U mnogim slučajevima, COUNT je potpuno napušten i
  zamijenjen izračunom približne vrijednosti
• umjesto

  UPPER(s) LIKE JOHN%’ 
  

  koristiti

  s ILIKE “John%”
  

Svaki specifičan zahtjev ponekad je bio ubrzan 3-1000 puta. Unatoč impresivnim performansama, isprva nam se činilo da nema smisla optimizirati upit koji traje 10 ms, jedan je od 3. stotine najtežih upita i zauzima stotinke postotka ukupnog vremena učitavanja baze podataka. Ali primjenom istog recepta na grupu upita iste vrste vratili smo nekoliko postotaka. Kako ne bismo gubili vrijeme na ručno pregledavanje svih stotina upita, napisali smo nekoliko jednostavnih skripti koje su koristile regularne izraze za pronalaženje upita iste vrste. Kao rezultat toga, automatsko pretraživanje grupa upita omogućilo nam je daljnje poboljšanje naše izvedbe uz skroman trud.

Kao rezultat toga, već tri godine radimo na istom hardveru. Prosječno dnevno opterećenje je oko 30%, u vrhuncu doseže 70%. Broj zahtjeva, kao i broj korisnika, porastao je otprilike 10 puta. I sve to zahvaljujući stalnom praćenju tih istih skupina TOP-MEDIUM zahtjeva. Čim se novi zahtjev pojavi u TOP grupi, odmah ga analiziramo i pokušavamo ubrzati. Grupu MEDIUM pregledavamo jednom tjedno pomoću skripti za analizu upita. Ako naiđemo na nove upite koje već znamo optimizirati, brzo ih mijenjamo. Ponekad nalazimo nove metode optimizacije koje se mogu primijeniti na nekoliko upita odjednom.

Prema našim predviđanjima, trenutni poslužitelj će izdržati povećanje broja korisnika za još 3-5 puta. Istina, imamo još jednog asa u rukavu - još uvijek nismo prenijeli SELECT upite na mirror, kao što se preporučuje. Ali mi to ne radimo svjesno, jer prvo želimo u potpunosti iscrpiti mogućnosti "pametne" optimizacije prije nego što uključimo "tešku artiljeriju".
Kritički pogled na obavljeni posao može sugerirati korištenje okomitog skaliranja. Kupite moćniji poslužitelj umjesto da gubite vrijeme na stručnjake. Poslužitelj možda neće koštati toliko, pogotovo jer još nismo iscrpili granice okomitog skaliranja. Međutim, samo se broj zahtjeva povećao 10 puta. Tijekom nekoliko godina funkcionalnost sustava je povećana i sada postoji više vrsta zahtjeva. Zahvaljujući predmemoriranju, funkcionalnost koja je postojala izvodi se u manje zahtjeva, a zahtjevi su učinkovitiji. To znači da možete sigurno pomnožiti s još 5 kako biste dobili pravi koeficijent ubrzanja. Dakle, prema najkonzervativnijim procjenama, možemo reći da je ubrzanje bilo 50 puta ili više. Okomito zakretanje poslužitelja koštalo bi 50 puta više. Pogotovo s obzirom da jednom provedena optimizacija radi cijelo vrijeme, a račun za zakupljeni server dolazi svaki mjesec.

Izvor: www.habr.com