ProHoster > Blogs > Administrācija > Datu bāzes vaicājumu optimizÄ“Å”ana, izmantojot B2B pakalpojuma piemēru celtniekiem

Datu bāzes vaicājumu optimizÄ“Å”ana, izmantojot B2B pakalpojuma piemēru celtniekiem

Kā palielināt datubāzes vaicājumu skaitu 10 reizes, nepārejot uz produktÄ«vāku serveri, un uzturēt sistēmas funkcionalitāti? Es jums pastāstÄ«Å”u, kā mēs tikām galā ar mÅ«su datu bāzes veiktspējas samazināŔanos, kā optimizējām SQL vaicājumus, lai tie apkalpotu pēc iespējas vairāk lietotāju un nepalielinātu skaitļoÅ”anas resursu izmaksas.

Veicu pakalpojumu biznesa procesu vadÄ«Å”anai bÅ«vniecÄ«bas uzņēmumos. Ar mums strādā apmēram 3 tÅ«kstoÅ”i uzņēmumu. Vairāk nekā 10 tÅ«kstoÅ”i cilvēku strādā ar mÅ«su sistēmu katru dienu 4-10 stundas. Tas atrisina dažādas plānoÅ”anas, paziņoÅ”anas, brÄ«dināŔanas, validācijas problēmas... Mēs izmantojam PostgreSQL 9.6. Mums datu bāzē ir aptuveni 300 tabulu un katru dienu tiek saņemti lÄ«dz 200 miljoniem vaicājumu (10 tÅ«kstoÅ”i dažādu). Vidēji mums ir 3-4 tÅ«kstoÅ”i pieprasÄ«jumu sekundē, aktÄ«vākajos brīžos vairāk nekā 10 tÅ«kstoÅ”i pieprasÄ«jumu sekundē. Lielākā daļa vaicājumu ir OLAP. Ir daudz mazāk papildinājumu, modifikāciju un svÄ«trojumu, kas nozÄ«mē, ka OLTP slodze ir salÄ«dzinoÅ”i neliela. Es norādÄ«ju visus Å”os skaitļus, lai jÅ«s varētu novērtēt mÅ«su projekta mērogu un saprast, cik noderÄ«ga jums var bÅ«t mÅ«su pieredze.

Attēls viens. Lirisks

Uzsākot izstrādi, mēs Ä«sti nedomājām par to, kāda slodze tiks pakļauta datu bāzei un ko mēs darÄ«tu, ja serveris pārtrauks vilkt. Veidojot datubāzi, mēs ievērojām vispārÄ«gus ieteikumus un centāmies neieÅ”aut sev kājā, taču neievērojām vispārÄ«gus ieteikumus, piemēram, "neizmantojiet modeli EntÄ«tijas atribÅ«tu vērtÄ«bas mēs neiegājām. Mēs izstrādājām, pamatojoties uz normalizācijas principiem, izvairoties no datu dublÄ“Å”anas un nerÅ«pējāmies par noteiktu vaicājumu paātrināŔanu. TiklÄ«dz ieradās pirmie lietotāji, mēs saskārāmies ar veiktspējas problēmu. Kā parasti, mēs tam bijām pilnÄ«gi nesagatavoti. Pirmās problēmas izrādÄ«jās vienkārÅ”as. Kā likums, viss tika atrisināts, pievienojot jaunu indeksu. Bet pienāca laiks, kad vienkārÅ”i ielāpi pārstāja darboties. Saprotot, ka mums trÅ«kst pieredzes un mums kļūst arvien grÅ«tāk saprast, kas izraisa problēmas, nolÄ«gām speciālistus, kuri palÄ«dzēja pareizi iestatÄ«t serveri, pieslēgt uzraudzÄ«bu un parādÄ«ja, kur meklēt. statistika.

Otrais attēls. Statistikas

Tātad mums ir aptuveni 10 tÅ«kstoÅ”i dažādu vaicājumu, kas tiek izpildÄ«ti mÅ«su datubāzē dienā. No Å”iem 10 tÅ«kstoÅ”iem ir monstri, kas tiek izpildÄ«ti 2-3 miljonus reižu ar vidējo izpildes laiku 0.1-0.3 ms, un ir vaicājumi ar vidējo izpildes laiku 30 sekundes, kas tiek izsaukti 100 reizes dienā.

