Optimizacija upita baze podataka na primjeru B2B usluge za graditelje

Kako povećati 10 puta broj upita bazi podataka bez prelaska na produktivniji server i održati funkcionalnost sistema? Reći ću vam kako smo se nosili sa padom performansi naše baze podataka, kako smo optimizovali SQL upite da opslužujemo što veći broj korisnika i da ne povećavamo troškove računarskih resursa.

Radim servis za vođenje poslovnih procesa u građevinskim firmama. Sa nama radi oko 3 hiljade kompanija. Više od 10 hiljada ljudi radi sa našim sistemom svaki dan 4-10 sati. Rješava razne probleme planiranja, obavještavanja, upozorenja, validacije... Koristimo PostgreSQL 9.6. Imamo oko 300 tabela u bazi i do 200 miliona upita (10 hiljada različitih) primamo svakog dana. U prosjeku imamo 3-4 hiljade zahtjeva u sekundi, u najaktivnijim trenucima više od 10 hiljada zahtjeva u sekundi. Većina upita je OLAP. Mnogo je manje dodataka, modifikacija i brisanja, što znači da je OLTP opterećenje relativno lagano. Naveo sam sve ove brojeve kako biste mogli procijeniti obim našeg projekta i shvatiti koliko vam naše iskustvo može biti korisno.

Slika prva. Lyrical

Kada smo započeli razvoj, nismo baš razmišljali o tome kakvo će opterećenje pasti na bazu podataka i šta ćemo učiniti ako server prestane da se povlači. Prilikom dizajniranja baze podataka, slijedili smo opće preporuke i nastojali da ne pucamo u nogu, ali smo otišli dalje od općih savjeta poput „nemoj koristiti obrazac Vrijednosti atributa entiteta nismo ušli. Dizajnirali smo po principima normalizacije, izbjegavajući redundantnost podataka i nismo vodili računa o ubrzavanju određenih upita. Čim su stigli prvi korisnici, naišli smo na problem sa performansama. Kao i obično, bili smo potpuno nespremni za ovo. Ispostavilo se da su prvi problemi jednostavni. U pravilu se sve rješavalo dodavanjem novog indeksa. Ali došlo je vrijeme kada su jednostavne zakrpe prestale raditi. Shvativši da nam nedostaje iskustva i da nam je sve teže da shvatimo šta uzrokuje probleme, angažovali smo stručnjake koji su nam pomogli da ispravno postavimo server, povežemo monitoring i pokazali gde da tražimo statistika.

Slika dva. Statistički

Dakle, dnevno imamo oko 10 hiljada različitih upita koji se izvršavaju u našoj bazi podataka. Od ovih 10 hiljada, postoje čudovišta koja se izvršavaju 2-3 miliona puta sa prosečnim vremenom izvršenja od 0.1-0.3 ms, a postoje upiti sa prosečnim vremenom izvršenja od 30 sekundi koji se pozivaju 100 puta dnevno.

Nije bilo moguće optimizirati svih 10 hiljada upita, pa smo odlučili da smislimo gdje da usmjerimo svoje napore kako bismo ispravno poboljšali performanse baze podataka. Nakon nekoliko iteracija, počeli smo dijeliti zahtjeve na tipove.

TOP zahtjevi

Ovo su najteži upiti koji oduzimaju najviše vremena (ukupno vrijeme). To su upiti koji se ili pozivaju vrlo često ili upiti za koje je potrebno jako dugo da se izvrše (dugi i česti upiti su optimizirani u prvim iteracijama borbe za brzinu). Kao rezultat toga, server troši najviše vremena na njihovo izvršavanje. Štaviše, važno je odvojiti najviše zahtjeve prema ukupnom vremenu izvršenja i odvojeno prema IO vremenu. Metode za optimizaciju takvih upita se malo razlikuju.

Uobičajena praksa svih kompanija je da rade sa TOP zahtevima. Malo ih je; optimizacija čak i jednog upita može osloboditi 5-10% resursa. Međutim, kako projekat sazrijeva, optimizacija TOP upita postaje sve više netrivijalan zadatak. Sve jednostavne metode su već razrađene, a za „najteži“ zahtjev je potrebno „samo“ 3-5% resursa. Ako TOP upiti ukupno zauzimaju manje od 30-40% vremena, onda ste najvjerovatnije već uložili napore da brzo rade i vrijeme je da pređete na optimizaciju upita iz sljedeće grupe.
Ostaje da se odgovori na pitanje koliko top upita treba uključiti u ovu grupu. Obično uzimam najmanje 10, ali ne više od 20. Trudim se da se vrijeme prvog i posljednjeg u TOP grupi ne razlikuje više od 10 puta. Odnosno, ako vrijeme izvršenja upita naglo padne sa 1. mjesta na 10., onda uzimam TOP-10, ako je pad postupniji, onda povećavam veličinu grupe na 15 ili 20.

Srednji seljaci

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

Rep

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

Kako ocijeniti svaku grupu?

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

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

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 kolone, od kojih svaka sadrži postotak vremena potrebnog za obradu upita iz ove grupe. Unutar zahtjeva postoje dva broja (u mom slučaju to su 20 i 800) koji odvajaju zahtjeve jedne grupe od druge.

Ovako se otprilike upoređuju udjeli zahtjeva u trenutku kada su radovi na optimizaciji započeli i sada.

