بلڈرز کے لیے B2B سروس کی مثال کا استعمال کرتے ہوئے ڈیٹا بیس کے سوالات کو بہتر بنانا

ڈیٹا بیس میں سوالات کی تعداد میں 10 گنا اضافہ کیسے کیا جائے بغیر زیادہ پیداواری سرور کی طرف جائے اور سسٹم کی فعالیت کو برقرار رکھا جائے؟ میں آپ کو بتاؤں گا کہ ہم نے اپنے ڈیٹا بیس کی کارکردگی میں کمی سے کیسے نمٹا، کس طرح ہم نے ایس کیو ایل کے استفسارات کو زیادہ سے زیادہ صارفین کی خدمت کے لیے بہتر بنایا اور کمپیوٹنگ وسائل کی لاگت میں اضافہ نہ کیا۔

میں تعمیراتی کمپنیوں میں کاروباری عمل کے انتظام کے لیے ایک خدمت بناتا ہوں۔ ہمارے ساتھ تقریباً 3 ہزار کمپنیاں کام کرتی ہیں۔ 10 ہزار سے زیادہ لوگ ہمارے سسٹم کے ساتھ روزانہ 4-10 گھنٹے کام کرتے ہیں۔ یہ منصوبہ بندی، اطلاع، وارننگ، تصدیق کے مختلف مسائل حل کرتا ہے... ہم PostgreSQL 9.6 استعمال کرتے ہیں۔ ہمارے پاس ڈیٹا بیس میں تقریباً 300 میزیں ہیں اور روزانہ 200 ملین سوالات (10 ہزار مختلف سوالات) موصول ہوتے ہیں۔ اوسطاً ہمارے پاس فی سیکنڈ 3-4 ہزار درخواستیں ہیں، انتہائی فعال لمحات میں فی سیکنڈ 10 ہزار سے زیادہ درخواستیں ہیں۔ زیادہ تر سوالات OLAP ہیں۔ اس میں بہت کم اضافے، ترامیم اور حذف ہیں، یعنی OLTP کا بوجھ نسبتاً ہلکا ہے۔ میں نے یہ تمام نمبرز فراہم کیے ہیں تاکہ آپ ہمارے پروجیکٹ کے پیمانے کا اندازہ لگا سکیں اور سمجھ سکیں کہ ہمارا تجربہ آپ کے لیے کتنا مفید ہو سکتا ہے۔

تصویر ایک۔ گیت

جب ہم نے ترقی شروع کی، تو ہم نے واقعی یہ نہیں سوچا کہ ڈیٹا بیس پر کس قسم کا بوجھ پڑے گا اور اگر سرور کھینچنا بند کر دے تو ہم کیا کریں گے۔ ڈیٹا بیس کو ڈیزائن کرتے وقت، ہم نے عمومی سفارشات پر عمل کیا اور کوشش کی کہ اپنے آپ کو پاؤں میں گولی نہ ماریں، لیکن عام مشورے سے آگے بڑھ گئے جیسے کہ "پیٹرن استعمال نہ کریں۔ ہستی کی خصوصیت کی قدریں۔ ہم اندر نہیں گئے ہم نے ڈیٹا کو فالتو ہونے سے گریز کرتے ہوئے نارملائزیشن کے اصولوں کی بنیاد پر ڈیزائن کیا اور کچھ سوالات کو تیز کرنے کی پرواہ نہیں کی۔ جیسے ہی پہلے صارفین پہنچے، ہمیں کارکردگی کے مسئلے کا سامنا کرنا پڑا۔ ہمیشہ کی طرح ہم اس کے لیے بالکل تیار نہیں تھے۔ پہلے مسائل آسان نکلے۔ ایک اصول کے طور پر، سب کچھ ایک نیا انڈیکس شامل کرکے حل کیا گیا تھا. لیکن ایک وقت ایسا آیا جب سادہ پیچ نے کام کرنا چھوڑ دیا۔ یہ سمجھتے ہوئے کہ ہمارے پاس تجربہ کی کمی ہے اور ہمارے لیے یہ سمجھنا مشکل ہوتا جا رہا ہے کہ کیا مسائل پیدا ہو رہے ہیں، ہم نے ایسے ماہرین کی خدمات حاصل کی جنہوں نے سرور کو درست طریقے سے ترتیب دینے، مانیٹرنگ کو مربوط کرنے میں ہماری مدد کی، اور ہمیں بتایا کہ کہاں جانا ہے۔ اعداد و شمار.

