পোস্টগ্রেস: ব্লোট, পিজি_রিপ্যাক এবং বিলম্বিত সীমাবদ্ধতা

ব্লোটিং টেবিল এবং ইনডেক্সের (ব্লোট) প্রভাব ব্যাপকভাবে পরিচিত এবং শুধুমাত্র পোস্টগ্রেসেই নেই। ভ্যাকুয়াম ফুল বা ক্লাস্টারের মতো "বাক্সের বাইরে" এর সাথে মোকাবিলা করার উপায় রয়েছে, তবে তারা অপারেশন চলাকালীন টেবিল লক করে রাখে এবং তাই সর্বদা ব্যবহার করা যায় না।

প্রবন্ধে কিছু তত্ত্ব থাকবে কিভাবে ব্লোট হয়, কিভাবে আপনি এর সাথে মোকাবিলা করতে পারেন, পিজি_রিপ্যাক এক্সটেনশন ব্যবহার করার জন্য স্থগিত সীমাবদ্ধতা এবং তারা যে সমস্যাগুলি নিয়ে আসে সে সম্পর্কে।

এই নিবন্ধটি উপর ভিত্তি করে আমার বক্তৃতা PgConf.Russia 2020 এ।

কেন একটি ফোলা আছে

পোস্টগ্রেস একটি বহু-সংস্করণ মডেলের উপর ভিত্তি করে (এমভিসিসি) এর সারমর্ম হল যে টেবিলের প্রতিটি সারিতে বেশ কয়েকটি সংস্করণ থাকতে পারে, যখন লেনদেনগুলি এই সংস্করণগুলির একটির বেশি দেখতে পায় না, তবে অগত্যা একই নয়৷ এটি একাধিক লেনদেন একই সাথে কাজ করার অনুমতি দেয় এবং একে অপরের উপর সামান্য প্রভাব ফেলে না।

স্পষ্টতই, এই সমস্ত সংস্করণ রাখা প্রয়োজন. Postgres মেমরি পৃষ্ঠার সাথে পৃষ্ঠা দ্বারা কাজ করে এবং একটি পৃষ্ঠা হল ন্যূনতম পরিমাণ ডেটা যা ডিস্ক থেকে পড়া বা লেখা যায়। এটি কীভাবে ঘটে তা বোঝার জন্য আসুন একটি ছোট উদাহরণ দেখি।

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

তারপরে আমরা একটি এন্ট্রি আপডেট করেছি এবং এইভাবে পুরানো সংস্করণটিকে পুরানো হিসাবে চিহ্নিত করেছি।

ধাপে ধাপে, সারি সংস্করণগুলি আপডেট করা এবং মুছে ফেলার মাধ্যমে, আমরা একটি পৃষ্ঠা পেয়েছি যেখানে প্রায় অর্ধেক ডেটা "আবর্জনা"। এই ডেটা কোনো লেনদেনের জন্য দৃশ্যমান নয়।

Postgres এর একটি প্রক্রিয়া আছে শূন্যস্থান, যা পুরানো সংস্করণগুলিকে পরিষ্কার করে এবং নতুন ডেটার জন্য জায়গা করে দেয়। কিন্তু যদি এটি যথেষ্ট আক্রমনাত্মকভাবে কনফিগার করা না হয় বা অন্য টেবিলে কাজ করতে ব্যস্ত থাকে, তাহলে "আবর্জনা ডেটা" থেকে যায় এবং আমাদের নতুন ডেটার জন্য অতিরিক্ত পৃষ্ঠাগুলি ব্যবহার করতে হবে।

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

