Meriv çawa 10 carî jimara lêpirsînan li databasê mezin dibe bêyî ku derbasî serverek hilbertir bibe û fonksiyona pergalê biparêze? Ez ê ji we re vebêjim ka me çawa bi kêmbûna performansa databasa xwe re mijûl kir, me çawa pirsên SQL xweşbîn kir da ku bi qasî ku gengaz ji bikarhêneran re xizmet bike û lêçûna çavkaniyên hesabkirinê zêde neke.
Ez karûbarek ji bo birêvebirina pêvajoyên karsaziyê di pargîdaniyên avahîsaziyê de çêdikim. Nêzî 3 hezar şîrket bi me re dixebitin. Her roj 10-4 saetan zêdetirî 10 hezar kes bi pergala me re dixebitin. Ew pirsgirêkên cûrbecûr yên plansaziyê, agahdarkirinê, hişyarkirinê, pejirandinê çareser dike... Em PostgreSQL 9.6 bikar tînin. Di databasê de nêzî 300 tabloyên me hene û her roj 200 mîlyon pirs (10 hezar cuda) têne wergirtin. Bi navînî di çirkeyê de 3-4 hezar daxwaziyên me hene, di kêliyên herî çalak de ji 10 hezarî zêdetir daxwaz di çirkeyê de. Piraniya pirsan OLAP in. Zêdekirin, guheztin û jêbirin pir hindiktir hene, tê vê wateyê ku barkirina OLTP bi nisbeten sivik e. Min van hemî hejmaran peyda kir da ku hûn pîvana projeya me binirxînin û fêm bikin ka ezmûna me ji we re çiqas bikêr e.
Wêne yek. Lyrical
Dema ku me dest bi pêşkeftinê kir, me bi rastî nedifikirî ku dê çi celeb bar bikeve ser databasê û ger server dev ji kişandina xwe berde em ê çi bikin. Dema sêwirana databasê, me pêşniyarên giştî şopand û me hewl da ku xwe neavêjin lingê xwe, lê ji şîretên gelemperî yên mîna "şablonê bikar neynin" derketin. em neketin hundir. Me li ser bingehê prensîbên normalîzekirinê sêwirand, ji zêdebûna daneyan dûr ket û bala xwe neda lezkirina hin pirsan. Gava ku bikarhênerên yekem hatin, em rastî pirsgirêkek performansê hatin. Wekî her car, em ji bo vê yekê bi tevahî ne amade bûn. Pirsgirêkên yekem hêsan derketin. Wekî qaîdeyek, her tişt bi lê zêdekirina pêvekek nû hate çareser kirin. Lê demek hat ku paçên hêsan ji kar rawestiyan. Fêm kirin ku em kêmbûna ezmûnê ne û her ku diçe ji me re dijwartir dibe ku em fam bikin ka sedema pirsgirêkan çi ye, me pisporên ku ji me re bûn alîkar kir ku server rast saz bikin, çavdêriyê girêbidin, û nîşanî me dan ku em li ku bigerin ku bigihîjin. .
Wêne du. Statistics
Ji ber vê yekê em rojane nêzîkî 10 hezar pirsên cihêreng hene ku li ser databasa me têne darve kirin. Ji van 10 hezarî, cinawirên ku 2-3 mîlyon caran bi navgîniya dema înfazê 0.1-0.3 ms têne îdamkirin hene, lêpirsînên bi navgîniya dema darvekirinê 30 saniye hene ku rojê 100 caran têne gotin.
Ne mimkun bû ku hemî 10 hezar pirsan xweşbîn bikin, ji ber vê yekê me biryar da ku em fêhm bikin ku em hewildanên xwe li ku bi rê ve bibin da ku performansa databasê rast baştir bikin. Piştî çend dubareyan, me dest bi dabeşkirina daxwazan li celeban kir.
daxwazên TOP
Vana pirsên herî giran in ku herî zêde wext digirin (dema tevahî). Vana pirsên ku pir caran têne gazî kirin an jî pirsên ku ji bo pêkanîna wan demek pir dirêj digire (lêpirsînên dirêj û pir caran di dubareyên yekem ên şerê ji bo lezê de xweşbîn bûne). Wekî encamek, server herî zêde wextê li ser darvekirina wan derbas dike. Digel vê yekê, girîng e ku hûn daxwazên top ji hêla tevahiya dema darvekirinê ve û ji hêla dema IO-yê veqetandî veqetînin. Rêbazên xweşbînkirina pirsên weha hinekî cûda ne.
Pratîka gelemperî ya hemî pargîdaniyan ev e ku bi daxwazên TOP re bixebitin. Ji wan hindik in; xweşbînkirina yek pirsê jî dikare 5-10% çavkaniyan azad bike. Lêbelê, her ku proje mezin dibe, xweşbînkirina pirsên TOP-ê her ku diçe karekî ne-pîvan dibe. Hemî rêbazên hêsan berê hatine xebitandin, û daxwaza herî "giran" "tenê" 3-5% çavkaniyan digire. Ger pirsên TOP bi tevahî kêmtirî 30-40% ji wextê digire, wê hingê bi îhtîmalek ku we berê hewil daye ku hûn wan zû bixebitin û ew dem e ku hûn biçin ser xweşbînkirina pirsan ji koma din.
Dimîne ku bersiva pirsê bide ka çend pirsên herî top divê di vê komê de werin bicîh kirin. Ez bi gelemperî herî kêm 10 digirim, lê ji 20-an zêdetir nagirim. Ez hewl didim ku dema koma TOP-ê ya yekem û ya paşîn ji 10 carî zêdetir cûda nebe. Ango, heke dema pêkanîna pirsê ji cîhê 1-an berbi 10-an ve bi tundî dakeve, wê hingê ez TOP-10-ê digirim, heke daketin hêdî hêdî be, wê hingê ez mezinahiya komê digihînim 15 an 20-an.
Gundiyên navîn
Vana hemî daxwazên ku tavilê piştî TOP têne, ji bilî 5-10% ya paşîn. Bi gelemperî, di xweşbînkirina van pirsan de fersendek heye ku meriv performansa serverê pir zêde bike. Ev daxwaz dikarin bigihîjin 80%. Lê her çend para wan ji %50 derbas bûbe jî, wextê wê ye ku meriv bi baldarî li wan binêre.
Terrî
Wekî ku hate gotin, ev pirs di dawiyê de têne û 5-10% ji demê digirin. Hûn dikarin wan ji bîr bikin tenê heke hûn amûrên analîzkirina lêpirsîna otomatîkî bikar neynin, wê hingê xweşbînkirina wan jî dikare erzan be.
Meriv çawa her komê dinirxîne?
Ez pirsek SQL bikar tînim ku ji bo PostgreSQL nirxandinek weha dibe alîkar (Ez piştrast im ku pirsek wusa dikare ji bo gelek DBMS-yên din were nivîsandin)
Pirsa SQL ji bo texmînkirina mezinahiya komên 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
Encama pirsê sê stûn e, ku her yek ji sedî rêjeya dema ku pêvajokirina pirsnameyên ji vê komê digire dihewîne. Di hundurê daxwazê de du hejmar hene (di rewşa min de ew 20 û 800 e) ku daxwazên ji komekê ji koma din vediqetînin.
Bi vî rengî parvekirinên daxwazan bi qasî dema ku xebata xweşbîniyê dest pê kir û naha berhev dike.
Di diagramê de tê dîtin ku para daxwazên TOP bi tundî kêm bûye, lê "gundiyên navîn" zêde bûne.
Di destpêkê de, daxwazên TOP xeletiyên berbiçav hene. Bi demê re, nexweşiyên zaroktiyê winda bûn, para daxwazên TOP kêm bû, û ji bo bilezkirina daxwazên dijwar diviyabû bêtir û bêtir hewil bihatana kirin.
Ji bo bidestxistina metna daxwazan em daxwaza jêrîn bikar tînin
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
Li vir navnîşek teknîkên ku herî gelemperî têne bikar anîn hene ku ji me re bûne alîkar ku pirsên TOP bilez bikin:
- Ji nû ve sêwirana pergalê, mînakî, ji nû ve xebitandina mantiqa ragihandinê bi karanîna brokerek peyamê li şûna pirsên demkî yên databasê.
- Zêdekirin an guhertina indexan
- Ji nû ve nivîsandina pirsên ORM ji SQL-ya paqij
- Ji nû ve nivîsandina mantiqa barkirina daneya lazî
- Caching bi rêya nermalîzasyona daneyê. Mînakî, pêwendiyek me ya sifrê heye Delivery -> Fato -> Daxwaz -> Serlêdan. Ango, her radestkirin bi serîlêdanek bi tabloyên din ve girêdayî ye. Ji bo ku di her daxwaziyekê de hemî tablo neyên girêdan, me lînka daxwazê di tabloya Radestkirinê de dubare kir.
- Cachkirina tabloyên statîk bi pirtûkên referansê re û kêm kêm tabloyên di bîranîna bernameyê de têne guhertin.
Carinan guheztin ji nû ve sêwiranek berbiçav pêk dihat, lê ew 5-10% barkirina pergalê peyda kirin û rastdar bûn. Bi demê re, exhaust piçûktir û piçûktir bû, û ji nû ve sêwirana bêtir û girantir hewce bû.
Dû re me bala xwe da ser koma duyemîn a daxwazan - koma gundiyên navîn. Di wê de gelek pirsên din hene û wusa dixuye ku analîzkirina tevahiya komê dê gelek dem bigire. Lêbelê, pir pirs ji xweşbîniyê pir hêsan derketin, û gelek pirsgirêk bi dehan caran di guhertoyên cihêreng de hatin dubare kirin. Li vir mînakên hin optimîzasyonên tîpîk hene ku me li bi dehan lêpirsînên mîna hev sepandin û her komek pirsên xweşbînkirî databasa 3-5% dakêşand.
- Li şûna kontrolkirina hebûna tomaran bi karanîna COUNT û tabloyek tam, EXISTS dest pê kir.
- Ji DISTINCT xilas bû (reçeteyek gelemperî tune, lê carinan hûn dikarin bi lezkirina daxwazê 10-100 carî bi hêsanî jê xilas bibin).
Mînakî, li şûna pirsek ku hemî ajokaran ji tabloyek mezin a radestkirinê hilbijêrin (DELIVERY)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDli ser tabloyeke nisbeten piçûk PERSON pirsek kir
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)Wusa dixuye ku me jêpirsînek pevgirêdayî bikar aniye, lê ew ji 10 carî zêdetir lezek dide.
- Di gelek rewşan de, COUNT bi tevahî hate terikandin û
- li gorî
UPPER(s) LIKE JOHN%’bikaranîn
s ILIKE “John%”
Her daxwazek taybetî carinan 3-1000 carî zûtir bû. Tevî performansa bi heybet, di destpêkê de ji me re xuya bû ku di xweşbînkirina pirsek ku ji bo temamkirina 10 ms digire, yek ji sed sed pirsên herî giran e, û ji sedî sed ji dema barkirina databasê ya giştî digire, ti xalek tune. Lê bi sepandina heman reçeteyê li komek pirsên ji heman celebê, me ji sedî çend paşde qezenc kir. Ji bo ku em bi destan vekolîna hemî sed pirsan dem winda nekin, me çend nivîsarên hêsan nivîsandin ku birêkûpêk bi kar anîn da ku pirsên ji heman celebê bibînin. Wekî encamek, lêgerîna bixweber komên pirsan hişt ku em bi hewildanek hûrgelê performansa xwe baştir bikin.
Di encamê de, ev sê sal in em li ser heman hardware dixebitin. Barkirina rojane ya navîn bi qasî 30%, di lûtkeyan de digihîje 70%. Hejmara daxwazan û her weha hejmara bikarhêneran bi qasî 10 carî zêde bûye. Û ev hemî bi saya çavdêriya domdar a van heman komên daxwazên TOP-MEDIUM. Mîna ku daxwazek nû di koma TOP de xuya bibe, em tavilê wê analîz dikin û hewl didin ku wê bilez bikin. Em heftê carekê koma MEDIUM bi karanîna skrîptên analîza pirsê dinirxînin. Ger em rastî pirsên nû werin ku em jixwe dizanin ka meriv çawa xweşbîn bike, em zû wan diguherînin. Carinan em rêbazên optimîzasyonê yên nû yên ku dikarin bi yekcarî li ser çend pirsan werin sepandin dibînin.
Li gorî pêşbîniyên me, servera heyî dê li hember zêdebûna hejmara bikarhêneran 3-5 carên din bisekinin. Rast e, me yek acizek din di berika xwe de heye - me hîn jî pirsên SELECT veguheztine neynikê, wekî ku tê pêşniyar kirin. Lê em vê yekê bi zanebûn nakin, ji ber ku em dixwazin pêşî îmkanên xweşbîniya "aqilmend" bi tevahî biqewirînin berî ku "topên giran" bizivirînin.
Nêrînek rexneyî li xebata hatî kirin dibe ku karanîna pîvana vertîkal pêşniyar bike. Li şûna ku hûn wextê pisporan winda bikin serverek bihêztir bikirin. Dibe ku server ew qas lêçûn neke, nemaze ji ber ku me hêj sînorên pîvana vertîkal xilas nekiriye. Lêbelê, tenê hejmara daxwazan 10 carî zêde bû. Di nav çend salan de, fonksiyona pergalê zêde bûye û naha bêtir celeb daxwaz hene. Bi xêra cachkirinê, fonksiyona ku hebû di kêm daxwazan, û daxwazên bikêrtir de pêk tê. Ev tê vê wateyê ku hûn dikarin bi ewlehî bi 5-ên din zêde bikin da ku rêjeya leza rastîn bistînin. Ji ber vê yekê, li gorî texmînên herî kevneperest, em dikarin bibêjin ku lezbûn 50 carî an jî zêdetir bû. Veguheztina vertîkal a serverek dê 50 carî bêtir lêçû. Nemaze ji ber vê yekê ku gava xweşbîn kirin ew her dem dixebite, û fatûreya servera kirêkirî her meh tê.
Source: www.habr.com