🥇به آمار داخلی PostgreSQL بیایید. الکسی لسوفسکی

رونوشت گزارش الکسی لسوفسکی در سال 2015 "غواصی عمیق در آمار داخلی PostgreSQL"

سلب مسئولیت از نویسنده گزارش: متذکر می شوم که تاریخ این گزارش نوامبر 2015 است - بیش از 4 سال می گذرد و زمان زیادی می گذرد. نسخه 9.4 مورد بحث در گزارش دیگر پشتیبانی نمی شود. در طول 4 سال گذشته، 5 نسخه جدید منتشر شده است که در آنها نوآوری ها، پیشرفت ها و تغییرات زیادی در مورد آمار وجود دارد و برخی از مطالب قدیمی و غیر مرتبط هستند. با مرور سعی کردم این مکان ها را علامت گذاری کنم تا خواننده را گمراه نکنم. من این قسمت ها را بازنویسی نکردم، تعداد آنها زیاد است و نتیجه یک گزارش کاملا متفاوت خواهد بود.

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

عصر بخیر اسم من الکسی است. همانطور که ایلیا گفت، من در مورد آمار PostgreSQL صحبت خواهم کرد.

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

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

چه چیزی در گزارش نخواهد بود؟ در این گزارش به آمار زمانبندی اشاره نمی کنم، زیرا ... این یک موضوع جداگانه برای یک گزارش جداگانه در مورد نحوه ذخیره داده ها در پایگاه داده و چگونگی دریافت ایده برنامه ریز پرس و جو از ویژگی های کیفی و کمی این داده ها است.

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

من می خواهم به شما نشان دهم که استفاده از آمار مفید است. لازم است. استفاده از آن بی خطر است. تنها چیزی که نیاز داریم SQL معمولی و دانش اولیه SQL است.

و بیایید در مورد اینکه چه آماری را برای حل مشکلات انتخاب کنیم صحبت کنیم.

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

ما می توانیم بار CPU را در آن ببینیم top، می توانیم به استفاده از حافظه توسط برخی ابزارهای سیستم نگاه کنیم، اما نمی توانیم داخل PostgreSQL را بررسی کنیم. برای این ما به ابزارهای دیگری نیاز داریم.

و در ادامه به شما خواهم گفت که زمان در کجا صرف شده است. اگر PostgreSQL را در قالب چنین نموداری تصور کنیم، می توانیم پاسخ دهیم که زمان در کجا صرف شده است. اینها دو چیز هستند: پردازش درخواست های مشتری از برنامه ها و وظایف پس زمینه ای است که PostgreSQL انجام می دهد تا خود را در حال اجرا نگه دارد.

اگر شروع به نگاه کردن به گوشه بالا سمت چپ کنیم، می‌توانیم ببینیم که درخواست‌های مشتری چگونه پردازش می‌شوند. درخواست از برنامه می آید و یک جلسه مشتری برای کار بیشتر باز می شود. درخواست به زمانبندی ارسال می شود. زمانبند یک طرح پرس و جو می سازد. برای اجرا بیشتر ارسال می کند. نوعی بلوک ورودی/خروجی داده مرتبط با جداول و نمایه ها وجود دارد. داده های لازم از دیسک ها به حافظه در یک منطقه ویژه "بافرهای مشترک" خوانده می شود. نتایج درخواست، اگر به‌روزرسانی یا حذف شده باشد، در لاگ تراکنش در WAL ثبت می‌شود. برخی از اطلاعات آماری به فهرست یا جمع آوری آمار ختم می شود. و نتیجه درخواست دوباره برای مشتری ارسال می شود. پس از آن مشتری می تواند همه چیز را دوباره با یک درخواست جدید تکرار کند.

در مورد وظایف پس زمینه و فرآیندهای پس زمینه چطور؟ ما چندین فرآیند داریم که پایگاه داده را در حالت عادی فعال نگه می دارد. این فرآیندها نیز در گزارش مورد بررسی قرار خواهند گرفت: autovacuum، checkpointer، فرآیندهای مرتبط با تکرار، پس‌زمینه‌نویس. در حین گزارش به هر یک از آنها خواهم پرداخت.

آمار چه مشکلاتی دارد؟

