Postgres: bloat، pg_repack و محدودیت‌های معوق

اثر نفخ روی جداول و نمایه ها به طور گسترده ای شناخته شده است و نه تنها در Postgres وجود دارد. راه هایی برای مقابله با آن خارج از جعبه وجود دارد، مانند VACUUM FULL یا CLUSTER، اما میزها را در حین کار قفل می کنند و بنابراین همیشه نمی توان از آنها استفاده کرد.

این مقاله حاوی تئوری کوچکی در مورد چگونگی ایجاد نفخ، نحوه مبارزه با آن، محدودیت‌های معوق و مشکلاتی است که آنها برای استفاده از پسوند pg_repack ایجاد می‌کنند.

این مقاله بر اساس نوشته شده است سخنرانی من در PgConf.Russia 2020.

چرا نفخ رخ می دهد؟

Postgres بر اساس یک مدل چند نسخه (MVCC). ماهیت آن این است که هر ردیف در جدول می تواند چندین نسخه داشته باشد، در حالی که تراکنش ها بیش از یکی از این نسخه ها را نمی بینند، اما لزوماً همان نسخه را ندارند. این اجازه می دهد تا چندین تراکنش به طور همزمان کار کنند و عملاً هیچ تأثیری بر یکدیگر نداشته باشند.

بدیهی است که تمام این نسخه ها باید ذخیره شوند. Postgres با حافظه صفحه به صفحه کار می کند و یک صفحه حداقل مقدار داده ای است که می توان از روی دیسک خواند یا نوشت. بیایید به یک مثال کوچک نگاه کنیم تا بفهمیم چگونه این اتفاق می افتد.

فرض کنید جدولی داریم که چندین رکورد به آن اضافه کرده ایم. داده های جدید در صفحه اول فایل که جدول در آن ذخیره می شود ظاهر شده است. اینها نسخه‌های زنده ردیف‌هایی هستند که پس از یک commit برای سایر تراکنش‌ها در دسترس هستند (برای سادگی، سطح جداسازی را Read Committed فرض می‌کنیم).

سپس یکی از ورودی‌ها را به‌روزرسانی کردیم، در نتیجه نسخه قدیمی را به‌عنوان نامربوط علامت‌گذاری کردیم.

گام به گام، با به‌روزرسانی و حذف نسخه‌های ردیف، به صفحه‌ای رسیدیم که تقریباً نیمی از داده‌ها «زباله» است. این داده ها برای هیچ تراکنش قابل مشاهده نیستند.

Postgres یک مکانیسم دارد واکسن، که نسخه های منسوخ را پاک می کند و فضا را برای داده های جدید باز می کند. اما اگر به اندازه کافی تهاجمی پیکربندی نشده باشد یا مشغول کار در جداول دیگر باشد، "اطلاعات زباله" باقی می ماند و ما باید از صفحات اضافی برای داده های جدید استفاده کنیم.

بنابراین در مثال ما، در یک نقطه از زمان جدول از چهار صفحه تشکیل شده است، اما تنها نیمی از آن حاوی داده های زنده است. در نتیجه، هنگام دسترسی به جدول، داده های بسیار بیشتری از آنچه لازم است می خوانیم.

حتی اگر VACUUM اکنون تمام نسخه های ردیف نامربوط را حذف کند، وضعیت به طور چشمگیری بهبود نخواهد یافت. فضای خالی در صفحات یا حتی کل صفحات برای ردیف‌های جدید خواهیم داشت، اما همچنان اطلاعات بیشتری از آنچه لازم است می‌خوانیم.
به هر حال، اگر یک صفحه کاملاً خالی (نمونه دوم در مثال ما) در انتهای فایل باشد، VACUUM می تواند آن را برش دهد. اما حالا او در وسط است، بنابراین هیچ کاری نمی توان با او کرد.

هنگامی که تعداد صفحات خالی یا بسیار پراکنده زیاد می شود که به آن bloat می گویند، شروع به تأثیر بر عملکرد می کند.

هر آنچه در بالا توضیح داده شد مکانیزم وقوع نفخ در جداول است. در نمایه ها این اتفاق تقریباً به همین صورت است.

آیا من نفخ دارم؟

راه های مختلفی برای تشخیص نفخ وجود دارد. ایده اول استفاده از آمار داخلی Postgres است که حاوی اطلاعات تقریبی در مورد تعداد ردیف های جداول، تعداد ردیف های "زنده" و غیره است. می توانید انواع مختلفی از اسکریپت های آماده را در اینترنت پیدا کنید. مبنا قرار دادیم اسکریپت از PostgreSQL Experts، که می تواند جداول bloat را به همراه شاخص های toast و bloat btree ارزیابی کند. در تجربه ما، خطای آن 10-20٪ است.

