Hagræðing gagnagrunnsfyrirspurna með því að nota dæmi um B2B þjónustu fyrir byggingaraðila

Hvernig á að fjölga 10 sinnum fjölda fyrirspurna í gagnagrunninn án þess að fara yfir á afkastameiri netþjón og viðhalda virkni kerfisins? Ég mun segja þér hvernig við brugðumst við samdrætti í afköstum gagnagrunnsins okkar, hvernig við fínstilltum SQL fyrirspurnir til að þjóna eins mörgum notendum og mögulegt er og ekki auka kostnað við tölvuauðlindir.

Ég bý til þjónustu fyrir stjórnun viðskiptaferla í byggingarfyrirtækjum. Um 3 þúsund fyrirtæki vinna með okkur. Meira en 10 þúsund manns vinna við kerfið okkar á hverjum degi í 4-10 klukkustundir. Það leysir ýmis vandamál varðandi skipulagningu, tilkynningar, viðvörun, staðfestingu ... Við notum PostgreSQL 9.6. Við erum með um 300 töflur í gagnagrunninum og allt að 200 milljónir fyrirspurna (10 þúsund mismunandi) berast á hverjum degi. Að meðaltali erum við með 3-4 þúsund beiðnir á sekúndu, á virkasta augnablikinu meira en 10 þúsund beiðnir á sekúndu. Flestar fyrirspurnirnar eru OLAP. Það eru miklu færri viðbætur, breytingar og eyðingar, sem þýðir að OLTP álagið er tiltölulega létt. Ég gaf upp allar þessar tölur svo þú getir metið umfang verkefnisins okkar og skilið hversu gagnleg reynsla okkar getur verið fyrir þig.

Mynd eitt. Ljóðrænt

Þegar við byrjuðum að þróa, hugsuðum við ekki í raun um hvers konar álag myndi falla á gagnagrunninn og hvað við myndum gera ef þjónninn hætti að toga. Við hönnun gagnagrunnsins fylgdum við almennum ráðleggingum og reyndum að skjóta okkur ekki í fótinn, en fórum lengra en almenn ráð eins og „ekki nota mynstrið Eiginleikagildi eininga við fórum ekki inn. Við hönnuðum byggt á meginreglum um eðlileg, forðast offramboð á gögnum og var sama um að flýta fyrir ákveðnum fyrirspurnum. Um leið og fyrstu notendurnir komu lentum við í frammistöðuvandamálum. Eins og venjulega vorum við algjörlega óviðbúin þessu. Fyrstu vandamálin reyndust einföld. Að jafnaði var allt leyst með því að bæta við nýrri vísitölu. En það kom tími þegar einföld plástrar hættu að virka. Þegar við áttum okkur á því að okkur skortir reynslu og það verður sífellt erfiðara fyrir okkur að skilja hvað er að valda vandamálunum, réðum við til okkar sérfræðinga sem hjálpuðu okkur að setja upp netþjóninn rétt, tengja eftirlit og sýna okkur hvert við getum leitað tölfræði.

Mynd tvö. Tölfræði

Þannig að við höfum um 10 þúsund mismunandi fyrirspurnir sem eru keyrðar á gagnagrunninum okkar á dag. Af þessum 10 þúsund eru skrímsli sem eru keyrð 2-3 milljón sinnum með að meðaltali framkvæmdartíma 0.1-0.3 ms og það eru fyrirspurnir með að meðaltali 30 sekúndur sem eru kallaðar 100 sinnum á dag.

Það var ekki hægt að fínstilla allar 10 þúsund fyrirspurnirnar, svo við ákváðum að finna út hvert ætti að beina kröftum okkar til að bæta árangur gagnagrunnsins á réttan hátt. Eftir nokkrar endurtekningar fórum við að skipta beiðnum í gerðir.

TOP beiðnir

Þetta eru þyngstu fyrirspurnirnar sem taka mestan tíma (heildartími). Þetta eru fyrirspurnir sem eru ýmist kallaðar mjög oft eða fyrirspurnir sem taka mjög langan tíma að framkvæma (langar og tíðar fyrirspurnir voru fínstilltar í fyrstu endurteknum hraðabaráttunni). Fyrir vikið eyðir þjónninn mestum tíma í framkvæmd þeirra. Þar að auki er mikilvægt að aðgreina efstu beiðnir eftir heildarframkvæmdartíma og sérstaklega eftir IO tíma. Aðferðirnar til að fínstilla slíkar fyrirspurnir eru aðeins öðruvísi.