اطلاعات زیادی وجود دارد. PostgreSQL 9.4 109 معیار را برای مشاهده داده های آماری ارائه می دهد. با این حال، اگر پایگاه داده بسیاری از جداول، طرحواره ها، پایگاه های داده را ذخیره کند، تمام این معیارها باید در تعداد مربوطه جداول، پایگاه داده ضرب شوند. یعنی اطلاعات بیشتر هم هست. و غرق شدن در آن بسیار آسان است.
مشکل بعدی این است که آمار با شمارنده نمایش داده می شود. اگر به این آمار نگاه کنیم، شاهد افزایش مداوم شمارنده ها خواهیم بود. و اگر زمان زیادی از بازنشانی آمار گذشته باشد، مقادیری را به میلیاردها خواهیم دید. و چیزی به ما نمی گویند.
بدون داستان اگر نوعی شکست داشتید، چیزی 15-30 دقیقه پیش سقوط کرد، نمی توانید از آمار استفاده کنید و ببینید 15-30 دقیقه قبل چه اتفاقی افتاده است. این مشکل است.
فقدان ابزاری که در PostgreSQL تعبیه شده است یک مشکل است. توسعه دهندگان هسته هیچ ابزاری ارائه نمی دهند. اونا همچین چیزی ندارن آنها به سادگی آمار را در پایگاه داده ارائه می کنند. از آن استفاده کنید، از آن درخواست کنید، هر کاری می خواهید انجام دهید.
از آنجایی که هیچ ابزاری در PostgreSQL تعبیه نشده است، این باعث ایجاد مشکل دیگری می شود. بسیاری از ابزارهای شخص ثالث. هر شرکتی که دست کم و بیش مستقیم دارد سعی می کند برنامه خود را بنویسد. و در نتیجه، جامعه ابزارهای زیادی دارد که می توان از آنها برای کار با آمار استفاده کرد. و برخی از ابزارها دارای قابلیت های خاصی هستند، ابزارهای دیگر قابلیت های دیگری ندارند و یا برخی از قابلیت های جدید وجود دارد. و شرایطی پیش می آید که شما باید از دو، سه یا چهار ابزار استفاده کنید که روی یکدیگر همپوشانی دارند و عملکردهای متفاوتی دارند. این خیلی ناخوشایند است.

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

و شما نیاز به دانش اولیه SQL دارید. برای به دست آوردن برخی از داده ها از آمار، باید پرس و جوهای SQL ایجاد کنید، یعنی باید بدانید انتخاب و پیوستن چگونه کامپایل می شوند.

آمار چندین چیز را به ما می گوید. آنها را می توان به دسته هایی تقسیم کرد.

دسته اول رویدادهایی هستند که در پایگاه داده رخ می دهند. این زمانی است که برخی از رویدادها در پایگاه داده رخ می دهد: یک درخواست، دسترسی به جدول، autovacuum، commit ها، سپس اینها همه رویدادها هستند. شمارنده های مربوط به این رویدادها افزایش می یابد. و ما می توانیم این رویدادها را دنبال کنیم.
دسته دوم ویژگی های اشیایی مانند جداول و پایگاه های داده است. خواصی دارند. این اندازه جداول است. ما می توانیم رشد جداول و رشد شاخص ها را دنبال کنیم. ما می توانیم تغییراتی را در دینامیک ببینیم.
و دسته سوم زمان صرف شده برای رویداد است. درخواست یک رویداد است. مدت زمان خاص خود را دارد. از اینجا شروع شد، اینجا به پایان رسید. ما می توانیم آن را پیگیری کنیم. یا زمان خواندن یک بلوک از دیسک یا نوشتن آن. چنین مواردی نیز نظارت می شود.

منابع آماری به شرح زیر ارائه شده است:

