Si të rritet 10 herë numri i pyetjeve në bazën e të dhënave pa kaluar në një server më produktiv dhe të ruani funksionalitetin e sistemit? Unë do t'ju tregoj se si e trajtuam rënien e performancës së bazës së të dhënave tona, si optimizuam pyetjet SQL për t'i shërbyer sa më shumë përdoruesve dhe për të mos rritur koston e burimeve kompjuterike.
Bëj një shërbim për menaxhimin e proceseve të biznesit në kompanitë e ndërtimit. Rreth 3 mijë kompani punojnë me ne. Më shumë se 10 mijë njerëz punojnë me sistemin tonë çdo ditë për 4-10 orë. Zgjidh probleme të ndryshme të planifikimit, njoftimit, paralajmërimit, vërtetimit... Ne përdorim PostgreSQL 9.6. Ne kemi rreth 300 tabela në bazën e të dhënave dhe çdo ditë pranohen deri në 200 milionë pyetje (10 mijë të ndryshme). Mesatarisht kemi 3-4 mijë kërkesa në sekondë, në momentet më aktive më shumë se 10 mijë kërkesa në sekondë. Shumica e pyetjeve janë OLAP. Ka shumë më pak shtesa, modifikime dhe fshirje, që do të thotë se ngarkesa OLTP është relativisht e lehtë. I dhashë të gjitha këto numra në mënyrë që të vlerësoni shkallën e projektit tonë dhe të kuptoni se sa e dobishme mund të jetë përvoja jonë për ju.
Foto një. Lirike
Kur filluam zhvillimin, nuk menduam vërtet se çfarë lloj ngarkese do të binte në bazën e të dhënave dhe çfarë do të bënim nëse serveri ndalonte së tërhequri. Gjatë dizajnimit të bazës së të dhënave, ne ndoqëm rekomandimet e përgjithshme dhe u përpoqëm të mos qëllonim veten në këmbë, por shkuam përtej këshillave të përgjithshme si "mos përdorni modelin ne nuk hymë. Ne projektuam bazuar në parimet e normalizimit, duke shmangur tepricën e të dhënave dhe nuk u kujdesëm për përshpejtimin e pyetjeve të caktuara. Sapo mbërritën përdoruesit e parë, hasëm një problem performancës. Si zakonisht, ne ishim plotësisht të papërgatitur për këtë. Problemet e para rezultuan të thjeshta. Si rregull, gjithçka zgjidhej duke shtuar një indeks të ri. Por erdhi një kohë kur arna të thjeshta pushuan së funksionuari. Duke kuptuar se na mungon përvoja dhe po bëhet gjithnjë e më e vështirë për ne të kuptojmë se çfarë po i shkakton problemet, punësuam specialistë që na ndihmuan të konfiguronim saktë serverin, të lidhnim monitorimin dhe na treguan se ku të kërkojmë për të marrë. .
Foto dy. Statistikore
Pra, ne kemi rreth 10 mijë pyetje të ndryshme që ekzekutohen në bazën tonë të të dhënave në ditë. Nga këto 10 mijë, ka monstra që ekzekutohen 2-3 milion herë me një kohë mesatare ekzekutimi 0.1-0.3 ms, dhe ka pyetje me një kohë mesatare ekzekutimi prej 30 sekondash që thirren 100 herë në ditë.
Nuk ishte e mundur të optimizonim të gjitha 10 mijë pyetjet, kështu që vendosëm të kuptojmë se ku t'i drejtojmë përpjekjet tona në mënyrë që të përmirësojmë saktë performancën e bazës së të dhënave. Pas disa përsëritjesh, filluam t'i ndajmë kërkesat në lloje.
Kërkesat TOP
Këto janë pyetjet më të rënda që marrin më shumë kohë (koha totale). Këto janë pyetje që ose thirren shumë shpesh ose pyetje që kërkojnë një kohë shumë të gjatë për t'u ekzekutuar (pyetjet e gjata dhe të shpeshta janë optimizuar në përsëritjet e para të luftës për shpejtësi). Si rezultat, serveri shpenzon më shumë kohë në ekzekutimin e tyre. Për më tepër, është e rëndësishme të ndahen kërkesat kryesore sipas kohës totale të ekzekutimit dhe veçmas sipas kohës IO. Metodat për optimizimin e pyetjeve të tilla janë paksa të ndryshme.
Praktika e zakonshme e të gjitha kompanive është të punojnë me kërkesat TOP. Ka pak prej tyre; optimizimi i qoftë edhe një pyetjeje mund të çlirojë 5-10% të burimeve. Megjithatë, ndërsa projekti maturohet, optimizimi i pyetjeve TOP bëhet një detyrë gjithnjë e më jo e parëndësishme. Të gjitha metodat e thjeshta tashmë janë përpunuar, dhe kërkesa më "e rëndë" merr "vetëm" 3-5% të burimeve. Nëse pyetjet TOP në total marrin më pak se 30-40% të kohës, atëherë ka shumë të ngjarë që tashmë keni bërë përpjekje për t'i bërë ato të funksionojnë shpejt dhe është koha për të kaluar në optimizimin e pyetjeve nga grupi tjetër.
Mbetet për t'iu përgjigjur pyetjes se sa pyetje kryesore duhet të përfshihen në këtë grup. Zakonisht marr të paktën 10, por jo më shumë se 20. Përpiqem të siguroj që koha e të parit dhe të fundit në grupin TOP të ndryshojë jo më shumë se 10 herë. Kjo do të thotë, nëse koha e ekzekutimit të pyetjes bie ndjeshëm nga vendi 1 në 10, atëherë unë marr TOP-10, nëse rënia është më graduale, atëherë e rris madhësinë e grupit në 15 ose 20.
Fshatarë të mesëm
Të gjitha këto janë kërkesa që vijnë menjëherë pas TOP-it, me përjashtim të 5-10% të fundit. Zakonisht, në optimizimin e këtyre pyetjeve qëndron mundësia për të rritur shumë performancën e serverit. Këto kërkesa mund të peshojnë deri në 80%. Por edhe nëse pjesa e tyre ka kaluar 50%, atëherë është koha t'i shikojmë më me kujdes.
Bishti
Siç u përmend, këto pyetje vijnë në fund dhe marrin 5-10% të kohës. Ju mund t'i harroni ato vetëm nëse nuk përdorni mjete automatike të analizës së pyetjeve, atëherë optimizimi i tyre mund të jetë gjithashtu i lirë.
Si të vlerësohet secili grup?
Unë përdor një pyetje SQL që ndihmon për të bërë një vlerësim të tillë për PostgreSQL (jam i sigurt se një pyetje e ngjashme mund të shkruhet për shumë DBMS të tjera)
Pyetje SQL për të vlerësuar madhësinë e grupeve TOP-MEDIUM-TAIL
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
Rezultati i pyetjes është tre kolona, secila prej të cilave përmban përqindjen e kohës që duhet për të përpunuar pyetjet nga ky grup. Brenda kërkesës ka dy numra (në rastin tim është 20 dhe 800) që ndajnë kërkesat nga një grup nga një tjetër.
Kështu krahasohen përafërsisht përqindjet e kërkesave në kohën kur filloi puna e optimizimit dhe tani.
Diagrami tregon se pjesa e kërkesave TOP është ulur ndjeshëm, por "fshatarët e mesëm" janë rritur.
Në fillim, kërkesat TOP përfshinin gabime të dukshme. Me kalimin e kohës, sëmundjet e fëmijërisë u zhdukën, pjesa e kërkesave TOP u zvogëlua dhe duhej të bëheshin gjithnjë e më shumë përpjekje për të shpejtuar kërkesat e vështira.
Për të marrë tekstin e kërkesave ne përdorim kërkesën e mëposhtme
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
Këtu është një listë e teknikave më të përdorura që na ndihmuan të shpejtojmë pyetjet TOP:
- Ridizajnimi i sistemit, për shembull, ripërpunimi i logjikës së njoftimit duke përdorur një ndërmjetës mesazhesh në vend të pyetjeve periodike në bazën e të dhënave
- Shtimi ose ndryshimi i indekseve
- Rishkrimi i pyetjeve ORM në SQL të pastër
- Rishkrimi i logjikës së ngarkimit të të dhënave dembel
- Caching përmes denormalizimit të të dhënave. Për shembull, ne kemi një lidhje tavoline Dorëzimi -> Faturë -> Kërkesë -> Aplikim. Kjo do të thotë, çdo dërgesë shoqërohet me një aplikacion përmes tabelave të tjera. Për të mos lidhur të gjitha tabelat në secilën kërkesë, ne dubluam lidhjen me kërkesën në tabelën e dorëzimit.
- Regjistrimi i tabelave statike me libra referencë dhe ndryshimi i rrallë i tabelave në memorien e programit.
Ndonjëherë ndryshimet arritën në një ridizajnim mbresëlënës, por ato siguruan 5-10% të ngarkesës së sistemit dhe u justifikuan. Me kalimin e kohës, shkarkimi u bë gjithnjë e më i vogël dhe kërkohej ridizajnim gjithnjë e më serioz.
Më pas e kthyem vëmendjen te grupi i dytë i kërkesave - grupi i fshatarëve të mesëm. Ka shumë pyetje të tjera në të dhe dukej se do të duhej shumë kohë për të analizuar të gjithë grupin. Sidoqoftë, shumica e pyetjeve rezultuan të ishin shumë të thjeshta për t'u optimizuar dhe shumë probleme u përsëritën dhjetëra herë në variacione të ndryshme. Këtu janë shembuj të disa optimizimeve tipike që kemi aplikuar në dhjetëra pyetje të ngjashme dhe secili grup pyetjesh të optimizuara shkarkoi bazën e të dhënave me 3-5%.
- Në vend që të kontrollohej prania e regjistrimeve duke përdorur COUNT dhe një skanim të plotë të tabelës, EXISTS filloi të përdoret
- E hoqa qafe DISTINCT (nuk ka recetë të përgjithshme, por ndonjëherë mund ta heqësh lehtësisht duke përshpejtuar kërkesën me 10-100 herë).
Për shembull, në vend të një pyetjeje për të zgjedhur të gjithë drejtuesit nga një tabelë e madhe dërgesash (DELIVERY)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDbëri një pyetje në një tryezë relativisht të vogël 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)Duket se kemi përdorur një nënpyetje të ndërlidhur, por ajo jep një shpejtësi prej më shumë se 10 herë.
- Në shumë raste, COUNT u braktis fare dhe
- në vend të
UPPER(s) LIKE JOHN%’përdorim
s ILIKE “John%”
Çdo kërkesë specifike nganjëherë shpejtohej nga 3-1000 herë. Pavarësisht performancës mbresëlënëse, në fillim na u duk se nuk kishte asnjë pikë për të optimizuar një pyetje që kërkon 10 ms për t'u përfunduar, është një nga pyetjet e 3-ta më të rënda dhe zë të qindtat e një për qind të kohës së përgjithshme të ngarkimit të bazës së të dhënave. Por duke aplikuar të njëjtën recetë për një grup pyetjesh të të njëjtit lloj, ne fituam disa përqind. Për të mos humbur kohë duke rishikuar manualisht të gjitha qindra pyetje, ne kemi shkruar disa skripta të thjeshtë që përdornin shprehje të rregullta për të gjetur pyetje të të njëjtit lloj. Si rezultat, kërkimi automatik i grupeve të pyetjeve na lejoi të përmirësonim më tej performancën tonë me përpjekje modeste.
Si rezultat, ne kemi punuar në të njëjtin pajisje për tre vjet tani. Ngarkesa mesatare ditore është rreth 30%, në maja arrin 70%. Numri i kërkesave, si dhe numri i përdoruesve është rritur afërsisht 10 herë. Dhe e gjithë kjo falë monitorimit të vazhdueshëm të po këtyre grupeve të kërkesave TOP-MEDIUM. Sapo të shfaqet një kërkesë e re në grupin TOP, ne e analizojmë menjëherë atë dhe përpiqemi ta përshpejtojmë. Ne rishikojmë grupin MEDIUM një herë në javë duke përdorur skriptet e analizës së pyetjeve. Nëse hasim pyetje të reja që tashmë dimë t'i optimizojmë, ne i ndryshojmë shpejt ato. Ndonjëherë gjejmë metoda të reja optimizimi që mund të aplikohen në disa pyetje njëherësh.
Sipas parashikimeve tona, serveri aktual do të përballojë një rritje të numrit të përdoruesve me 3-5 herë të tjera. Vërtetë, ne kemi një ACE më shumë në mëngë - ne ende nuk i kemi transferuar pyetjet SELECT në pasqyrë, siç rekomandohet. Por ne nuk e bëjmë këtë me vetëdije, sepse duam që së pari të shterojmë plotësisht mundësitë e optimizimit "të zgjuar" përpara se të ndezim "artilerinë e rëndë".
Një vështrim kritik i punës së bërë mund të sugjerojë përdorimin e shkallëzimit vertikal. Bleni një server më të fuqishëm në vend që të humbisni kohën e specialistëve. Serveri mund të mos kushtojë aq shumë, veçanërisht pasi nuk i kemi shteruar ende kufijtë e shkallëzimit vertikal. Megjithatë, vetëm numri i kërkesave u rrit 10 herë. Gjatë disa viteve, funksionaliteti i sistemit është rritur dhe tani ka më shumë lloje kërkesash. Falë caching-ut, funksionaliteti që ekzistonte kryhet në më pak kërkesa dhe kërkesa më efikase. Kjo do të thotë që ju mund të shumëzoni në mënyrë të sigurt me 5 të tjera për të marrë koeficientin real të nxitimit. Pra, sipas vlerësimeve më konservatore, mund të themi se nxitimi ishte 50 herë ose më shumë. Lëkundje vertikale e një serveri do të kushtonte 50 herë më shumë. Sidomos duke pasur parasysh që pasi të kryhet optimizimi, ai funksionon gjatë gjithë kohës, dhe fatura për serverin e marrë me qira vjen çdo muaj.
Burimi: www.habr.com