এমনকি যদি VACUUM এখন সমস্ত অপ্রাসঙ্গিক সারি সংস্করণগুলি সরিয়ে দেয়, পরিস্থিতি নাটকীয়ভাবে উন্নত হবে না। নতুন লাইনের জন্য আমাদের পৃষ্ঠাগুলিতে বা এমনকি পুরো পৃষ্ঠাগুলিতে ফাঁকা জায়গা থাকবে, তবে আমরা এখনও আমাদের প্রয়োজনের চেয়ে বেশি ডেটা পড়ব।
যাইহোক, যদি একটি সম্পূর্ণ ফাঁকা পৃষ্ঠা (আমাদের উদাহরণের দ্বিতীয়টি) ফাইলের শেষে থাকে, তাহলে VACUUM এটিকে কেটে ফেলতে পারে। কিন্তু এখন সে মাঝখানে, তাই তাকে দিয়ে কিছু করা যাচ্ছে না।

যখন এই ধরনের খালি বা ভারী স্পার্স পৃষ্ঠাগুলির সংখ্যা বড় হয়ে যায়, যাকে ব্লোট বলা হয়, এটি কর্মক্ষমতা প্রভাবিত করতে শুরু করে।

উপরে বর্ণিত সবকিছুই টেবিলে ফোলা হওয়ার মেকানিক্স। সূচীতে, এটি অনেকটা একইভাবে ঘটে।

আমি একটি ফোলা আছে?

আপনার ফোলা আছে কিনা তা নির্ধারণ করার বিভিন্ন উপায় রয়েছে। প্রথমটির ধারণাটি হল পোস্টগ্রেস অভ্যন্তরীণ পরিসংখ্যান ব্যবহার করা, যাতে টেবিলের সারির সংখ্যা, "লাইভ" সারির সংখ্যা ইত্যাদি সম্পর্কে আনুমানিক তথ্য রয়েছে। ইন্টারনেটে তৈরি স্ক্রিপ্টের অনেক বৈচিত্র রয়েছে। আমরা ভিত্তি হিসেবে নিয়েছি লিপি PostgreSQL বিশেষজ্ঞদের কাছ থেকে, যা টোস্ট এবং ব্লট বিট্রি ইনডেক্সের সাথে টেবিল ব্লোট মূল্যায়ন করতে পারে। আমাদের অভিজ্ঞতায়, এর ত্রুটি 10-20%।

আরেকটি উপায় হল এক্সটেনশন ব্যবহার করা pgstattuple, যা আপনাকে পৃষ্ঠাগুলির ভিতরে দেখতে এবং ব্লোটের আনুমানিক এবং সঠিক মান উভয়ই পেতে দেয়। কিন্তু দ্বিতীয় ক্ষেত্রে, আপনাকে পুরো টেবিলটি স্ক্যান করতে হবে।

অল্প পরিমাণে ফোলা, 20% পর্যন্ত, গ্রহণযোগ্য। এটি জন্য ফিলফ্যাক্টরের একটি অ্যানালগ হিসাবে বিবেচনা করা যেতে পারে ছককাটা তালিকাভুক্ত করণ и সূচক. 50% এবং তার উপরে, কর্মক্ষমতা সমস্যা শুরু হতে পারে।

ফোলা মোকাবেলা করার উপায়

পোস্টগ্রেসে ব্লোট মোকাবেলা করার জন্য বেশ কিছু অপ্রীতিকর উপায় রয়েছে, তবে সেগুলি সর্বদা থেকে অনেক দূরে এবং সবার জন্য উপযুক্ত নাও হতে পারে।

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

AUTOVACUUM টেবিল প্রক্রিয়া করতে ব্যর্থ হওয়ার আরেকটি সাধারণ কারণ হল দীর্ঘ-চলমান লেনদেনের উপস্থিতি যা এই লেনদেনের জন্য উপলব্ধ থাকার কারণে ডেটা পরিষ্কার করতে বাধা দেয়। এখানে সুপারিশটিও সুস্পষ্ট - "ঝুলন্ত" লেনদেন থেকে মুক্তি পান এবং সক্রিয় লেনদেনের সময় কমিয়ে দিন। কিন্তু যদি আপনার অ্যাপ্লিকেশনের লোডটি OLAP এবং OLTP-এর একটি সংকর হয়, তাহলে আপনি একই সাথে প্রচুর ঘন ঘন আপডেট এবং সংক্ষিপ্ত প্রশ্ন এবং দীর্ঘমেয়াদী অপারেশন উভয়ই থাকতে পারেন - উদাহরণস্বরূপ, একটি প্রতিবেদন তৈরি করা। এই ধরনের পরিস্থিতিতে, আপনার বিভিন্ন ঘাঁটিতে লোড ছড়িয়ে দেওয়ার বিষয়ে চিন্তা করা উচিত, যা আপনাকে তাদের প্রতিটিকে সূক্ষ্ম-টিউন করার অনুমতি দেবে।

