জাভা বিকাশকারীর চোখের মাধ্যমে পোস্টগ্রেএসকিউএল-এ সূচীর স্বাস্থ্য

হ্যালো।

আমার নাম ভানিয়া এবং আমি একজন জাভা বিকাশকারী। এটা তাই ঘটে যে আমি PostgreSQL-এর সাথে অনেক কাজ করি - ডাটাবেস সেট আপ করি, গঠন, কর্মক্ষমতা অপ্টিমাইজ করি এবং সপ্তাহান্তে একটু ডিবিএ খেলি।

সম্প্রতি আমি আমাদের মাইক্রোসার্ভিসে বেশ কিছু ডাটাবেস সাজিয়েছি এবং একটি জাভা লাইব্রেরি লিখেছি pg-সূচক-স্বাস্থ্য, যা এই কাজটিকে সহজ করে, আমার সময় বাঁচায় এবং বিকাশকারীদের দ্বারা করা কিছু সাধারণ ভুল এড়াতে সাহায্য করে। এই লাইব্রেরির কথাই আজ আমরা কথা বলব।

দায়িত্ব অস্বীকার

PostgreSQL এর মূল সংস্করণটি হল 10টি। আমি যে সকল এসকিউএল কোয়েরি ব্যবহার করি সেগুলোও 11 সংস্করণে পরীক্ষা করা হয়। ন্যূনতম সমর্থিত সংস্করণ হল 9.6।

প্রাগঐতিহাসিক

এটি প্রায় এক বছর আগে আমার কাছে অদ্ভুত পরিস্থিতির সাথে শুরু হয়েছিল: নীলের বাইরে একটি সূচকের প্রতিযোগিতামূলক সৃষ্টি একটি ত্রুটির সাথে শেষ হয়েছিল। সূচক নিজেই, যথারীতি, একটি অবৈধ অবস্থায় ডাটাবেসে রয়ে গেছে। লগ বিশ্লেষণে ঘাটতি দেখা গেছে temp_file_limit. এবং আমরা চলে যাই... আরও গভীরে খনন করে, আমি ডাটাবেস কনফিগারেশনে সমস্যাগুলির একটি সম্পূর্ণ গুচ্ছ আবিষ্কার করেছি এবং, আমার হাতা গুটিয়ে, আমার চোখে একটি ঝলক দিয়ে সেগুলি ঠিক করতে শুরু করেছি।

সমস্যা এক - ডিফল্ট কনফিগারেশন

সম্ভবত সবাই পোস্টগ্রেস সম্পর্কে রূপক নিয়ে বেশ ক্লান্ত, যা একটি কফি প্রস্তুতকারকে চালানো যেতে পারে, কিন্তু... ডিফল্ট কনফিগারেশন সত্যিই অনেক প্রশ্ন উত্থাপন করে। সর্বনিম্ন, এটি মনোযোগ দিতে মূল্যবান রক্ষণাবেক্ষণ_কাজ_মেম, temp_file_limit, স্টেটমেন্ট_টাইমআউট и lock_timeout.

আমাদের ক্ষেত্রে রক্ষণাবেক্ষণ_কাজ_মেম ডিফল্ট ছিল 64 MB, এবং temp_file_limit প্রায় 2 গিগাবাইট - একটি বড় টেবিলে একটি সূচক তৈরি করার জন্য আমাদের কাছে যথেষ্ট মেমরি ছিল না।

অতএব, মধ্যে pg-সূচক-স্বাস্থ্য আমি একটি সিরিজ সংগ্রহ করেছি মূল, আমার মতে, প্রতিটি ডাটাবেসের জন্য যে প্যারামিটারগুলি কনফিগার করা উচিত।

সমস্যা দুই - ডুপ্লিকেট ইনডেক্স

