د جوړونکو لپاره د B2B خدمت مثال په کارولو سره د ډیټابیس پوښتنو اصلاح کول

څنګه کولی شو ډیټابیس ته د پوښتنو شمیر 10 ځله وده وکړو پرته لدې چې ډیر ګټور سرور ته لاړ شو او د سیسټم فعالیت وساتو؟ زه به تاسو ته ووایم چې موږ څنګه د خپل ډیټابیس په فعالیت کې کمښت سره معامله وکړه، موږ څنګه د SQL پوښتنو ته د امکان تر حده ډیرو کاروونکو ته خدمت کولو او د کمپیوټري سرچینو لګښت زیات نه کړو.

زه په ساختماني شرکتونو کې د سوداګرۍ پروسې اداره کولو لپاره خدمت کوم. شاوخوا 3 زره شرکتونه زموږ سره کار کوي. له 10 زرو څخه ډیر خلک هره ورځ زموږ سیسټم سره د 4-10 ساعتونو لپاره کار کوي. دا د پلان کولو، خبرتیا، خبرتیا، تایید کولو مختلف ستونزې حل کوي ... موږ د PostgreSQL 9.6 کاروو. موږ په ډیټابیس کې شاوخوا 300 میزونه لرو او هره ورځ تر 200 ملیون پوښتنې (10 زره بیلابیلې) ترلاسه کیږي. په اوسط ډول موږ په هره ثانیه کې 3-4 زره غوښتنې لرو، په خورا فعاله شیبو کې په هره ثانیه کې له 10 زرو څخه ډیر غوښتنې. ډیری پوښتنې OLAP دي. دلته ډیر لږ اضافې، تعدیلات او حذفونه شتون لري، پدې معنی چې د OLTP بار نسبتا لږ دی. ما دا ټولې شمیرې چمتو کړې ترڅو تاسو زموږ د پروژې پیمانه ارزونه وکړئ او پوه شئ چې زموږ تجربه ستاسو لپاره څومره ګټوره کیدی شي.

یو انځور. شعري

کله چې موږ پراختیا پیل کړه، موږ واقعیا فکر نه کاوه چې کوم ډول بار به په ډیټابیس کې راشي او موږ به څه وکړو که چیرې سرور ایستل بند کړي. کله چې د ډیټابیس ډیزاین کول، موږ عمومي سپارښتنې تعقیب کړې او هڅه یې وکړه چې ځان په پښو و نه غورځوو، مګر د عمومي مشورې څخه هاخوا لاړ شو لکه "د نمونې څخه کار مه اخلئ. د وجود خاصیت ارزښتونه موږ داخل نه شو. موږ د نورمال کولو اصولو پراساس ډیزاین کړی ، د ډیټا بې ځایه کیدو څخه مخنیوی کوي او د ځینې پوښتنو ګړندي کولو پروا نه کوي. هرڅومره ژر چې لومړي کارونکي راغلل ، موږ د فعالیت ستونزې سره مخ شو. د معمول په څیر، موږ د دې لپاره بشپړ چمتو نه وو. لومړنۍ ستونزې ساده وې. د یوې قاعدې په توګه، هرڅه د نوي شاخص په اضافه کولو سره حل شوي. مګر یو وخت راغی کله چې ساده پیچ کار کول بند کړل. په دې پوهیدل چې موږ تجربه نه لرو او دا زموږ لپاره ورځ تر بلې ستونزمن کیږي چې پوه شو چې څه شی د ستونزو لامل کیږي، موږ متخصصین استخدام کړل چې زموږ سره یې د سرور په سمه توګه تنظیم کولو کې مرسته وکړه، څارنه یې وصل کړه، او موږ ته یې وښودله چې چیرته ترلاسه کول. احصایې.

دوهم انځور. احصایوي

نو موږ شاوخوا 10 زره بیلابیل پوښتنې لرو چې هره ورځ زموږ په ډیټابیس کې اجرا کیږي. د دې 10 زرو څخه، داسې راکشسونه شتون لري چې د 2-3 ms اوسط اعدام وخت سره 0.1-0.3 ملیون ځله اعدام شوي، او د 30 ثانیو اوسط اعدام وخت سره پوښتنې شتون لري چې په ورځ کې 100 ځله ویل کیږي.

دا ممکنه نه وه چې ټولې 10 زره پوښتنې اصلاح کړو، نو موږ پریکړه وکړه چې دا معلومه کړو چې د ډیټابیس فعالیت په سمه توګه د ښه کولو لپاره خپلې هڅې چیرته لارښوونه کوو. د څو تکرارونو وروسته، موږ غوښتنې په ډولونو ویشل پیل کړل.

TOP غوښتنې