আরেকটি উদাহরণ - এমনকি যদি প্রোফাইলটি সমজাতীয় হয়, কিন্তু ডাটাবেসটি খুব বেশি লোডের অধীনে থাকে, তাহলে এমনকি সবচেয়ে আক্রমনাত্মক AUTOVACUUMও মোকাবেলা করতে সক্ষম হবে না এবং ফোলাভাব ঘটবে। স্কেলিং (উল্লম্ব বা অনুভূমিক) একমাত্র সমাধান।

আপনি যখন অটোভ্যাকুয়াম কনফিগার করেন তখন কীভাবে এমন পরিস্থিতিতে থাকা যায়, তবে ফোলা বাড়তে থাকে।

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

টীম ক্লাস্টার VACUUM FULL এর মতো একইভাবে টেবিলের বিষয়বস্তু পুনর্নির্মাণ করে, যখন আপনাকে একটি সূচক নির্দিষ্ট করার অনুমতি দেয় যা অনুযায়ী ডেটা শারীরিকভাবে ডিস্কে অর্ডার করা হবে (তবে অর্ডারটি ভবিষ্যতে নতুন সারিগুলির জন্য নিশ্চিত নয়)। নির্দিষ্ট পরিস্থিতিতে, এটি বেশ কয়েকটি প্রশ্নের জন্য একটি ভাল অপ্টিমাইজেশন - সূচকে বেশ কয়েকটি রেকর্ড পড়ার সাথে। কমান্ডের অসুবিধা VACUUM FULL এর মতোই - এটি অপারেশন চলাকালীন টেবিলটিকে লক করে।

টীম REINDEX আগের দুটির মতো, কিন্তু একটি নির্দিষ্ট সূচক বা একটি টেবিলের সমস্ত সূচী পুনর্নির্মাণ করে। লকগুলি কিছুটা দুর্বল: একটি টেবিলে ShareLock (পরিবর্তন প্রতিরোধ করে, কিন্তু নির্বাচনের অনুমতি দেয়) এবং একটি পুনর্নির্মাণযোগ্য সূচকে AccessExclusiveLock (এই সূচক ব্যবহার করে প্রশ্নগুলি ব্লক করে)। যাইহোক, Postgres 12 বিকল্পটি চালু করেছে একযোগে, যা আপনাকে সমসাময়িক সংযোজন, পরিবর্তন, বা রেকর্ড মুছে ফেলাকে ব্লক না করেই একটি সূচক পুনর্নির্মাণের অনুমতি দেয়।

Postgres এর পূর্ববর্তী সংস্করণগুলিতে, আপনি একই সাথে REINDEX এর মতো ফলাফল অর্জন করতে পারেন একযোগে সূচক তৈরি করুন. এটি আপনাকে একটি শক্তিশালী লক ছাড়াই একটি সূচক তৈরি করতে দেয় (ShareUpdateExclusiveLock, যা সমান্তরাল প্রশ্নের সাথে হস্তক্ষেপ করে না), তারপরে পুরানো সূচকটিকে একটি নতুন দিয়ে প্রতিস্থাপন করুন এবং পুরানো সূচীটি মুছুন৷ এটি আপনাকে আপনার অ্যাপ্লিকেশনে হস্তক্ষেপ না করেই সূচক ফোলা দূর করতে দেয়। এটি বিবেচনা করা গুরুত্বপূর্ণ যে সূচকগুলি পুনর্নির্মাণের সময়, ডিস্ক সাবসিস্টেমে একটি অতিরিক্ত লোড থাকবে।