Dijagram pokazuje da je udio TOP zahtjeva naglo opao, ali su se „srednjaci“ povećali.
U početku su TOP zahtjevi uključivali očite greške. Vremenom su nestale dječje bolesti, smanjio se udio TOP zahtjeva, a sve više se moralo ulagati napor da se teški zahtjevi ubrzaju.

Za dobijanje 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 liste najčešće korištenih tehnika koje su nam pomogle da ubrzamo TOP upite:

• Redizajn sistema, na primjer, prerada logike obavještavanja korištenjem posrednika poruka umjesto periodičnih upita bazi podataka
• Dodavanje ili mijenjanje indeksa
• Prepisivanje ORM upita na čisti SQL
• Ponovno pisanje logike učitavanja lijenih podataka
• Keširanje kroz denormalizaciju podataka. Na primjer, imamo tabelu vezu Isporuka -> Račun -> Zahtjev -> Prijava. To jest, svaka isporuka je povezana s aplikacijom preko drugih tabela. Kako ne bismo povezivali sve tabele u svakom zahtjevu, duplirali smo vezu na zahtjev u tabeli isporuke.
• Keširanje statičkih tabela sa referentnim knjigama i retko menjanje tabela u programskoj memoriji.

Ponekad su promjene dovele do impresivnog redizajna, ali su obezbjeđivale 5-10% opterećenja sistema i bile su opravdane. Vremenom je auspuh postajao sve manji, pa je bio potreban sve ozbiljniji redizajn.

Zatim smo skrenuli pažnju na drugu grupu zahteva - grupu srednjih seljaka. U njemu ima mnogo više upita i činilo se da će za analizu cijele grupe biti potrebno dosta vremena. Međutim, pokazalo se da je većina upita vrlo jednostavna za optimizaciju, a mnogi problemi su se ponavljali desetine puta u različitim varijacijama. Evo primjera nekih tipičnih optimizacija koje smo primijenili na desetine sličnih upita i svaka grupa optimiziranih upita je rasteretila bazu podataka za 3-5%.

• Umjesto provjere prisutnosti zapisa pomoću COUNT i skeniranja cijele tablice, počeo se koristiti EXISTS
• Riješio se DISTINCT-a (ne postoji opći recept, ali ga se ponekad lako možete riješiti tako što ćete zahtjev ubrzati 10-100 puta).

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

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

  napravio upit na relativno maloj tabeli 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 koristili korelirani podupit, ali on daje ubrzanje više od 10 puta.

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

  UPPER(s) LIKE JOHN%’ 
  

  koristite

  s ILIKE “John%”
  

Svaki konkretan zahtjev je ponekad ubrzan za 3-1000 puta. Uprkos impresivnim performansama, u početku nam se činilo da nema smisla optimizirati upit koji traje 10 ms da se završi, jedan je od 3. stotine najtežih upita i zauzima stoti dio procenta ukupnog vremena učitavanja baze podataka. Ali primjenom istog recepta na grupu upita istog tipa, 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 istog tipa. Kao rezultat toga, automatsko pretraživanje grupa upita omogućilo nam je da uz skroman napor dodatno poboljšamo naše performanse.

Kao rezultat toga, već tri godine radimo na istom hardveru. Prosječno dnevno opterećenje je oko 30%, u špicama dostiže 70%. Broj zahtjeva, kao i broj korisnika, povećan je otprilike 10 puta. I sve to zahvaljujući stalnom praćenju ovih istih grupa TOP-MEDIUM zahtjeva. Čim se pojavi novi zahtjev u TOP grupi, odmah ga analiziramo i pokušavamo da ga ubrzamo. Pregledamo MEDIUM grupu jednom sedmično koristeći skripte za analizu upita. Ako naiđemo na nove upite koje već znamo optimizirati, brzo ih mijenjamo. Ponekad pronađemo nove metode optimizacije koje se mogu primijeniti na nekoliko upita odjednom.

Prema našim prognozama, sadašnji server će izdržati povećanje broja korisnika za još 3-5 puta. Istina, imamo još jednog keca u rukavu - još uvijek nismo prenijeli SELECT upite u ogledalo, kao što se preporučuje. Ali mi to ne radimo svjesno, jer želimo prvo u potpunosti iscrpiti mogućnosti “pametne” optimizacije prije nego što uključimo “tešku artiljeriju”.
Kritički pogled na obavljeni posao može predložiti korištenje vertikalnog skaliranja. Kupite moćniji server umjesto da gubite vrijeme stručnjaka. Server možda neće koštati toliko, pogotovo jer još nismo iscrpili granice vertikalnog skaliranja. Međutim, samo se broj zahtjeva povećao 10 puta. Tokom nekoliko godina funkcionalnost sistema se povećala i sada ima više vrsta zahtjeva. Zahvaljujući keširanju, funkcionalnost koja je postojala se izvodi u manjem broju zahtjeva i efikasnijim zahtjevima. To znači da možete sigurno pomnožiti sa još 5 da biste dobili pravi koeficijent ubrzanja. Dakle, prema najkonzervativnijim procjenama, možemo reći da je ubrzanje bilo 50 ili više puta. Vertikalno okretanje servera koštalo bi 50 puta više. Pogotovo imajući u vidu da jednom kada se izvrši optimizacija radi stalno, a račun za iznajmljeni server dolazi svaki mjesec.

izvor: www.habr.com