دا خورا درنې پوښتنې دي چې ډیری وخت نیسي (ټول وخت). دا هغه پوښتنې دي چې یا ډیر ځله ویل کیږي یا هغه پوښتنې چې د پلي کولو لپاره خورا اوږد وخت نیسي (اوږدې او پرله پسې پوښتنې د سرعت لپاره د مبارزې په لومړي تکرار کې مطلوبې شوې وې). د پایلې په توګه، سرور د دوی په اجرا کولو کې ډیری وخت لګوي. برسېره پردې، دا مهمه ده چې لوړ غوښتنې د بشپړ اجرا کولو وخت او د IO وخت په جلا توګه جلا کړئ. د دې ډول پوښتنو د اصلاح کولو میتودونه یو څه توپیر لري.

د ټولو شرکتونو معمول تمرین د TOP غوښتنو سره کار کول دي. د دوی څخه لږ دي؛ حتی د یوې پوښتنې اصلاح کول کولی شي 5-10٪ سرچینې خلاصې کړي. په هرصورت، لکه څنګه چې پروژه وده کوي، د TOP پوښتنو اصلاح کول په زیاتیدونکي توګه غیر معمولي کار کیږي. ټولې ساده میتودونه لا دمخه کار شوي، او خورا "درنه" غوښتنه د سرچینو "یوازې" 3-5٪ اخلي. که په مجموع کې غوره پوښتنې له 30-40٪ څخه لږ وخت ونیسي، نو ډیر احتمال لري چې تاسو لا دمخه د دوی د چټک کار کولو لپاره هڅې کړې وي او دا وخت دی چې د راتلونکي ګروپ څخه د پوښتنو اصلاح کولو ته لاړ شئ.
دا د دې پوښتنې ځواب ته پاتې دي چې په دې ګروپ کې څومره لوړ پوښتنې باید شاملې شي. زه معمولا لږترلږه 10 اخلم، مګر له 20 څخه ډیر نه. زه هڅه کوم چې ډاډ ترلاسه کړم چې په TOP ګروپ کې د لومړي او وروستي وخت له 10 څخه زیات توپیر نلري. دا دی، که چیرې د پوښتنې اجرا کولو وخت په چټکۍ سره له لومړي ځای څخه 1 ته راټیټ شي، نو زه TOP-10 اخلم، که چیرې کمښت ډیر تدریجي وي، نو زه د ګروپ اندازه 10 یا 15 ته لوړه کوم.

منځنی کروندګر

دا ټولې غوښتنې دي چې سمدلاسه د TOP وروسته راځي، د وروستي 5-10٪ استثنا سره. عموما، د دې پوښتنو په ښه کولو کې د سرور فعالیت خورا زیاتولو فرصت شتون لري. دا غوښتنې تر 80٪ پورې وزن لري. مګر حتی که د دوی ونډه له 50٪ څخه زیاته وي، نو دا وخت دی چې دوی په ډیر احتیاط سره وګورئ.

دم

لکه څنګه چې یادونه وشوه، دا پوښتنې په پای کې راځي او د 5-10٪ وخت نیسي. تاسو کولی شئ یوازې د دوی په اړه هیر کړئ که تاسو د اتوماتیک پوښتنې تحلیل وسیلې ونه کاروئ ، نو د دوی اصلاح کول هم ارزانه کیدی شي.

هر ګروپ څنګه ارزوئ؟

زه د SQL پوښتنه کاروم چې د PostgreSQL لپاره دا ډول ارزونه کې مرسته کوي (زه ډاډه یم چې ورته پوښتنه د ډیری نورو DBMSs لپاره لیکل کیدی شي)

د 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

دلته د خورا عام کارول شوي تخنیکونو لیست دی چې موږ سره یې د غوره پوښتنو ګړندي کولو کې مرسته کړې:

• د سیسټم بیا ډیزاین، د بیلګې په توګه، ډیټابیس ته د دوراني پوښتنو پرځای د پیغام بروکر په کارولو سره د خبرتیا منطق بیا کار کول
• د شاخصونو اضافه کول یا بدلول
• خالص SQL ته د ORM پوښتنو بیا لیکل
• د سست ډیټا بارولو منطق بیا لیکل
• د ډیټا غیر عادي کولو له لارې کیچ کول. د مثال په توګه، موږ د میز پیوستون تحویلي -> رسید -> غوښتنه -> غوښتنلیک لرو. دا دی، هر تحویل د نورو میزونو له لارې د غوښتنلیک سره تړاو لري. د دې لپاره چې په هره غوښتنه کې ټول جدولونه ونه تړل شي، موږ د تحویلي جدول کې غوښتنې ته لینک نقل کړی.
• د حوالې کتابونو سره جامد میزونو کیچ کول او په ندرت سره د برنامه حافظه کې میزونه بدلول.

ځینې ​​​​وختونه بدلونونه یو اغیزمن بیا ډیزاین ته رسیدلی، مګر دوی د سیسټم بار 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
  

  په یوه نسبتا کوچني میز کې پوښتنه وکړه 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)
  

  داسې ښکاري چې موږ یو اړونده فرعي پوښتنه کارولې، مګر دا د 10 ځله څخه ډیر سرعت ورکوي.