এইভাবে, যদি গরম ফোলা দূর করার জন্য সূচীগুলির জন্য উপায় থাকে, তবে টেবিলের জন্য কোনটি নেই। এখানেই বাহ্যিক এক্সটেনশনগুলি কার্যকর হয়: pg_repack (আগে pg_reorg), pgcompact, pgcompacttable এবং অন্যদের. এই নিবন্ধের কাঠামোর মধ্যে, আমি তাদের তুলনা করব না এবং শুধুমাত্র pg_repack সম্পর্কে কথা বলব, যা কিছু পরিমার্জন করার পরে, আমরা বাড়িতে ব্যবহার করি।

কিভাবে pg_repack কাজ করে

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

এর পরে, pg_repack পুরানো টেবিল থেকে নতুন টেবিলে ডেটা স্থানান্তর করে, স্বয়ংক্রিয়ভাবে সমস্ত অপ্রাসঙ্গিক সারি ফিল্টার করে, এবং তারপর নতুন টেবিলের জন্য সূচী তৈরি করে। এই সমস্ত ক্রিয়াকলাপ সম্পাদনের সময়, লগ টেবিলে পরিবর্তনগুলি জমা হয়।

পরবর্তী ধাপ হল পরিবর্তনগুলিকে নতুন টেবিলে স্থানান্তর করা। মাইগ্রেশনটি বেশ কয়েকটি পুনরাবৃত্তিতে করা হয়, এবং যখন লগ টেবিলে 20 টিরও কম এন্ট্রি অবশিষ্ট থাকে, তখন pg_repack একটি শক্তিশালী লক অর্জন করে, সর্বশেষ ডেটা স্থানান্তরিত করে এবং Postgres সিস্টেম টেবিলে নতুন একটি দিয়ে পুরানো টেবিল প্রতিস্থাপন করে। এটিই একমাত্র এবং খুব ছোট মুহূর্ত যখন আপনি টেবিলের সাথে কাজ করতে পারবেন না। এর পরে, পুরানো টেবিল এবং লগ সহ টেবিলটি মুছে ফেলা হয় এবং ফাইল সিস্টেমে স্থান খালি করা হয়। প্রক্রিয়া সম্পন্ন হয়েছে।

তত্ত্বে, সবকিছু দুর্দান্ত দেখায়, তবে অনুশীলনে কী হবে? আমরা pg_repack লোড ছাড়াই এবং লোডের নিচে পরীক্ষা করেছি, এবং একটি অকাল স্টপ (অন্য কথায়, Ctrl+C দ্বারা) এর ক্রিয়াকলাপ পরীক্ষা করেছি। সব পরীক্ষা পজিটিভ ছিল।

আমরা প্রোডে গিয়েছিলাম - এবং তারপরে সবকিছু ভুল হয়ে গেছে, যেমনটি আমরা আশা করেছিলাম।

প্রথম প্যানকেক বিক্রি হচ্ছে

প্রথম ক্লাস্টারে, আমরা একটি অনন্য সীমাবদ্ধতা লঙ্ঘন সম্পর্কে একটি ত্রুটি পেয়েছি:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

এই সীমাবদ্ধতার স্বয়ংক্রিয়-উত্পন্ন নাম index_16508 ছিল, pg_repack দ্বারা তৈরি। এর সংমিশ্রণে অন্তর্ভুক্ত বৈশিষ্ট্যগুলির দ্বারা, আমরা "আমাদের" সীমাবদ্ধতা নির্ধারণ করেছি যা এটির সাথে মিলে যায়। সমস্যাটি দেখা গেল যে এটি একটি সাধারণ সীমাবদ্ধতা নয়, তবে একটি বিলম্বিত (বিলম্বিত সীমাবদ্ধতা), i.e. এর বৈধতা sql কমান্ডের পরে সঞ্চালিত হয়, যা অপ্রত্যাশিত পরিণতির দিকে নিয়ে যায়।

বিলম্বিত সীমাবদ্ধতা: কেন তাদের প্রয়োজন এবং তারা কীভাবে কাজ করে

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

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique
);

