تعمير ڪندڙن لاءِ 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 ملين ڀيرا اعدام ڪيو ويو آهي، ۽ 30 سيڪنڊن جي اوسط عملدرآمد وقت سان سوال آهن جن کي 100 ڀيرا سڏيو ويندو آهي.

سڀني 10 هزار سوالن کي بهتر ڪرڻ ممڪن نه هو، تنهن ڪري اسان اهو معلوم ڪرڻ جو فيصلو ڪيو ته ڊيٽابيس جي ڪارڪردگي کي صحيح طريقي سان بهتر ڪرڻ لاءِ اسان جي ڪوششن کي ڪٿي سڌو ڪرڻ گهرجي. ڪيترن ئي ورهاڱي کان پوء، اسان درخواستن کي قسمن ۾ ورهائڻ شروع ڪيو.

TOP درخواستون

اهي تمام وڏا سوال آهن جيڪي تمام گهڻو وقت وٺن ٿا (ڪل وقت). اهي سوال آهن جن کي يا ته اڪثر سڏيو ويندو آهي يا اهي سوال جيڪي عمل ۾ تمام گهڻو وقت وٺندا آهن (ڊگهي ۽ بار بار سوالن کي رفتار جي جنگ جي پهرين ورهاڱي ۾ بهتر ڪيو ويو). نتيجي طور، سرور ان جي عمل تي تمام گهڻو وقت گذاريندو آهي. ان کان علاوه، اهو ضروري آهي ته مٿين درخواستن کي مڪمل عمل جي وقت طرفان ۽ الڳ الڳ IO وقت طرفان. اهڙن سوالن کي بهتر ڪرڻ جا طريقا ٿورا مختلف آهن.

سڀني ڪمپنين جو عام رواج TOP درخواستن سان ڪم ڪرڻ آهي. انهن مان ٿورا آهن؛ هڪ سوال کي به بهتر ڪرڻ سان 5-10٪ وسيلن کي آزاد ڪري سگهجي ٿو. بهرحال، جيئن پروجيڪٽ جي پختگي ٿيندي آهي، تيئن تيئن سوالن کي بهتر ڪرڻ هڪ غير معمولي ڪم بڻجي ويندو آهي. سڀ سادو طريقا اڳ ۾ ئي ڪم ڪيا ويا آهن، ۽ سڀ کان وڌيڪ "ڳري" درخواست "صرف" وسيلن جو 3-5٪ وٺندو آهي. جيڪڏهن TOP سوالن ۾ مجموعي طور تي 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

هتي عام طور تي استعمال ٿيل ٽيڪنالاجي جي هڪ فهرست آهي جيڪا اسان جي مدد ڪئي مٿين سوالن کي تيز ڪرڻ ۾:

• سسٽم کي ٻيهر ڊزائين ڪرڻ، مثال طور، ڊيٽابيس ڏانهن وقتي سوالن جي بدران پيغام بروکر استعمال ڪندي نوٽيفڪيشن منطق کي ٻيهر ڪم ڪرڻ
• انڊيڪس شامل ڪرڻ يا تبديل ڪرڻ
• ORM سوالن کي خالص SQL ڏانهن ٻيهر لکڻ
• سست ڊيٽا لوڊ ڪرڻ واري منطق کي ٻيهر لکڻ
• ڊيٽا denormalization ذريعي ڪيشنگ. مثال طور، اسان وٽ ٽيبل ڪنيڪشن آهي پهچائڻ -> انوائس -> درخواست -> ايپليڪيشن. اهو آهي، هر ترسيل ٻين جدولن ذريعي ايپليڪيشن سان لاڳاپيل آهي. هر درخواست ۾ سڀني جدولن کي ڳنڍڻ لاءِ نه، اسان ڊليوري ٽيبل ۾ درخواست جي لنڪ کي نقل ڪيو.
• ريفرنس جي ڪتابن سان جامد جدولن کي ڪيش ڪرڻ ۽ پروگرام ميموري ۾ گهٽ ۾ گهٽ ٽيبل تبديل ڪرڻ.

ڪڏهن ڪڏهن تبديليون هڪ شاندار ريزائن جي مقدار ۾ هونديون آهن، پر انهن کي سسٽم لوڊ جو 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 ms وٺندو آهي مڪمل ڪرڻ لاء، 3rd سئو وڏن سوالن مان هڪ آهي، ۽ مجموعي ڊيٽابيس لوڊ وقت جو سئو سيڪڙو حصو وٺندو آهي. پر ساڳئي قسم جي سوالن جي هڪ گروهه تي ساڳيو نسخو لاڳو ڪرڻ سان، اسان ڪجهه سيڪڙو واپس کٽيو. وقت ضايع نه ڪرڻ لاءِ دستي طور تي سڀني سوين سوالن جو جائزو وٺڻ لاءِ، اسان ڪيترائي سادي اسڪرپٽ لکيا آهن جيڪي ساڳي قسم جا سوال ڳولڻ لاءِ باقاعده اظهار استعمال ڪندا هئا. نتيجي طور، خود بخود سوالن جا گروپ ڳولڻ اسان کي اجازت ڏني ته اسان جي ڪارڪردگي کي معمولي ڪوشش سان وڌيڪ بهتر بڻائي.