تصویر دو۔ شماریاتی

لہذا ہمارے پاس تقریباً 10 ہزار مختلف سوالات ہیں جو ہمارے ڈیٹا بیس پر روزانہ کیے جاتے ہیں۔ ان 10 ہزار میں سے، ایسے راکشس ہیں جن کو 2-3 ایم ایس کے اوسط عملدرآمد کے وقت کے ساتھ 0.1-0.3 ملین بار پھانسی دی جاتی ہے، اور 30 ​​سیکنڈ کے اوسط پر عمل درآمد کے وقت کے سوالات ہیں جنہیں دن میں 100 بار کہا جاتا ہے۔

تمام 10 ہزار سوالات کو بہتر بنانا ممکن نہیں تھا، اس لیے ہم نے فیصلہ کیا کہ ڈیٹا بیس کی کارکردگی کو درست طریقے سے بہتر بنانے کے لیے اپنی کوششوں کو کہاں سے آگے بڑھانا ہے۔ کئی تکرار کے بعد، ہم نے درخواستوں کو اقسام میں تقسیم کرنا شروع کیا۔

ٹاپ درخواستیں۔

یہ سب سے بھاری سوالات ہیں جن میں سب سے زیادہ وقت لگتا ہے (کل وقت)۔ یہ وہ سوالات ہیں جنہیں یا تو بہت کثرت سے کہا جاتا ہے یا وہ سوالات جن پر عمل درآمد میں بہت لمبا وقت لگتا ہے (طویل اور متواتر سوالات کو رفتار کی لڑائی کے پہلے تکرار میں بہتر بنایا گیا تھا)۔ نتیجے کے طور پر، سرور ان کے عمل میں سب سے زیادہ وقت صرف کرتا ہے۔ مزید برآں، یہ ضروری ہے کہ سرفہرست درخواستوں کو عمل درآمد کے کل وقت اور IO وقت کے حساب سے الگ کیا جائے۔ اس طرح کے سوالات کو بہتر بنانے کے طریقے قدرے مختلف ہیں۔

تمام کمپنیوں کا معمول یہ ہے کہ TOP درخواستوں کے ساتھ کام کریں۔ ان میں سے چند ہیں؛ ایک سوال کو بھی بہتر بنانے سے 5-10% وسائل خالی ہو سکتے ہیں۔ تاہم، جیسے جیسے پروجیکٹ پختہ ہوتا جاتا ہے، TOP سوالات کو بہتر بنانا ایک غیر معمولی کام بن جاتا ہے۔ تمام آسان طریقے پہلے ہی تیار ہو چکے ہیں، اور سب سے زیادہ "بھاری" درخواست میں "صرف" 3-5% وسائل لگتے ہیں۔ اگر مجموعی طور پر TOP سوالات میں 30-40% سے بھی کم وقت لگتا ہے، تو غالباً آپ نے پہلے ہی ان کو تیزی سے کام کرنے کے لیے کوششیں کی ہوں گی اور اب وقت آگیا ہے کہ اگلے گروپ سے سوالات کو بہتر بنانے کی طرف بڑھیں۔
اس سوال کا جواب دینا باقی ہے کہ اس گروپ میں کتنے سرفہرست سوالات شامل کیے جائیں۔ میں عام طور پر کم از کم 10 لیتا ہوں، لیکن 20 سے زیادہ نہیں۔ میں اس بات کو یقینی بنانے کی کوشش کرتا ہوں کہ ٹاپ گروپ میں پہلے اور آخری کا وقت 10 سے زیادہ کا فرق نہ ہو۔ یعنی، اگر استفسار پر عمل درآمد کا وقت تیزی سے پہلی جگہ سے 1ویں تک گر جاتا ہے، تو میں TOP-10 لیتا ہوں، اگر ڈراپ زیادہ بتدریج ہے، تو میں گروپ کا سائز بڑھا کر 10 یا 15 کر دیتا ہوں۔

