ለግንበኞች የB2B አገልግሎት ምሳሌ በመጠቀም የውሂብ ጎታ መጠይቆችን ማመቻቸት

ወደ የበለጠ ውጤታማ አገልጋይ ሳይንቀሳቀሱ እና የስርዓት ተግባራትን ሳይጠብቁ 10 ጊዜ የጥያቄዎችን ብዛት ወደ ዳታቤዝ እንዴት ማሳደግ ይቻላል? የውሂብ ጎታችን አፈጻጸም ማሽቆልቆሉን እንዴት እንዳስተናገድን፣ በተቻለ መጠን ብዙ ተጠቃሚዎችን ለማገልገል የ SQL መጠይቆችን እንዴት እንዳመቻቸን እና የማስላት ሀብቶችን ወጪ እንዳንጨምር እነግርዎታለሁ።

በግንባታ ኩባንያዎች ውስጥ የንግድ ሥራ ሂደቶችን ለማስተዳደር አገልግሎት እሰራለሁ. ወደ 3 ሺህ የሚጠጉ ኩባንያዎች ከእኛ ጋር ይሰራሉ. ከ 10 ሺህ በላይ ሰዎች በየቀኑ ከ4-10 ሰአታት ከስርዓታችን ጋር ይሰራሉ. የተለያዩ የማቀድ፣ የማሳወቂያ፣ የማስጠንቀቂያ፣ የማረጋገጫ ችግሮችን ይፈታል... PostgreSQL 9.6 እንጠቀማለን። በመረጃ ቋቱ ውስጥ ወደ 300 የሚጠጉ ሠንጠረዦች አሉን እና በየቀኑ እስከ 200 ሚሊዮን የሚደርሱ መጠይቆች (10 ሺህ የተለያዩ) ይቀበላሉ። በአማካይ በሴኮንድ ከ3-4 ሺህ ጥያቄዎች አሉን፣ በጣም ንቁ በሆኑ ጊዜያት በሰከንድ ከ10 ሺህ በላይ ጥያቄዎች አሉ። አብዛኛዎቹ መጠይቆች OLAP ናቸው። በጣም ያነሱ ጭማሪዎች፣ ማሻሻያዎች እና ስረዛዎች አሉ፣ ይህም ማለት የOLTP ጭነት በአንጻራዊነት ቀላል ነው። የፕሮጀክታችንን ስፋት እንድትገመግሙ እና የእኛ ልምድ ለእርስዎ ምን ያህል ጠቃሚ እንደሆነ እንዲረዱ እነዚህን ሁሉ ቁጥሮች አቅርቤያለሁ።

ምስል አንድ. ግጥማዊ

ልማትን ስንጀምር በመረጃ ቋቱ ላይ ምን አይነት ጭነት እንደሚወድቅ እና አገልጋዩ መጎተት ቢያቆም ምን እንደምናደርግ በትክክል አላሰብንም። የመረጃ ቋቱን በምንቀርፅበት ጊዜ አጠቃላይ ምክሮችን ተከትለን እራሳችንን በእግር ላይ ላለመተኮስ ሞከርን ፣ ግን ከአጠቃላይ ምክሮች አልፈናል ፣ “ስርዓተ-ጥለትን አይጠቀሙ የህጋዊ አካል መለያ እሴቶች አልገባንም። እኛ በመደበኛነት መርሆዎች ላይ በመመስረት ንድፍ አዘጋጅተናል ፣ የውሂብ ድግግሞሽን በማስወገድ እና የተወሰኑ ጥያቄዎችን ለማፋጠን ግድ አልነበረንም። የመጀመሪያዎቹ ተጠቃሚዎች እንደደረሱ የአፈጻጸም ችግር አጋጥሞናል። እንደተለመደው ለዚህ ሙሉ በሙሉ ዝግጁ አልነበርንም። የመጀመሪያዎቹ ችግሮች ቀላል ሆነው ተገኝተዋል. እንደ አንድ ደንብ, ሁሉም ነገር አዲስ ኢንዴክስ በማከል ተፈትቷል. ነገር ግን ቀላል ጥገናዎች መስራት ያቆሙበት ጊዜ መጣ. የልምድ ማነስ እና የችግሮቹን መንስኤ ለመረዳት አዳጋች እየሆነብን መምጣቱን በመረዳት አገልጋዩን በትክክል በማዘጋጀት ፣ክትትልን በማገናኘት የረዱን ልዩ ባለሙያዎችን ቀጥረን የት ማግኘት እንዳለብን አሳይተናል። ስታቲስቲክስ.