ধরুন আমাদের প্রথম এবং দ্বিতীয় গাড়িগুলিকে জায়গায় অদলবদল করতে হবে। হেড-অন সমাধান হল প্রথম মানটিকে দ্বিতীয়টিতে এবং দ্বিতীয়টিকে প্রথমটিতে আপডেট করা:

begin;
  update cars set ord = 2 where name = 'audi';
  update cars set ord = 1 where name = 'bmw';
commit;

কিন্তু যখন আমরা এই কোডটি চালাই, তখন আমরা একটি সীমাবদ্ধতা লঙ্ঘন করার আশা করছি কারণ টেবিলের মানগুলির ক্রম অনন্য:

[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.

কিভাবে এটা ভিন্নভাবে করতে? বিকল্প এক: একটি অর্ডারের সাথে মানটির একটি অতিরিক্ত প্রতিস্থাপন যোগ করুন যা টেবিলে উপস্থিত না থাকার গ্যারান্টিযুক্ত, উদাহরণস্বরূপ "-1"৷ প্রোগ্রামিং-এ একে বলা হয় "এক তৃতীয়াংশের মাধ্যমে দুটি ভেরিয়েবলের মান বিনিময় করা।" এই পদ্ধতির একমাত্র খারাপ দিক হল অতিরিক্ত আপডেট।

বিকল্প দুই: পূর্ণসংখ্যার পরিবর্তে এক্সপোনেন্ট মানের জন্য একটি ফ্লোটিং পয়েন্ট ডেটা টাইপ ব্যবহার করার জন্য টেবিলটি পুনরায় ডিজাইন করুন। তারপর, 1 থেকে মান আপডেট করার সময়, উদাহরণস্বরূপ, 2.5-এ, প্রথম এন্ট্রিটি স্বয়ংক্রিয়ভাবে দ্বিতীয় এবং তৃতীয়টির মধ্যে "স্ট্যান্ড" হবে৷ এই সমাধান কাজ করে, কিন্তু দুটি সীমাবদ্ধতা আছে। প্রথমত, এটি আপনার জন্য কাজ করবে না যদি মানটি ইন্টারফেসের কোথাও ব্যবহার করা হয়। দ্বিতীয়ত, ডেটা টাইপের নির্ভুলতার উপর নির্ভর করে, সমস্ত রেকর্ডের মান পুনঃগণনা করার আগে আপনার কাছে সীমিত সংখ্যক সম্ভাব্য সন্নিবেশ থাকবে।

বিকল্প তিন: সীমাবদ্ধতাটি স্থগিত করুন যাতে এটি শুধুমাত্র প্রতিশ্রুতির সময় পরীক্ষা করা হয়:

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique deferrable initially deferred
);

যেহেতু আমাদের প্রাথমিক অনুরোধের যুক্তি নিশ্চিত করে যে প্রতিশ্রুতি তৈরি হওয়ার সময় সমস্ত মান অনন্য, তাই প্রতিশ্রুতি সফল হবে।

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

সাধারণভাবে, পোস্টগ্রেসে সীমাবদ্ধতার প্রকারের উপর নির্ভর করে, তাদের বৈধতার গ্রানুলারিটির তিনটি স্তর রয়েছে: সারি, লেনদেন এবং অভিব্যক্তি।

উত্স: begriffs

CHECK এবং NOT NULL সর্বদা সারি স্তরে চেক করা হয়, অন্যান্য সীমাবদ্ধতার জন্য, যেমন টেবিল থেকে দেখা যায়, বিভিন্ন বিকল্প রয়েছে। আপনি আরো পড়তে পারেন এখানে.

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

উন্নতি pg_repack

আমরা স্থগিত সীমাবদ্ধতাগুলি কভার করেছি, কিন্তু সেগুলি আমাদের সমস্যার সাথে কীভাবে সম্পর্কিত? আমরা আগে যে ত্রুটিটি পেয়েছি তা স্মরণ করুন:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