در حافظه مشترک (بافرهای مشترک) بخشی برای ذخیره داده های ایستا وجود دارد، همچنین شمارنده هایی وجود دارد که هنگام وقوع رویدادهای خاص یا برخی لحظات در عملکرد پایگاه داده به طور مداوم افزایش می یابند.
همه این شمارنده ها برای کاربر و حتی مدیر قابل دسترسی نیستند. اینها چیزهای سطح پایینی است. برای دسترسی به آنها، PostgreSQL یک رابط در قالب توابع SQL فراهم می کند. می‌توانیم با استفاده از این توابع پرتاب‌های انتخابی انجام دهیم و نوعی متریک (یا مجموعه‌ای از معیارها) به دست آوریم.
با این حال، استفاده از این توابع همیشه راحت نیست، بنابراین توابع اساس نماها (VIEWs) هستند. اینها جداول مجازی هستند که آمار مربوط به یک زیرسیستم خاص یا مجموعه خاصی از رویدادها در پایگاه داده را ارائه می دهند.
این نماهای تعبیه شده (VIEWs) رابط کاربری اولیه برای کار با آمار هستند. آنها به طور پیش فرض بدون هیچ تنظیمات اضافی در دسترس هستند، می توانید بلافاصله از آنها استفاده کنید، آنها را نگاه کنید و اطلاعات را از آنها بگیرید. و سپس سهم وجود دارد. مشارکت ها رسمی هستند. می توانید بسته postgresql-contrib (مثلا postgresql94-contrib) را نصب کنید، ماژول مورد نیاز را در پیکربندی بارگذاری کنید، پارامترهای آن را مشخص کنید، PostgreSQL را مجددا راه اندازی کنید و می توانید از آن استفاده کنید. (توجه داشته باشید. بسته به توزیع، در نسخه های اخیر بسته مشارکت بخشی از بسته اصلی است).
و مشارکت غیر رسمی وجود دارد. آنها در توزیع استاندارد PostgreSQL گنجانده نشده اند. آنها باید یا کامپایل شوند یا به صورت کتابخانه نصب شوند. بسته به آنچه که توسعه دهنده این مشارکت غیر رسمی به آن دست یافته است، گزینه ها می توانند بسیار متفاوت باشند.

این اسلاید همه نماها و برخی از توابع موجود در PostgreSQL 9.4 را ارائه می دهد. همانطور که می بینیم، تعداد زیادی از آنها وجود دارد. و اگر برای اولین بار با آن روبرو شوید، گیج شدن بسیار آسان است.

با این حال، اگر عکس قبلی را بگیریم Как тратится время на PostgreSQL و سازگار با این لیست، این تصویر را دریافت می کنیم. وقتی PostgreSQL در حال اجرا است، می‌توانیم از هر view (VIEWs) یا هر تابع برای یک هدف یا دیگری برای بدست آوردن آمار مربوطه استفاده کنیم. و ما قبلاً می توانیم اطلاعاتی در مورد عملکرد زیرسیستم به دست آوریم.

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

چه چیزهای مفیدی می توانیم از آنجا بگیریم؟ بیایید با ساده ترین چیزها شروع کنیم.

select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;

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

واضح است که هرچه تعداد بازدیدهای کش بیشتری داشته باشیم، بهتر است. ما این معیار را به صورت درصد اندازه گیری می کنیم. و به عنوان مثال، اگر درصد ما از این کش ها بیش از 90٪ باشد، این خوب است. اگر به زیر 90 درصد برسد، به این معنی است که ما حافظه کافی برای نگهداری هد داغ داده در حافظه نداریم. و برای استفاده از این داده ها، PostgreSQL مجبور به دسترسی به دیسک می شود و این نسبت به زمانی که داده ها از حافظه خوانده می شد کندتر است. و باید به افزایش حافظه فکر کنید: یا بافرهای مشترک را افزایش دهید یا حافظه سخت افزاری (RAM) را افزایش دهید.

select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;

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

ما می توانیم از این درخواست استفاده کنیم. این SQL بسیار ساده است. و ما می توانیم به این داده ها در اینجا نگاه کنیم.

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

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

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

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

Pg_stat_bgwriter - این نما عملکرد دو زیرسیستم پس زمینه PostgreSQL را توصیف می کند: checkpointer и background writer.

ابتدا به نقاط کنترل، به اصطلاح نگاه می کنیم. checkpoints. نقاط کنترل چیست؟ چک پوینت موقعیتی در گزارش تراکنش است که نشان می دهد تمام تغییرات داده های ثبت شده در گزارش با موفقیت با داده های روی دیسک همگام شده است. این فرآیند بسته به حجم کاری و تنظیمات، می‌تواند طولانی باشد و بیشتر شامل همگام‌سازی صفحات کثیف در بافرهای مشترک با فایل‌های داده روی دیسک است. این برای چیست؟ اگر PostgreSQL دائماً به دیسک دسترسی پیدا می کرد و داده ها را از آنجا واکشی می کرد و روی هر دسترسی می نوشت، سرعت آن کند می شد. بنابراین، PostgreSQL یک بخش حافظه دارد که اندازه آن به تنظیمات در پیکربندی بستگی دارد. Postgres داده های زنده را برای پردازش یا پرس و جو بعدی در این حافظه ذخیره می کند. در صورت درخواست تغییر داده ها تغییر می کند. و ما دو نسخه از داده ها را دریافت می کنیم. یکی در حافظه ما است، دیگری روی دیسک. و به صورت دوره ای باید این داده ها را همگام سازی کنید. ما باید آنچه را که در حافظه تغییر کرده است با دیسک همگام کنیم. برای این شما به پست های بازرسی نیاز دارید.

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

