Cum poți crește de 10 ori numărul de interogări către baza de date fără a trece pe un server mai performant și păstrând funcționalitatea sistemului? Voi povesti despre cum am combătut scăderea performanței bazei noastre de date, cum am optimizat interogările SQL pentru a deservi cât mai mulți utilizatori fără a crește costurile resurselor de calcul.
Fac un serviciu pentru gestionarea proceselor de afaceri în companiile de construcții. Colaborează cu noi aproximativ 3.000 de companii. Peste 10.000 de persoane folosesc sistemul nostru zilnic timp de 4-10 ore. Rezolvă diverse sarcini de planificare, notificare, avertizare, validare… Folosim PostgreSQL 9.6. În baza de date avem aproximativ 300 de tabele, iar zilnic primim până la 200 milioane de interogări (10.000 diferite). În medie avem 3-4.000 de interogări pe secundă, iar în cele mai active momente, mai mult de 10.000 de interogări pe secundă. Majoritatea interogărilor sunt OLAP. Adăugările, modificările și ștergerile sunt mult mai puține, adică încărcătura OLTP este relativ mică. Toate aceste cifre le-am menționat pentru a putea evalua amploarea proiectului nostru și a înțelege cât de util poate fi experiența noastră pentru voi.
Imaginea întâi. Lirică
Când am început dezvoltarea, nu ne-am gândit prea mult la ce încărcare va suporta baza de date și ce vom face dacă serverul nu va mai putea face față. Când am proiectat baza de date, am urmat recomandările generale și ne-am străduit să nu ne facem singuri rău, dar mai departe de recomandările generale cum ar fi „nu utilizați modelul nu am avansat. Am proiectat având în vedere principiile normalizării, evitând redundanța datelor și fără a ne îngrijora de accelerarea anumitor interogări. Odată ce au venit primii utilizatori, ne-am confruntat cu probleme de performanță. Ca de obicei, nu am fost deloc pregătiți pentru asta. Primele probleme au fost simple. De obicei, totul s-a rezolvat prin adăugarea unui nou index. Dar a venit un moment când soluțiile simple au încetat să mai funcționeze. Realizând că ne lipsește experiența și că ne este din ce în ce mai greu să înțelegem cauzele problemelor, am angajat specialiști care ne-au ajutat să configurăm corect serverul, să conectăm monitorizarea și ne-au arătat unde să ne uităm pentru a obține .
Imaginea a doua. Statistică
Așadar, avem aproximativ 10.000 de cereri diverse care se execută în baza noastră de date într-o zi. Dintre cele 10.000, există monștri care se execută de 2-3 milioane de ori, cu un timp mediu de execuție de 0,1-0,3 ms, și cereri cu un timp mediu de execuție de 30 de secunde, care sunt apelate de 100 de ori pe zi.
Optimizarea tuturor celor 10.000 de cereri nu a fost posibilă, așa că am decis să ne concentrăm asupra aspectelor unde putem direcționa eforturile pentru a îmbunătăți performanța bazei de date în mod corespunzător. După câteva iterații, am început să clasificăm cererile pe tipuri.
CERERILE TOP
Acestea sunt cele mai grele cereri, care consumă cel mai mult timp (timp total). Acestea sunt cereri care fie sunt foarte frecvente, fie cereri care durează foarte mult să se execute (cereri lungi și frecvente au fost optimizate încă de la primele iterații în căutarea vitezei). Ca rezultat, serverul cheltuie cel mai mult timp pentru executarea lor. Este important să separăm cererile de top după timpul total de execuție și separat după timpul IO. Metodele de optimizare a acestor cereri sunt puțin diferite.
Practicile obișnuite ale tuturor companiilor constau în a lucra cu cererile TOP. Acestea sunt puține, optimizarea chiar și a unei singure cereri poate elibera 5-10% din resurse. Totuși, pe măsură ce proiectul „îmbătrânește”, optimizarea cererilor TOP devine o sarcină din ce în ce mai complicată. Toate metodele simple au fost deja aplicate, iar cea mai „greu” cerere consumă „doar” 3-5% din resurse. Dacă cererile TOP, în total, reprezintă mai puțin de 30-40% din timp, este foarte probabil că deja ați depus eforturi pentru a le face să funcționeze rapid și a venit momentul să treceți la optimizarea cererilor din următoarea grupă.
Rămâne de răspuns la întrebarea câte cereri de top să includem în această grupă. De obicei, iau nu mai puțin de 10, dar nu mai mult de 20. Încerc să mă asigur că timpul primului și celui din urmă din grupă TOP diferă cu nu mai mult de 10 ori. Așadar, dacă timpul de execuție al cererilor scade brusc de la locul 1 la 10, iau TOP-10; dacă scăderea este mai lină, atunci măresc dimensiunea grupului la 15 sau 20.
Cererile medii
Acestea sunt toate cererile care vin imediat după cererile TOP, cu excepția ultimelor 5-10%. De obicei, în optimizarea acestor cereri se află oportunitatea de a crește semnificativ performanța serverului. Aceste cereri pot „intra” până la 80%. Dar chiar dacă ponderea lor depășește 50%, înseamnă că este timpul să le privim mai atent.
Coadă
Așa cum a fost menționat, aceste interogări sunt la final și durează 5-10% din timp. Le poți uita, doar dacă nu folosești instrumente automate de analiză a interogărilor, atunci optimizarea lor poate fi ieftină.
Cum să evaluăm fiecare grupă?
Folosesc o interogare SQL, care ajută la evaluarea acesteia pentru PostgreSQL (sunt sigur că pentru multe alte SGBD-uri se poate scrie o interogare similară).
Interogare SQL pentru evaluarea dimensiunii grupelor 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 AND 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
Rezultatul interogării - trei coloane, fiecare conținând procentajul de timp care este folosit pentru procesarea interogărilor din acest grup. În interiorul interogării sunt două numere (în cazul meu, acestea sunt 20 și 800), care separă interogările unei grupe de alta.
Așa arată proporțiile interogărilor la începutul lucrărilor de optimizare și acum.
Din diagramă se observă că ponderea interogărilor TOP a scăzut brusc, în schimb au crescut „intermediarele”.
La început, interogările TOP conțineau evidente erori. În timp, bolile copilăriei au dispărut, ponderea interogărilor TOP a scăzut, a fost necesar să depunem din ce în ce mai multe eforturi pentru a accelera interogările dificile.
Pentru a obține textul interogărilor, folosim următoarea interogare.
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 AND rn 800 -- TAIL
Iată lista celor mai frecvent utilizate metode, care ne-au ajutat să accelerăm interogările TOP:
- Redesign al sistemului, de exemplu, refacerea logicii notificărilor pe un message broker în loc de interogări periodice către baza de date.
- Adăugarea sau modificarea indecșilor.
- Rescrierea interogărilor ORM în SQL simplu.
- Rescrierea logicii de încărcare lazy a datelor.
- Cache prin denormalizarea datelor. De exemplu, avem o legătură între tabelele Livrare -> Factură -> Interogare -> Cerere. Asta înseamnă că fiecare livrare este legată de o cerere prin alte tabele. Pentru a nu lega toate tabelele în fiecare interogare, am duplicat referința la cerere în tabela Livrare.
- Cache-ul tabelelor statice cu referințe și tabele rareori modificate în memoria programului.
Uneori, modificările duceau la un redesign semnificativ, dar ofereau o reducere de 5-10% a sarcinii sistemului și erau justificate. Cu timpul, beneficiile deveneau din ce în ce mai mici, iar redesignul necesita o atenție mai serioasă.
Atunci am observat a doua grupă de cereri - grupul mijlociu. Aceasta conținea mult mai multe cereri și părea că analiza întregii grupe va dura foarte mult timp. Cu toate acestea, majoritatea cererilor s-au dovedit a fi foarte simple pentru optimizare, iar multe probleme se repetau zeci de ori în diverse variații. Iată exemple de optimizări tipice pe care le-am aplicat la zeci de cereri asemănătoare, fiecare grupă de cereri optimizate reducând sarcina bazei de date cu 3-5%.
- În loc să verificăm existența înregistrărilor prin COUNT și să facem o scanare completă a tabelei, am început să folosim EXISTS.
- Ne-am descurcat de DISTINCT (nu există o rețetă universală, dar uneori se poate renunța la el cu ușurință, accelerând cererea de 10-100 de ori).
De exemplu, în loc de cererea pentru extragerea tuturor șoferilor dintr-o mare tabelă de livrări (DELIVERY)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDam realizat cererea pe o tabelă relativ mică de 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)Părea că am folosit un subquery corelat, dar acesta oferă un avans de peste 10 ori.
- În multe cazuri am renunțat complet la COUNT și
- în loc de
UPPER(s) LIKE JOHN%folosim
s ILIKE “John%”,
Fiecare cerere specifică a fost accelerată de 3-1000 de ori. În ciuda statisticilor impresionante, la început ne-a părut că nu are sens optimizarea unei cereri care se execută în 10 ms, se află în a treia sută dintre cele mai grele cereri și în ansamblul timpului de încărcare a bazei de date ocupă procente mici. Dar aplicând aceeași rețetă unui grup de cereri omogene, ne-am recâștigat câteva procente. Pentru a nu pierde timp cu vizionarea manuală a tuturor sutelor de cereri, am scris câteva scripturi simple care, prin expresii regulate, găseau cereri similare. Ca rezultat, căutarea automată a grupurilor de cereri ne-a permis să îmbunătățim și mai mult performanța noastră, investind eforturi modeste.
În cele din urmă, am lucrat timp de trei ani pe același hardware. Sarcina medie zilnică este de aproximativ 30%, iar în momentele de vârf ajunge la 70%. Numărul de solicitări, la fel ca și numărul de utilizatori, a crescut de aproximativ 10 ori. Și toate acestea datorită monitorizării constante a grupurilor de solicitări TOP-MEDIUM. De îndată ce apare o nouă solicitare în grupul TOP, o analizăm imediat și încercăm să o accelerăm. Grupul MEDIUM este revizuit o dată pe săptămână cu ajutorul scripturilor de analiză a solicitărilor. Dacă găsim noi solicitări pe care știm deja cum să le optimizăm, le modificăm rapid. Uneori găsim noi metode de optimizare care pot fi aplicate imediat mai multor solicitări.
Conform prognozelor noastre, serverul actual va suporta o creștere a numărului de utilizatori de încă 3-5 ori. Cu toate acestea, mai avem un atu în mânecă - nu am migrat încă solicitările SELECT pe oglindă, așa cum se recomandă. Dar nu facem acest lucru în mod conștient, deoarece dorim să epuizăm mai întâi posibilitățile de optimizare „inteligentă”, înainte de a activa „arta grea”.
O privire critică asupra muncii efectuate poate sugera utilizarea scalării verticale. Să cumpărăm un server mai puternic, în loc să pierdem timpul specialiștilor. Un server nu poate costa atât de mult, mai ales că limitele scalării verticale nu sunt încă epuizate. Totuși, numărul solicitărilor a crescut de 10 ori. În câțiva ani, funcționalitatea sistemului a crescut și acum varietățile de solicitări s-au înmulțit. Funcționalitatea existentă, datorită memorizării cache, este realizată cu un număr mai mic de solicitări, iar dezechilibrat mai eficient. Așadar, putem să multiplicăm cu 5 pentru a obține un coeficient real de accelerare. Astfel, în cele mai modeste estimări, se poate spune că accelerarea a fost de 50 de ori sau mai mult. A scala vertical un server de 50 de ori ar costa mai mult. În special având în vedere că o optimizare realizată odată funcționează tot timpul, iar factura pentru serverul închiriat vine în fiecare lună.
Sursa: habr.com