ምስል ሁለት. ስታቲስቲካዊ

ስለዚህ በቀን ወደ 10 ሺህ የሚጠጉ የተለያዩ መጠይቆች በመረጃ ቋታችን ላይ ይፈጸማሉ። ከእነዚህ 10 ሺዎች ውስጥ በአማካይ ከ2-3 ሚሊዮን ጊዜ የሚፈፀሙ ጭራቆች 0.1-0.3 ms የሚፈፀሙ ሲሆን በቀን 30 ጊዜ የሚጠሩ በአማካይ 100 ሰከንድ የሚፈጁ ጥያቄዎች አሉ።

ሁሉንም የ 10 ሺህ ጥያቄዎችን ማመቻቸት አልተቻለም, ስለዚህ የውሂብ ጎታውን አፈፃፀም በትክክል ለማሻሻል ጥረታችንን የት እንደሚመራ ለማወቅ ወስነናል. ከብዙ ድግግሞሾች በኋላ ጥያቄዎችን ወደ ዓይነቶች መከፋፈል ጀመርን።

ከፍተኛ ጥያቄዎች

ብዙ ጊዜ የሚወስዱት በጣም ከባድ ጥያቄዎች ናቸው (ጠቅላላ ጊዜ)። እነዚህ በጣም ብዙ ጊዜ ተብለው የሚጠሩ መጠይቆች ወይም ለመፈፀም በጣም ረጅም ጊዜ የሚፈጁ መጠይቆች ናቸው (ረጅም እና ተደጋጋሚ መጠይቆች በመጀመሪያዎቹ የፍጥነት ትግሉ ድግግሞሾች የተመቻቹ) ናቸው። በውጤቱም, አገልጋዩ በአፈፃፀማቸው ላይ ብዙ ጊዜ ያሳልፋል. ከዚህም በላይ ከፍተኛ ጥያቄዎችን በጠቅላላ የማስፈጸሚያ ጊዜ እና በተናጥል በ IO ጊዜ መለየት አስፈላጊ ነው. እንደነዚህ ያሉ መጠይቆችን የማመቻቸት ዘዴዎች ትንሽ ለየት ያሉ ናቸው.

የሁሉም ኩባንያዎች የተለመደ አሰራር ከ TOP ጥያቄዎች ጋር መስራት ነው። ጥቂቶቹ ናቸው፤ አንድ መጠይቅ እንኳን ማመቻቸት ከ5-10% ሃብትን ነጻ ማድረግ ይችላል። ነገር ግን፣ ፕሮጀክቱ ሲበስል፣ TOP መጠይቆችን ማመቻቸት ከጊዜ ወደ ጊዜ ቀላል ያልሆነ ስራ ይሆናል። ሁሉም ቀላል ዘዴዎች ቀድሞውኑ ተሠርተዋል, እና በጣም "ከባድ" ጥያቄ ከ 3-5% ሀብቶች "ብቻ" ይወስዳል. የ TOP መጠይቆች በአጠቃላይ ከ30-40% ያነሰ ጊዜ የሚወስዱ ከሆነ፣ ምናልባት እርስዎ በፍጥነት እንዲሰሩ ለማድረግ ጥረቶችን ሰርተዋል እና ከሚቀጥለው ቡድን የሚመጡ መጠይቆችን ወደ ማመቻቸት ለመቀጠል ጊዜው አሁን ነው።
በዚህ ቡድን ውስጥ ምን ያህል ዋና ጥያቄዎች መካተት አለባቸው የሚለውን ጥያቄ ለመመለስ ይቀራል። እኔ ብዙውን ጊዜ ቢያንስ 10 እወስዳለሁ ፣ ግን ከ 20 አይበልጥም ። በ TOP ቡድን ውስጥ የመጀመሪያ እና የመጨረሻ ጊዜ ከ 10 ጊዜ በማይበልጥ ጊዜ እንደሚለያይ ለማረጋገጥ እሞክራለሁ። ማለትም የጥያቄው ማስፈጸሚያ ጊዜ ከ 1 ኛ ደረጃ ወደ 10 ኛ በከፍተኛ ሁኔታ ከቀነሰ TOP-10 ን እወስዳለሁ ፣ ጠብታው ቀስ በቀስ ከሆነ ፣ ከዚያ የቡድኑን መጠን ወደ 15 ወይም 20 እጨምራለሁ ።