Venjuleg venja allra fyrirtækja er að vinna með TOP beiðnir. Þeir eru fáir; fínstilling jafnvel einni fyrirspurn getur losað um 5-10% af fjármagni. Hins vegar, eftir því sem verkefnið þroskast, verður fínstilling TOP-fyrirspurna sífellt léttvægara verkefni. Allar einfaldar aðferðir hafa þegar verið útfærðar og „þungasta“ beiðnin tekur „aðeins“ 3-5% af fjármagni. Ef TOP-fyrirspurnir samtals taka minna en 30-40% af tímanum, þá er líklegast að þú hafir þegar reynt að láta þær virka hratt og það er kominn tími til að halda áfram að fínstilla fyrirspurnir frá næsta hópi.
Eftir er að svara spurningunni um hversu margar efstu fyrirspurnir eigi að vera með í þessum hópi. Ég tek venjulega að minnsta kosti 10, en ekki fleiri en 20. Ég reyni að tryggja að tími fyrsta og síðasta í TOP hópnum sé ekki meira en 10 munur. Það er, ef framkvæmdartími fyrirspurnar lækkar verulega úr 1. sæti í 10. þá tek ég TOP-10, ef lækkunin er hægfara, þá stækka ég hópstærðina í 15 eða 20.

Miðbændur

Þetta eru allt beiðnir sem koma strax á eftir TOP, að undanskildum síðustu 5-10%. Venjulega, við að fínstilla þessar fyrirspurnir, liggur tækifærið til að auka afköst netþjónsins til muna. Þessar beiðnir geta vegið allt að 80%. En jafnvel þótt hlutur þeirra hafi farið yfir 50%, þá er kominn tími til að skoða þá betur.

Hala

Eins og fram hefur komið koma þessar fyrirspurnir í lokin og taka 5-10% tímans. Þú getur aðeins gleymt þeim ef þú notar ekki sjálfvirk fyrirspurnagreiningartæki, þá getur hagræðing þeirra líka verið ódýr.

Hvernig á að meta hvern hóp?

Ég nota SQL fyrirspurn sem hjálpar til við að gera slíkt mat fyrir PostgreSQL (ég er viss um að svipaða fyrirspurn er hægt að skrifa fyrir margar aðrar DBMS)

SQL fyrirspurn til að áætla stærð TOP-MEDIUM-TAIL hópa

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

Niðurstaða fyrirspurnarinnar eru þrír dálkar sem hver um sig inniheldur hlutfall tíma sem það tekur að vinna úr fyrirspurnum úr þessum hópi. Inni í beiðninni eru tvö númer (í mínu tilfelli eru það 20 og 800) sem aðgreina beiðnir frá einum hópi frá öðrum.

Þannig eru hlutdeild beiðna í grófum dráttum borin saman á þeim tíma sem hagræðingarvinna hófst og nú.

Skýringarmyndin sýnir að hlutur TOP beiðna hefur verulega minnkað, en „miðbændum“ hefur fjölgað.
Í fyrstu innihéldu TOP beiðnirnar gróf mistök. Með tímanum hurfu barnasjúkdómar, hlutur TOP-beiðna minnkaði og sífellt þurfti að reyna að flýta erfiðum beiðnum.

Til að fá texta beiðna notum við eftirfarandi beiðni

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

Hér er listi yfir algengustu aðferðirnar sem hjálpuðu okkur að flýta fyrir TOP fyrirspurnum:

• Endurhönnun kerfisins, til dæmis að endurvinna tilkynningarrökfræðina með því að nota skilaboðamiðlara í stað reglubundinna fyrirspurna í gagnagrunninn
• Bæta við eða breyta vísitölum
• Endurskrifa ORM fyrirspurnir í hreint SQL
• Endurskrifa lata gagnahleðslurökfræði
• Skyndiminni í gegnum afeðlun gagna. Til dæmis erum við með töflutengingu Afhending -> Reikningur -> Beiðni -> Umsókn. Það er að segja að hver sending er tengd forriti í gegnum aðrar töflur. Til þess að tengja ekki allar töflur í hverri beiðni, afrituðum við tengilinn á beiðnina í Afhendingartöflunni.
• Að vista kyrrstæðar töflur í skyndiminni með uppflettibókum og sjaldan skipta um töflur í forritaminni.

Stundum snéru breytingarnar sér að glæsilegri endurhönnun, en þær gáfu 5-10% af kerfisálagi og voru réttlætanlegar. Með tímanum varð útblástursloftið sífellt minna og þörf var á meiri og alvarlegri endurhönnun.

Þá beinum við sjónum okkar að öðrum hópi beiðna - hópi miðbænda. Það eru miklu fleiri fyrirspurnir í henni og virtist sem það myndi taka mikinn tíma að greina allan hópinn. Hins vegar reyndust flestar fyrirspurnir mjög einfaldar í fínstillingu og mörg vandamál voru endurtekin tugum sinnum í mismunandi afbrigðum. Hér eru dæmi um nokkrar dæmigerðar hagræðingar sem við beittum á tugi svipaðra fyrirspurna og hver hópur fínstilltra fyrirspurna losaði gagnagrunninn um 3-5%.