আমাদের ডাটাবেসগুলি SSD ড্রাইভে থাকে এবং আমরা ব্যবহার করি HA- একাধিক ডেটা সেন্টার, মাস্টার হোস্ট এবং সহ কনফিগারেশন n- প্রতিলিপি সংখ্যা. ডিস্ক স্পেস আমাদের জন্য একটি অত্যন্ত মূল্যবান সম্পদ; এটি কর্মক্ষমতা এবং CPU খরচের চেয়ে কম গুরুত্বপূর্ণ নয়। অতএব, একদিকে, আমাদের দ্রুত পড়ার জন্য সূচীগুলির প্রয়োজন, এবং অন্যদিকে, আমরা ডাটাবেসে অপ্রয়োজনীয় সূচকগুলি দেখতে চাই না, যেহেতু তারা স্থান খায় এবং ডেটা আপডেট করার গতি কমিয়ে দেয়।

এবং এখন, সবকিছু পুনরুদ্ধার হচ্ছে অবৈধ সূচক এবং যথেষ্ট দেখা হয়েছে Oleg Bartunov দ্বারা রিপোর্ট, আমি একটি সংগঠিত করার সিদ্ধান্ত নিয়েছি "মহান" শোধন. এটি প্রমাণিত হয়েছে যে বিকাশকারীরা ডাটাবেস ডকুমেন্টেশন পড়তে পছন্দ করেন না। তারা এটা খুব একটা পছন্দ করে না। এই কারণে, দুটি সাধারণ ত্রুটি দেখা দেয় - একটি প্রাথমিক কীতে একটি ম্যানুয়ালি তৈরি সূচক এবং একটি অনন্য কলামে একটি অনুরূপ "ম্যানুয়াল" সূচক৷ আসল বিষয়টি হ'ল তাদের প্রয়োজন নেই - পোস্টগ্রেস নিজেই সবকিছু করবে। এই জাতীয় সূচীগুলি নিরাপদে মুছে ফেলা যেতে পারে এবং এই উদ্দেশ্যে ডায়াগনস্টিকস উপস্থিত হয়েছে ডুপ্লিকেটেড_ইনডেক্স.

সমস্যা তিন - সূচকগুলিকে ছেদ করছে

বেশিরভাগ নবীন বিকাশকারীরা একটি একক কলামে সূচী তৈরি করে। ধীরে ধীরে, এই ব্যবসার পুঙ্খানুপুঙ্খভাবে অভিজ্ঞতার পরে, লোকেরা তাদের প্রশ্নগুলিকে অপ্টিমাইজ করতে শুরু করে এবং আরও জটিল সূচী যোগ করে যার মধ্যে বেশ কয়েকটি কলাম রয়েছে৷ এভাবেই কলামে সূচী দেখা যায় A, এ + বি, এ + বি + সি এবং তাই এই সূচকগুলির প্রথম দুটি নিরাপদে ফেলে দেওয়া যেতে পারে, যেহেতু তারা তৃতীয়টির উপসর্গ। এটি ডিস্কের অনেক জায়গাও বাঁচায় এবং এর জন্য ডায়াগনস্টিকস রয়েছে intersected_indexes.

সমস্যা চার - সূচী ছাড়া বিদেশী কী

পোস্টগ্রেস আপনাকে একটি ব্যাকিং সূচক উল্লেখ না করেই বিদেশী কী সীমাবদ্ধতা তৈরি করতে দেয়। অনেক পরিস্থিতিতে এটি একটি সমস্যা নয়, এবং এমনকি নিজেকে প্রকাশ করতে পারে না... আপাতত...

এটি আমাদের সাথে একই ছিল: এটি ঠিক যে কোনও সময়ে একটি কাজ, একটি সময়সূচী অনুযায়ী চলমান এবং পরীক্ষার আদেশের ডাটাবেস পরিষ্কার করা, মাস্টার হোস্ট দ্বারা আমাদের সাথে "যোগ করা" শুরু হয়েছিল। সিপিইউ এবং আইও নষ্ট হয়ে গেছে, অনুরোধগুলি ধীর হয়ে গেছে এবং সময় শেষ হয়ে গেছে, পরিষেবাটি পাঁচশ ছিল। দ্রুত বিশ্লেষণ pg_stat_activity দেখিয়েছে যে প্রশ্নগুলি যেমন:

delete from <table> where id in (…)