درمیانی کسان

یہ تمام درخواستیں ہیں جو TOP کے فوراً بعد آتی ہیں، سوائے آخری 5-10% کے۔ عام طور پر، ان سوالات کو بہتر بنانے میں سرور کی کارکردگی کو بہت زیادہ بڑھانے کا موقع ملتا ہے۔ ان درخواستوں کا وزن 80% تک ہو سکتا ہے۔ لیکن یہاں تک کہ اگر ان کا حصہ 50% سے تجاوز کر گیا ہے، تو اب وقت آگیا ہے کہ انہیں زیادہ غور سے دیکھا جائے۔

دم

جیسا کہ ذکر کیا گیا ہے، یہ سوالات آخر میں آتے ہیں اور 5-10% وقت لیتے ہیں۔ آپ ان کے بارے میں صرف اس صورت میں بھول سکتے ہیں جب آپ خودکار استفسار کے تجزیہ کے اوزار استعمال نہیں کرتے ہیں، پھر ان کو بہتر بنانا سستا بھی ہو سکتا ہے۔

ہر گروپ کا اندازہ کیسے لگایا جائے؟

میں ایک ایس کیو ایل استفسار استعمال کرتا ہوں جو 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 ہے) جو ایک گروپ کی درخواستوں کو دوسرے گروپ سے الگ کرتے ہیں۔

اس طرح درخواستوں کے حصص کا موازنہ اس وقت کیا جاتا ہے جب اصلاح کا کام شروع ہوا اور اب۔

خاکہ ظاہر کرتا ہے کہ ٹاپ درخواستوں کا حصہ تیزی سے کم ہوا ہے، لیکن "درمیانی کسانوں" میں اضافہ ہوا ہے۔
سب سے پہلے، سب سے اوپر کی درخواستوں میں صریح غلطیاں شامل تھیں۔ وقت گزرنے کے ساتھ، بچپن کی بیماریاں غائب ہو گئیں، ٹاپ درخواستوں کا حصہ کم ہو گیا، اور مشکل درخواستوں کو تیز کرنے کے لیے زیادہ سے زیادہ کوششیں کرنا پڑیں۔

درخواستوں کا متن حاصل کرنے کے لیے ہم درج ذیل درخواست کا استعمال کرتے ہیں۔

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 گنا تیز کرکے آسانی سے اس سے چھٹکارا پا سکتے ہیں)۔

  مثال کے طور پر، ڈیلیوری کی ایک بڑی میز سے تمام ڈرائیوروں کو منتخب کرنے کے لیے سوال کے بجائے (DELIVERY)

  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 ایم ایس کا وقت لگتا ہے، جو تیسرے سو بھاری سوالات میں سے ایک ہے، اور ڈیٹا بیس کے مجموعی لوڈ ٹائم کا سو فیصد حصہ لیتی ہے۔ لیکن ایک ہی قسم کے سوالات کے ایک گروپ پر ایک ہی نسخہ کو لاگو کرنے سے، ہم نے کچھ فیصد جیت لیا۔ تمام سیکڑوں سوالات کا دستی طور پر جائزہ لینے میں وقت ضائع نہ کرنے کے لیے، ہم نے کئی آسان اسکرپٹ لکھے جن میں ایک ہی قسم کے سوالات کو تلاش کرنے کے لیے باقاعدہ تاثرات استعمال کیے گئے۔ نتیجے کے طور پر، سوالات کے خودکار گروپوں کی تلاش نے ہمیں معمولی کوشش کے ساتھ اپنی کارکردگی کو مزید بہتر بنانے کی اجازت دی۔