লগ টেবিল থেকে একটি নতুন টেবিলে ডেটা কপি করা হলে এটি ঘটে। এই অদ্ভুত দেখায়, কারণ লগ টেবিলের ডেটা মূল টেবিলের ডেটার সাথে প্রতিশ্রুতিবদ্ধ। যদি তারা মূল টেবিলের সীমাবদ্ধতাগুলিকে সন্তুষ্ট করে তবে কীভাবে তারা নতুনটিতে একই সীমাবদ্ধতা লঙ্ঘন করতে পারে?

যেহেতু এটি পরিণত হয়েছে, সমস্যার মূলটি pg_repack-এর পূর্ববর্তী ধাপে রয়েছে, যা শুধুমাত্র সূচী তৈরি করে, কিন্তু সীমাবদ্ধতা নয়: পুরানো টেবিলের একটি অনন্য সীমাবদ্ধতা ছিল এবং নতুনটি পরিবর্তে একটি অনন্য সূচক তৈরি করেছে।

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

সুতরাং, সমস্যার সারমর্মটি "বিলম্বিত" চেকের মধ্যে রয়েছে: মূল টেবিলে, এটি কমিটের সময় ঘটে এবং নতুনটিতে, sql কমান্ড কার্যকর করার সময়। সুতরাং আমাদের নিশ্চিত করতে হবে যে উভয় ক্ষেত্রেই চেকগুলি একইভাবে সম্পাদিত হয়: হয় সর্বদা বিলম্বিত হয়, বা সর্বদা অবিলম্বে।

তাই আমরা কি ধারনা আছে.

বিলম্বিত অনুরূপ সূচক তৈরি করুন

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

চেকগুলি সর্বদা অপরিবর্তিত মোডে থাকে তা নিশ্চিত করতে, আমরা মূল বিলম্বিত সীমাবদ্ধতার অনুরূপ একটি নতুন সূচক তৈরি করেছি:

CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;

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

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

ধারণা কাজ করেনি. সেই মুহুর্তে, আমরা আরও দুটি সমাধান দেখেছি: বিলম্বিত বিধিনিষেধ পরিত্যাগ করার জন্য আমাদের অ্যাপ্লিকেশন কোডটি পুনরায় লিখুন, অথবা তাদের সাথে কাজ করতে "শিখুন" pg_repack৷ আমরা দ্বিতীয়টি বেছে নিয়েছি।

মূল টেবিল থেকে বিলম্বিত সীমাবদ্ধতার সাথে নতুন টেবিলে সূচীগুলি প্রতিস্থাপন করুন

সংশোধনের উদ্দেশ্য সুস্পষ্ট ছিল - যদি মূল টেবিলে একটি বিলম্বিত সীমাবদ্ধতা থাকে, তাহলে নতুনটির জন্য এই ধরনের সীমাবদ্ধতা তৈরি করা প্রয়োজন, একটি সূচক নয়।

আমাদের পরিবর্তনগুলি পরীক্ষা করার জন্য, আমরা একটি সাধারণ পরীক্ষা লিখেছি:

• বিলম্বিত সীমাবদ্ধতা এবং একটি রেকর্ড সহ টেবিল;
• আমরা লুপে ডেটা সন্নিবেশ করি যা বিদ্যমান রেকর্ডের সাথে বিরোধপূর্ণ;
• একটি আপডেট করুন - ডেটা আর দ্বন্দ্ব করে না;
• পরিবর্তন কমিট

create table test_table
(
  id serial,
  val int,
  constraint uk_test_table__val unique (val) deferrable initially deferred 
);

INSERT INTO test_table (val) VALUES (0);
FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (0) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    COMMIT;
  END;
END LOOP;

pg_repack এর আসল সংস্করণটি সর্বদা প্রথম সন্নিবেশে ক্র্যাশ হয়, পরিবর্তিত সংস্করণটি ত্রুটি ছাড়াই কাজ করে। দারুণ।

আমরা prod-এ যাই এবং লগ টেবিল থেকে নতুন একটিতে ডেটা কপি করার একই পর্যায়ে আমরা আবার একটি ত্রুটি পাই:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

