{"id":36704,"date":"2019-10-31T22:13:15","date_gmt":"2019-10-31T19:13:15","guid":{"rendered":"https:\/\/prohoster.info\/blog\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej\/"},"modified":"2019-10-31T22:13:15","modified_gmt":"2019-10-31T19:13:15","slug":"optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","status":"publish","type":"post","link":"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","title":{"rendered":"Optimizarea interog\u0103rilor de baz\u0103 de date pe exemplul unui serviciu B2B pentru constructori","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Cum po\u021bi cre\u0219te de 10 ori num\u0103rul de interog\u0103ri c\u0103tre baza de date f\u0103r\u0103 a trece pe un server mai performant \u0219i p\u0103str\u00e2nd func\u021bionalitatea sistemului? Voi povesti despre cum am comb\u0103tut sc\u0103derea performan\u021bei bazei noastre de date, cum am optimizat interog\u0103rile SQL pentru a deservi c\u00e2t mai mul\u021bi utilizatori f\u0103r\u0103 a cre\u0219te costurile resurselor de calcul.
\n<\/a><\/noindex>
\nFac un serviciu pentru gestionarea proceselor de afaceri \u00een companiile de construc\u021bii. Colaboreaz\u0103 cu noi aproximativ 3.000 de companii. Peste 10.000 de persoane folosesc sistemul nostru zilnic timp de 4-10 ore. Rezolv\u0103 diverse sarcini de planificare, notificare, avertizare, validare\u2026 Folosim PostgreSQL 9.6. \u00cen baza de date avem aproximativ 300 de tabele, iar zilnic primim p\u00e2n\u0103 la 200 milioane de interog\u0103ri (10.000 diferite). \u00cen medie avem 3-4.000 de interog\u0103ri pe secund\u0103, iar \u00een cele mai active momente, mai mult de 10.000 de interog\u0103ri pe secund\u0103. Majoritatea interog\u0103rilor sunt OLAP. Ad\u0103ug\u0103rile, modific\u0103rile \u0219i \u0219tergerile sunt mult mai pu\u021bine, adic\u0103 \u00eenc\u0103rc\u0103tura OLTP este relativ mic\u0103. Toate aceste cifre le-am men\u021bionat pentru a putea evalua amploarea proiectului nostru \u0219i a \u00een\u021belege c\u00e2t de util poate fi experien\u021ba noastr\u0103 pentru voi.<\/p>\n

Imaginea \u00eent\u00e2i. Liric\u0103<\/h3>\n

\nC\u00e2nd am \u00eenceput dezvoltarea, nu ne-am g\u00e2ndit prea mult la ce \u00eenc\u0103rcare va suporta baza de date \u0219i ce vom face dac\u0103 serverul nu va mai putea face fa\u021b\u0103. C\u00e2nd am proiectat baza de date, am urmat recomand\u0103rile generale \u0219i ne-am str\u0103duit s\u0103 nu ne facem singuri r\u0103u, dar mai departe de recomand\u0103rile generale cum ar fi \u201enu utiliza\u021bi modelul Entity Attribute Values<\/a><\/noindex> nu am avansat. Am proiectat av\u00e2nd \u00een vedere principiile normaliz\u0103rii, evit\u00e2nd redundan\u021ba datelor \u0219i f\u0103r\u0103 a ne \u00eengrijora de accelerarea anumitor interog\u0103ri. Odat\u0103 ce au venit primii utilizatori, ne-am confruntat cu probleme de performan\u021b\u0103. Ca de obicei, nu am fost deloc preg\u0103ti\u021bi pentru asta. Primele probleme au fost simple. De obicei, totul s-a rezolvat prin ad\u0103ugarea unui nou index. Dar a venit un moment c\u00e2nd solu\u021biile simple au \u00eencetat s\u0103 mai func\u021bioneze. Realiz\u00e2nd c\u0103 ne lipse\u0219te experien\u021ba \u0219i c\u0103 ne este din ce \u00een ce mai greu s\u0103 \u00een\u021belegem cauzele problemelor, am angajat speciali\u0219ti care ne-au ajutat s\u0103 configur\u0103m corect serverul, s\u0103 conect\u0103m monitorizarea \u0219i ne-au ar\u0103tat unde s\u0103 ne uit\u0103m pentru a ob\u021bine statistic\u0103<\/a><\/noindex>.<\/p>\n

Imaginea a doua. Statistic\u0103<\/h3>\n

\nA\u0219adar, avem aproximativ 10.000 de cereri diverse care se execut\u0103 \u00een baza noastr\u0103 de date \u00eentr-o zi. Dintre cele 10.000, exist\u0103 mon\u0219tri care se execut\u0103 de 2-3 milioane de ori, cu un timp mediu de execu\u021bie de 0,1-0,3 ms, \u0219i cereri cu un timp mediu de execu\u021bie de 30 de secunde, care sunt apelate de 100 de ori pe zi.<\/p>\n

Optimizarea tuturor celor 10.000 de cereri nu a fost posibil\u0103, a\u0219a c\u0103 am decis s\u0103 ne concentr\u0103m asupra aspectelor unde putem direc\u021biona eforturile pentru a \u00eembun\u0103t\u0103\u021bi performan\u021ba bazei de date \u00een mod corespunz\u0103tor. Dup\u0103 c\u00e2teva itera\u021bii, am \u00eenceput s\u0103 clasific\u0103m cererile pe tipuri.<\/p>\n

CERERILE TOP<\/h4>\n

\nAcestea sunt cele mai grele cereri, care consum\u0103 cel mai mult timp (timp total). Acestea sunt cereri care fie sunt foarte frecvente, fie cereri care dureaz\u0103 foarte mult s\u0103 se execute (cereri lungi \u0219i frecvente au fost optimizate \u00eenc\u0103 de la primele itera\u021bii \u00een c\u0103utarea vitezei). Ca rezultat, serverul cheltuie cel mai mult timp pentru executarea lor. Este important s\u0103 separ\u0103m cererile de top dup\u0103 timpul total de execu\u021bie \u0219i separat dup\u0103 timpul IO. Metodele de optimizare a acestor cereri sunt pu\u021bin diferite.<\/p>\n

Practicile obi\u0219nuite ale tuturor companiilor constau \u00een a lucra cu cererile TOP. Acestea sunt pu\u021bine, optimizarea chiar \u0219i a unei singure cereri poate elibera 5-10% din resurse. Totu\u0219i, pe m\u0103sur\u0103 ce proiectul \u201e\u00eemb\u0103tr\u00e2ne\u0219te\u201d, optimizarea cererilor TOP devine o sarcin\u0103 din ce \u00een ce mai complicat\u0103. Toate metodele simple au fost deja aplicate, iar cea mai \u201egreu\u201d cerere consum\u0103 \u201edoar\u201d 3-5% din resurse. Dac\u0103 cererile TOP, \u00een total, reprezint\u0103 mai pu\u021bin de 30-40% din timp, este foarte probabil c\u0103 deja a\u021bi depus eforturi pentru a le face s\u0103 func\u021bioneze rapid \u0219i a venit momentul s\u0103 trece\u021bi la optimizarea cererilor din urm\u0103toarea grup\u0103.
\nR\u0103m\u00e2ne de r\u0103spuns la \u00eentrebarea c\u00e2te cereri de top s\u0103 includem \u00een aceast\u0103 grup\u0103. De obicei, iau nu mai pu\u021bin de 10, dar nu mai mult de 20. \u00cencerc s\u0103 m\u0103 asigur c\u0103 timpul primului \u0219i celui din urm\u0103 din grup\u0103 TOP difer\u0103 cu nu mai mult de 10 ori. A\u0219adar, dac\u0103 timpul de execu\u021bie al cererilor scade brusc de la locul 1 la 10, iau TOP-10; dac\u0103 sc\u0103derea este mai lin\u0103, atunci m\u0103resc dimensiunea grupului la 15 sau 20.
\n\"Optimizarea
\n<\/p>\n

Cererile medii<\/h4>\n

\nAcestea sunt toate cererile care vin imediat dup\u0103 cererile TOP, cu excep\u021bia ultimelor 5-10%. De obicei, \u00een optimizarea acestor cereri se afl\u0103 oportunitatea de a cre\u0219te semnificativ performan\u021ba serverului. Aceste cereri pot \u201eintra\u201d p\u00e2n\u0103 la 80%. Dar chiar dac\u0103 ponderea lor dep\u0103\u0219e\u0219te 50%, \u00eenseamn\u0103 c\u0103 este timpul s\u0103 le privim mai atent.<\/p>\n

Coad\u0103<\/h4>\n

\nA\u0219a cum a fost men\u021bionat, aceste interog\u0103ri sunt la final \u0219i dureaz\u0103 5-10% din timp. Le po\u021bi uita, doar dac\u0103 nu folose\u0219ti instrumente automate de analiz\u0103 a interog\u0103rilor, atunci optimizarea lor poate fi ieftin\u0103.<\/p>\n

Cum s\u0103 evalu\u0103m fiecare grup\u0103?<\/p>\n

Folosesc o interogare SQL, care ajut\u0103 la evaluarea acesteia pentru PostgreSQL (sunt sigur c\u0103 pentru multe alte SGBD-uri se poate scrie o interogare similar\u0103).<\/p>\n

Interogare SQL pentru evaluarea dimensiunii grupelor TOP-MEDIUM-TAIL<\/b><\/p>\n

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail\nFROM\n(\n  SELECT CASE WHEN rn  20 AND rn  800 THEN tt_percent ELSE 0 END AS time_tail\n  FROM (\n    SELECT total_time \/ (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,\n    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn\n    FROM pg_stat_statements\n    ORDER BY total_time DESC\n  ) AS t\n)\nAS ts\n<\/code><\/pre>\n
Rezultatul interog\u0103rii - trei coloane, fiecare con\u021bin\u00e2nd procentajul de timp care este folosit pentru procesarea interog\u0103rilor din acest grup. \u00cen interiorul interog\u0103rii sunt dou\u0103 numere (\u00een cazul meu, acestea sunt 20 \u0219i 800), care separ\u0103 interog\u0103rile unei grupe de alta.<\/p>\n
A\u0219a arat\u0103 propor\u021biile interog\u0103rilor la \u00eenceputul lucr\u0103rilor de optimizare \u0219i acum.<\/p>\n
\"Optimizarea
\n
\nDin diagram\u0103 se observ\u0103 c\u0103 ponderea interog\u0103rilor TOP a sc\u0103zut brusc, \u00een schimb au crescut \u201eintermediarele\u201d.
\nLa \u00eenceput, interog\u0103rile TOP con\u021bineau evidente erori. \u00cen timp, bolile copil\u0103riei au disp\u0103rut, ponderea interog\u0103rilor TOP a sc\u0103zut, a fost necesar s\u0103 depunem din ce \u00een ce mai multe eforturi pentru a accelera interog\u0103rile dificile. <\/p>\n
Pentru a ob\u021bine textul interog\u0103rilor, folosim urm\u0103toarea interogare.<\/b><\/p>\n
SELECT * FROM (\n  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\n  FROM pg_stat_statements\n  ORDER BY total_time DESC\n) AS T\nWHERE\nrn  20 AND rn  800  -- TAIL\n<\/code><\/pre>\n
Iat\u0103 lista celor mai frecvent utilizate metode, care ne-au ajutat s\u0103 acceler\u0103m interog\u0103rile TOP:<\/p>\n