۱۰ اشتباه رایج PostgreSQL و نحوه اجتناب از آنها

PostgreSQL برای رسیدگی به طیف وسیعی از موارد استفاده ساخته شده است، اما انعطاف پذیری یک طرف دیگر نیز دارد. مطمئن شوید که این اشتباهات بسیار رایج در طراحی، پیکربندی، تنظیم و سایر اشتباهات را مرتکب نمی شوید.

• اشتباه شماره ۱: اجرای پیکربندی پیش‌فرض
• اشتباه شماره ۲: طراحی و معماری پایگاه داده بهینه نشده
• اشتباه شماره ۳: تنظیم نکردن پایگاه داده برای حجم کاری
• اشتباه شماره ۴: مدیریت نادرست اتصال
• اشتباه شماره ۵: جاروبرقی به درستی کار نمی کند
• اشتباه شماره ۶: ارتباطات سرکش و تراکنش های طولانی مدت
• اشتباه شماره ۷: نمایه سازی بیش از حد یا زیر نمایه سازی
• اشتباه شماره ۸: پشتیبان گیری ناکافی و HA
• اشتباه شماره ۹: مدیریت نادرست برنامه های افزودنی
• اشتباه شماره ۱۰: نادیده گرفتن ابزارهای پشتیبانی

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

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

اشتباه شماره ۱: اجرای پیکربندی پیش فرض

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

ابزار pgtune زیرمجموعه‌ای از پیکربندی‌ها را بر اساس منابع سخت‌افزاری و نوع حجم کار ارائه می‌دهد. این نقطه شروع خوبی برای پیکربندی خوشه PostgreSQL بر اساس حجم کاری شما است. علاوه بر این، ممکن است مجبور باشید متغیرهای نگهداری خودکار، گزارش، نقطه بازرسی و WAL (گزارش پیش‌نویس) را پیکربندی کنید.

این واقعا مهم است که سرور شما برای هر نیاز فوری آینده پیکربندی شده باشد تا از راه اندازی مجدد غیر ضروری جلوگیری شود. بنابراین در نمای کاتالوگ pg_settings به همه GUCها با زمینه “پست مستر” نگاهی بیندازید.

SELECT name, setting, boot_val
FROM   pg_settings
WHERE  context = 'postmaster';

این امر مخصوصاً هنگام راه‌اندازی یک خوشه با دسترسی بالا (HA) بسیار مهم است زیرا هرگونه خرابی برای سرور اصلی باعث کاهش خوشه می‌شود و باعث ارتقای یک سرور آماده به کار به نقش سرور اصلی می‌شود.

اشتباه شماره ۲: طراحی و معماری پایگاه داده بهینه نشده

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

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

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

فراتر از ORMها، بهبود معماری پایگاه داده شما در مورد ساختاردهی داده ها است به طوری که عملیات خواندن و نوشتن شما برای شاخص ها و همچنین روابط بهینه باشد. یکی از روش‌هایی که می‌تواند کمک کند، غیرعادی کردن پایگاه داده است، زیرا این امر پیچیدگی پرس و جوی SQL و پیوندهای مرتبط را کاهش می‌دهد، بنابراین ممکن است داده‌ها را از روابط کمتری واکشی کنید.

در پایان، عملکرد توسط یک فرآیند سه مرحله‌ای ساده «تعریف، اندازه‌گیری و بهینه‌سازی» در محیط شما برای برنامه و حجم کاری شما هدایت می‌شود.

اشتباه شماره ۳: تنظیم نکردن پایگاه داده برای حجم کاری

تنظیم حجم کاری به اطلاعاتی در مورد میزان داده‌هایی که می‌خواهید ذخیره کنید، ماهیت برنامه، و نوع جستارهایی که باید اجرا شوند نیاز دارد. همیشه می‌توانید تنظیمات خود را تنظیم و محک بزنید تا زمانی که از مصرف منابع در یک بار شدید راضی باشید.

