ProHoster > Blog > Rianachd > A’ dèanamh feum as fheàrr de cheistean stòr-dàta a’ cleachdadh eisimpleir seirbheis B2B airson luchd-togail

A’ dèanamh feum as fheàrr de cheistean stòr-dàta a’ cleachdadh eisimpleir seirbheis B2B airson luchd-togail

Mar a dh’ fhàsas tu 10 uiread an àireamh de cheistean chun stòr-dàta gun a bhith a’ gluasad gu frithealaiche nas cinneasaiche agus a’ cumail suas gnìomhachd an t-siostaim? Innsidh mi dhut mar a dhèilig sinn ris a’ chrìonadh ann an coileanadh ar stòr-dàta, mar a leasaich sinn ceistean SQL gus a bhith a’ frithealadh uimhir de luchd-cleachdaidh sa ghabhas agus gun a bhith ag àrdachadh cosgais ghoireasan coimpiutaireachd.

Bidh mi a’ dèanamh seirbheis airson pròiseasan gnìomhachais a stiùireadh ann an companaidhean togail. Bidh timcheall air 3 mìle companaidh ag obair còmhla rinn. Bidh còrr air 10 mìle neach ag obair leis an t-siostam againn gach latha airson 4-10 uairean. Bidh e a’ fuasgladh diofar dhuilgheadasan planaidh, fios, rabhadh, dearbhadh... Bidh sinn a’ cleachdadh PostgreSQL 9.6. Tha timcheall air 300 clàr againn san stòr-dàta agus gheibhear suas ri 200 millean ceist (10 mìle eadar-dhealaichte) gach latha. Gu cuibheasach tha 3-4 mìle iarrtas againn gach diog, aig na h-amannan as gnìomhaiche còrr air 10 mìle iarrtas gach diog. Tha a’ mhòr-chuid de na ceistean OLAP. Tha mòran nas lugha de chur-ris, atharrachaidhean agus sguabadh às, a’ ciallachadh gu bheil an luchd OLTP gu math aotrom. Thug mi seachad na h-àireamhan sin gu lèir gus an urrainn dhut meud a’ phròiseict againn a mheasadh agus tuigse fhaighinn air cho feumail sa dh’ fhaodas ar n-eòlas a bhith dhut.

Dealbh a h-aon. Lyrics

Nuair a thòisich sinn air leasachadh, cha do smaoinich sinn dha-rìribh air dè an seòrsa luchd a bhiodh a’ tuiteam air an stòr-dàta agus dè a dhèanadh sinn nan stadadh am frithealaiche a’ tarraing. Nuair a bha sinn a’ dealbhadh an stòr-dàta, lean sinn molaidhean coitcheann agus dh’ fheuch sinn gun a bhith gar losgadh fhèin sa chas, ach chaidh sinn seachad air comhairle choitcheann mar “na cleachd am pàtran Luachan Buidhne eintiteas cha deach sinn a-steach. Dhealbhaich sinn stèidhichte air prionnsapalan gnàthachaidh, a’ seachnadh call dàta agus cha robh dragh againn mu bhith a’ luathachadh cuid de cheistean. Cho luath ‘s a ràinig a’ chiad luchd-cleachdaidh, thachair sinn ri duilgheadas coileanaidh. Mar as àbhaist, bha sinn gu tur gun ullachadh airson seo. Thàinig a 'chiad dhuilgheadasan gu bhith sìmplidh. Mar riaghailt, chaidh a h-uile càil fhuasgladh le bhith a 'cur clàr-amais ùr ris. Ach thàinig àm nuair a sguir pìosan sìmplidh a bhith ag obair. A’ tuigsinn gu bheil dìth eòlais againn agus gu bheil e a’ sìor fhàs doirbh dhuinn tuigsinn dè a tha ag adhbhrachadh nan duilgheadasan, dh’fhastaidh sinn eòlaichean a chuidich sinn le bhith a’ stèidheachadh an fhrithealaiche gu ceart, a’ ceangal sgrùdadh, agus a’ sealltainn dhuinn càite am bu chòir dhuinn coimhead airson faighinn. staitistig.

Dealbh a dhà. Staitistigeil

Mar sin tha timcheall air 10 mìle ceist eadar-dhealaichte againn a thèid a chuir gu bàs air an stòr-dàta againn gach latha. Den 10 mìle sin, tha uilebheistean air an cur gu bàs 2-3 millean uair le ùine cur gu bàs cuibheasach de 0.1-0.3 ms, agus tha ceistean ann le ùine cur gu bàs cuibheasach de 30 diogan ris an canar 100 uair san latha.