একটি ক্লাসিক পরিস্থিতি: সবকিছু পরীক্ষার পরিবেশে কাজ করে, কিন্তু উৎপাদনে নয়?!

APPLY_COUNT এবং দুটি ব্যাচের সংযোগস্থল

আমরা কোডটিকে আক্ষরিকভাবে লাইন দ্বারা লাইন বিশ্লেষণ করতে শুরু করেছি এবং একটি গুরুত্বপূর্ণ পয়েন্ট আবিষ্কার করেছি: ডেটা লগ টেবিল থেকে ব্যাচে নতুনটিতে স্থানান্তরিত হয়, APPLY_COUNT ধ্রুবক ব্যাচের আকার নির্দেশ করে:

for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);

if (num > MIN_TUPLES_BEFORE_SWITCH)
     continue;  /* there might be still some tuples, repeat. */
...
}

সমস্যা হল মূল লেনদেনের ডেটা, যেখানে বেশ কয়েকটি অপারেশন সম্ভাব্য সীমাবদ্ধতা লঙ্ঘন করতে পারে, স্থানান্তরের সময় দুটি ব্যাচের সংযোগস্থলে শেষ হতে পারে - কমান্ডের অর্ধেক প্রথম ব্যাচে প্রতিশ্রুতিবদ্ধ হবে, এবং বাকি অর্ধেক দ্বিতীয়টিতে এবং এখানে, কত ভাগ্যবান: যদি প্রথম ব্যাচের দলগুলি কিছু লঙ্ঘন না করে, তবে সবকিছু ঠিক আছে, তবে যদি তারা করে তবে একটি ত্রুটি ঘটে।

APPLY_COUNTটি 1000টি রেকর্ডের সমান, যা ব্যাখ্যা করে কেন আমাদের পরীক্ষা সফল হয়েছে - তারা "ব্যাচ জংশন" কেস কভার করেনি৷ আমরা দুটি কমান্ড ব্যবহার করেছি - সন্নিবেশ এবং আপডেট, তাই দুটি কমান্ডের ঠিক 500টি লেনদেন সর্বদা একটি ব্যাচে রাখা হয়েছিল এবং আমরা সমস্যা অনুভব করিনি। দ্বিতীয় আপডেট যোগ করার পরে, আমাদের সম্পাদনা কাজ করা বন্ধ করে দিয়েছে:

FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (1) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    UPDATE test_table set val = i where id = v_id; -- one more update
    COMMIT;
  END;
END LOOP;

সুতরাং, পরবর্তী কাজটি হল নিশ্চিত করা যে মূল টেবিলের ডেটা যা একটি লেনদেনে পরিবর্তিত হয়েছিল সেটিও একটি লেনদেনের মধ্যে নতুন টেবিলে প্রবেশ করে।

ব্যাচিং প্রত্যাখ্যান

এবং আমরা আবার দুটি সমাধান ছিল. প্রথম: আসুন আমরা সম্পূর্ণভাবে ব্যাচে বিভক্ত হতে অস্বীকার করি এবং ডেটা স্থানান্তরকে একটি একক লেনদেন করি। এই সিদ্ধান্তের পক্ষে এটির সরলতা ছিল - প্রয়োজনীয় কোড পরিবর্তনগুলি ন্যূনতম (যাইহোক, তখন পুরানো সংস্করণগুলিতে, pg_reorg সেভাবে কাজ করেছিল)। কিন্তু একটি সমস্যা আছে - আমরা একটি দীর্ঘমেয়াদী লেনদেন তৈরি করছি, এবং এটি, যেমনটি আগে উল্লিখিত হয়েছে, একটি নতুন ব্লাটের উত্থানের জন্য হুমকি।

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