به عنوان مثال، آیا کل پایگاه داده شما می تواند در RAM موجود دستگاه شما قرار گیرد؟ اگر بله، پس بدیهی است که می خواهید مقدار shared_buffers را برای آن افزایش دهید. به طور مشابه، درک حجم کار برای نحوه پیکربندی ایست بازرسی و فرآیندهای خلاء خودکار کلیدی است. به‌عنوان مثال، شما این موارد را برای یک بار کاری فقط پیوستی در مقایسه با حجم کاری پردازش تراکنش آنلاین ترکیبی که با معیار شورای عملکرد پردازش تراکنش نوع C مطابقت دارد، بسیار متفاوت پیکربندی خواهید کرد.

ابزارهای مفید زیادی وجود دارد که بینش عملکرد پرس و جو را ارائه می دهد. می‌توانید پست وبلاگ من را بررسی کنید بینش عملکرد را جستجو کنید، که برخی از گزینه‌های منبع باز موجود را مورد بحث قرار می‌دهد، یا به ارائه من در YouTube مراجعه کنید< /a>.

• PMM – نظارت و مدیریت Percona یک پروژه رایگان و کاملاً متن باز که یک رابط گرافیکی با آمار دقیق سیستم و تجزیه و تحلیل پرس و جو فراهم می کند. نمونه نمایشی PMM را که به MySQL، MongoDB، و PostgreSQL پاسخ می دهد، بی زحمت امتحان کنید.
• pg_stat_monitor – این نسخه پیشرفته‌تری از pg_stat_statements است که اطلاعات دقیق‌تری در مورد الگوهای عملکرد پرس‌وجو ارائه می‌کند. طرح پرس و جو و متن پرس و جو با مقادیر پارامتر. در لینوکس از صفحه دانلود ما یا به عنوان بسته های RPM از مخازن یام جامعه PostgreSQL در دسترس است.

اشتباه شماره ۴: مدیریت نادرست اتصال

پیکربندی اتصالات در نگاه اول بی ضرر به نظر می رسد. با این حال، من مواردی را دیده‌ام که مقدار بسیار زیادی برای max_connections باعث خطاهای حافظه شده است. بنابراین پیکربندی max_connection نیاز به توجه دارد.

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

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

اشتباه شماره ۵: جاروبرقی به درستی کار نمی کند

امیدواریم که autovacuum را غیرفعال نکرده باشید. ما در بسیاری از محیط‌های تولید دیده‌ایم که کاربران معمولاً به دلیل برخی مشکلات اساسی، خلاء خودکار را به طور کلی غیرفعال کرده‌اند. اگر جارو برقی واقعاً در محیط شما کار نمی کند، فقط سه دلیل برای آن وجود دارد:

1. فرآیند خلاء راه‌اندازی نمی‌شود، یا حداقل آن‌طور که باید تکرار نمی‌شود.
2. جاروبرقی خیلی کند است.
3. جاروبرقی ردیف‌های مرده را پاک نمی‌کند.

هر دو ۱ و ۲ مستقیماً با گزینه های پیکربندی مرتبط هستند. با جستجو در نمای pg_settings می توانید گزینه های مربوط به خلاء را مشاهده کنید.

 
SELECT  name
        , short_desc
        , setting
        , unit
        , CASE
            WHEN context = 'postmaster' THEN 'restart'
            WHEN context = 'sighup'     THEN 'reload'
            ELSE context
          END "server requires"
FROM    pg_settings
WHERE   name LIKE '%vacuum%';

سرعت را می توان با تنظیم autovacuum_work_mem و تعداد کارگران موازی به طور بالقوه بهبود بخشید. راه اندازی فرآیند خلاء ممکن است از طریق پیکربندی فاکتورهای مقیاس یا آستانه تنظیم شود.

وقتی فرآیند خلاء تاپل‌های مرده را پاک نمی‌کند، نشان‌دهنده این است که چیزی منابع کلیدی را متوقف می‌کند. مقصر می تواند یک یا چند مورد از این موارد باشد:

• پرسش‌ها یا تراکنش‌های طولانی مدت.
• سرورهای آماده به کار در یک محیط تکرار با گزینه hot_standby_feedback روشن است.
• مقدار vacuum_defer_cleanup_age بزرگتر از مقدار لازم.
• شاخه‌های تکراری که مقدار xmin را پایین نگه می‌دارند و از تمیز کردن تاپل‌های مرده توسط جاروبرقی جلوگیری می‌کنند.