راه دیگر استفاده از پسوند است pgstattuple، که به شما امکان می دهد به داخل صفحات نگاه کنید و هم مقدار تخمینی و هم یک مقدار دقیق bloat را بدست آورید. اما در حالت دوم، باید کل جدول را اسکن کنید.

ما یک مقدار نفخ کوچک، تا 20٪ را قابل قبول در نظر می گیریم. می توان آن را به عنوان آنالوگ فیل فاکتور برای در نظر گرفت میزها и شاخص ها. در 50٪ و بالاتر، مشکلات عملکرد ممکن است شروع شود.

راه های مبارزه با نفخ

Postgres چندین راه برای مقابله با نفخ خارج از جعبه دارد، اما همیشه برای همه مناسب نیست.

AUTOVACUUM را طوری پیکربندی کنید که نفخ رخ ندهد. یا به طور دقیق تر، آن را در سطح قابل قبول خود نگه دارید. به نظر می رسد این توصیه "کاپیتان" است، اما در واقعیت همیشه به راحتی نمی توان به آن دست یافت. به عنوان مثال، شما توسعه فعالی با تغییرات منظم در طرح داده دارید، یا نوعی انتقال داده در حال انجام است. در نتیجه، نمایه بار شما ممکن است اغلب تغییر کند و معمولاً از جدولی به جدول دیگر متفاوت است. این بدان معنی است که شما باید دائماً کمی جلوتر کار کنید و AUTOVACUUM را با مشخصات متغیر هر میز تنظیم کنید. اما بدیهی است که انجام این کار آسان نیست.

یکی دیگر از دلایل متداول که چرا AUTOVACUUM نمی تواند با جداول همراه شود این است که تراکنش های طولانی مدت وجود دارد که از پاک کردن داده های موجود در آن تراکنش ها جلوگیری می کند. توصیه در اینجا نیز واضح است - از شر معاملات "آویزان" خلاص شوید و زمان تراکنش های فعال را به حداقل برسانید. اما اگر بار روی برنامه شما ترکیبی از OLAP و OLTP باشد، می توانید همزمان به روز رسانی های مکرر و پرس و جوهای کوتاه و همچنین عملیات طولانی مدت داشته باشید - به عنوان مثال، ایجاد یک گزارش. در چنین شرایطی، باید به فکر پخش بار در پایه های مختلف بود که امکان تنظیم دقیق هر یک از آنها را فراهم می کند.

مثال دیگر - حتی اگر نمایه همگن باشد، اما پایگاه داده تحت بار بسیار بالایی قرار دارد، در این صورت حتی تهاجمی ترین AUTOVACUUM نیز ممکن است با آن مقابله نکند و نفخ رخ دهد. مقیاس بندی (عمودی یا افقی) تنها راه حل است.

در شرایطی که AUTOVACUUM را راه اندازی کرده اید، اما نفخ همچنان در حال رشد است، چه باید کرد.

تیم VACUUM FULL محتویات جداول و نمایه ها را بازسازی می کند و فقط داده های مرتبط را در آنها باقی می گذارد. برای از بین بردن bloat، کاملاً کار می کند، اما در حین اجرای آن یک قفل انحصاری روی میز گرفته می شود (AccessExclusiveLock) که اجازه اجرای کوئری ها در این جدول را نمی دهد، حتی انتخاب می کند. اگر می توانید برای مدتی (از ده ها دقیقه تا چند ساعت بسته به اندازه پایگاه داده و سخت افزار خود) سرویس یا بخشی از آن را متوقف کنید، این گزینه بهترین است. متأسفانه، ما زمان اجرای VACUUM FULL را در طول تعمیر و نگهداری برنامه ریزی شده نداریم، بنابراین این روش برای ما مناسب نیست.

تیم خوشه محتویات جداول را به همان روش VACUUM FULL بازسازی می کند، اما به شما امکان می دهد شاخصی را مشخص کنید که براساس آن داده ها به صورت فیزیکی روی دیسک مرتب شوند (اما در آینده ترتیب برای ردیف های جدید تضمین نمی شود). در شرایط خاص، این یک بهینه سازی خوب برای تعدادی پرس و جو است - با خواندن چندین رکورد بر اساس شاخص. نقطه ضعف فرمان مانند VACUUM FULL است - میز را در حین کار قفل می کند.