Nebija iespējams optimizēt visus 10 tÅ«kstoÅ”us vaicājumu, tāpēc nolēmām izdomāt, kur likt pÅ«les, lai pareizi uzlabotu datu bāzes veiktspēju. Pēc vairākām iterācijām mēs sākām dalÄ«t pieprasÄ«jumus pa veidiem.

TOP pieprasījumi

Å ie ir smagākie vaicājumi, kas aizņem visvairāk laika (kopējais laiks). Tie ir vaicājumi, kas tiek saukti vai nu ļoti bieži, vai arÄ« vaicājumi, kuru izpilde prasa ļoti ilgu laiku (ilgi un bieži vaicājumi tika optimizēti pirmajās cīņas par ātrumu iterācijās). Tā rezultātā serveris visvairāk laika pavada to izpildei. Turklāt ir svarÄ«gi nodalÄ«t populārākos pieprasÄ«jumus pēc kopējā izpildes laika un atseviŔķi pēc IO laika. Šādu vaicājumu optimizÄ“Å”anas metodes nedaudz atŔķiras.

Visu uzņēmumu ierastā prakse ir strādāt ar TOP pieprasÄ«jumiem. To ir maz; pat viena vaicājuma optimizÄ“Å”ana var atbrÄ«vot 5-10% resursu. Tomēr, projektam tuvojoties, TOP vaicājumu optimizÄ“Å”ana kļūst par arvien nenozÄ«mÄ«gāku uzdevumu. Visas vienkārŔās metodes jau ir izstrādātas, un ā€œsmagākaisā€ pieprasÄ«jums aizņem ā€œtikaiā€ 3-5% resursu. Ja TOP vaicājumi kopumā aizņem mazāk nekā 30-40% laika, tad, visticamāk, jÅ«s jau esat pielicis pÅ«les, lai tie darbotos ātri, un ir pienācis laiks pāriet uz nākamās grupas vaicājumu optimizÄ“Å”anu.
Atliek atbildēt uz jautājumu, cik populārāko vaicājumu jāiekļauj Å”ajā grupā. Es parasti ņemu vismaz 10, bet ne vairāk kā 20. CenÅ”os nodroÅ”ināt, lai TOP grupas pirmās un pēdējās laiks atŔķirtos ne vairāk kā 10 reizes. Tas ir, ja vaicājuma izpildes laiks strauji samazinās no 1. vietas uz 10., tad es ņemu TOP-10, ja kritums ir pakāpeniskāks, tad es palielināju grupas lielumu lÄ«dz 15 vai 20.
Datu bāzes vaicājumu optimizÄ“Å”ana, izmantojot B2B pakalpojuma piemēru celtniekiem

Vidējie zemnieki

Tie visi ir pieprasÄ«jumi, kas nāk uzreiz aiz TOP, izņemot pēdējos 5-10%. Parasti Å”o vaicājumu optimizÄ“Å”ana slēpjas iespēja ievērojami palielināt servera veiktspēju. Å o pieprasÄ«jumu svars var sasniegt 80%. Bet pat tad, ja to daļa ir pārsniegusi 50%, ir pienācis laiks tos aplÅ«kot rÅ«pÄ«gāk.

Aste

Kā minēts, Å”ie vaicājumi parādās beigās un aizņem 5ā€“10% laika. Par tiem varat aizmirst tikai tad, ja neizmantojat automātiskos vaicājumu analÄ«zes rÄ«kus, tad arÄ« to optimizÄ“Å”ana var bÅ«t lēta.

Kā novērtēt katru grupu?

Es izmantoju SQL vaicājumu, kas palÄ«dz veikt Ŕādu PostgreSQL novērtējumu (esmu pārliecināts, ka lÄ«dzÄ«gu vaicājumu var uzrakstÄ«t arÄ« daudzām citām DBVS)

SQL vaicājums, lai novērtētu TOP-MEDIUM-TAIL grupu lielumu

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

Vaicājuma rezultāts ir trÄ«s kolonnas, no kurām katra satur procentuālo laiku, kas nepiecieÅ”ams Ŕīs grupas vaicājumu apstrādei. PieprasÄ«juma iekÅ”pusē ir divi cipari (manā gadÄ«jumā tas ir 20 un 800), kas atdala pieprasÄ«jumus no vienas grupas no citas.