دو نوع ایست بازرسی وجود دارد. یک ایست بازرسی با تایم اوت اجرا می شود. این ایست بازرسی مفید و خوب است - checkpoint_timed. و پست های بازرسی در صورت تقاضا وجود دارد - checkpoint required. این نقطه بازرسی زمانی اتفاق می افتد که ما یک رکورد داده بسیار بزرگ داشته باشیم. ما بسیاری از گزارش تراکنش ها را ثبت کردیم. و PostgreSQL معتقد است که باید همه اینها را در سریع ترین زمان ممکن همگام کند، یک نقطه بازرسی ایجاد کند و ادامه دهد.

و اگر به آمار نگاه کنید pg_stat_bgwriter و دیدم چه داری checkpoint_req بسیار بزرگتر از checkpoint_timed است، پس این بد است. چرا بد؟ این بدان معناست که PostgreSQL زمانی که نیاز به نوشتن داده ها روی دیسک دارد، تحت استرس دائمی قرار دارد. ایست بازرسی تایم اوت استرس کمتری دارد و طبق برنامه داخلی انجام می شود و به مرور زمان پخش می شود. PostgreSQL این قابلیت را دارد که کار را متوقف کند و زیرسیستم دیسک را تحت فشار قرار ندهد. این برای PostgreSQL مفید است. و پرس و جوهایی که در حین بازرسی اجرا می شوند، استرس را از این واقعیت که زیرسیستم دیسک مشغول است، تجربه نمی کنند.

و برای تنظیم ایست بازرسی سه پارامتر وجود دارد:

сheckpoint_segments.
сheckpoint_timeout.
сheckpoint_competion_target.

آنها به شما امکان می دهند عملکرد نقاط کنترل را تنظیم کنید. اما من به آنها نمی پردازم. تأثیر آنها موضوع جداگانه ای است.

هشدار: نسخه 9.4 مورد بحث در گزارش دیگر مرتبط نیست. در نسخه های مدرن PostgreSQL پارامتر checkpoint_segments با پارامترها جایگزین شده است min_wal_size и max_wal_size.

زیرسیستم بعدی پس زمینه نویس است - background writer. داره چیکار میکنه؟ به طور مداوم در یک حلقه بی پایان اجرا می شود. صفحات را در بافرهای مشترک اسکن می کند و صفحات کثیفی را که پیدا می کند به دیسک می ریزد. بنابراین، به بازرسی کمک می کند تا در هنگام اجرای ایست بازرسی کار کمتری انجام دهد.

دیگر برای چه چیزی لازم است؟ این نیاز به صفحات خالی در بافرهای مشترک را در صورت نیاز ناگهانی (در مقادیر زیاد و فورا) برای قرار دادن داده ها فراهم می کند. فرض کنید شرایطی پیش آمد که صفحات خالی برای تکمیل یک درخواست مورد نیاز بودند و آنها قبلاً در بافرهای مشترک بودند. پساگریز backend او فقط آنها را برمی دارد و استفاده می کند، او مجبور نیست خودش چیزی را تمیز کند. اما اگر به طور ناگهانی چنین صفحاتی وجود نداشته باشند، backend کار را متوقف می کند و شروع به جستجوی صفحات می کند تا آنها را روی دیسک ریخته و آنها را برای نیازهای خود ببرد - که بر زمان اجرای درخواست فعلی تأثیر منفی می گذارد. اگر می بینید که یک پارامتر دارید maxwritten_clean بزرگ، این بدان معناست که پس‌زمینه‌نویس کار خود را انجام نمی‌دهد و باید پارامترها را افزایش دهید bgwriter_lru_maxpages، تا بتواند در یک چرخه کارهای بیشتری انجام دهد، صفحات بیشتری را پاک کند.