تیم REINDEX مشابه دو مورد قبلی است، اما یک نمایه خاص یا تمام نمایه های جدول را بازسازی می کند. قفل‌ها کمی ضعیف‌تر هستند: ShareLock روی جدول (از تغییرات جلوگیری می‌کند، اما امکان انتخاب را می‌دهد) و AccessExclusiveLock در نمایه‌ای که بازسازی می‌شود (پرس‌وجوها را با استفاده از این فهرست مسدود می‌کند). با این حال، در نسخه دوازدهم Postgres یک پارامتر ظاهر شد به طور همزمان، که به شما امکان می دهد فهرست را بدون مسدود کردن اضافه کردن، تغییر یا حذف همزمان رکوردها بازسازی کنید.

در نسخه‌های قبلی Postgres، می‌توانید با استفاده همزمان به نتیجه‌ای مشابه با REINDEX برسید. ایجاد شاخص به طور همزمان. این به شما امکان می دهد یک نمایه بدون قفل سخت ایجاد کنید (ShareUpdateExclusiveLock، که با پرس و جوهای موازی تداخلی ندارد)، سپس فهرست قدیمی را با یک نمایه جدید جایگزین کنید و فهرست قدیمی را حذف کنید. این به شما امکان می دهد نفخ شاخص را بدون تداخل با برنامه خود از بین ببرید. در نظر گرفتن این نکته مهم است که هنگام بازسازی ایندکس ها بار اضافی روی زیرسیستم دیسک وجود خواهد داشت.

بنابراین، اگر برای شاخص‌ها راه‌هایی برای از بین بردن نفخ «در حال پرواز» وجود داشته باشد، برای جداول هیچ راه‌حلی وجود ندارد. اینجاست که برنامه‌های افزودنی خارجی مختلف وارد عمل می‌شوند: pg_repack (قبلا pg_reorg)، pgcompact, pgcompactable و دیگران. در این مقاله من آنها را با هم مقایسه نمی کنم و فقط در مورد pg_repack صحبت می کنم که پس از تغییراتی، خودمان از آن استفاده می کنیم.

pg_repack چگونه کار می کند

بیایید بگوییم که ما یک جدول کاملاً معمولی داریم - با شاخص ها، محدودیت ها و، متأسفانه، با نفخ. اولین مرحله از pg_repack ایجاد یک جدول گزارش برای ذخیره داده‌های مربوط به همه تغییرات در حین اجرای آن است. ماشه این تغییرات را برای هر درج، به‌روزرسانی و حذف تکرار می‌کند. سپس جدولی درست می شود که از نظر ساختاری مشابه جدول اصلی است، اما بدون فهرست و محدودیت، تا روند درج داده ها کند نشود.

در مرحله بعد، pg_repack داده ها را از جدول قدیمی به جدول جدید منتقل می کند، به طور خودکار تمام ردیف های نامربوط را فیلتر می کند و سپس برای جدول جدید فهرست هایی ایجاد می کند. در طول اجرای تمام این عملیات، تغییرات در جدول گزارش جمع می شود.

مرحله بعدی انتقال تغییرات به جدول جدید است. مهاجرت طی چندین بار تکرار انجام می شود و زمانی که کمتر از 20 ورودی در جدول log باقی می ماند، 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 ایجاد شد. بر اساس ویژگی های موجود در ترکیب آن، محدودیت "ما" را تعیین کردیم که با آن مطابقت دارد. مشکل این بود که این یک محدودیت کاملا معمولی نیست، بلکه یک محدودیت معوق است (محدودیت معوق) یعنی تأیید آن دیرتر از دستور 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، اولین ورودی به طور خودکار بین دوم و سوم قرار می گیرد. این راه حل کار می کند، اما دو محدودیت وجود دارد. اول، اگر مقدار در جایی در رابط استفاده شود، برای شما کار نخواهد کرد. دوم، بسته به دقت نوع داده، قبل از محاسبه مجدد مقادیر همه رکوردها، تعداد محدودی از درج های ممکن را خواهید داشت.

گزینه سه: محدودیت را به تعویق بیاندازید تا فقط در زمان commit بررسی شود:

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

از آنجایی که منطق درخواست اولیه ما تضمین می کند که همه مقادیر در زمان commit منحصر به فرد هستند، موفق خواهد شد.

مثال مورد بحث در بالا، البته، بسیار مصنوعی است، اما این ایده را آشکار می کند. در برنامه ما، از محدودیت‌های معوق برای پیاده‌سازی منطقی استفاده می‌کنیم که مسئول حل تضادها است، زمانی که کاربران به طور همزمان با اشیاء ویجت مشترک روی برد کار می‌کنند. استفاده از چنین محدودیت هایی به ما اجازه می دهد تا کد برنامه را کمی ساده تر کنیم.

