جاوا ڊولپر جي اکين ذريعي PostgreSQL ۾ انڊيڪس جي صحت

هيلو

منهنجو نالو وانيا آهي ۽ مان هڪ جاوا ڊولپر آهيان. ائين ٿئي ٿو ته مان PostgreSQL سان تمام گهڻو ڪم ڪري رهيو آهيان - ڊيٽابيس کي ترتيب ڏيڻ، جوڙجڪ کي بهتر ڪرڻ، ڪارڪردگي، ۽ هفتي جي آخر ۾ ٿورو DBA کيڏڻ.

تازو مون اسان جي مائڪرو سروسز ۾ ڪيترن ئي ڊيٽابيس کي ترتيب ڏنو آهي ۽ هڪ جاوا لائبريري لکيو آهي pg-index-health، جيڪو هن ڪم کي آسان بڻائي ٿو، منهنجو وقت بچائي ٿو ۽ مون کي ڊولپرز پاران ڪيل ڪجهه عام غلطين کان بچڻ ۾ مدد ڪري ٿو. اها اها لائبريري آهي جنهن بابت اسين اڄ ڳالهائينداسين.

اعلان

PostgreSQL جو مکيه نسخو آئون ڪم ڪريان ٿو 10. سڀئي SQL سوال جيڪي آئون استعمال ڪريان ٿو نسخو 11 تي پڻ آزمايا ويا آهن. گھٽ ۾ گھٽ سپورٽ ورزن 9.6 آھي.

prehistory

اهو سڀ ڪجهه تقريبا هڪ سال اڳ هڪ اهڙي صورتحال سان شروع ڪيو جيڪو مون لاء عجيب هو: نيري مان هڪ انڊيڪس جي مقابلي واري تخليق هڪ غلطي سان ختم ٿي وئي. انڊيڪس پاڻ، هميشه وانگر، هڪ غلط حالت ۾ ڊيٽابيس ۾ رهي. لاگ تجزيو هڪ گهٽتائي ڏيکاري temp_file_limit. ۽ اسان وڃون ٿا... اونهي کوٽائي ڪندي، مون ڊيٽابيس جي ترتيب ۾ مسئلن جو هڪ سڄو گروپ دريافت ڪيو ۽، منهنجي آستين کي ڇڪيندي، منهنجي اکين ۾ چمڪ سان انهن کي حل ڪرڻ شروع ڪيو.

مسئلو هڪ - ڊفالٽ ترتيب

شايد هرڪو پوسٽ گريس بابت استعارا کان تمام گهڻو ٿڪجي چڪو آهي، جيڪو ڪافي ٺاهيندڙ تي هلائي سگهجي ٿو، پر ... ڊفالٽ ترتيب ڏيڻ واقعي ڪيترن ئي سوالن کي جنم ڏئي ٿو. گهٽ ۾ گهٽ، اهو ڌيان ڏيڻ جي قابل آهي سار سنڀال_ڪم_ميم, temp_file_limit, بيان_وقت ختم и lock_timeout.

اسان جي صورت ۾ سار سنڀال_ڪم_ميم ڊفالٽ 64 MB هو، ۽ temp_file_limit ڪجھ 2 GB جي آس پاس - اسان وٽ صرف ايتري ميموري نه ھئي ته ھڪڙي وڏي ٽيبل تي انڊيڪس ٺاھي سگھون.

تنهن ڪري، ۾ pg-index-health مون هڪ سلسلو گڏ ڪيو چاٻي, منهنجي خيال ۾، پيٽرول جيڪي هر ڊيٽابيس لاء ترتيب ڏيڻ گهرجن.

مسئلو ٻه - نقل انڊيڪس