এই ক্ষেত্রে, অবশ্যই, টার্গেট টেবিলে আইডি দ্বারা একটি সূচক ছিল এবং শর্ত অনুসারে খুব কম রেকর্ড মুছে ফেলা হয়েছিল। দেখে মনে হচ্ছিল সবকিছুই কাজ করা উচিত, কিন্তু, হায়, তা হয়নি।

বিস্ময়কর এক উদ্ধার এসেছিলেন ব্যাখ্যা বিশ্লেষণ এবং বলেছে যে টার্গেট টেবিলে রেকর্ড মুছে ফেলার পাশাপাশি, একটি রেফারেন্সিয়াল ইন্টিগ্রিটি চেকও রয়েছে এবং সম্পর্কিত টেবিলগুলির একটিতে এই চেকটি ব্যর্থ হয় ক্রমিক স্ক্যান উপযুক্ত সূচকের অভাবের কারণে। এইভাবে ডায়াগনস্টিকসের জন্ম হয়েছিল বিদেশী_কী_বিহীন_সূচী.

সমস্যা পাঁচ - সূচকে শূন্য মান

ডিফল্টরূপে, পোস্টগ্রেস btree সূচীতে নাল মান অন্তর্ভুক্ত করে, কিন্তু সাধারণত সেখানে তাদের প্রয়োজন হয় না। অতএব, আমি অধ্যবসায়ের সাথে এই নালগুলি বের করার চেষ্টা করি (ডায়াগনস্টিকস indexes_with_null_values), টাইপ অনুসারে বাতিলযোগ্য কলামগুলিতে আংশিক সূচী তৈরি করা where <A> is not null. এইভাবে আমি আমাদের একটি সূচকের আকার 1877 MB থেকে 16 KB এ কমাতে সক্ষম হয়েছি। এবং পরিষেবাগুলির একটিতে, সূচকগুলি থেকে শূন্য মানগুলি বাদ দেওয়ার কারণে ডাটাবেসের আকার মোট 16% (পরম সংখ্যায় 4.3 GB দ্বারা) হ্রাস পেয়েছে। খুব সাধারণ পরিবর্তনের সাথে ডিস্কের জায়গায় প্রচুর সঞ্চয়। 🙂

সমস্যা ছয় - প্রাথমিক কীগুলির অভাব

যান্ত্রিক প্রকৃতির কারণে পোস্টগ্রেসে এমভিসিসি এই মত একটি পরিস্থিতি সম্ভব ফোলাযখন বিপুল সংখ্যক মৃত রেকর্ডের কারণে আপনার টেবিলের আকার দ্রুত বাড়ছে। আমি সরলভাবে বিশ্বাস করতাম যে এটি আমাদের হুমকি দেবে না, এবং এটি আমাদের ঘাঁটিতে ঘটবে না, কারণ আমরা, বাহ!!!, সাধারণ বিকাশকারী... আমি কতটা বোকা এবং সরল ছিলাম...

একদিন, একটি বিস্ময়কর স্থানান্তর একটি বড় এবং সক্রিয়ভাবে ব্যবহৃত টেবিলে সমস্ত রেকর্ড গ্রহণ এবং আপডেট করে। আমরা নীল থেকে টেবিলের আকারে +100 GB পেয়েছি। এটা খুবই লজ্জাজনক ছিল, কিন্তু আমাদের দুর্দশা সেখানেই শেষ হয়নি। এই টেবিলের অটোভ্যাকুয়াম 15 ঘন্টা পরে শেষ হওয়ার পরে, এটি স্পষ্ট হয়ে গেল যে শারীরিক অবস্থান ফিরে আসবে না। আমরা পরিষেবাটি বন্ধ করে ভ্যাকুয়াম পূর্ণ করতে পারিনি, তাই আমরা ব্যবহার করার সিদ্ধান্ত নিয়েছি pg_repack. এবং তারপর এটা যে পরিণত pg_repack প্রাথমিক কী বা অন্যান্য স্বতন্ত্রতা সীমাবদ্ধতা ছাড়া কীভাবে টেবিলগুলি প্রক্রিয়া করতে হয় তা জানে না এবং আমাদের টেবিলে একটি প্রাথমিক কী ছিল না। এইভাবে ডায়াগনস্টিকসের জন্ম হয়েছিল সারণী_বিহীন_প্রাথমিক_কী.