نتیجے کے طور پر، ہم تین سالوں سے اسی ہارڈ ویئر پر کام کر رہے ہیں۔ اوسط یومیہ بوجھ تقریباً 30% ہے، چوٹیوں میں یہ 70% تک پہنچ جاتا ہے۔ درخواستوں کی تعداد کے ساتھ ساتھ صارفین کی تعداد میں تقریباً 10 گنا اضافہ ہوا ہے۔ اور یہ سب TOP-MEDIUM درخواستوں کے انہی گروپوں کی مسلسل نگرانی کی بدولت ہے۔ جیسے ہی TOP گروپ میں کوئی نئی درخواست ظاہر ہوتی ہے، ہم فوری طور پر اس کا تجزیہ کرتے ہیں اور اسے تیز کرنے کی کوشش کرتے ہیں۔ ہم استفسار کے تجزیہ کے اسکرپٹس کا استعمال کرتے ہوئے ہفتے میں ایک بار MEDIUM گروپ کا جائزہ لیتے ہیں۔ اگر ہمیں ایسے نئے سوالات آتے ہیں جن کو بہتر بنانا ہم پہلے ہی جانتے ہیں، تو ہم انہیں فوری طور پر تبدیل کر دیتے ہیں۔ بعض اوقات ہمیں اصلاح کے نئے طریقے ملتے ہیں جن کا اطلاق ایک ساتھ کئی سوالات پر کیا جا سکتا ہے۔

ہماری پیشین گوئیوں کے مطابق، موجودہ سرور صارفین کی تعداد میں مزید 3-5 گنا اضافہ برداشت کرے گا۔ سچ ہے، ہمارے پاس اپنی آستین پر ایک اور اککا ہے - ہم نے ابھی تک SELECT سوالات کو آئینے میں منتقل نہیں کیا ہے، جیسا کہ تجویز کیا جاتا ہے۔ لیکن ہم جان بوجھ کر ایسا نہیں کرتے ہیں، کیونکہ ہم سب سے پہلے "ہیوی آرٹلری" کو آن کرنے سے پہلے "سمارٹ" آپٹیمائزیشن کے امکانات کو مکمل طور پر ختم کرنا چاہتے ہیں۔
کئے گئے کام پر ایک تنقیدی نظر عمودی اسکیلنگ کا استعمال کرنے کا مشورہ دے سکتی ہے۔ ماہرین کا وقت ضائع کرنے کے بجائے زیادہ طاقتور سرور خریدیں۔ سرور کی اتنی قیمت نہیں ہوسکتی ہے، خاص طور پر چونکہ ہم نے ابھی تک عمودی اسکیلنگ کی حدیں ختم نہیں کی ہیں۔ تاہم، صرف درخواستوں کی تعداد میں 10 گنا اضافہ ہوا۔ کئی سالوں کے دوران، سسٹم کی فعالیت میں اضافہ ہوا ہے اور اب مزید قسم کی درخواستیں ہیں۔ کیشنگ کی بدولت، جو فعالیت موجود ہے وہ کم درخواستوں اور زیادہ موثر درخواستوں میں انجام پاتی ہے۔ اس کا مطلب ہے کہ آپ حقیقی ایکسلریشن گتانک حاصل کرنے کے لیے محفوظ طریقے سے مزید 5 سے ضرب کر سکتے ہیں۔ لہذا، انتہائی قدامت پسند اندازوں کے مطابق، ہم کہہ سکتے ہیں کہ سرعت 50 گنا یا اس سے زیادہ تھی۔ سرور کو عمودی طور پر جھولنے پر 50 گنا زیادہ لاگت آئے گی۔ خاص طور پر اس بات پر غور کرتے ہوئے کہ ایک بار جب اصلاح ہو جائے تو یہ ہر وقت کام کرتا ہے، اور کرائے کے سرور کا بل ہر ماہ آتا ہے۔

ماخذ: www.habr.com