• په ډیرو مواردو کې، COUNT په بشپړه توګه پریښودل شوي او
  د اټکل شوي ارزښت د محاسبې لخوا بدل شوی
• د ځای پرځای

  UPPER(s) LIKE JOHN%’ 
  

  کارول

  s ILIKE “John%”
  

هره ځانګړې غوښتنه کله ناکله د 3-1000 ځله چټکه شوې. د اغیزمن فعالیت سره سره، په لومړي سر کې موږ ته داسې بریښي چې د یوې پوښتنې په ښه کولو کې هیڅ معنی نشته چې بشپړولو لپاره 10 ms وخت نیسي، د دریم سوو درنو پوښتنو څخه یو دی، او د ټول ډیټابیس بار بار سل سلنې وخت نیسي. مګر د ورته ډول پوښتنو یوې ډلې ته د ورته ترکیب په پلي کولو سره ، موږ یو څو سلنه بیرته وګټو. د دې لپاره چې په لاسي ډول د ټولو سلګونو پوښتنو بیاکتنې وخت ضایع نه کړو، موږ ډیری ساده سکریپټونه لیکلي چې د ورته ډول پوښتنو موندلو لپاره منظم بیانونه کاروي. د پایلې په توګه، په اتوماتيک ډول د پوښتنو ګروپونو لټون موږ ته اجازه راکړه چې د لږې هڅې سره خپل فعالیت نور هم ښه کړو.

د پایلې په توګه، موږ اوس د دریو کلونو لپاره په ورته هارډویر کار کوو. اوسط ورځنی بار شاوخوا 30٪ دی، په لوړو څوکو کې دا 70٪ ته رسیږي. د غوښتنو شمیر، او همدارنګه د کاروونکو شمیر، نږدې 10 ځله زیات شوی. او دا ټول د TOP-MEDIUM غوښتنو د ورته ګروپونو دوامداره څارنې څخه مننه. هرڅومره ژر چې نوې غوښتنه په TOP ګروپ کې راښکاره شي ، موږ سمدلاسه دا تحلیل کوو او هڅه کوو چې ګړندي کړو. موږ په اونۍ کې یو ځل د پوښتنو تحلیل سکریپټونو په کارولو سره د میډیم ګروپ بیاکتنه کوو. که موږ د نویو پوښتنو سره مخ شو چې موږ دمخه پوهیږو چې څنګه اصلاح کړو، موږ یې ژر تر ژره بدلوو. ځینې ​​​​وختونه موږ د اصلاح کولو نوي میتودونه ګورو چې په یوځل کې په څو پوښتنو کې پلي کیدی شي.

زموږ د وړاندوینو له مخې ، اوسنی سرور به د نورو 3-5 ځله د کاروونکو شمیر کې زیاتوالی سره مقاومت وکړي. ریښتیا، موږ خپل آستین ته یو بل اکس لرو - موږ لا تر اوسه عکس ته د SELECT پوښتنې نه دي لیږدولي، لکه څنګه چې سپارښتنه کیږي. مګر موږ دا په شعوري توګه نه کوو، ځکه چې موږ غواړو لومړی د "درنې توپخانې" د فعالولو دمخه د "سمارټ" اصلاح کولو امکانات په بشپړه توګه له منځه یوسو.
ترسره شوي کار ته یو انتقادي کتنه ممکن د عمودی پیمانه کارولو وړاندیز وکړي. د متخصصینو وخت ضایع کولو پرځای یو پیاوړی سرور واخلئ. سرور ممکن دومره لګښت ونه کړي، په ځانګړې توګه له هغه وخته چې موږ د عمودی اندازه کولو محدودیتونه نه دي ختم کړي. په هرصورت، یوازې د غوښتنو شمیر 10 ځله زیات شوی. د څو کلونو په جریان کې، د سیسټم فعالیت زیات شوی او اوس د غوښتنو ډولونه ډیر دي. د کیچ کولو څخه مننه، هغه فعالیت چې شتون لري په لږو غوښتنو، او ډیرو اغیزمنو غوښتنو کې ترسره کیږي. دا پدې مانا ده چې تاسو کولی شئ په خوندي ډول د بل 5 لخوا ضرب کړئ ترڅو د اصلي سرعت ضمیمه ترلاسه کړئ. نو، د خورا محافظه کار اټکلونو له مخې، موږ کولی شو ووایو چې سرعت 50 ځله یا ډیر و. په عمودی توګه د سرور بدلول به 50 ځله ډیر لګښت ولري. په ځانګړي توګه په پام کې نیولو سره چې یوځل اصلاح ترسره کیږي دا ټول وخت کار کوي ، او د کرایه شوي سرور لپاره بیل هره میاشت راځي.

سرچینه: www.habr.com