اسان جا ڊيٽابيس SSD ڊرائيو تي رهن ٿا، ۽ اسان استعمال ڪندا آهيون HAڪيترن ئي ڊيٽا سينٽرن سان ترتيب ڏيڻ، ماسٽر ميزبان ۽ n- replicas جو تعداد. ڊسڪ اسپيس اسان لاء هڪ تمام قيمتي وسيلو آهي؛ اهو ڪارڪردگي ۽ سي پي يو جي استعمال کان گهٽ اهم ناهي. تنهن ڪري، هڪ طرف، اسان کي تيز پڙهڻ لاء انڊيڪس جي ضرورت آهي، ۽ ٻئي طرف، اسان ڊيٽابيس ۾ غير ضروري انڊيڪس ڏسڻ نٿا چاهيون، ڇاڪاڻ ته اهي جڳهه کائيندا آهن ۽ ڊيٽا جي تازه ڪاري کي سست ڪندا آهن.

۽ هاڻي، سڀڪنھن شيء کي بحال غلط انڊيڪس ۽ ڪافي ڏٺو Oleg Bartunov پاران رپورٽون، مون هڪ ”عظيم“ صفائي کي منظم ڪرڻ جو فيصلو ڪيو. اهو ظاهر ٿيو ته ڊولپرز ڊيٽابيس دستاويز پڙهڻ پسند نٿا ڪن. اهي تمام گهڻو پسند نه ڪندا آھن. انهي جي ڪري، ٻه عام غلطيون پيدا ٿينديون آهن - هڪ دستي طور تي ٺاهيل انڊيڪس هڪ پرائمري ڪيچ تي ۽ ساڳئي "دستي" انڊيڪس هڪ منفرد ڪالمن تي. حقيقت اها آهي ته انهن جي ضرورت نه آهي - Postgres پاڻ سڀڪنھن شيء کي ڪندا. اهڙيون انڊيڪس محفوظ طور تي ختم ڪري سگھجن ٿيون، ۽ تشخيص هن ​​مقصد لاء ظاهر ڪيا ويا آهن duplicated_indexes.

مسئلو ٽيون - ٽڪرا ٽڪرا اشارا

اڪثر نوان ڊولپرز هڪ ڪالم تي انڊيڪس ٺاهيندا آهن. تدريجي طور تي، هن ڪاروبار کي چڱي طرح تجربو ڪرڻ بعد، ماڻهو پنهنجن سوالن کي بهتر ڪرڻ شروع ڪن ٿا ۽ وڌيڪ پيچيده انڊيڪس شامل ڪن ٿا جن ۾ ڪيترائي ڪالمن شامل آهن. اهڙي طرح ڪالمن تي انڊيڪس ظاهر ٿيندا آهن A, هڪ + بي, A+B+C ۽ ايئن. انهن انگن اکرن مان پهرين ٻن کي محفوظ طور تي اڇلائي سگهجي ٿو، ڇاڪاڻ ته اهي ٽئين جا اڳڀرائي آهن. اهو پڻ تمام گهڻو ڊسڪ اسپيس بچائيندو آهي ۽ ان لاءِ تشخيص موجود آهن intersected_indexes.

چار مسئلو - انڊيڪس کان سواءِ پرڏيهي ڪنجيون

Postgres توهان کي اجازت ڏئي ٿو ته بغير ڪنهن پٺڀرائي واري انڊيڪس جي وضاحت ڪرڻ جي غير ملڪي اهم رڪاوٽون. ڪيترين ئي حالتن ۾ اهو مسئلو ناهي، ۽ شايد پاڻ کي ظاهر به نه ڪري سگهي... هن وقت تائين...

اهو اسان سان به ساڳيو هو: اهو صرف اهو آهي ته ڪنهن وقت ۾ هڪ نوڪري، هڪ شيڊول مطابق هلائڻ ۽ ٽيسٽ آرڊر جي ڊيٽابيس کي صاف ڪرڻ، ماسٽر ميزبان طرفان اسان کي "شامل" ٿيڻ شروع ڪيو. CPU ۽ IO ضايع ٿي ويا، درخواستون سست ٿي ويون ۽ وقت ختم ٿي ويا، سروس پنج سئو هئي. تڪڙو تجزيو pg_stat_activity ڏيکاريو ته سوالن وانگر:

delete from <table> where id in (…)