نتيجي طور، اسان ساڳئي هارڊويئر تي ٽن سالن تائين ڪم ڪري رهيا آهيون. سراسري روزاني لوڊ تقريبا 30٪ آهي، چوٽي ۾ اهو 70٪ تائين پهچي ٿو. درخواستن جو تعداد، گڏوگڏ استعمال ڪندڙن جو تعداد، تقريبن 10 ڀيرا وڌي چڪو آھي. ۽ اهو سڀ ڪجهه TOP-MEDIUM درخواستن جي انهن ساڳين گروپن جي مسلسل نگراني جي مهرباني. جيئن ئي TOP گروپ ۾ نئين درخواست ظاهر ٿئي ٿي، اسان فوري طور تي ان جو تجزيو ڪيو ۽ ان کي تيز ڪرڻ جي ڪوشش ڪندا. اسان هفتي ۾ هڪ ڀيرو MEDIUM گروپ جو جائزو وٺون ٿا سوال تجزياتي اسڪرپٽس استعمال ڪندي. جيڪڏهن اسان نوان سوالن ۾ ايندا آهيون ته اسان اڳ ۾ ئي ڄاڻون ٿا ته ڪيئن بهتر ڪرڻ، اسان انهن کي جلدي تبديل ڪندا آهيون. ڪڏهن ڪڏهن اسان نئين اصلاح جا طريقا ڳوليندا آهيون جيڪي هڪ ئي وقت ڪيترن ئي سوالن تي لاڳو ٿي سگهن ٿيون.

اسان جي اڳڪٿين جي مطابق، موجوده سرور استعمال ڪندڙن جي تعداد ۾ اضافو 3-5 ڀيرا وڌيڪ برداشت ڪندو. سچ، اسان وٽ ھڪڙو وڌيڪ اڪو آھي اسان جي آستين - اسان اڃا تائين SELECT سوالن کي آئيني ڏانھن منتقل نه ڪيو آھي، جيئن سفارش ڪئي وئي آھي. پر اسان اهو شعوري طور تي نه ٿا ڪريون، ڇو ته اسان چاهيون ٿا ته ”هيوي آرٽلري“ کي چالو ڪرڻ کان پهريان ”سمارٽ“ اصلاح جي امڪانن کي مڪمل طور تي ختم ڪري ڇڏيو.
ڪيل ڪم تي هڪ نازڪ نظر شايد عمودي اسڪيلنگ استعمال ڪرڻ جي صلاح ڏين. ماهرن جو وقت ضايع ڪرڻ بدران وڌيڪ طاقتور سرور خريد ڪريو. سرور شايد ايترو خرچ نٿو ڪري، خاص طور تي جڏهن اسان اڃا تائين عمودي اسڪيلنگ جي حدن کي ختم نه ڪيو آهي. بهرحال، صرف درخواستن جو تعداد 10 ڀيرا وڌايو ويو. ڪيترن سالن کان، سسٽم جي ڪارڪردگي وڌي وئي آهي ۽ هاڻي درخواستن جا وڌيڪ قسم آهن. ڪيشنگ جي مهرباني، ڪارڪردگي جيڪا موجود هئي گهٽ درخواستن ۾، ۽ وڌيڪ موثر درخواستن ۾ ڪئي وئي آهي. ان جو مطلب آهي ته توهان محفوظ طور تي ٻئي 5 سان ضرب ڪري سگهو ٿا حقيقي تيز رفتار جي کوٽائي حاصل ڪرڻ لاءِ. تنهن ڪري، سڀ کان وڌيڪ قدامت پسند اندازن جي مطابق، اسان اهو چئي سگهون ٿا ته تيز رفتار 50 ڀيرا يا وڌيڪ هئي. عمودي طور تي سرور کي جھلڻ جي قيمت 50 ڀيرا وڌيڪ هوندي. خاص طور تي غور ڪندي ته هڪ ڀيرو اصلاح ڪئي وئي آهي اهو هر وقت ڪم ڪري ٿو، ۽ رينجر سرور جو بل هر مهيني اچي ٿو.

جو ذريعو: www.habr.com