• Í stað þess að athuga hvort skrár séu til staðar með því að nota COUNT og fulla töfluskönnun, byrjaði að nota EXISTS
• Losaði við DISTINCT (það er engin almenn uppskrift, en stundum er auðvelt að losna við hana með því að flýta beiðninni um 10-100 sinnum).

  Til dæmis, í stað fyrirspurnar til að velja alla ökumenn úr stórri afhendingartöflu (DELIVERY)

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

  gerði fyrirspurn um tiltölulega litla borðpersónu

  SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM PERSON
  WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
  

  Það virðist sem við höfum notað tengda undirfyrirspurn, en hún gefur meira en 10 sinnum hraða.

• Í mörgum tilfellum var COUNT hætt með öllu og
  komi útreikningur á áætlaðri verðmæti
• í staðinn fyrir

  UPPER(s) LIKE JOHN%’ 
  

  nota

  s ILIKE “John%”
  

Hverri tiltekinni beiðni var stundum flýtt um 3-1000 sinnum. Þrátt fyrir glæsilega frammistöðu virtist okkur í fyrstu enginn tilgangur að fínstilla fyrirspurn sem tekur 10 ms að ljúka, er ein af 3. hundrað þyngstu fyrirspurnunum og tekur upp hundraðustu úr prósentum af heildarhleðslutíma gagnagrunnsins. En með því að nota sömu uppskriftina á hóp fyrirspurna af sömu tegund fengum við nokkur prósent til baka. Til þess að eyða ekki tíma í að fara yfir öll hundruð fyrirspurna handvirkt skrifuðum við nokkur einföld forskrift sem notuðu reglubundnar tjáningar til að finna fyrirspurnir af sömu gerð. Fyrir vikið gerði sjálfvirk leit í fyrirspurnahópum okkur kleift að bæta árangur okkar enn frekar með hóflegri fyrirhöfn.

Þess vegna höfum við unnið að sama vélbúnaði í þrjú ár núna. Meðalálag á dag er um 30%, í toppum nær það 70%. Fjöldi beiðna, sem og fjöldi notenda, hefur um það bil 10 sinnum fjölgað. Og allt þetta þökk sé stöðugu eftirliti með þessum sömu hópum af TOP-MEDIUM beiðnum. Um leið og ný beiðni birtist í TOP hópnum greinum við hana strax og reynum að flýta henni. Við skoðum MEDIUM hópinn einu sinni í viku með því að nota fyrirspurnagreiningarforskriftir. Ef við rekumst á nýjar fyrirspurnir sem við vitum nú þegar hvernig á að hagræða breytum við þeim fljótt. Stundum finnum við nýjar hagræðingaraðferðir sem hægt er að beita á nokkrar fyrirspurnir í einu.

Samkvæmt spám okkar mun núverandi netþjónn þola fjölgun notenda um 3-5 sinnum til viðbótar. Að vísu erum við með enn einn ásinn uppi í erminni - enn höfum við ekki flutt SELECT fyrirspurnir í spegilinn, eins og mælt er með. En við gerum þetta ekki meðvitað, vegna þess að við viljum fyrst algjörlega klára möguleikana á „snjöllri“ hagræðingu áður en við kveikjum á „þungu stórskotaliðinu“.
Gagnrýnt horf á vinnuna sem unnið er getur bent til þess að nota lóðrétta mælikvarða. Kauptu öflugri netþjón í stað þess að sóa tíma sérfræðinga. Miðlarinn kostar kannski ekki svo mikið, sérstaklega þar sem við höfum ekki enn tæmt mörk lóðréttrar stærðar. Hins vegar fjölgaði aðeins beiðnum 10 sinnum. Á nokkrum árum hefur virkni kerfisins aukist og nú eru fleiri tegundir beiðna. Þökk sé skyndiminni er virknin sem var til framkvæmt í færri beiðnum og skilvirkari beiðnum. Þetta þýðir að þú getur örugglega margfaldað með öðrum 5 til að fá raunverulegan hröðunarstuðul. Þannig að samkvæmt varfærnustu áætlunum getum við sagt að hröðunin hafi verið 50 sinnum eða meira. Lóðrétt sveifla netþjóns myndi kosta 50 sinnum meira. Sérstaklega með hliðsjón af því að þegar hagræðing er framkvæmd virkar hún allan tímann og reikningurinn fyrir leigða netþjóninn kemur í hverjum mánuði.

Heimild: www.habr.com