و یکی دیگر از شاخص های بسیار مفید است buffers_backend_fsync. Backend ها به دلیل کندی همگام سازی نمی شوند. آنها fsync را از چکپوینت پشته IO عبور می دهند. چک پوینتگر صف خاص خود را دارد، به صورت دوره ای fsync را پردازش می کند و صفحات موجود در حافظه را با فایل های روی دیسک همگام می کند. اگر صف در checkpointer بزرگ و پر باشد، پس‌زمینه مجبور می‌شود خودش fsync را انجام دهد و این کار باطن را کند می‌کند.، یعنی مشتری دیرتر از زمانی که می توانست پاسخ دریافت کند. اگر می بینید که مقدار شما بزرگتر از صفر است، این یک مشکل است و باید به تنظیمات پس‌زمینه‌نویس توجه کنید و همچنین عملکرد زیرسیستم دیسک را ارزیابی کنید.

هشدار: _متن زیر نماهای آماری مرتبط با تکرار را تشریح می کند. بسیاری از نام‌های نما و تابع در Postgres 10 تغییر نام دادند. ماهیت تغییر نام جایگزینی بود. xlog بر wal и location بر lsn در نام توابع/نما و غیره مثال خاص، تابع pg_xlog_location_diff() تغییر نام داده شد به pg_wal_lsn_diff()._

ما اینجا هم خیلی چیزها داریم. اما ما فقط به موارد مربوط به مکان نیاز داریم.

اگر می بینیم که همه مقادیر برابر هستند، این یک گزینه ایده آل است و ماکت از استاد عقب نمی ماند.

این موقعیت هگزادسیمال در اینجا موقعیت در گزارش تراکنش است. اگر فعالیتی در پایگاه داده وجود داشته باشد، دائماً افزایش می یابد: درج، حذف و غیره.

сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());

اگر این چیزها متفاوت است، پس نوعی تاخیر وجود دارد. Lag تاخیر بین replica و master است، یعنی داده ها بین سرورها متفاوت است.

سه دلیل برای تاخیر وجود دارد:

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

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

توجه: _به جای pg_xlog_locationتابع diff() می تواند از عملگر تفریق استفاده کند و یک مکان را از مکان دیگر تفریق کند. راحت.

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

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

select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;

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

با این حال، یک معیار دوم وجود دارد - seq_tup_read. این تعداد ردیف هایی است که از اسکن متوالی برگردانده شده است. اگر میانگین تعداد از 1، 000،10، 000،50، 000،100 تجاوز کند، پس این نشانگر این است که شاید شما نیاز به ایجاد یک نمایه در جایی دارید تا پرس و جوها بر اساس ایندکس باشند، یا امکان بهینه سازی پرس و جوهایی که از چنین اسکن های متوالی استفاده می کنند وجود دارد. که این اتفاق نمی افتد بود.

یک مثال ساده - فرض کنید درخواستی با هزینه های OFFSET و LIMIT زیاد. به عنوان مثال، 100 ردیف در یک جدول اسکن می شود و پس از آن 000 ردیف مورد نیاز گرفته می شود و سطرهای اسکن شده قبلی حذف می شوند. این هم یک مورد بد است. و چنین پرس و جوهایی باید بهینه شوند. و در اینجا یک پرس و جو ساده SQL وجود دارد که می توانید به آن نگاه کنید و اعداد حاصل را ارزیابی کنید.

select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;

اندازه جدول را نیز می توان با استفاده از این جدول و با استفاده از توابع اضافی بدست آورد pg_total_relation_size(), pg_relation_size().

به طور کلی، دستورات متا وجود دارد dt и diکه در PSQL قابل استفاده است و همچنین اندازه جداول و ایندکس ها را مشاهده می کند.

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

فعالیت ضبط ضبط چیست؟ بیایید به عملیات نگاه کنیم UPDATE - عملیات به روز رسانی ردیف ها در یک جدول. در واقع، به روز رسانی دو عملیات (یا حتی بیشتر) است. این درج یک نسخه جدید از ردیف و علامت گذاری نسخه قدیمی ردیف به عنوان منسوخ است. متعاقباً، جارو برقی می آید و این نسخه های قدیمی خطوط را پاک می کند و این مکان را به عنوان در دسترس برای استفاده مجدد علامت گذاری می کند.

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

select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;

