{"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\/hr\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","title":{"rendered":"Optimizacija upita baza podataka na primjeru B2B usluge za gra\u0111evinare","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Kako pove\u0107ati broj upita na bazi podataka deset puta bez preseljenja na ja\u010di server i zadr\u017eati funkcionalnost sistema? Objasni\u0107u kako smo se borili protiv opadanja performansi na\u0161e baze podataka, kako smo optimizovali SQL upite da slu\u017ee \u0161to ve\u0107em broju korisnika, a da ne pove\u0107amo tro\u0161kove ra\u010dunarskih resursa.
\n<\/a><\/noindex>
\nRazvijam servis za upravljanje poslovnim procesima u gra\u0111evinskim kompanijama. Sa nama radi oko 3.000 kompanija. Vi\u0161e od 10.000 ljudi svakodnevno koristi na\u0161 sistem od 4 do 10 sati. Re\u0161ava razne zadatke planiranja, obave\u0161tavanja, upozoravanja, validacije\u2026 Koristimo PostgreSQL 9.6. U bazi podataka imamo oko 300 tabela i svakodnevno se izvr\u0161i do 200 miliona upita (10.000 razli\u010ditih). U proseku imamo 3.000 do 4.000 upita u sekundi, a u najsna\u017enijim trenucima vi\u0161e od 10.000 upita po sekundi. Veliki deo upita su OLAP. Dodavanja, modifikacije i brisanja su znatno manja, \u0161to zna\u010di da je OLTP optere\u0107enje relativno malo. Sve ove brojke sam naveo kako biste mogli proceniti razmeru na\u0161eg projekta i razumeti koliko na\u0161u iskustvo mo\u017ee biti korisno za vas.<\/p>\n

Prva slika. Lirska<\/h3>\n

\nKada smo zapo\u010deli razvoj, nismo mnogo razmi\u0161ljali o tome kakvo optere\u0107enje \u0107e biti na bazi podataka i \u0161ta \u0107emo raditi ukoliko server prestane da izdr\u017ei. Tokom projektovanja baze podataka pratili smo op\u0161te preporuke i trudili se da ne napravimo gre\u0161ke, ali nismo i\u0161li dalje od op\u0107ih saveta poput \"ne koristite obrazac Entity Attribute Values<\/a><\/noindex> nismo i\u0161li. Projektovali smo prema principima normalizacije izbegavaju\u0107i suvi\u0161nost podataka i nismo se brinuli o ubrzanju odre\u0111enih upita. Kada su prvi korisnici do\u0161li, suo\u010dili smo se s problemom performansi. Kao i obi\u010dno, nismo bili apsolutno spremni za to. Prvi problemi su bili jednostavni. Uglavnom se sve re\u0161avalo dodavanjem novog indeksa. Me\u0111utim, do\u0161ao je trenutak kada su jednostavne nadogradnje prestale da funkcioni\u0161u. Svjesni da nam nedostaje iskustva i da nam je sve te\u017ee razumeti uzrok problema, anga\u017eovali smo stru\u010dnjake koji su nam pomogli da pravilno konfiguri\u0161emo server, pove\u017eemo monitoring i pokazali nam gde da gledamo za dobijanje statistike<\/a><\/noindex>.<\/p>\n

Druga slika. Statisti\u010dka<\/h3>\n

\nDakle, imamo oko 10 tisu\u0107a razli\u010ditih zahtjeva koji se izvr\u0161avaju na na\u0161oj bazi podataka svakodnevno. Od tih 10 tisu\u0107a, postoje 'monstri' koji se izvr\u0161avaju 2-3 milijuna puta s prosje\u010dnim vremenom izvr\u0161enja od 0.1-0.3 ms, te zahtjevi koji imaju prosje\u010dno vreme izvr\u0161enja od 30 sekundi, a pozivaju se 100 puta dnevno.<\/p>\n

Optimizirati svih 10 tisu\u0107a zahtjeva nije bilo mogu\u0107e, pa smo odlu\u010dili istra\u017eiti kuda usmjeriti napore kako bismo pravilno pove\u0107ali performanse baze podataka. Nakon nekoliko iteracija, po\u010deli smo dijeliti zahtjeve prema vrstama.<\/p>\n

TOP zahtjevi<\/h4>\n

\nTo su najte\u017ei zahtjevi koji zauzimaju najvi\u0161e vremena (ukupno vrijeme). To su zahtjevi koji se ili vrlo \u010desto pozivaju ili zahtjevi koji se upravo vrlo dugo izvr\u0161avaju (dugi i \u010desti zahtjevi su optimizirani jo\u0161 u prvim iteracijama borbe za brzinu). Na kraju, server tro\u0161i najvi\u0161e vremena na njihovo izvr\u0161avanje. Va\u017eno je odvojiti top zahtjeve prema ukupnom vremenu izvr\u0161enja i zasebno prema IO vremenu. Na\u010dini optimizacije takvih zahtjeva su malo druga\u010diji.<\/p>\n

Uobi\u010dajena praksa svih kompanija je rad s TOP zahtjevima. Ima ih malo i optimizacija \u010dak i jednog zahtjeva mo\u017ee osloboditi 5-10% resursa. Me\u0111utim, kako projekt 'odrasta', optimizacija TOP zahtjeva postaje sve slo\u017eeniji zadatak. Svi jednostavni na\u010dini su ve\u0107 isprobani, a najte\u017ei zahtjev tro\u0161i 'samo' 3-5% resursa. Ako TOP zahtjevi ukupno zauzimaju manje od 30-40% vremena, vjerojatno ste ve\u0107 ulo\u017eili napore kako bi radili brzo i do\u0161lo je vrijeme za prelazak na optimizaciju zahtjeva iz sljede\u0107e grupe.
\nPreostaje odgovoriti na pitanje koliko gornjih zahtjeva uklju\u010diti u ovu grupu. Obi\u010dno uzimam ne manje od 10, ali ne vi\u0161e od 20. Poku\u0161avam osigurati da se vrijeme prvog i posljednjeg u TOP grupi razlikuje ne vi\u0161e od 10 puta. To jest, ako se vrijeme izvr\u0161enja zahtjeva naglo smanjuje sa 1. mjesta na 10., uzimam TOP-10, ako je pad postupan, pove\u0107avam veli\u010dinu grupe na 15 ili 20.
\n\"Optimizacija
\n<\/p>\n

Srednjaci (medium)<\/h4>\n

\nTo su svi zahtjevi koji dolaze odmah nakon TOP-a, osim posljednjih 5-10%. Obi\u010dno u optimizaciji upravo tih zahtjeva le\u017ei mogu\u0107nost zna\u010dajnog pove\u0107anja performansi servera. Ovi zahtjevi mogu 'zauzimati' do 80%. Ali \u010dak i ako njihov udio prema\u0161i 50%, tada je pravo vrijeme da im se posveti pa\u017enja.<\/p>\n

Rep (tail)<\/h4>\n

\nKao \u0161to je re\u010deno, ovi upiti dolaze na kraju i oduzimaju 5-10% vremena. Mo\u017eete ih zaboraviti samo ako ne koristite automatske alate za analizu upita, tada njihova optimizacija tako\u0111e mo\u017ee biti povoljnija.<\/p>\n

Kako procijeniti svaku grupu?<\/p>\n

Koristim SQL upit koji poma\u017ee u toj procjeni za PostgreSQL (siguran sam da se mo\u017ee napisati sli\u010dan upit za mnoge druge DBMS).<\/p>\n

SQL upit za procjenu veli\u010dine TOP-MEDIUM-TAIL grupa.<\/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
Rezultat upita - tri kolone, svaka od njih sadr\u017ei postotak vremena koje se tro\u0161i na obradu upita iz te grupe. Unutra upita postoje dva broja (u mom slu\u010daju to su 20 i 800), koji odvajaju upite jedne grupe od druge.<\/p>\n
Ovako otprilike izgleda odnos izme\u0111u postotaka upita u trenutku po\u010detka optimizacije i sada.<\/p>\n
\"Optimizacija
\n
\nIz dijagrama se vidi da je udio TOP upita naglo smanjen, a udio \"srednjaka\" se pove\u0107ao.
\nIsprva su u TOP upite ulazili o\u010digledni propusti. S vremenom su dje\u010dje bolesti nestale, udio TOP upita se smanjivao, a bilo je potrebno ulo\u017eiti sve vi\u0161e truda da se ubrzaju te\u0161ki upiti. <\/p>\n
Da bismo dobili tekst upita, koristimo ovakav upit.<\/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
Evo popisa naj\u010de\u0161\u0107e kori\u0161tenih tehnika koje su nam pomagale da ubrzamo TOP upite:<\/p>\n