به طور کلی، بسته به نوع محدودیت، Postgres دارای سه سطح دانه بندی برای بررسی آنها است: سطوح ردیف، تراکنش و سطح بیان.

منبع: طلبکاران

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.

زمانی اتفاق می‌افتد که داده‌ها از یک جدول گزارش به یک جدول جدید کپی می‌شوند. این عجیب به نظر می رسد زیرا ... داده های جدول log همراه با داده های جدول منبع تعهد می شود. اگر آنها محدودیت های جدول اصلی را برآورده کنند، چگونه می توانند همان محدودیت ها را در جدول جدید نقض کنند؟

همانطور که مشخص شد، ریشه مشکل در مرحله قبلی pg_repack نهفته است، که فقط شاخص‌ها را ایجاد می‌کند، اما محدودیت‌ها را ایجاد نمی‌کند: جدول قدیمی یک محدودیت منحصربه‌فرد داشت و جدول جدید به جای آن یک شاخص منحصربه‌فرد ایجاد کرد.

در اینجا ذکر این نکته ضروری است که اگر محدودیت نرمال باشد و به تعویق نیفتد، پس شاخص منحصر به فرد ایجاد شده در عوض معادل این محدودیت است، زیرا محدودیت های منحصر به فرد در Postgres با ایجاد یک شاخص منحصر به فرد پیاده سازی می شوند. اما در مورد یک محدودیت معوق، رفتار یکسان نیست، زیرا شاخص قابل تعویق نیست و همیشه در زمان اجرای دستور sql بررسی می شود.

بنابراین، ماهیت مشکل در "تاخیر" چک است: در جدول اصلی در زمان commit رخ می دهد، و در جدول جدید در زمان اجرای دستور sql. این بدان معنی است که ما باید مطمئن شویم که بررسی ها در هر دو مورد یکسان انجام می شود: یا همیشه با تأخیر، یا همیشه بلافاصله.

پس چه ایده هایی داشتیم؟

یک نمایه شبیه به deferred ایجاد کنید

ایده اول این است که هر دو بررسی را در حالت فوری انجام دهید. این ممکن است چندین محدودیت مثبت کاذب ایجاد کند، اما اگر تعداد کمی از آنها وجود داشته باشد، این نباید بر کار کاربران تأثیر بگذارد، زیرا چنین درگیری هایی برای آنها یک وضعیت عادی است. برای مثال زمانی که دو کاربر همزمان شروع به ویرایش یک ویجت می کنند و مشتری کاربر دوم زمانی برای دریافت اطلاعاتی که ویجت قبلاً برای ویرایش توسط کاربر اول مسدود شده است را ندارد. در چنین شرایطی، سرور کاربر دوم را رد می کند و کلاینت آن تغییرات را برمی گرداند و ویجت را مسدود می کند. کمی بعد، وقتی کاربر اول ویرایش را کامل کرد، کاربر دوم اطلاعاتی دریافت می کند که ویجت دیگر مسدود نیست و می تواند عمل خود را تکرار کند.

برای اطمینان از اینکه چک ها همیشه در حالت غیر معوق هستند، یک شاخص جدید مشابه محدودیت اصلی معوق ایجاد کردیم:

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

در محیط تست، ما فقط چند خطای مورد انتظار را دریافت کردیم. موفقیت! ما دوباره pg_repack را در زمان تولید اجرا کردیم و در یک ساعت کار، 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 همیشه در اولین درج خراب می شد، نسخه اصلاح شده بدون خطا کار می کرد. عالی.

ما به سمت تولید می رویم و دوباره در همان مرحله کپی داده ها از جدول گزارش به جدول جدید با خطا مواجه می شویم:

$ ./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 رکورد است، که توضیح می‌دهد که چرا آزمایش‌های ما موفقیت‌آمیز بوده است - آنها مورد «تقاطع دسته‌ای» را پوشش نمی‌دهند. ما از دو دستور - insert و update استفاده کردیم، بنابراین دقیقاً 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 دقیقاً همینطور کار می کرد). اما یک مشکل وجود دارد - ما در حال ایجاد یک معامله طولانی مدت هستیم و این، همانطور که قبلاً گفته شد، تهدیدی برای ظهور یک نفخ جدید است.