লাইব্রেরি সংস্করণে 0.1.5 টেবিল এবং সূচীগুলির ব্লোট থেকে ডেটা সংগ্রহ এবং একটি সময়মত পদ্ধতিতে এটির প্রতিক্রিয়া করার ক্ষমতা যুক্ত করা হয়েছে।

সমস্যা সাত এবং আট - অপর্যাপ্ত সূচক এবং অব্যবহৃত সূচক

নিম্নলিখিত দুটি ডায়গনিস্টিক হল: সারণী_সহ_অনুপস্থিত_সূচী и unused_indexes - তুলনামূলকভাবে সম্প্রতি তাদের চূড়ান্ত আকারে হাজির। বিন্দু হল যে তারা শুধু নেওয়া এবং যোগ করা যাবে না।

আমি ইতিমধ্যেই লিখেছি, আমরা বেশ কয়েকটি প্রতিলিপি সহ একটি কনফিগারেশন ব্যবহার করি এবং বিভিন্ন হোস্টে পড়ার লোড মৌলিকভাবে আলাদা। ফলস্বরূপ, পরিস্থিতি দেখা যাচ্ছে যে কিছু হোস্টের কিছু টেবিল এবং সূচী কার্যত ব্যবহার করা হয় না এবং বিশ্লেষণের জন্য আপনাকে ক্লাস্টারের সমস্ত হোস্ট থেকে পরিসংখ্যান সংগ্রহ করতে হবে। পরিসংখ্যান রিসেট করুন ক্লাস্টারের প্রতিটি হোস্টে এটি প্রয়োজনীয়; আপনি এটি শুধুমাত্র মাস্টারের উপর করতে পারবেন না।

এই পদ্ধতির সাহায্যে আমরা কখনোই ব্যবহৃত হয়নি এমন সূচীগুলিকে সরিয়ে দিয়ে, সেইসাথে খুব কমই ব্যবহৃত টেবিলে অনুপস্থিত সূচীগুলিকে যুক্ত করে কয়েক দশ গিগাবাইট সংরক্ষণ করতে পারি।

একটি উপসংহার হিসাবে

অবশ্যই, প্রায় সমস্ত ডায়াগনস্টিকসের জন্য আপনি কনফিগার করতে পারেন বর্জন তালিকা. এইভাবে, আপনি দ্রুত আপনার অ্যাপ্লিকেশনে চেকগুলি প্রয়োগ করতে পারেন, নতুন ত্রুটিগুলি উপস্থিত হওয়া থেকে রোধ করতে পারেন এবং তারপরে ধীরে ধীরে পুরানোগুলি ঠিক করতে পারেন৷

কিছু ডায়াগনস্টিক ডাটাবেস মাইগ্রেশন রোল আউট করার সাথে সাথে কার্যকরী পরীক্ষায় সঞ্চালিত হতে পারে। এবং এটি সম্ভবত আমার লাইব্রেরির সবচেয়ে শক্তিশালী বৈশিষ্ট্যগুলির মধ্যে একটি। ব্যবহারের একটি উদাহরণ পাওয়া যাবে ডেমো.

অব্যবহৃত বা অনুপস্থিত সূচীগুলির জন্য, সেইসাথে ব্লোটের জন্য, শুধুমাত্র একটি বাস্তব ডাটাবেসের জন্য পরীক্ষা করা অর্থপূর্ণ। সংগৃহীত মান রেকর্ড করা যেতে পারে ক্লিকহাউস বা মনিটরিং সিস্টেমে পাঠানো হয়েছে।

আমি সত্যিই যে আশা pg-সূচক-স্বাস্থ্য দরকারী এবং চাহিদা হবে. আপনি যে সমস্যাগুলি খুঁজে পান এবং নতুন ডায়াগনস্টিকসের পরামর্শ দিয়ে লাইব্রেরির উন্নয়নে অবদান রাখতে পারেন৷

উত্স: www.habr.com