መካከለኛ ገበሬዎች

እነዚህ ሁሉ ከ TOP በኋላ ወዲያውኑ የሚመጡ ጥያቄዎች ናቸው, ከመጨረሻው 5-10% በስተቀር. ብዙውን ጊዜ፣ እነዚህን ጥያቄዎች በማመቻቸት የአገልጋይ አፈጻጸምን በእጅጉ የመጨመር እድሉ አለ። እነዚህ ጥያቄዎች እስከ 80% ሊመዝኑ ይችላሉ. ነገር ግን የእነሱ ድርሻ ከ 50% በላይ ቢሆንም, ከዚያ የበለጠ በጥንቃቄ ለመመልከት ጊዜው አሁን ነው.

ጅራት

እንደተጠቀሰው, እነዚህ ጥያቄዎች መጨረሻ ላይ ይመጣሉ እና ከ5-10% ጊዜ ይወስዳሉ. ስለእነሱ ሊረሱ የሚችሉት አውቶማቲክ የመጠይቅ ትንተና መሳሪያዎችን ካልተጠቀሙ ብቻ ነው, ከዚያም እነሱን ማመቻቸት ርካሽ ሊሆን ይችላል.

እያንዳንዱን ቡድን እንዴት መገምገም ይቻላል?

ለ PostgreSQL እንደዚህ ያለ ግምገማ ለማድረግ የሚረዳ የSQL መጠይቅን እጠቀማለሁ (ተመሳሳይ ጥያቄ ለብዙ ሌሎች ዲቢኤምኤስ ሊፃፍ እንደሚችል እርግጠኛ ነኝ)

TOP-MEDIUM-TAIL ቡድኖችን መጠን ለመገመት የSQL ጥያቄ

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

የጥያቄው ውጤት ሶስት አምዶች ሲሆን እያንዳንዳቸው ከዚህ ቡድን የሚመጡ ጥያቄዎችን ለማስኬድ የሚፈጀውን ጊዜ መቶኛ ይይዛል። በጥያቄው ውስጥ ከአንድ ቡድን የሚነሱ ጥያቄዎችን የሚለዩ ሁለት ቁጥሮች (በእኔ ሁኔታ 20 እና 800) አሉ።

የጥያቄዎች አክሲዮኖች የማመቻቸት ሥራ በጀመረበት ጊዜ እና አሁን ሲነፃፀሩ እንደዚህ ነው።

ስዕሉ እንደሚያሳየው የ TOP ጥያቄዎች ድርሻ በከፍተኛ ሁኔታ ቀንሷል, ነገር ግን "መካከለኛ ገበሬዎች" ጨምረዋል.
በመጀመሪያ፣ የ TOP ጥያቄዎች ግልጽ ስህተቶችን ያካትታሉ። ከጊዜ በኋላ የልጅነት በሽታዎች ጠፍተዋል, የ TOP ጥያቄዎች ድርሻ ቀንሷል, እና አስቸጋሪ ጥያቄዎችን ለማፋጠን ብዙ እና ብዙ ጥረቶች መደረግ ነበረባቸው.

የጥያቄዎችን ጽሑፍ ለማግኘት የሚከተለውን ጥያቄ እንጠቀማለን።

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

TOP መጠይቆችን እንድናፋጥን የረዱን በብዛት ጥቅም ላይ የዋሉ ቴክኒኮች ዝርዝር ይኸውና፡

• የስርዓቱን ዳግም ዲዛይን ማድረግ፣ ለምሳሌ፣ በየጊዜው ወደ ዳታቤዝ ከሚጠየቁ ጥያቄዎች ይልቅ የመልእክት ደላላን በመጠቀም የማሳወቂያ አመክንዮ እንደገና መስራት።
• ኢንዴክሶችን ማከል ወይም መለወጥ
• የ ORM ጥያቄዎችን ወደ ንጹህ SQL እንደገና በመፃፍ ላይ
• ሰነፍ ውሂብ የመጫኛ አመክንዮ እንደገና መጻፍ
• በመረጃ መበላሸት መሸጎጫ። ለምሳሌ፣ የጠረጴዛ ግንኙነት ማድረስ -> ደረሰኝ -> ጥያቄ -> መተግበሪያ አለን። ያም ማለት እያንዳንዱ ማቅረቢያ በሌሎች ሰንጠረዦች በኩል ከማመልከቻ ጋር የተያያዘ ነው. በእያንዳንዱ ጥያቄ ውስጥ ሁሉንም ጠረጴዛዎች ላለማገናኘት ፣በመላኪያ ሠንጠረዥ ውስጥ ካለው ጥያቄ ጋር ያለውን አገናኝ ደጋግመናል።
• የማይንቀሳቀሱ ሰንጠረዦችን ከማጣቀሻ መጽሃፍቶች ጋር መሸጎጥ እና በፕሮግራም ማህደረ ትውስታ ውስጥ ብዙ ጊዜ ሰንጠረዦችን መለወጥ።