راه حل دوم پیچیده تر است، اما احتمالاً صحیح تر است: ایجاد یک ستون در جدول گزارش با شناسه تراکنش که داده ها را به جدول اضافه می کند. سپس، وقتی داده‌ها را کپی می‌کنیم، می‌توانیم آن‌ها را با این ویژگی گروه‌بندی کنیم و اطمینان حاصل کنیم که تغییرات مرتبط با هم منتقل می‌شوند. دسته از چندین تراکنش (یا یک تراکنش بزرگ) تشکیل می شود و اندازه آن بسته به میزان تغییر داده در این تراکنش ها متفاوت خواهد بود. توجه به این نکته ضروری است که از آنجایی که داده‌های تراکنش‌های مختلف به صورت تصادفی وارد جدول گزارش می‌شوند، دیگر مانند قبل، خواندن متوالی آن امکان‌پذیر نخواهد بود. seqscan برای هر درخواست با فیلتر کردن توسط tx_id بسیار گران است، یک شاخص مورد نیاز است، اما همچنین به دلیل سربار به روز رسانی آن، روش را کند می کند. به طور کلی، مثل همیشه، شما باید چیزی را قربانی کنید.

بنابراین، تصمیم گرفتیم با گزینه اول شروع کنیم، زیرا ساده تر است. اول، لازم بود درک کنیم که آیا یک معامله طولانی یک مشکل واقعی خواهد بود یا خیر. از آنجایی که انتقال اصلی داده ها از جدول قدیمی به جدول جدید نیز در یک تراکنش طولانی انجام می شود، این سوال به این تبدیل شد که "این تراکنش را چقدر افزایش خواهیم داد؟" مدت زمان اولین تراکنش عمدتاً به اندازه جدول بستگی دارد. مدت زمان یک جدید بستگی به تعداد تغییراتی دارد که در طول انتقال داده در جدول جمع می شود، یعنی. بر شدت بار اجرای pg_repack در زمان حداقل بار سرویس رخ داد و حجم تغییرات در مقایسه با اندازه اصلی جدول به طور نامتناسبی کم بود. ما تصمیم گرفتیم که می توانیم زمان یک تراکنش جدید را نادیده بگیریم (برای مقایسه، به طور متوسط ​​1 ساعت و 2-3 دقیقه است).

آزمایشات مثبت بود. راه اندازی در تولید بیش از حد. برای وضوح تصویری به اندازه یکی از پایگاه های داده پس از اجرا در اینجا آمده است:

از آنجایی که ما از این راه حل کاملا راضی بودیم، سعی نکردیم راه حل دوم را پیاده سازی کنیم، اما در حال بررسی امکان گفتگو با توسعه دهندگان برنامه های افزودنی هستیم. متأسفانه نسخه فعلی ما هنوز برای انتشار آماده نیست، زیرا ما فقط مشکل را با محدودیت های معوق منحصربفرد حل کردیم و برای یک پچ کامل باید از انواع دیگر پشتیبانی کرد. امیدواریم در آینده بتوانیم این کار را انجام دهیم.

شاید برای شما سوال پیش بیاید که چرا ما حتی با اصلاح pg_repack درگیر این داستان شدیم و مثلاً از آنالوگ های آن استفاده نکردیم؟ در مقطعی ما نیز به این موضوع فکر کردیم، اما تجربه مثبت استفاده از آن زودتر، در جداول بدون محدودیت معوق، ما را به تلاش برای درک اصل مشکل و رفع آن ترغیب کرد. علاوه بر این، استفاده از راه حل های دیگر نیز نیاز به زمان برای انجام آزمایش دارد، بنابراین تصمیم گرفتیم که ابتدا سعی کنیم مشکل را در آن برطرف کنیم و اگر متوجه شدیم که نمی توانیم در یک زمان معقول این کار را انجام دهیم، آنگاه شروع به بررسی آنالوگ کنیم. .

یافته ها

آنچه ما می توانیم بر اساس تجربه خود توصیه کنیم:

1. نفخ خود را کنترل کنید بر اساس داده های مانیتورینگ، می توانید بفهمید که خلاء خودکار چقدر خوب پیکربندی شده است.
2. AUTOVACUUM را تنظیم کنید تا نفخ در سطح قابل قبولی حفظ شود.
3. اگر نفخ همچنان در حال رشد است و نمی توانید با استفاده از ابزارهای خارج از جعبه بر آن غلبه کنید، از استفاده از افزونه های خارجی نترسید. نکته اصلی این است که همه چیز را به خوبی آزمایش کنید.
4. از تغییر راه حل های خارجی برای مطابقت با نیازهای خود نترسید - گاهی اوقات این می تواند مؤثرتر و حتی ساده تر از تغییر کد شما باشد.

منبع: www.habr.com