Cha robh e comasach na 10 mìle ceist gu lèir a bharrachadh, agus mar sin chuir sinn romhainn faighinn a-mach càite an stiùir sinn ar n-oidhirpean gus coileanadh an stòr-dàta a leasachadh gu ceart. Às deidh grunn thursan, thòisich sinn air iarrtasan a roinn ann an seòrsachan.

Iarrtasan TOP

Is iad seo na ceistean as truime a bheir an ùine as motha (ùine iomlan). Is iad sin ceistean a chanar gu math tric no ceistean a bheir ùine mhòr airson an cur an gnìomh (chaidh ceistean fada is tric a mheudachadh anns a’ chiad chuairtean den t-sabaid airson astar). Mar thoradh air an sin, bidh am frithealaiche a’ caitheamh an ùine as motha air an cur gu bàs. A bharrachd air an sin, tha e cudromach prìomh iarrtasan a sgaradh a rèir ùine cur gu bàs iomlan agus fa leth le ùine IO. Tha na dòighean airson a leithid de cheistean a bharrachadh beagan eadar-dhealaichte.

Is e an cleachdadh àbhaisteach aig a h-uile companaidh a bhith ag obair le iarrtasan TOP. Chan eil mòran dhiubh ann; faodaidh eadhon aon cheist 5-10% de ghoireasan a shaoradh. Ach, mar a bhios am pròiseact a’ tighinn gu ìre, bidh a bhith a’ dèanamh an fheum as fheàrr de cheistean TOP gu bhith na obair nach eil cho cudromach. Tha a h-uile modh sìmplidh air obrachadh a-mach mar-thà, agus tha an t-iarrtas as “trom” a’ toirt “dìreach” 3-5% de ghoireasan. Ma bheir ceistean TOP gu h-iomlan nas lugha na 30-40% den ùine, tha e glè choltach gu bheil thu air oidhirpean a dhèanamh mar-thà gus toirt orra obrachadh gu sgiobalta agus tha an t-àm ann gluasad air adhart gu bhith ag ùrachadh cheistean bhon ath bhuidheann.
Tha e fhathast gus a’ cheist a fhreagairt cia mheud prìomh cheistean a bu chòir a bhith sa bhuidheann seo. Mar as trice bidh mi a 'toirt co-dhiù 10, ach gun a bhith nas fhaide na 20. Bidh mi a' feuchainn ri dèanamh cinnteach nach eil ùine a 'chiad agus an tè mu dheireadh anns a' bhuidheann TOP eadar-dhealaichte nas fhaide na 10 tursan. Is e sin, ma tha ùine cur an gnìomh a’ cheist a’ tuiteam gu mòr bhon àite 1st gu 10mh, an uairsin bidh mi a’ gabhail TOP-10, ma tha an tuiteam nas mean air mhean, an uairsin àrdaichidh mi meud na buidhne gu 15 no 20.
A’ dèanamh feum as fheàrr de cheistean stòr-dàta a’ cleachdadh eisimpleir seirbheis B2B airson luchd-togail

Luchd-tuatha meadhanach

Tha iad sin uile nan iarrtasan a thig dìreach às deidh TOP, ach a-mhàin an 5-10% mu dheireadh. Mar as trice, ann a bhith a’ dèanamh an fheum as fheàrr de na ceistean sin tha an cothrom ann coileanadh frithealaiche àrdachadh gu mòr. Faodaidh na h-iarrtasan sin cuideam suas ri 80%. Ach eadhon ged a tha an roinn aca air a dhol thairis air 50%, tha an t-àm ann coimhead orra nas faiceallach.

Earball

Mar a chaidh ainmeachadh, thig na ceistean sin aig an deireadh agus bheir iad 5-10% den ùine. Chan urrainn dhut dìochuimhneachadh mun deidhinn a-mhàin mura cleachd thu innealan sgrùdaidh ceist fèin-ghluasadach, faodaidh an dèanamh as fheàrr a bhith saor cuideachd.

Ciamar a nì thu measadh air gach buidheann?

Bidh mi a’ cleachdadh ceist SQL a chuidicheas le bhith a’ dèanamh a leithid de mheasadh airson PostgreSQL (tha mi cinnteach gun gabh ceist coltach ris a sgrìobhadh airson mòran DBMS eile)

Ceist SQL gus tuairmse a dhèanamh air meud bhuidhnean 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

