آنچه توضیح می دهد در مورد آن ساکت است و چگونه می توان آن را به صحبت پرداخت

سوال کلاسیکی که یک توسعه دهنده برای DBA خود یا یک صاحب کسب و کار برای مشاور PostgreSQL مطرح می کند تقریباً همیشه یکسان به نظر می رسد: "چرا تکمیل درخواست ها در پایگاه داده اینقدر طول می کشد؟"

مجموعه دلایل سنتی:

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

و اگر گرفتن و تجزیه و تحلیل مسدود کردن بسیار دشوار است، پس برای هر چیز دیگری نیاز داریم طرح پرس و جو، که با استفاده از آن می توان به دست آورد اپراتور توضیح دهید (البته بهتر است فوراً توضیح دهید (تجزیه و تحلیل، بافرها) ...) و یا ماژول auto_explain.

اما همانطور که در همان مستندات آمده است

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

اما اگر از ابزار مناسب استفاده کنید، می توانید بدون آن کار کنید!

طرح پرس و جو معمولاً چگونه به نظر می رسد؟ یه چیزی شبیه اون:

Index Scan using pg_class_relname_nsp_index on pg_class (actual time=0.049..0.050 rows=1 loops=1)
  Index Cond: (relname = $1)
  Filter: (oid = $0)
  Buffers: shared hit=4
  InitPlan 1 (returns $0,$1)
    ->  Limit (actual time=0.019..0.020 rows=1 loops=1)
          Buffers: shared hit=1
          ->  Seq Scan on pg_class pg_class_1 (actual time=0.015..0.015 rows=1 loops=1)
                Filter: (relkind = 'r'::"char")
                Rows Removed by Filter: 5
                Buffers: shared hit=1

یا مثل این:

"Append  (cost=868.60..878.95 rows=2 width=233) (actual time=0.024..0.144 rows=2 loops=1)"
"  Buffers: shared hit=3"
"  CTE cl"
"    ->  Seq Scan on pg_class  (cost=0.00..868.60 rows=9972 width=537) (actual time=0.016..0.042 rows=101 loops=1)"
"          Buffers: shared hit=3"
"  ->  Limit  (cost=0.00..0.10 rows=1 width=233) (actual time=0.023..0.024 rows=1 loops=1)"
"        Buffers: shared hit=1"
"        ->  CTE Scan on cl  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.021..0.021 rows=1 loops=1)"
"              Buffers: shared hit=1"
"  ->  Limit  (cost=10.00..10.10 rows=1 width=233) (actual time=0.117..0.118 rows=1 loops=1)"
"        Buffers: shared hit=2"
"        ->  CTE Scan on cl cl_1  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.001..0.104 rows=101 loops=1)"
"              Buffers: shared hit=2"
"Planning Time: 0.634 ms"
"Execution Time: 0.248 ms"

اما خواندن طرح در متن "از برگه" بسیار دشوار و نامشخص است:

• در گره نمایش داده می شود جمع بر اساس منابع زیردرخت
  یعنی برای درک اینکه چقدر زمان برای اجرای یک گره خاص طول کشیده است، یا اینکه این قرائت از جدول دقیقاً چقدر داده را از دیسک آورده است، باید به نحوی یکی را از دیگری کم کنید.
• زمان گره مورد نیاز است ضرب در حلقه ها
  بله، تفریق پیچیده ترین عملیاتی نیست که باید "در سر" انجام شود - از این گذشته، زمان اجرا به عنوان میانگین برای یک اجرای یک گره نشان داده شده است و می تواند صدها مورد از آنها وجود داشته باشد.
• خوب، و همه اینها با هم ما را از پاسخ به سؤال اصلی باز می دارد - پس چه کسی "ضعیف ترین حلقه"?

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

و این بدان معناست که ما نیاز داریم ...

ابزار

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

قابل مشاهده بودن طرح ها

آیا درک این طرح آسان است که به نظر می رسد؟

Seq Scan on pg_class (actual time=0.009..1.304 rows=6609 loops=1)
  Buffers: shared hit=263
Planning Time: 0.108 ms
Execution Time: 1.800 ms

نه واقعا

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

اما اگر نقشه پیچیده تر باشد، او به کمک خواهد آمد توزیع زمان پیچارت توسط گره ها:

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

برای مثال، موقعیت‌های کاملاً غیر ضروری وجود دارد که یک طرح ممکن است بیش از یک ریشه واقعی داشته باشد:

سرنخ های ساختاری

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

ما قبلاً چند دوجین از این الگوهای توصیه را جمع آوری کرده ایم.

نمایه ساز پرس و جو خط به خط

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

... یا حتی مانند این:

جایگزینی پارامترها در یک درخواست

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

• با جایگزینی ارزش در درخواست
  برای اجرای مستقیم در پایگاه شما و نمایه سازی بیشتر

  SELECT 'const', 'param'::text;

• با جایگزینی ارزش از طریق PREPARE/EXECUTE
  برای تقلید از کار زمانبندی، زمانی که می توان بخش پارامتریک را نادیده گرفت - به عنوان مثال، هنگام کار بر روی جداول پارتیشن بندی شده

  DEALLOCATE ALL;
  PREPARE q(text) AS SELECT 'const', $1::text;
  EXECUTE q('param'::text);
  

آرشیو طرح ها

چسباندن، تجزیه و تحلیل، به اشتراک گذاری با همکاران! طرح ها بایگانی می شوند و می توانید بعداً به آنها بازگردید: توضیح.tensor.ru/archive

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

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

منبع: www.habr.com