انهي صورت ۾، يقينا، ٽارگيٽ ٽيبل ۾ id طرفان هڪ انڊيڪس هو، ۽ شرط جي مطابق تمام ٿورا رڪارڊ ختم ڪيا ويا. اهو لڳي ٿو ته هر شيء ڪم ڪرڻ گهرجي، پر افسوس، اهو نه ٿيو.

عجيب هڪ بچاء لاء آيو وضاحت ڪريو تجزيو ۽ چيو ته ٽارگيٽ ٽيبل ۾ رڪارڊ کي ختم ڪرڻ کان علاوه، هڪ حوالو سالميت چيڪ پڻ آهي، ۽ لاڳاپيل جدولن مان هڪ تي هي چيڪ ناڪام ٿئي ٿو. ترتيب وار اسڪين مناسب انڊيڪس جي کوٽ جي ڪري. اهڙيء طرح تشخيص پيدا ٿيو پرڏيهي_ڪيز_without_index.

مسئلو پنج - انڊيڪس ۾ null قدر

ڊفالٽ طور، پوسٽ گريس ۾ بيٽري انڊيڪسس ۾ null قدر شامل آهن، پر عام طور تي انهن جي ضرورت ناهي. تنهن ڪري، مان ڪوشش ڪريان ٿو ته انهن نالن کي ٻاهر ڪڍڻ جي ڪوشش ڪريان (تشخيص indexes_with_null_values)، جزوي انڊيڪسس ٺاھڻ nullable ڪالمن تي قسم جي لحاظ کان where <A> is not null. اهڙي طرح مان اسان جي هڪ انڊيڪس جي سائيز کي 1877 MB کان گھٽائي 16 KB ڪرڻ جي قابل ٿيس. ۽ ھڪڙي خدمتن ۾، ڊيٽابيس جي سائيز گھٽجي وئي مجموعي طور تي 16٪ (مطلق انگن ۾ 4.3 GB جي ذريعي) انڊيڪس مان نڪتل قدرن جي خارج ٿيڻ جي ڪري. تمام سادي ترميمن سان ڊسڪ اسپيس ۾ وڏي بچت. 🙂

مسئلو ڇهين - پرائمري ڪنجين جي کوٽ

ميڪانيزم جي فطرت جي ڪري Postgres ۾ MVCC اهڙي صورتحال ممڪن آهي دٻاءُجڏهن توهان جي ٽيبل جي سائيز تيزي سان وڌي رهي آهي ڇاڪاڻ ته وڏي تعداد ۾ مئل رڪارڊ. مون کي بيحد يقين هو ته اهو اسان کي خطرو نه ڪندو، ۽ اهو اسان جي بنياد سان نه ٿيندو، ڇاڪاڻ ته، واهه !!!، اسان عام ڊولپر آهيون ... مان ڪيترو بيوقوف ۽ بيوقوف آهيان ...

هڪ ڏينهن، هڪ شاندار لڏپلاڻ هڪ وڏي ۽ فعال طور تي استعمال ٿيل ٽيبل ۾ سڀني رڪارڊ کي ورتو ۽ اپڊيٽ ڪيو. اسان حاصل ڪيو +100 GB نيري مان ٽيبل جي سائيز تائين. اها هڪ شرم جي ڳالهه هئي، پر اسان جي بدانتظامي اتي ختم نه ٿي. هن ميز تي آٽو ويڪيوم 15 ڪلاڪن بعد ختم ٿيڻ کان پوء، اهو واضح ٿيو ته جسماني مقام واپس نه ايندي. اسان خدمت کي روڪي نه سگهياسين ۽ VACUUM FULL ڪري سگهون ٿا، تنهنڪري اسان استعمال ڪرڻ جو فيصلو ڪيو pg_repack. ۽ پوء اهو ظاهر ٿيو ته pg_repack کي خبر ناهي ته جدولن کي پرائمري ڪيئي يا ٻي انفراديت جي پابندي کان سواءِ ڪيئن پروسيس ڪجي، ۽ اسان جي ٽيبل وٽ پرائمري ڪي نه هئي. اهڙيء طرح تشخيص پيدا ٿيو ٽيبل_بغير_پرائمري_ڪي.