و به دلیل طراحی جدید، UPDATE یک عملیات سنگین وزن است. اما می توان آنها را آسان تر کرد. بخور hot updates. آنها در PostgreSQL نسخه 8.3 ظاهر شدند. و این چیه؟ این یک به روز رسانی سبک وزن است که باعث نمی شود فهرست ها دوباره ساخته شوند. یعنی رکورد را به روز کردیم اما فقط رکورد صفحه (که متعلق به جدول است) به روز شد و ایندکس ها همچنان به همان رکورد در صفحه اشاره می کنند. یک منطق عملیاتی جالب وجود دارد: وقتی خلاء می آید، این زنجیره ها را ایجاد می کند hot بازسازی می شود و همه چیز بدون به روز رسانی شاخص ها به کار خود ادامه می دهد و همه چیز با اتلاف کمتر منابع اتفاق می افتد.

و چه زمانی n_tup_hot_upd بزرگ است، پس خیلی خوب است. این به این معنی است که به روز رسانی های سبک غالب هستند و از نظر منابع برای ما ارزان تر است و همه چیز خوب است.

ALTER TABLE table_name SET (fillfactor = 70);

نحوه افزایش حجم hot updateov ما میتوانیم استفاده کنیم fillfactor. اندازه فضای آزاد رزرو شده را هنگام پر کردن صفحه در جدول با استفاده از INSERT تعیین می کند. هنگامی که درج ها به جدول اضافه می شوند، صفحه را کاملا پر می کنند و فضای خالی باقی نمی گذارند. سپس یک صفحه جدید برجسته می شود. داده ها دوباره پر می شوند. و این رفتار پیش فرض است، fillfactor = 100%.

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

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

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

توجه: _ با شروع Postgres 10، وضعیت ردیابی Vatovac بسیار بهبود یافته است - نمای pg_stat_progress ظاهر شدخلاء، که به طور قابل توجهی موضوع نظارت بر خلاء خودرو را ساده می کند.

ما می توانیم از این پرس و جو ساده استفاده کنیم. و ما می توانیم ببینیم که چه زمانی باید خلاء ایجاد شود. اما چگونه و چه زمانی باید خلاء شروع شود؟ اینها نسخه های قدیمی خطوطی هستند که قبلاً در مورد آنها صحبت کردم. به روز رسانی رخ داد، نسخه جدیدی از خط درج شد. یک نسخه قدیمی از رشته ظاهر شده است. در جدول pg_stat_user_tables چنین پارامتری وجود دارد n_dead_tup. تعداد خطوط "مرده" را نشان می دهد. و به محض اینکه تعداد ردیف های مرده از یک آستانه خاص بیشتر شود، یک اتوواکیوم روی میز می آید.

و این آستانه چگونه محاسبه می شود؟ این درصد بسیار مشخصی از تعداد کل ردیف‌های جدول است. یک پارامتر وجود دارد autovacuum_vacuum_scale_factor. درصد را تعیین می کند. فرض کنید 10٪ + یک آستانه پایه اضافی 50 خط وجود دارد. و چه اتفاقی می افتد؟ وقتی تعداد ردیف‌های مرده بیشتر از «10% + 50» از تمام ردیف‌های جدول باشد، جدول را روی autovacuum قرار می‌دهیم.

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

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

برای آن نوشتند افزونه munin، که این موارد را در نظر می گیرد. یک دستمال دو ورقی آنجاست. اما به درستی محاسبه می کند و کاملاً مؤثر به ما امکان می دهد مکان هایی را که برای جداولی که در آن کم است به خلاء زیادی نیاز داریم ارزیابی کنیم.

در مورد آن جه می توانیم انجام دهیم؟ اگر یک صف بزرگ داشته باشیم و اتو وکیوم نتواند با آن مقابله کند، می‌توانیم تعداد کارگران جاروبرقی را افزایش دهیم یا به سادگی خلاء را تهاجمی‌تر کنیم.، به طوری که زودتر فعال شود، جدول را در قطعات کوچک پردازش می کند. و به این ترتیب صف کاهش می یابد. - نکته اصلی در اینجا نظارت بر بار روی دیسک است، زیرا ... خلاء چیز رایگانی نیست، اگرچه با ظهور دستگاه های SSD/NVMe این مشکل کمتر قابل توجه است.