አንዳንድ ጊዜ ለውጦቹ አስደናቂ የሆነ የድጋሚ ንድፍ ነበራቸው, ነገር ግን ከ5-10% የስርዓት ጭነት አቅርበዋል እና ይጸድቃሉ. ከጊዜ በኋላ የጭስ ማውጫው እየቀነሰ እና እየቀነሰ ይሄዳል, እና የበለጠ እና የበለጠ ከባድ የሆነ የመልሶ ማልማት ያስፈልጋል.

ከዚያም ትኩረታችንን ወደ ሁለተኛው የጥያቄዎች ቡድን - የመካከለኛው ገበሬዎች ቡድን አዙረናል. በውስጡ ብዙ ተጨማሪ ጥያቄዎች አሉ እና ሁሉንም ቡድን ለመተንተን ብዙ ጊዜ የሚወስድ ይመስላል። ሆኖም፣ አብዛኞቹ ጥያቄዎች ለማመቻቸት በጣም ቀላል ሆነው ተገኝተዋል፣ እና ብዙ ችግሮች በተለያዩ ልዩነቶች ውስጥ በደርዘን የሚቆጠሩ ጊዜያት ተደጋግመዋል። በደርዘኖች ለሚቆጠሩ ተመሳሳይ መጠይቆች የተመለከትናቸው አንዳንድ የተለመዱ ማሻሻያዎች ምሳሌዎች እና እያንዳንዱ የተመቻቹ መጠይቆች ከ3-5% የውሂብ ጎታውን አውርደዋል።

• COUNT እና ሙሉ የሰንጠረዥ ቅኝት በመጠቀም መዝገቦች መኖራቸውን ከመፈተሽ ይልቅ EXISTS ስራ ላይ መዋል ጀመረ
• ከ DISTINCT ተወግዷል (አጠቃላይ የምግብ አዘገጃጀት መመሪያ የለም, ነገር ግን አንዳንድ ጊዜ ጥያቄውን በ 10-100 ጊዜ በማፋጠን በቀላሉ ማስወገድ ይችላሉ).

  ለምሳሌ፣ ሁሉንም ሾፌሮች ከትልቅ የማድረሻ ሠንጠረዥ ለመምረጥ ከመጠየቅ ይልቅ (ማድረስ)

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

  በአንጻራዊ ትንሽ ጠረጴዛ ላይ ጥያቄ አቅርቧል ሰው

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

  ተዛማጅ ንዑስ መጠይቅ የተጠቀምን ይመስላል፣ ግን ከ10 ጊዜ በላይ ፍጥነትን ይሰጣል።

• በብዙ አጋጣሚዎች፣ COUNT ሙሉ በሙሉ ተትቷል እና
  በግምታዊ እሴት ስሌት ተተካ
• በ

  UPPER(s) LIKE JOHN%’ 
  

  ተጠቀም

  s ILIKE “John%”
  

እያንዳንዱ የተለየ ጥያቄ አንዳንድ ጊዜ በ3-1000 ጊዜ ተፋጠነ። ምንም እንኳን አስደናቂ አፈፃፀሙ ቢኖርም ፣ መጀመሪያ ላይ ለመጨረስ 10 ms የሚፈጅ ፣ ከ 3 ኛ መቶ በጣም ከባድ ጥያቄዎች ውስጥ አንዱ እና ከጠቅላላው የውሂብ ጎታ ጭነት ጊዜ በመቶኛ መቶኛ የሚወስድ ጥያቄን ለማሻሻል ምንም ፋይዳ የሌለው መስሎ ታየን። ነገር ግን ተመሳሳዩን የምግብ አሰራር ለተመሳሳይ አይነት መጠይቆች ቡድን በመተግበር ጥቂት በመቶዎችን አሸንፈናል። ሁሉንም በመቶዎች የሚቆጠሩ መጠይቆችን በእጅ ለመገምገም ጊዜን ላለማባከን፣ ተመሳሳይ አይነት መጠይቆችን ለማግኘት መደበኛ አገላለጾችን የሚጠቀሙ ብዙ ቀላል ስክሪፕቶችን ጻፍን። በውጤቱም፣ በራስ-ሰር የቡድን መጠይቆችን መፈለግ መጠነኛ ጥረት በማድረግ አፈጻጸማችንን የበለጠ እንድናሻሽል አስችሎናል።