সুতরাং, আমরা একটি সহজ বিকল্প হিসাবে প্রথম বিকল্পটি দিয়ে শুরু করার সিদ্ধান্ত নিয়েছি। প্রথমত, এটি একটি দীর্ঘ লেনদেন একটি বাস্তব সমস্যা হবে কি না বোঝা প্রয়োজন ছিল. যেহেতু পুরানো টেবিল থেকে নতুনটিতে ডেটার মূল স্থানান্তরও একটি দীর্ঘ লেনদেনে ঘটে, তাই প্রশ্নটি "আমরা এই লেনদেনটি কতটা বাড়াব?" এ রূপান্তরিত হয়েছে। প্রথম লেনদেনের সময়কাল মূলত টেবিলের আকারের উপর নির্ভর করে। নতুনটির সময়কাল ডেটা স্থানান্তরের সময় টেবিলে কতগুলি পরিবর্তন জমা হবে তার উপর নির্ভর করে, যেমন লোডের তীব্রতার উপর। pg_repack রান ন্যূনতম পরিষেবা লোডের সময়ে ঘটেছিল এবং মূল টেবিলের আকারের তুলনায় পরিবর্তনের পরিমাণ তুলনামূলকভাবে কম ছিল। আমরা সিদ্ধান্ত নিয়েছি যে আমরা একটি নতুন লেনদেনের সময়কে অবহেলা করতে পারি (তুলনার জন্য, গড় হল 1 ঘন্টা এবং 2-3 মিনিট)।

পরীক্ষাগুলি ইতিবাচক ছিল। বিক্রিতেও লঞ্চ করুন। স্পষ্টতার জন্য, এখানে রানের পরে একটি বেসের আকার সহ একটি ছবি রয়েছে:

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

সম্ভবত আপনার একটি প্রশ্ন আছে, কেন আমরা এমনকি pg_repack এর পরিমার্জনার সাথে এই গল্পে জড়িত হয়েছিলাম এবং উদাহরণস্বরূপ, এর অ্যানালগগুলি ব্যবহার করিনি? কিছু সময়ে, আমরা এটি সম্পর্কেও চিন্তা করেছি, কিন্তু বিলম্বিত বাধা ছাড়াই টেবিলে এটি ব্যবহার করার ইতিবাচক অভিজ্ঞতা আমাদের সমস্যার সারমর্ম বোঝার এবং এটি ঠিক করার চেষ্টা করতে অনুপ্রাণিত করেছে। এছাড়াও, অন্যান্য সমাধানগুলি ব্যবহার করেও পরীক্ষাগুলি পরিচালনা করার জন্য সময় প্রয়োজন, তাই আমরা সিদ্ধান্ত নিয়েছি যে আমরা প্রথমে এটিতে সমস্যাটি সমাধান করার চেষ্টা করব এবং যদি আমরা বুঝতে পারি যে আমরা এটি একটি যুক্তিসঙ্গত সময়ে করতে পারি না, তাহলে আমরা অ্যানালগগুলি বিবেচনা করা শুরু করব৷

তথ্যও

আমাদের নিজস্ব অভিজ্ঞতার ভিত্তিতে আমরা কী সুপারিশ করতে পারি:

1. আপনার ফোলা নিরীক্ষণ. মনিটরিং ডেটার উপর ভিত্তি করে, আপনি বুঝতে সক্ষম হবেন যে অটোভ্যাকুয়াম কতটা ভাল সুর করা হয়েছে।
2. একটি গ্রহণযোগ্য স্তরে ফোলা রাখতে AUTOVACUUM সেট করুন।
3. যদি ফোলাটি এখনও বাড়তে থাকে এবং আপনি বাক্সের বাইরের সরঞ্জামগুলির সাথে এটি মোকাবেলা করতে না পারেন তবে বাহ্যিক এক্সটেনশনগুলি ব্যবহার করতে ভয় পাবেন না। প্রধান জিনিস সবকিছু ভাল পরীক্ষা করা হয়।
4. আপনার প্রয়োজন অনুসারে বাহ্যিক সমাধানগুলি সংশোধন করতে ভয় পাবেন না - কখনও কখনও এটি আপনার নিজের কোড পরিবর্তন করার চেয়ে আরও দক্ষ এবং এমনকি সহজ হতে পারে।

উত্স: www.habr.com