Is e toradh na ceiste trì colbhan, agus anns gach fear dhiubh an àireamh sa cheud de ùine a bheir e gus ceistean bhon chuantal seo a làimhseachadh. Taobh a-staigh an iarrtais tha dà àireamh (sa chùis agamsa 's e 20 agus 800) a tha a' sgaradh iarrtasan bho aon bhuidheann bho bhuidheann eile.

Seo mar a tha na h-earrannan de dh'iarrtasan a 'dèanamh coimeas eadar an àm a thòisich obair optimization agus a-nis.

A’ dèanamh feum as fheàrr de cheistean stòr-dàta a’ cleachdadh eisimpleir seirbheis B2B airson luchd-togail

Tha an diagram a’ sealltainn gu bheil an roinn de dh’iarrtasan TOP air a dhol sìos gu mòr, ach tha na “tuathanaich mheadhanach” air a dhol suas.
An toiseach, bha na h-iarrtasan TOP a’ toirt a-steach blunders soilleir. Thar ùine, chaidh galairean leanabachd à bith, chaidh an roinn de dh’iarrtasan TOP sìos, agus dh’ fheumadh barrachd is barrachd oidhirpean a dhèanamh gus iarrtasan duilich a luathachadh.

Gus teacsa iarrtasan fhaighinn bidh sinn a’ cleachdadh an iarrtas a leanas

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

Seo liosta de na dòighean as cumanta a chuidich sinn le bhith a’ luathachadh cheistean TOP:

  • Ath-dhealbhadh an t-siostaim, mar eisimpleir, ag ath-obrachadh an loidsig fios a’ cleachdadh broker teachdaireachd an àite ceistean bho àm gu àm chun stòr-dàta
  • A’ cur ris no ag atharrachadh clàran-amais
  • Ag ath-sgrìobhadh cheistean ORM gu SQL fìor
  • Logic luchdachadh dàta leisg ath-sgrìobhadh
  • A’ tasgadh tro dhì-normalachadh dàta. Mar eisimpleir, tha ceangal bùird againn Lìbhrigeadh -> Fàirdeal -> Iarrtas -> Iarrtas. Is e sin, tha gach lìbhrigeadh co-cheangailte ri tagradh tro chlàran eile. Gus nach ceangail sinn a h-uile clàr anns gach iarrtas, dhùblaich sinn an ceangal ris an iarrtas sa chlàr Lìbhrigeadh.
  • A’ tasgadh chlàran statach le leabhraichean fiosrachaidh agus bùird nach eil ag atharrachadh ach ainneamh ann an cuimhne a’ phrògraim.

Aig amannan bha na h-atharrachaidhean gu bhith nan ath-dhealbhadh drùidhteach, ach thug iad seachad 5-10% de luchd an t-siostaim agus bha iad reusanta. Thar ùine, dh'fhàs an t-inneal-fuadain nas lugha agus nas lugha, agus bha feum air ath-dhealbhadh barrachd is barrachd.

An uairsin thionndaidh sinn ar n-aire chun dàrna buidheann de dh'iarrtasan - a 'bhuidheann de luchd-tuatha meadhanach. Tha tòrr a bharrachd cheistean ann agus bha e coltach gun toireadh e tòrr ùine mion-sgrùdadh a dhèanamh air a’ bhuidheann gu lèir. Ach, bha a’ mhòr-chuid de cheistean gu math sìmplidh airson an cleachdadh as fheàrr, agus chaidh mòran dhuilgheadasan ath-aithris grunn thursan ann an diofar atharrachaidhean. Seo eisimpleirean de chuid de optimizations àbhaisteach a chuir sinn an sàs ann an dusanan de cheistean coltach ris agus chuir gach buidheann de cheistean làn-leasaichte an stòr-dàta sìos 3-5%.

  • An àite a bhith a’ cumail sùil air làthaireachd chlàran a’ cleachdadh COUNT agus làn-sgan clàr, thòisich EXISTS air a chleachdadh
  • Fhuair thu cuidhteas DISTINCT (chan eil reasabaidh coitcheann ann, ach uaireannan gheibh thu cuidhteas e gu furasta le bhith a’ luathachadh an iarrtais 10-100 uair).

    Mar eisimpleir, an àite ceist gus a h-uile dràibhear a thaghadh bho bhòrd mòr lìbhrigidh (LÌBHRIGEADH) 

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

    ceist a chuir air bòrd an ìre mhath beag DAOINE

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

    Bhiodh e coltach gun do chleachd sinn subquery co-cheangailte, ach tha e a’ toirt astar nas àirde na 10 tursan.

  • Ann an iomadh cùis, chaidh COUNT a thrèigsinn gu tur agus
    air a chur na àite le àireamhachadh de luach tuairmseach
  • an àite 
    UPPER(s) LIKE JOHN%’

    cleachdadh 

    s ILIKE “John%”

Aig amannan chaidh gach iarrtas sònraichte a luathachadh 3-1000 uair. A dh’aindeoin a’ choileanaidh dhrùidhteach, bha e coltach dhuinn an toiseach nach robh feum sam bith ann a bhith a’ dèanamh an fheum as fheàrr de cheist a bheir 10 ms airson a chrìochnachadh, is e seo aon de na 3mh ceud ceist as truime, agus a’ toirt suas na ceudan sa cheud de ùine luchdaidh an stòr-dàta iomlan. Ach le bhith a’ cleachdadh an aon reasabaidh ri buidheann de cheistean den aon sheòrsa, bhuannaich sinn beagan sa cheud air ais. Gus nach caith sinn ùine le làimh ag ath-sgrùdadh nan ceudan de cheistean, sgrìobh sinn grunn sgriobtaichean sìmplidh a chleachd abairtean cunbhalach gus ceistean den aon sheòrsa a lorg. Mar thoradh air an sin, le bhith a’ sgrùdadh bhuidhnean cheistean gu fèin-ghluasadach leig sinn leinn ar coileanadh adhartachadh le beagan oidhirp.

Mar thoradh air an sin, tha sinn air a bhith ag obair air an aon bhathar-cruaidh airson trì bliadhna a-nis. Tha an luchd cuibheasach làitheil timcheall air 30%, ann an stùcan bidh e a’ ruighinn 70%. Tha an àireamh de dh'iarrtasan, a thuilleadh air an àireamh de luchd-cleachdaidh, air a dhol suas mu 10 tursan. Agus seo uile mar thoradh air a bhith a’ cumail sùil leantainneach air na h-aon bhuidhnean sin de dh’ iarrtasan TOP-MEDIUM. Cho luath ‘s a nochdas iarrtas ùr anns a’ bhuidheann TOP, bidh sinn ga sgrùdadh sa bhad agus a ’feuchainn ri a luathachadh. Bidh sinn ag ath-sgrùdadh a’ bhuidheann MEDIUM uair san t-seachdain a’ cleachdadh sgriobtaichean mion-sgrùdadh ceist. Ma thig sinn tarsainn air ceistean ùra a tha fios againn mu thràth mar a nì sinn an fheum as fheàrr, bidh sinn gan atharrachadh gu sgiobalta. Aig amannan lorgaidh sinn dòighean optimization ùra a dh’ fhaodar a chuir an sàs ann an grunn cheistean aig an aon àm.

A rèir ar ro-innsean, seasaidh am frithealaiche gnàthach àrdachadh san àireamh de luchd-cleachdaidh 3-5 tursan eile. Fìor, tha aon ace eile againn suas ar caol - chan eil sinn fhathast air ceistean SELECT a ghluasad chun sgàthan, mar a thathas a ’moladh. Ach cha bhith sinn a’ dèanamh seo gu mothachail, oir tha sinn airson an-toiseach na cothroman air optimization “smart” a chuir às gu tur mus tionndaidh sinn air an “làmhachas trom”.
Is dòcha gu bheil sealladh breithneachail air an obair a chaidh a dhèanamh a’ moladh a bhith a’ cleachdadh sgèileadh dìreach. Ceannaich frithealaiche nas cumhachdaiche an àite a bhith a’ caitheamh ùine nan eòlaichean. Is dòcha nach cosg am frithealaiche na h-uimhir, gu h-àraidh leis nach eil sinn fhathast air crìoch a chur air crìochan sgèileadh dìreach. Ach, cha do dh'èirich ach an àireamh de dh'iarrtasan 10 tursan. Thar grunn bhliadhnaichean, tha gnìomhachd an t-siostam air a dhol suas agus a-nis tha barrachd sheòrsan iarrtasan ann. Taing dha caching, tha an gnìomh a bha ann air a choileanadh ann an nas lugha de dh’ iarrtasan, agus iarrtasan nas èifeachdaiche. Tha seo a’ ciallachadh gun urrainn dhut iomadachadh gu sàbhailte le 5 eile gus an fhìor cho-èifeachd luathachaidh fhaighinn. Mar sin, a rèir nan tuairmsean as gleidhidh, faodaidh sinn a ràdh gu robh an luathachadh 50 uair no barrachd. Cosgadh frithealaiche gu dìreach 50 uair a bharrachd. Gu sònraichte leis gu bheil aon uair ‘s gu bheil optimization air a dhèanamh bidh e ag obair fad na h-ùine, agus thig am bile airson an fhrithealaiche màil gach mìos.

Source: www.habr.com

Cuir beachd ann