{"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\/sl\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","title":{"rendered":"Optimizacija poizvedb v bazi podatkov na primeru B2B storitve za gradbenike","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Kako pove\u010dati \u0161tevilo poizvedb v podatkovni bazi za 10-krat, ne da bi pre\u0161li na zmogljivej\u0161i stre\u017enik, in ohraniti delovanje sistema? Povedal bom, kako smo se borili z upadom zmogljivosti na\u0161e baze podatkov in kako smo optimizirali SQL poizvedbe, da bi slu\u017eili \u010dim ve\u010d uporabnikom, ne da bi pove\u010dali stro\u0161ke ra\u010dunalni\u0161kih virov.
\n<\/a><\/noindex>
\nUstvarjam storitev za upravljanje poslovnih procesov v gradbenih podjetjih. Z nami sodeluje pribli\u017eno 3000 podjetij. Ve\u010d kot 10.000 ljudi vsak dan uporablja na\u0161 sistem od 4 do 10 ur. Re\u0161uje razli\u010dne naloge na\u010drtovanja, obve\u0161\u010danja, opozarjanja, validacije\u2026 Uporabljamo PostgreSQL 9.6. V na\u0161i podatkovni bazi je pribli\u017eno 300 tabel in vsak dan v njej obdelamo do 200 milijonov poizvedb (10.000 razli\u010dnih). Povpre\u010dno imamo od 3000 do 4000 poizvedb na sekundo, v najaktivnej\u0161ih trenutkih pa ve\u010d kot 10.000 poizvedb na sekundo. Ve\u010dina poizvedb je OLAP. Dodatkov, sprememb in brisanj je bistveno manj, torej je obremenitev OLTP relativno majhna. Te \u0161tevilke sem navedel, da lahko ocenite obseg na\u0161ega projekta in razumete, kako koristen je lahko na\u0161 izku\u0161nja za vas.<\/p>\n

Prva slika. Liri\u010dna<\/h3>\n

\nKo smo za\u010deli z razvojem, nismo posebej razmi\u0161ljali o tem, kak\u0161na obremenitev bo na podatkovni bazi in kaj bomo storili, \u010de stre\u017enik ne bo zmogel. Pri na\u010drtovanju podatkovne baze smo se dr\u017eali splo\u0161nih priporo\u010dil in se trudili, da si ne bi streljali v koleno, vendar nismo \u0161li dlje od splo\u0161nih nasvetov, kot je 'ne uporabljajte vzorca Entity Attribute Values<\/a><\/noindex> Nehali smo se ukvarjati. Na\u010drtovali smo na osnovi na\u010del normalizacije, ki so se izogibala odve\u010dni podatkov in se nismo skrbeli za pospe\u0161evanje dolo\u010denih poizvedb. Ko so prvi uporabniki pri\u0161li, smo se soo\u010dili s te\u017eavami produktivnosti. Kot ponavadi, nismo bili popolnoma pripravljeni na to. Prve te\u017eave so bile preproste. Obi\u010dajno je vse re\u0161ila dodajanje novega indeksa. Toda pri\u0161el je trenutek, ko preproste popravke niso ve\u010d delovale. Ko smo spoznali, da nam primanjkuje izku\u0161enj in da postaja vse te\u017eje razumeti, v \u010dem so te\u017eave, smo najeli strokovnjake, ki so nam pomagali pravilno nastaviti stre\u017enik, povezati nadzor in nam pokazali, kam gledati, da bi dobili statistika<\/a><\/noindex>.<\/p>\n

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

\nImamo pribli\u017eno 10.000 razli\u010dnih poizvedb, ki se na na\u0161i bazi podatkov izvajajo na dan. Od teh 10.000 so 'monstri', ki se izvajajo 2-3 milijone krat s povpre\u010dnim \u010dasom izvajanja 0,1-0,3 ms, in so poizvedbe s povpre\u010dnim \u010dasom izvajanja 30 sekund, ki se izvajajo 100 krat na dan.<\/p>\n

Optimizirati vseh 10.000 poizvedb ni bilo mogo\u010de, zato smo se odlo\u010dili, da ugotovimo, kam usmeriti prizadevanja za pravilno pove\u010danje zmogljivosti baze podatkov. Po ve\u010d iteracijah smo za\u010deli razdeljevati poizvedbe na tipe.<\/p>\n

TOP poizvedbe<\/h4>\n

\nTo so najte\u017eje poizvedbe, ki zahtevajo najve\u010d \u010dasa (skupni \u010das). To so poizvedbe, ki se bodisi zelo pogosto izvajajo ali poizvedbe, ki se zelo dolgo izvajajo (dolge in pogoste poizvedbe so bile optimizirane \u017ee v prvih iteracijah boja za hitrost). Kon\u010dno za njihovo izvajanje stre\u017enik porabi najve\u010d \u010dasa. Pomembno je lo\u010diti top poizvedbe po skupnem \u010dasu izvajanja in posebej po \u010dasu IO. Na\u010dini optimizacije takih poizvedb so nekoliko razli\u010dni.<\/p>\n

Obi\u010dajna praksa vseh podjetij je delo s TOP poizvedbami. Teh je malo; optimizacija tudi ene same poizvedbe lahko sprosti 5-10% virov. Vendar pa z 'odra\u0161\u010danjem' projekta optimizacija TOP poizvedb postaja vse bolj nenavadna naloga. Vse enostavne metode so \u017ee bile uporabljene, in najte\u017eja poizvedba vzame 'samo' 3-5% virov. \u010ce TOP poizvedbe skupaj zahtevajo manj kot 30-40% \u010dasa, ste verjetno \u017ee vlo\u017eili trud, da delujejo hitro, in pri\u0161el je \u010das, da preidete na optimizacijo poizvedb iz naslednje skupine.
\nOstaja odgovoriti na vpra\u0161anje, koliko vrhunskih poizvedb vklju\u010diti v to skupino. Obi\u010dajno izberem najmanj 10, najve\u010d 20. Trudim se, da se \u010das prve in zadnje poizvedbe v TOP skupini ne razlikuje ve\u010d kot 10-krat. To pomeni, da \u010de \u010das izvajanja poizvedb mo\u010dno pade s 1. mesta na 10., izberem TOP-10, \u010de pa je padanje bolj postopno, pove\u010dam velikost skupine na 15 ali 20.
\n\"Optimizacija
\n<\/p>\n

Srednjaki (medium)<\/h4>\n

\nTo so vse poizvedbe, ki so takoj za TOP, razen zadnjih 5-10%. Obi\u010dajno se v optimizaciji teh poizvedb skriva mo\u017enost, da mo\u010dno pove\u010date zmogljivost stre\u017enika. Te poizvedbe lahko 'te\u017eo' do 80%. Vendar, \u010de njihov dele\u017e prese\u017ee 50%, je \u010das, da se nanje povrnejo bolj pozorno.<\/p>\n

Rep (tail)<\/h4>\n

\nKot je bilo omenjeno, ti zahtevki prihajajo na koncu in zanje gre 5-10% \u010dasa. Nanje lahko pozabite, razen \u010de ne uporabljate avtomatskih sredstev za analizo zahtevkov, takrat lahko tudi njihova optimizacija ostane nizkostro\u0161kovna.<\/p>\n

Kako ovrednotiti vsako skupino?<\/p>\n

Uporabljam SQL poizvedbo, ki pomaga dose\u010di to oceno za PostgreSQL (prepri\u010dan sem, da lahko za mnoge druge DBMS napi\u0161emo podobno poizvedbo)<\/p>\n

SQL poizvedba za oceno velikosti skupin TOP-MEDIUM-TAIL<\/b><\/p>\n

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_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\n<\/code><\/pre>\n
Rezultat poizvedbe so trije stolpci, od katerih vsak vsebuje dele\u017e \u010dasa, ki se porabi za obdelavo poizvedb iz te skupine. Znotraj poizvedbe sta dve \u0161tevilki (v mojem primeru 20 in 800), ki lo\u010dujeta poizvedbe ene skupine od druge.<\/p>\n
Tako se pribli\u017eno razmerja dele\u017eev poizvedb na za\u010detku optimizacijskih del in zdaj.<\/p>\n
\"Optimizacija
\n
\nIz grafa je razvidno, da se je dele\u017e TOP poizvedb mo\u010dno zmanj\u0161al, medtem ko so se \u00bbsrednji\u00ab pove\u010dali.
\nSprva so v TOP poizvedbe vstopali o\u010ditni napaki. S\u010dasoma so otro\u0161ke bolezni izginile, dele\u017e TOP poizvedb pa se je zmanj\u0161al, kar je zahtevalo vedno ve\u010d truda za pospe\u0161evanje te\u017ekih poizvedb. <\/p>\n
Da pridobimo besedilo poizvedb, uporabimo tak\u0161no poizvedbo<\/b><\/p>\n
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\n<\/code><\/pre>\n
Tukaj je seznam najpogosteje uporabljenih metod, ki so nam pomagale pospe\u0161iti TOP poizvedbe:<\/p>\n