Pg_stat_all_indexes آمار مربوط به ایندکس ها است. او بزرگ نیست. و می توانیم از آن برای به دست آوردن اطلاعات در مورد استفاده از شاخص ها استفاده کنیم. و به عنوان مثال، ما می توانیم تعیین کنیم که کدام شاخص های اضافی داریم.

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

توجه: هنگام جستجوی نمایه های استفاده نشده در مورد خوشه های تکراری جریان، باید همه گره های خوشه را بررسی کنید، زیرا آمار جهانی نیست، و اگر ایندکس در Master استفاده نشود، می توان از آن بر روی replica ها استفاده کرد (اگر بار وجود دارد).

دو لینک:

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

اینها نمونه های پرس و جوی پیشرفته تری در مورد چگونگی جستجوی نمایه های استفاده نشده هستند.

لینک دوم یک درخواست نسبتا جالب است. یک منطق بسیار غیر پیش پا افتاده وجود دارد. من آن را برای مرجع توصیه می کنم.

چه چیز دیگری ارزش خلاصه کردن با استفاده از شاخص ها را دارد؟

شاخص های استفاده نشده بد هستند.
فضا را اشغال می کنند.
کاهش سرعت عملیات به روز رسانی
کار اضافی برای خلاء.

اگر ایندکس های استفاده نشده را حذف کنیم، فقط پایگاه داده را بهتر می کنیم.

ارائه بعدی است pg_stat_activity. این یک آنالوگ از ابزار است ps، فقط در PostgreSQL. اگر psپس شما به فرآیندهای سیستم عامل نگاه کنید pg_stat_activity فعالیت داخل PostgreSQL را به شما نشان می دهد.

چه چیزهای مفیدی می توانیم از آنجا بگیریم؟

select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;

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

select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;

ما می‌توانیم یک پرس و جو مانند این را اجرا کنیم و درصد کل اتصالات را نسبت به حداکثر محدودیت اتصال ببینیم و ببینیم چه کسی بیشترین اتصال را دارد. و در این مورد ما آن کاربر را می بینیم cron_role 508 اتصال را باز کرد. و در آنجا اتفاقی برای او افتاد. ما باید با آن مقابله کنیم و به آن نگاه کنیم. و کاملاً ممکن است که این نوعی تعداد غیرعادی اتصالات باشد.

اگر حجم کاری OLTP داریم، پرس و جوها باید سریع، بسیار سریع باشند و پرس و جوهای طولانی وجود نداشته باشد. با این حال، اگر سؤالات طولانی ایجاد شود، در کوتاه مدت چیزی برای نگرانی وجود ندارد، اما در درازمدت، پرس‌و‌جوهای طولانی به پایگاه داده آسیب می‌رسانند؛ آن‌ها اثر bloat جداول را در هنگام تکه تکه شدن جدول افزایش می‌دهند. شما باید از شر نفخ و پرس و جوهای طولانی خلاص شوید.

select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;

لطفا توجه داشته باشید: با این درخواست ما می توانیم پرس و جوها و تراکنش های طولانی را شناسایی کنیم. ما از تابع استفاده می کنیم clock_timestamp() برای تعیین زمان عملیات پرس و جوهای طولانی که پیدا کردیم، می توانیم آنها را به خاطر بسپاریم، آنها را برآورده کنیم explain، به طرح ها نگاه کنید و به نوعی بهینه سازی کنید. ما درخواست های طولانی فعلی را کنار می گذاریم و به زندگی خود ادامه می دهیم.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

تراکنش‌های بد، معاملاتی هستند که در حالت بی‌کاری در معامله و بی‌حرکتی در معاملات (مقطع) هستند.

چه مفهومی داره؟ تراکنش ها چندین حالت دارند. و در هر زمانی می توان یکی از این حالت ها را فرض کرد. یک فیلد برای تعریف حالت ها وجود دارد state در این ارائه و از آن برای تعیین وضعیت استفاده می کنیم.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

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

اگر می بینید که بیش از 5-10-20 مورد از آنها را در پایگاه داده خود دارید، باید نگران باشید و شروع به انجام کاری با آنها کنید.

در اینجا برای زمان محاسبه نیز استفاده می کنیم clock_timestamp(). ما تراکنش ها را شلیک می کنیم و برنامه را بهینه می کنیم.