اگر می خواهید خلاء یک رابطه را به صورت دستی مدیریت کنید، قانون پارتو (معروف به قانون ۲۰/۸۰) را دنبال کنید. خوشه را روی یک پیکربندی بهینه تنظیم کنید و سپس به طور خاص برای آن چند جدول تنظیم کنید. به یاد داشته باشید که autovacuum یا toast.autovacuum ممکن است برای یک رابطه خاص با مشخص کردن گزینه ذخیره سازی مرتبط در طی دستور ایجاد یا تغییر غیرفعال شود.

اشتباه شماره ۶: ارتباطات سرکش و تراکنش های طولانی مدت

تعدادی از چیزها می توانند خوشه PostgreSQL شما را گروگان نگه دارند، و اتصالات سرکش یکی از آنهاست. به غیر از نگه داشتن اسلات های اتصال که می توانند توسط سایر برنامه ها استفاده شوند، اتصالات سرکش و تراکنش های طولانی مدت منابع کلیدی را نگه می دارند که می توانند کل سیستم را ویران کنند. تا حدی کمتر، در یک محیط تکثیر که hot_standby_feedback روشن است، تراکنش‌های طولانی‌مدت در حالت آماده‌باش ممکن است مانع از انجام خلاء در سرور اصلی شود.

یک برنامه باگ را در نظر بگیرید که تراکنش را باز می کند و پس از آن دیگر پاسخ نمی دهد. ممکن است قفل ها را نگه داشته باشد یا به سادگی مانع از تمیز کردن تاپل های مرده توسط جاروبرقی شود زیرا در چنین تراکنش هایی قابل مشاهده هستند. اگر آن برنامه تعداد زیادی از این تراکنش ها را باز کند چه می شود؟

اغلب اوقات، می‌توانید با پیکربندی idle_in_transaction_session_timeout روی مقداری که برای درخواست‌های شما تنظیم شده است، از شر چنین تراکنش‌هایی خلاص شوید. البته، هر زمان که شروع به تنظیم پارامتر کردید، همیشه رفتار برنامه خود را در نظر داشته باشید.

فراتر از تنظیم idle_in_transaction_session_timeout، pg_stat_activity را برای پرس و جوهای طولانی مدت یا هر جلسه ای که بیشتر از زمان مورد انتظار منتظر رویدادهای مرتبط با سرویس گیرنده است، نظارت کنید. مراقب مُهرهای زمانی، رویدادهای انتظار، و ستون‌های وضعیت باشید.

 
backend_start    | ۲۰۲۲-۱۰-۲۵ ۰۹:۲۵:۰۷.۹۳۴۶۳۳+۰۰
xact_start       | ۲۰۲۲-۱۰-۲۵ ۰۹:۲۵:۱۱.۲۳۸۰۶۵+۰۰
query_start      | ۲۰۲۲-۱۰-۲۵ ۰۹:۲۵:۱۱.۲۳۸۰۶۵+۰۰
state_change     | ۲۰۲۲-۱۰-۲۵ ۰۹:۲۵:۱۱.۲۳۸۳۸۱+۰۰
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction

غیر از اینها، تراکنش های آماده شده (مخصوصاً تراکنش های آماده شده یتیم) همچنین می توانند در منابع کلیدی سیستم (قفل ها یا مقدار xmin) نگهداری شوند. من توصیه می کنم یک نامگذاری برای معاملات آماده تنظیم کنید تا سن آنها را مشخص کنید. مثلاً، یک تراکنش آماده شده با حداکثر سن ۵ دقیقه ممکن است به عنوان PREPARE TRANSACTION 'foo_prepared 5m' ایجاد شود.

 
SELECT  gid
        , prepared
        , REGEXP_REPLACE(gid, '.* ', '') AS age
FROM    pg_prepared_xacts
WHERE   prepared + CAST(regexp_replace(gid, '.* ', '') AS INTERVAL) < NOW();

این طرحی را برای برنامه‌ها فراهم می‌کند تا سن تراکنش‌های آماده‌شده خود را تعریف کنند. یک cronjob یا یک کار زمان‌بندی‌شده می‌تواند تراکنش‌های آماده‌شده‌ای را که بیش از سن مورد نظرشان فعال باقی می‌مانند، نظارت کرده و به عقب برگرداند.

اشتباه شماره ۷: نمایه سازی بیش از حد یا زیر نمایه سازی