በዚህም ምክንያት ለሦስት ዓመታት ያህል በተመሳሳይ ሃርድዌር እየሠራን ነው። አማካይ ዕለታዊ ጭነት 30% ያህል ነው ፣ በከፍታዎች ውስጥ 70% ይደርሳል። የጥያቄዎች ብዛት፣ እንዲሁም የተጠቃሚዎች ብዛት በግምት 10 ጊዜ ጨምሯል። እና ይሄ ሁሉ ለእነዚህ ተመሳሳይ የ TOP-MEDIUM ጥያቄዎች የማያቋርጥ ክትትል ምስጋና ይግባው. በ TOP ቡድን ውስጥ አዲስ ጥያቄ እንደታየ ወዲያውኑ እንመረምረዋለን እና ለማፋጠን እንሞክራለን። የጥያቄ ትንተና ስክሪፕቶችን በመጠቀም የ MEDIUM ቡድን በሳምንት አንድ ጊዜ እንገመግማለን። እንዴት ማመቻቸት እንዳለብን የምናውቃቸው አዳዲስ መጠይቆች ካጋጠሙን በፍጥነት እንቀይራቸዋለን። አንዳንድ ጊዜ ለብዙ መጠይቆች በአንድ ጊዜ ሊተገበሩ የሚችሉ አዲስ የማመቻቸት ዘዴዎችን እናገኛለን.

እንደ ትንበያዎቻችን, የአሁኑ አገልጋይ የተጠቃሚዎችን ቁጥር በሌላ 3-5 ጊዜ መጨመርን ይቋቋማል. እውነት ነው፣ በእጃችን ላይ አንድ ተጨማሪ ኤሲ አለን - አሁንም የ SELECT መጠይቆችን ወደ መስታወት አላስተላለፍንም፣ እንደሚመከር። ነገር ግን ይህንን በንቃተ-ህሊና አናደርግም, ምክንያቱም "ከባድ መሳሪያ" ከማብራትዎ በፊት በመጀመሪያ "ብልጥ" የማመቻቸት እድሎችን ሙሉ በሙሉ ማሟጠጥ እንፈልጋለን.
የተከናወነውን ስራ ወሳኝ እይታ በአቀባዊ ሚዛን መጠቀምን ሊያመለክት ይችላል. የልዩ ባለሙያዎችን ጊዜ ከማባከን ይልቅ የበለጠ ኃይለኛ አገልጋይ ይግዙ። አገልጋዩ ያን ያህል ወጪ ላያወጣ ይችላል፣በተለይ የቁመት ልኬቱን ወሰን እስካሁን ስላላሟጠጠን። ሆኖም የጥያቄዎች ብዛት 10 ጊዜ ጨምሯል። በበርካታ አመታት ውስጥ, የስርዓቱ ተግባራዊነት ጨምሯል እና አሁን ብዙ አይነት ጥያቄዎች አሉ. ለመሸጎጥ ምስጋና ይግባውና የነበረው ተግባራዊነት በጥቂት ጥያቄዎች እና ይበልጥ ቀልጣፋ በሆኑ ጥያቄዎች ነው የሚከናወነው። ይህ ማለት ትክክለኛውን የፍጥነት መጠን ለማግኘት በደህና በሌላ 5 ማባዛት ይችላሉ። ስለዚህ, በጣም ወግ አጥባቂ ግምቶች መሰረት, ማፋጠን 50 ጊዜ ወይም ከዚያ በላይ ነበር ማለት እንችላለን. አገልጋይን በአቀባዊ ማወዛወዝ 50 እጥፍ የበለጠ ያስከፍላል። በተለይም ማመቻቸት አንዴ ከተከናወነ ሁል ጊዜ እንደሚሰራ እና ለተከራየው አገልጋይ ክፍያ በየወሩ ይመጣል።

ምንጭ: hab.com