Lūk, kā aptuveni salīdzina pieprasījumu īpatsvaru optimizācijas darbu sākŔanas laikā un tagad.

Datu bāzes vaicājumu optimizÄ“Å”ana, izmantojot B2B pakalpojuma piemēru celtniekiem

Diagrammā redzams, ka strauji samazinājies TOP pieprasÄ«jumu Ä«patsvars, bet pieauguÅ”i ā€œvidējie zemniekiā€.
Sākumā TOP pieprasījumos bija klajas kļūdas. Laika gaitā bērnu slimības izzuda, TOP pieprasījumu īpatsvars samazinājās, un arvien vairāk bija jāpieliek pūles, lai paātrinātu sarežģītus pieprasījumus.

Lai iegÅ«tu pieprasÄ«jumu tekstu, mēs izmantojam Ŕādu pieprasÄ«jumu

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

Šeit ir saraksts ar visbiežāk izmantotajām metodēm, kas mums palīdzēja paātrināt TOP vaicājumu izpildi.

  • Sistēmas pārprojektÄ“Å”ana, piemēram, paziņojumu loÄ£ikas pārstrādāŔana, izmantojot ziņojumu brokeri, nevis periodiskus datu bāzes vaicājumus
  • Indeksu pievienoÅ”ana vai mainÄ«Å”ana
  • ORM vaicājumu pārrakstÄ«Å”ana uz tÄ«ru SQL
  • Slinkas datu ielādes loÄ£ikas pārrakstÄ«Å”ana
  • KeÅ”atmiņa, izmantojot datu denormalizāciju. Piemēram, mums ir tabulas savienojums Piegāde -> Rēķins -> PieprasÄ«jums -> Pieteikums. Tas nozÄ«mē, ka katra piegāde ir saistÄ«ta ar lietojumprogrammu, izmantojot citas tabulas. Lai nesaista visas tabulas katrā pieprasÄ«jumā, mēs dublējām saiti uz pieprasÄ«jumu tabulā Piegāde.
  • Statisko tabulu saglabāŔana keÅ”atmiņā ar atsauces grāmatām un reti maināmas tabulas programmas atmiņā.

Dažkārt izmaiņas bija iespaidÄ«gas, taču tās nodroÅ”ināja 5-10% no sistēmas slodzes un bija pamatotas. Laika gaitā izplÅ«des gāze kļuva arvien mazāka, un bija nepiecieÅ”ama arvien nopietnāka pārbÅ«ve.

Tad mēs pievērsām uzmanÄ«bu otrajai pieprasÄ«jumu grupai - vidējo zemnieku grupai. Tajā ir daudz vairāk jautājumu, un Ŕķita, ka visas grupas analÄ«ze prasÄ«s daudz laika. Tomēr lielākā daļa vaicājumu izrādÄ«jās ļoti vienkārÅ”i optimizējami, un daudzas problēmas tika atkārtotas desmitiem reižu dažādās variācijās. Å eit ir dažu tipisku optimizāciju piemēri, ko izmantojām desmitiem lÄ«dzÄ«gu vaicājumu, un katra optimizēto vaicājumu grupa izlādēja datubāzi par 3ā€“5%.

  • Tā vietā, lai pārbaudÄ«tu ierakstu esamÄ«bu, izmantojot COUNT un pilnu tabulas skenÄ“Å”anu, sāka izmantot EXISTS.
  • Tiku vaļā no DISTINCT (vispārējas receptes nav, bet dažreiz var viegli tikt vaļā, paātrinot pieprasÄ«jumu 10-100 reizes).

    Piemēram, vaicājuma vietā, lai atlasītu visus draiverus no lielas piegādes tabulas (PIEGĀDE) 

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

    veica vaicājumu salīdzinoŔi mazā tabulā 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)

    Å Ä·iet, ka mēs izmantojām korelētu apakÅ”vaicājumu, taču tas paātrina vairāk nekā 10 reizes.

  • Daudzos gadÄ«jumos COUNT tika pilnÄ«bā pamests un
    aizstāj ar aptuvenās vērtības aprēķinu
  • nevis 
    UPPER(s) LIKE JOHN%ā€™

    izmantot 

    s ILIKE ā€œJohn%ā€