لائبريري ورزن ۾ 0.1.5 جدولن ۽ انڊيڪس جي بلوٽ مان ڊيٽا گڏ ڪرڻ ۽ ان کي بروقت جواب ڏيڻ جي صلاحيت شامل ڪئي وئي آهي.

مسئلا ست ۽ اٺ - ناقص انڊيڪس ۽ اڻ استعمال ٿيل انڊيڪس

هيٺيان ٻه تشخيص آهن: tables_with_missing_indexes и unused_indexes - نسبتا تازو ان جي آخري شڪل ۾ ظاهر ٿيو. نقطي اهو آهي ته اهي صرف نه ٿي سگهيا ۽ شامل ڪيا ويا.

جيئن ته مون اڳ ۾ ئي لکيو آهي، اسان ڪيترن ئي نقلن سان هڪ ترتيب استعمال ڪندا آهيون، ۽ مختلف ميزبان تي پڙهڻ وارو لوڊ بنيادي طور تي مختلف آهي. نتيجي طور، صورتحال ظاهر ٿئي ٿي ته ڪجهه ميز ۽ انڊيڪس ڪجهه ميزبان تي عملي طور استعمال نه ڪيا ويا آهن، ۽ تجزيي لاء توهان کي ڪلستر ۾ سڀني ميزبانن کان انگ اکر گڏ ڪرڻ جي ضرورت آهي. انگ اکر ٻيهر سيٽ ڪريو اهو ڪلستر ۾ هر ميزبان تي پڻ ضروري آهي؛ توهان اهو نه ٿا ڪري سگهو صرف ماسٽر تي.

هن طريقي سان اسان کي انڊيڪس کي هٽائڻ سان ڪيترن ئي گيگا بائيٽس کي بچائڻ جي اجازت ڏني وئي جيڪي ڪڏهن به استعمال نه ڪيا ويا هئا، انهي سان گڏ گهٽ ۾ گهٽ استعمال ٿيل جدولن ۾ غائب انڊيڪس شامل ڪرڻ.

نتيجي ۾

يقينا، تقريبن سڀني تشخيص لاء توهان ترتيب ڏئي سگهو ٿا خارج ڪرڻ جي فهرست. اهو طريقو، توهان جلدي پنهنجي ايپليڪيشن ۾ چيڪ لاڳو ڪري سگهو ٿا، نئين غلطين کي ظاهر ٿيڻ کان روڪيو، ۽ پوءِ آهستي آهستي پراڻين کي درست ڪري سگھو ٿا.

ڊيٽابيس لڏپلاڻ ڪرڻ کان پوءِ فوري طور تي فنڪشنل ٽيسٽن ۾ ڪجهه تشخيص ٿي سگهن ٿيون. ۽ اهو شايد منهنجي لائبريري جي سڀ کان وڌيڪ طاقتور خاصيتن مان هڪ آهي. استعمال جو هڪ مثال ۾ ملي سگهي ٿو ڊيمو.

اهو سمجھ ۾ اچي ٿو ته غير استعمال ٿيل يا گم ٿيل انڊيڪسز لاءِ چيڪ ڪرڻ، ۽ گڏوگڏ بلوٽ لاءِ، صرف حقيقي ڊيٽابيس تي. گڏ ڪيل قدرن ۾ رڪارڊ ڪري سگھجي ٿو ڪلڪ ڪريو هائوس يا مانيٽرنگ سسٽم ڏانهن موڪليو ويو.

مون کي واقعي اها اميد آهي pg-index-health مفيد ۽ طلب ۾ ٿيندو. توهان پڻ لائبريري جي ترقي ۾ مدد ڪري سگهو ٿا مسئلن جي رپورٽ ڪندي جيڪي توهان ڳوليندا آهيو ۽ نئين تشخيص جو مشورو ڏيو.

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