مطمئناً هیچ اشکالی در فهرست کردن بیش از حد یک رابطه وجود ندارد. یا وجود دارد؟ برای به دست آوردن بهترین عملکرد از نمونه PostgreSQL خود، ضروری است که بدانید PostgreSQL چگونه فهرست ها را مدیریت می کند.

در PostgreSQL چندین نوع نمایه وجود دارد. هرکدام کاربرد متفاوتی دارند و هرکدام هزینه های سربار خاص خود را دارند. B-tree رایج ترین نوع شاخص مورد استفاده است. برای کلیدهای اصلی نیز استفاده می شود. چند نسخه اصلی گذشته شاهد بهبودهای زیادی در رابطه با عملکرد (و ناامیدکننده) در شاخص های B-tree بوده است. اینجا یکی از است. پست های وبلاگ من که در مورد تغییرات نسخه تکراری در PostgreSQL 14 بحث می کند.

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

به طور مشابه، زیر نمایه سازی باعث اسکن هیپ بیشتر می شود، که به طور بالقوه منجر به عملیات ورودی/خروجی بیشتر و در نتیجه کاهش عملکرد می شود.

نمایه سازی فقط به تعداد نمایه هایی که در یک رابطه دارید نیست. میزان بهینه سازی آن شاخص ها برای موارد استفاده مورد نظر است. در حالت ایده‌آل، می‌خواهید هر بار یک اسکن فقط فهرستی انجام دهید، اما محدودیت‌هایی وجود دارد. اگرچه نمایه‌های درخت B از اسکن‌های فقط شاخص برای همه اپراتورها پشتیبانی می‌کنند، اندیس‌های GiST و SP-GiST فقط برای برخی از اپراتورها از آن‌ها پشتیبانی می‌کنند. برای جزئیات بیشتر به اسناد مراجعه کنید.

پیروی از یک چک لیست ساده می تواند به شما کمک کند تا تأیید کنید که سیستم شما برای نمایه ها بهینه تنظیم شده است:

• مطمئن شوید که پیکربندی به درستی تنظیم شده است (به عنوان مثال، هزینه صفحه تصادفی برای سخت افزار شما تنظیم شده است).
• بررسی کنید که آمار به روز باشد یا حداقل دستورات تجزیه و تحلیل یا خلاء بر روی روابط با شاخص ها اجرا شوند. این اطمینان حاصل می کند که آمار کم و بیش به روز است تا برنامه ریز احتمال بیشتری برای انتخاب اسکن فهرست داشته باشد.
• نوع مناسب شاخص (B-tree، hash یا نوع دیگری) را ایجاد کنید.
• از نمایه ها در ستون سمت راست استفاده کنید. فراموش نکنید که برای جلوگیری از دسترسی به پشته، ستون‌های غیر نمایه‌سازی شده را اضافه کنید. همه انواع نمایه اجازه پوشش نمایه ها را نمی دهند، بنابراین اسناد را بررسی کنید.
• از شر ایندکس های غیر ضروری خلاص شوید. به pg_statio_user_indexes مراجعه کنید بینش بیشتر در مورد نمایه ها و بازدیدهای بلوک.
• تأثیر پوشش نمایه‌ها بر ویژگی‌هایی مانند کپی‌برداری، تکرار نسخه‌های تکراری و اسکن‌های فقط فهرست را درک کنید.

برای پرس و جوهای مفیدتر به این صفحه ویکی نگهداری فهرست مراجعه کنید.

اشتباه شماره ۸: پشتیبان گیری ناکافی و HA

HA فقط به کار نگه داشتن یک سرویس و راه اندازی آن نیست. همچنین در مورد حصول اطمینان از اینکه سرویس در چارچوب معیارهای پذیرش تعریف شده پاسخ می دهد و اهداف RPO (هدف نقطه بازیابی) و RTO (هدف زمان بازیابی) را برآورده می کند. برای مطابقت با الزامات آپتایم و تعداد ۹ موردی که هدف قرار داده اید، به این صفحه ویکی مراجعه کنید. برای محاسبات درصد.

• PostgreSQL
• پایگاه داده
• پایگاه‌های داده رابطه‌ای< /a>
• توسعه نرم افزار< /a>
• رایانش ابری< /a>