همانطور که در بالا گفتم، مسدود کردن زمانی است که دو یا چند تراکنش برای یک یا گروهی از منابع با هم مبارزه کنند. برای این ما یک میدان داریم waiting با مقدار بولی true یا false.

درست است - این به این معنی است که روند در انتظار است، کاری باید انجام شود. هنگامی که یک فرآیند در انتظار است، به این معنی است که مشتری که این فرآیند را آغاز کرده است نیز منتظر است. مشتری در مرورگر می نشیند و همچنین منتظر می ماند.

هشدار: _شروع از قسمت Postgres نسخه 9.6 waiting حذف شد و به جای آن دو قسمت آموزنده دیگر اضافه شد wait_event_type и wait_event._

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

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

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

و در اینجا دو پرس و جو وجود دارد که به شما امکان می دهد مسدود شدن را ردیابی کنید. ما از view استفاده می کنیم pg_locks، که به شما امکان می دهد قفل های سنگین را ردیابی کنید.

و اولین لینک خود متن درخواست است. خیلی طولانیه

و لینک دوم مقاله ای در مورد قفل است. خواندن آن مفید است، بسیار جالب است.

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

اینگونه می توانیم بفهمیم که چه کسی چه کسی را قفل کرده است، چه کسی را نگه داشته و می توانیم بیشتر با آن برخورد کنیم.

ماژول بعدی است pg_stat_statements. همانطور که گفتم این یک ماژول است. برای استفاده از آن، باید کتابخانه آن را در تنظیمات بارگذاری کنید، PostgreSQL را مجددا راه اندازی کنید، ماژول را (با یک دستور) نصب کنید و سپس یک نمای جدید خواهیم داشت.

Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;

Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;

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

ما می‌توانیم به فعال‌ترین تراکنش‌های نوشتن در پایگاه داده نگاه کنیم که داده‌ها را در بافرهای مشترک تغییر می‌دهند. ببینید چه کسی داده‌ها را در آنجا به‌روزرسانی یا حذف می‌کند.

و ما می توانیم به سادگی به آمارهای مختلف برای این درخواست ها نگاه کنیم.

https://github.com/dataegret/pg-utils/blob/master/sql/global_reports/query_stat_total.sql

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

ما چه کار می کنیم؟ ما آمار کلی را برای همه درخواست ها محاسبه می کنیم. سپس، برای هر درخواست، سهم فردی آن را در این آمار کلی محاسبه می کنیم.

و چه چیزی را می توانیم تماشا کنیم؟ می‌توانیم کل زمان اجرای همه درخواست‌های یک نوع خاص را در پس‌زمینه همه درخواست‌های دیگر بررسی کنیم. می‌توانیم به استفاده از منابع CPU و I/O نسبت به تصویر کلی نگاه کنیم. و از قبل این پرس و جوها را بهینه کنید. ما بر اساس این گزارش در حال ساختن پرسش‌های برتر هستیم و در حال حاضر در حال بهینه‌سازی اطلاعات هستیم.

چه چیزی در پشت صحنه به جا گذاشته ایم؟ هنوز چند مورد ارسالی باقی مانده است که من آنها را بررسی نکردم زیرا زمان محدود است.

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

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

ماژول بعدی است pgfincore. این اجازه می دهد تا عملیات جدول سطح پایین از طریق تماس سیستمی انجام شود mincore()، یعنی به شما امکان می دهد یک جدول را در بافرهای مشترک بارگذاری کنید یا آن را تخلیه کنید. و از جمله موارد دیگر، به بررسی کش صفحه سیستم عامل، یعنی اینکه جدول چه مقدار فضای در کش صفحه، در بافرهای مشترک اشغال می کند، بازرسی کنیم، و به سادگی به ما امکان می دهد حجم کاری جدول را ارزیابی کنیم.

ماژول بعدی – pg_stat_kcache. همچنین از تماس سیستمی استفاده می کند getrusage(). و آن را قبل و بعد از اجرای درخواست اجرا می کند. و در آمار به دست آمده، به ما امکان می دهد تخمین بزنیم که درخواست ما در ورودی/خروجی دیسک چقدر هزینه شده است، یعنی عملیات با سیستم فایل و به استفاده از پردازنده نگاه می کند. با این حال، ماژول جوان است (سرفه سرفه) و برای عملکرد آن به PostgreSQL 9.4 و pg_stat_statements نیاز دارد که قبلاً به آنها اشاره کردم.

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