Katrs konkrētais pieprasÄ«jums dažkārt tika paātrināts 3-1000 reizes. Neskatoties uz iespaidÄ«go veiktspēju, sākumā mums Ŕķita, ka nav jēgas optimizēt vaicājumu, kura aizpildÄ«Å”ana aizņem 10 ms, ir viens no 3. simts smagākajiem vaicājumiem un aizņem simtdaļas no kopējā datu bāzes ielādes laika. Taču, piemērojot vienu un to paÅ”u recepti viena veida vaicājumu grupai, mēs atguvām dažus procentus. Lai netērētu laiku, manuāli pārskatot visus simtiem vaicājumu, mēs uzrakstÄ«jām vairākus vienkārÅ”us skriptus, kas izmantoja regulāras izteiksmes, lai atrastu viena veida vaicājumus. Tā rezultātā automātiska meklÄ“Å”ana vaicājumu grupās ļāva mums vēl vairāk uzlabot mÅ«su veiktspēju ar pieticÄ«gām pÅ«lēm.

Rezultātā mēs jau trÄ«s gadus strādājam pie vienas un tās paÅ”as aparatÅ«ras. Vidējā dienas slodze ir aptuveni 30%, maksimumos tā sasniedz 70%. PieprasÄ«jumu skaits, kā arÄ« lietotāju skaits ir pieaudzis aptuveni 10 reizes. Un tas viss, pateicoties pastāvÄ«gai Å”o paÅ”u TOP-MEDIUM pieprasÄ«jumu grupu uzraudzÄ«bai. TiklÄ«dz TOP grupā parādās jauns pieprasÄ«jums, mēs to nekavējoties analizējam un cenÅ”amies paātrināt. Mēs pārskatām grupu MEDIUM reizi nedēļā, izmantojot vaicājumu analÄ«zes skriptus. Ja mēs saskaramies ar jauniem vaicājumiem, kurus jau zinām, kā optimizēt, mēs tos ātri mainām. Dažreiz mēs atrodam jaunas optimizācijas metodes, kuras var piemērot vairākiem vaicājumiem vienlaikus.

Pēc mÅ«su prognozēm, paÅ”reizējais serveris izturēs lietotāju skaita pieaugumu vēl 3-5 reizes. Tiesa, mums ir vēl viens dÅ«zis - mēs joprojām neesam pārsÅ«tÄ«juÅ”i SELECT vaicājumus uz spoguli, kā ieteikts. Bet mēs to nedarām apzināti, jo vispirms vēlamies pilnÄ«bā izsmelt ā€œgudrāsā€ optimizācijas iespējas, pirms ieslēdzam ā€œsmago artilērijuā€.
Paveiktā darba kritiska apskate var ieteikt izmantot vertikālo mērogoÅ”anu. Pērciet jaudÄ«gāku serveri, nevis tērējiet speciālistu laiku. Serveris var nemaksāt tik dārgi, jo Ä«paÅ”i tāpēc, ka mēs vēl neesam izsmēluÅ”i vertikālās mērogoÅ”anas robežas. Taču tikai pieprasÄ«jumu skaits pieauga 10 reizes. Vairāku gadu laikā sistēmas funkcionalitāte ir palielinājusies, un tagad ir vairāk pieprasÄ«jumu veidu. Pateicoties keÅ”atmiņai, esoŔā funkcionalitāte tiek izpildÄ«ta ar mazāku pieprasÄ«jumu skaitu un efektÄ«vākiem pieprasÄ«jumiem. Tas nozÄ«mē, ka varat droÅ”i reizināt ar vēl 5, lai iegÅ«tu reālo paātrinājuma koeficientu. Tātad, pēc konservatÄ«vākajām aplēsēm, mēs varam teikt, ka paātrinājums bija 50 vai vairāk reižu. Servera vertikālā ŔūpoÅ”ana izmaksātu 50 reizes vairāk. ÄŖpaÅ”i ņemot vērā, ka pēc optimizācijas tas darbojas visu laiku, un rēķins par nomāto serveri nāk katru mēnesi.

Avots: www.habr.com

Pievieno komentāru