بهینه سازی انبوه پرس و جوهای PostgreSQL. کریل بوروویکوف (تنسور)

این گزارش رویکردهایی را ارائه می دهد که اجازه می دهد نظارت بر عملکرد پرس و جوهای SQL زمانی که میلیون ها مورد در روز وجود دارد، و صدها سرور PostgreSQL نظارت شده وجود دارد.

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

چه کسی علاقه مند است؟ تجزیه و تحلیل مسائل خاص و تکنیک های مختلف بهینه سازی پرس و جوهای SQL و حل مشکلات معمول DBA در PostgreSQL - شما همچنین می توانید یک سری مقاله بخوانید در این مورد.


نام من کریل بوروویکوف است، من نماینده هستم شرکت تنسور. به طور خاص، من در کار با پایگاه های داده در شرکت خود تخصص دارم.

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

به طور کلی، تنسور برای یک میلیون مشتری ما است VLSI برنامه ما است: شبکه اجتماعی شرکتی، راه حل هایی برای ارتباط تصویری، برای جریان اسناد داخلی و خارجی، سیستم های حسابداری برای حسابداری و انبارها، ... یعنی چنین "مگا ترکیبی" برای مدیریت یکپارچه کسب و کار که در آن بیش از 100 مورد مختلف وجود دارد. پروژه های داخلی

برای اطمینان از اینکه همه آنها به طور عادی کار می کنند و توسعه می یابند، ما 10 مرکز توسعه در سراسر کشور داریم که تعداد بیشتری در آنها وجود دارد 1000 توسعه دهنده.

ما از سال 2008 با PostgreSQL کار می کنیم و مقدار زیادی از آنچه را که پردازش می کنیم - داده های مشتری، آماری، تحلیلی، داده های سیستم های اطلاعات خارجی - جمع آوری کرده ایم. بیش از 400 ترابایت. حدود 250 سرور به تنهایی در حال تولید هستند و در مجموع حدود 1000 سرور پایگاه داده وجود دارد که ما نظارت می کنیم.

SQL یک زبان اعلامی است. شما نه "چگونه" چیزی باید کار کند، بلکه "آنچه" را می خواهید به دست آورید، توصیف می کنید. DBMS بهتر می داند که چگونه یک JOIN ایجاد کند - چگونه جداول خود را به هم متصل کند، چه شرایطی را اعمال کند، چه چیزی را از طریق فهرست عبور می کند، چه چیزی را انجام نمی دهد ...

برخی از DBMS ها نکات را می پذیرند: "نه، این دو جدول را در یک صف به هم متصل کنید" اما PostgreSQL نمی تواند این کار را انجام دهد. این موضع آگاهانه توسعه دهندگان پیشرو است: "ما ترجیح می دهیم بهینه ساز پرس و جو را تمام کنیم تا اینکه به توسعه دهندگان اجازه دهیم از نوعی نکات استفاده کنند."

اما، علیرغم این واقعیت که PostgreSQL به "خارج" اجازه نمی دهد خود را کنترل کند، کاملاً اجازه می دهد ببین درونش چه خبرهزمانی که پرس و جو خود را اجرا می کنید و در کجا مشکل دارد.

به طور کلی، یک توسعه دهنده [به یک DBA] معمولاً با چه مشکلات کلاسیکی مواجه می شود؟ "در اینجا ما درخواست را برآورده کردیم، و همه چیز با ما کند استهمه چیز معلق است، یک اتفاقی در حال رخ دادن است... یک جور دردسر!»

دلایل تقریباً همیشه یکسان است:

• الگوریتم پرس و جو ناکارآمد
  توسعه دهنده: "اکنون من 10 جدول در SQL از طریق JOIN به او می دهم..." - و انتظار دارد که شرایط او به طور معجزه آسایی "باز" ​​شود و او همه چیز را به سرعت دریافت کند. اما معجزه اتفاق نمی افتد و هر سیستمی با چنین تنوع (10 جدول در یک FROM) همیشه نوعی خطا می دهد. [مقاله]
• آمار قدیمی
  این نکته به طور خاص برای PostgreSQL بسیار مرتبط است، زمانی که شما یک مجموعه داده بزرگ را روی سرور «ریختید»، درخواستی را ارسال کردید، و تبلت شما را «سکسی» کرد. زیرا دیروز 10 رکورد در آن وجود داشت و امروز 10 میلیون است، اما PostgreSQL هنوز از این موضوع آگاه نیست و باید در مورد آن به آن بگوییم. [مقاله]
• منابع را "وصل کنید".
  شما یک پایگاه داده بزرگ و به شدت بارگذاری شده روی سرور ضعیفی نصب کرده اید که عملکرد دیسک، حافظه یا پردازنده کافی ندارد. و این همه چیز است... جایی سقف عملکردی وجود دارد که دیگر نمی توانید از آن بپرید.
• مسدود کردن
  این یک نقطه دشوار است، اما آنها برای پرس و جوهای مختلف اصلاحی (INSERT، UPDATE، DELETE) مرتبط هستند - این یک موضوع بزرگ جداگانه است.

گرفتن یک طرح

... و برای هر چیز دیگری ما نیاز به یک طرح! باید ببینیم داخل سرور چه اتفاقی می افتد.

طرح اجرای پرس و جو برای PostgreSQL درختی از الگوریتم اجرای پرس و جو در نمایش متن است. این دقیقاً الگوریتمی است که در نتیجه تجزیه و تحلیل توسط برنامه ریز، مؤثرترین الگوریتم است.

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

برای دریافت طرح پرس و جو، ساده ترین راه اجرای عبارت است EXPLAIN. برای به دست آوردن تمام ویژگی های واقعی، یعنی اجرای یک پرس و جو در پایه - EXPLAIN (ANALYZE, BUFFERS) SELECT ....

بخش بد: وقتی آن را اجرا می کنید، "اینجا و اکنون" اتفاق می افتد، بنابراین فقط برای اشکال زدایی محلی مناسب است. اگر سروری با بارگذاری بالا را بگیرید که تحت یک جریان قوی داده است، تغییر می کند و می بینید: "اوه! در اینجا ما یک اجرای آهسته داریمکمپینگ درخواست." نیم ساعت، یک ساعت پیش - در حالی که در حال اجرا بودید و این درخواست را از گزارش‌ها دریافت می‌کردید و آن را به سرور برمی‌گردانید، کل مجموعه داده و آمار شما تغییر کرد. شما آن را برای اشکال زدایی اجرا می کنید - و به سرعت اجرا می شود! و نمی توانید بفهمید چرا، چرا بود به آرامی

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

اگر متوجه شود که برخی از درخواست ها بیشتر از حدی که به آن گفته اید اجرا می شود، انجام می دهد "عکس فوری" از طرح این درخواست و آنها را با هم در گزارش می نویسد.

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

به نظر می رسد همه چیز خوب است - اما هیچ چیز مشخص نیست که واقعاً چه اتفاقی افتاده است. به غیر از زمان کلی، ما واقعاً چیزی نمی بینیم. زیرا نگاه کردن به چنین "بره" متن ساده معمولاً بصری نیست.

اما حتی اگر واضح نباشد، حتی اگر ناخوشایند باشد، مشکلات اساسی تری وجود دارد:

• گره نشان می دهد مجموع منابع کل زیردرخت زیر او به این معنی که اگر شرایط تودرتو در زیر آن وجود داشته باشد، نمی‌توانید بفهمید که چقدر زمان صرف این Index Scan شده است. ما باید به صورت پویا نگاه کنیم تا ببینیم آیا «فرزندان» و متغیرهای شرطی، CTE در داخل وجود دارد یا خیر - و همه اینها را «در ذهنمان» کم کنیم.
• نکته دوم: زمانی که روی گره درج شده است زمان اجرای تک گره. اگر این گره در نتیجه، برای مثال، چندین بار ضبط یک حلقه از طریق جدول اجرا شود، تعداد حلقه ها - چرخه های این گره - در پلان افزایش می یابد. اما خود زمان اجرای اتمی از نظر برنامه ثابت می ماند. یعنی برای اینکه بفهمید این گره در مجموع چه مدت انجام شده است ، باید یک چیز را در دیگری ضرب کنید - دوباره "در سر خود".

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

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

تجسم برنامه

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

ما ابتدا "از طریق بازار" رفتیم - بیایید به اینترنت نگاه کنیم تا ببینیم چه چیزی وجود دارد.

اما معلوم شد که راه حل های نسبتاً "زنده" بسیار کمی وجود دارد که کم و بیش در حال توسعه هستند - به معنای واقعی کلمه فقط یکی: توضیح.depesz.com توسط هوبرت لوباچوسکی وقتی وارد فیلد "feed" می‌شوید که نمایش متنی از طرح است، جدولی با داده‌های تجزیه‌شده به شما نشان می‌دهد:

• زمان پردازش خود گره
• زمان کل برای کل زیردرخت
• تعداد رکوردهای بازیابی شده که از نظر آماری مورد انتظار بود
• خود بدنه گره

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

اما مشکلات کوچکی نیز وجود دارد.

اولا، مقدار زیادی "کپی پیست". شما یک تکه از چوب را بردارید، آن را در آنجا بچسبانید و دوباره و دوباره.

دوم، بدون تجزیه و تحلیل مقدار داده های خوانده شده - همان بافرهایی که خروجی دارند EXPLAIN (ANALYZE, BUFFERS)، ما آن را اینجا نمی بینیم. او به سادگی نمی داند چگونه آنها را جدا کند، آنها را درک کند و با آنها کار کند. زمانی که در حال خواندن داده های زیادی هستید و متوجه می شوید که ممکن است دیسک و حافظه پنهان را به اشتباه تخصیص داده باشید، این اطلاعات بسیار مهم است.

سومین نکته منفی توسعه بسیار ضعیف این پروژه است. commit ها بسیار کوچک هستند، اگر هر شش ماه یک بار باشد، خوب است و کد در Perl باشد.

اما اینها همه "اشعار" است، ما می توانیم به نوعی با آن زندگی کنیم، اما یک چیز وجود دارد که ما را به شدت از این سرویس دور کرد. اینها خطاهایی در تجزیه و تحلیل Common Table Expression (CTE) و گره های پویا مختلف مانند InitPlan/SubPlan هستند.

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

بعد متوجه شدیم که وقت آن رسیده است که خودمان را بنویسیم - هورا! هر توسعه‌دهنده‌ای می‌گوید: "حالا ما خودمان را خواهیم نوشت، بسیار آسان خواهد بود!"

ما یک پشته معمولی برای خدمات وب انتخاب کردیم: یک هسته مبتنی بر Node.js + Express، از Bootstrap و D3.js برای نمودارهای زیبا استفاده کرد. و انتظارات ما کاملاً توجیه شد - ما اولین نمونه اولیه را در 2 هفته دریافت کردیم:

• تجزیه کننده طرح سفارشی
  یعنی اکنون می‌توانیم هر طرحی را از برنامه‌های تولید شده توسط PostgreSQL تجزیه کنیم.
• تجزیه و تحلیل صحیح گره های پویا - CTE Scan، InitPlan، SubPlan
• تجزیه و تحلیل توزیع بافر - جایی که صفحات داده از حافظه، کجا از حافظه پنهان محلی، کجا از دیسک خوانده می شوند
• وضوح گرفت
  به طوری که همه اینها را در سیاهه "حفاری" نکنید، بلکه "ضعیف ترین پیوند" را بلافاصله در تصویر مشاهده کنید.

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

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

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

ما به گره اشاره می کنیم و می بینیم - معلوم می شود که Seq Scan کمتر از یک چهارم کل زمان را گرفته است و 3/4 باقی مانده توسط CTE Scan گرفته شده است. وحشت! این یک یادداشت کوچک در مورد "نرخ آتش" اسکن CTE است اگر به طور فعال از آنها در جستجوهای خود استفاده کنید. آنها خیلی سریع نیستند - آنها حتی نسبت به اسکن معمول جدول پایین تر هستند. [مقاله] [مقاله]

اما معمولاً چنین نمودارهایی جالب‌تر و پیچیده‌تر می‌شوند، زمانی که ما بلافاصله به یک بخش اشاره می‌کنیم و مثلاً می‌بینیم که بیش از نیمی از مواقع Seq Scan "خورده است". علاوه بر این، نوعی فیلتر در داخل وجود داشت، بسیاری از رکوردها طبق آن دور ریخته شدند ... می توانید مستقیماً این تصویر را به توسعه دهنده بیندازید و بگویید: "واسیا، اینجا همه چیز برای شما بد است! آن را دریابید، نگاه کنید - چیزی اشتباه است!

به طور طبیعی، برخی از "کلنگ ها" درگیر بود.

اولین چیزی که به آن برخورد کردیم مشکل گرد بود. زمان هر گره مجزا در پلان با دقت 1 میکرو ثانیه نشان داده شده است. و هنگامی که تعداد چرخه های گره از 1000 تجاوز می کند - پس از اجرای PostgreSQL "با دقت" تقسیم می شود، سپس هنگام محاسبه مجدد، کل زمان "جایی بین 0.95 میلی ثانیه و 1.05 میلی ثانیه" را دریافت می کنیم. وقتی شمارش به میکروثانیه می‌رود، اشکالی ندارد، اما وقتی [میلی] ثانیه است، باید این اطلاعات را هنگام «باز کردن» منابع به گره‌های طرح «چه کسی چقدر مصرف کرده» در نظر بگیرید.

نکته دوم، پیچیده تر، توزیع منابع (آن بافرها) بین گره های پویا است. این برای ما 2 هفته اول نمونه اولیه به علاوه 4 هفته دیگر هزینه داشت.

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

ما به طرح نگاه می کنیم و متوجه می شویم - عجیب است، ما 3 بافر (صفحه داده) "مصرف شده" در Seq Scan، 1 مورد دیگر در CTE Scan و 2 مورد دیگر در CTE Scan دوم داریم. یعنی اگر به سادگی همه چیز را جمع کنیم 6 می گیریم اما از تبلت فقط 3 می خوانیم! CTE Scan چیزی را از جایی نمی خواند، اما مستقیماً با حافظه پردازش کار می کند. یعنی اینجا یک چیزی به وضوح اشتباه است!

در واقع معلوم می شود که در اینجا تمام آن 3 صفحه داده ای است که از Seq Scan درخواست شده است، ابتدا 1 مورد برای اولین اسکن CTE و سپس 1 و 2 صفحه دیگر برای او خوانده شده است. 2 صفحه داده خوانده شد نه 3.

و این تصویر ما را به این درک سوق داد که اجرای یک طرح دیگر یک درخت نیست، بلکه فقط نوعی گراف غیر چرخه ای است. و ما نموداری مانند این دریافت کردیم، به طوری که می‌فهمیم «در وهله اول چه چیزی از کجا آمده است». یعنی اینجا یک CTE از pg_class ساختیم و دوبار درخواست کردیم و تقریباً تمام وقت ما صرف شعبه دوم شد که بار دوم درخواست کردیم. واضح است که خواندن ورودی 2 بسیار گرانتر از خواندن مدخل 101 از تبلت است.

کمی نفس را بیرون دادیم. گفتند: «حالا نئو، تو کونگ فو بلدی! اکنون تجربه ما دقیقاً روی صفحه نمایش شما است. حالا می توانید از آن استفاده کنید." [مقاله]

ادغام گزارش

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

به طور کلی، یک ماژول استاندارد وجود دارد که می تواند آمار را جمع آوری کند، با این حال، باید در پیکربندی نیز فعال شود - این ماژول pg_stat_statements. اما او برای ما مناسب نبود.

اولاً، با استفاده از طرح‌های مختلف در پایگاه داده یکسان، به پرس و جوهای یکسانی اختصاص می‌دهد QueryId های مختلف. یعنی اگر ابتدا انجام دهید SET search_path = '01'; SELECT * FROM user LIMIT 1;و بعد SET search_path = '02'; و همان درخواست، سپس آمار این ماژول دارای رکوردهای متفاوتی خواهد بود و من نمی توانم آمار کلی را به طور خاص در زمینه این پروفایل درخواست جمع آوری کنم، بدون در نظر گرفتن طرح ها.

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

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

بنابراین تصمیم گرفتیم با کپی پیست مبارزه کنیم و شروع به نوشتن کردیم کلکتور.

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

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

بر این اساس، ما دو اتصال را "کشش" می کنیم: اولی برای "گوش دادن" به خود سیاهه و بردن آن به سمت خود و دوم برای درخواست دوره ای از پایگاه. "اما گزارش نشان می دهد که علامت با oid 123 مسدود شده است"، اما این برای توسعه دهنده معنایی ندارد، و بهتر است از پایگاه داده بپرسید، "به هر حال OID = 123 چیست؟" و بنابراین ما به طور دوره‌ای از پایگاه می‌پرسیم که هنوز درباره خودمان نمی‌دانیم.

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

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

اما صرفاً «ریختن» داده ها واقعاً فناوری ما نیست. زیرا اگر شما تقریباً 50 هزار درخواست در ثانیه در صد سرور داشته باشید، در این صورت 100-150 گیگابایت گزارش در روز تولید می شود. بنابراین، ما مجبور شدیم پایه را با دقت "برش دهیم".

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

ثانیاً ما یاد گرفتیم (اجبار کردیم) بسیار بسیار سریع برای نوشتن با استفاده از COPY. یعنی نه فقط COPYزیرا او سریعتر از INSERT، و حتی سریعتر.

نکته سوم - مجبور بودم به ترتیب تریگرها و کلیدهای خارجی را رها کنید. یعنی اصلاً یکپارچگی ارجاعی نداریم. زیرا اگر جدولی دارید که دارای یک جفت FK است و در ساختار پایگاه داده می گویید که "اینجا یک رکورد log است که توسط FK به عنوان مثال به گروهی از رکوردها ارجاع داده می شود"، وقتی آن را وارد می کنید، PostgreSQL چیزی باقی نمانده جز اینکه چگونه آن را بگیریم و صادقانه انجام دهیم SELECT 1 FROM master_fk1_table WHERE ... با شناسه‌ای که می‌خواهید درج کنید - فقط برای اینکه بررسی کنید که این رکورد در آنجا وجود دارد، و این کلید خارجی را با درج کردن خود "شکن" نکنید.

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

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

حال تصور کنید که جدولی دارید که در آن به سادگی تعداد درخواست هایی را که از یک هاست خاص عبور کرده اند می شمارید: +1, +1, +1, ..., +1. و شما، در اصل، به این نیاز ندارید - همه چیز ممکن است مجموع در حافظه در جمع کننده و یکباره به پایگاه داده ارسال کنید +10.

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

در مورد هش کردن هم همینطور است. یک درخواست خاص به سمت شما می رود، شما یک شناسه مشخص را از آن در پایگاه داده محاسبه می کنید، آن را در پایگاه داده می نویسید و سپس به همه می گویید. همه چیز خوب است تا زمانی که در زمان ضبط، شخص دومی به سراغ شما می آید که می خواهد همان چیزی را ضبط کند - و شما بلاک می شوید، و این قبلاً بد است. بنابراین اگر بتوانید تولید برخی از ID ها را به کلاینت (نسبت به پایگاه داده) انتقال دهید، بهتر است این کار را انجام دهید.

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

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

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

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

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

علاوه بر این، متوجه شدیم که برای چنین نمایه بارگیری، هر گونه تجمع، زمانی که رکوردها به صورت دسته‌ای جمع‌آوری می‌شوند، شر است. شر کلاسیک است INSERT ... VALUES و 1000 رکورد دیگر. زیرا در آن مرحله شما یک اوج نوشتن در رسانه دارید و هر کس دیگری که سعی می کند چیزی را روی دیسک بنویسد منتظر خواهد بود.

برای خلاص شدن از شر چنین ناهنجاری ها، به سادگی چیزی را جمع نکنید، اصلا بافر نکنید. و اگر بافر روی دیسک اتفاق بیفتد (خوشبختانه، Stream API در Node.js به شما اجازه می دهد تا متوجه شوید) - این اتصال را به تعویق بیندازید. وقتی رویدادی را دریافت کردید که دوباره رایگان است، از صف انباشته شده برای آن بنویسید. و در حالی که شلوغ است، رایگان بعدی را از استخر بردارید و به آن بنویسید.

قبل از معرفی این روش برای ضبط داده ها، تقریباً عملیات نوشتن 4K داشتیم و به این ترتیب بار را 4 برابر کاهش دادیم. اکنون آنها 6 برابر دیگر به دلیل پایگاه داده های نظارت شده جدید - تا 100 مگابایت بر ثانیه رشد کرده اند. و اکنون ما لاگ های 3 ماه گذشته را در حجمی بین 10 تا 15 ترابایت ذخیره می کنیم، به این امید که هر توسعه دهنده ای بتواند هر مشکلی را در عرض سه ماه حل کند.

ما مشکلات را درک می کنیم

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

اما میلیون ها نفر غیرقابل مدیریت هستند، ابتدا باید "کوچکتر" را انجام دهیم. و اول از همه، شما باید تصمیم بگیرید که چگونه این چیز "کوچکتر" را سازماندهی کنید.

ما سه نکته کلیدی را شناسایی کرده ایم:

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

برای درک اینکه "چه کسی" درخواستی را برای ما ارسال کرده است، از یک ابزار استاندارد استفاده می کنیم - تنظیم یک متغیر جلسه: SET application_name = '{bl-host}:{bl-method}'; - نام میزبان منطق تجاری را که درخواست از آن ارسال می شود، و نام روش یا برنامه ای که آن را آغاز کرده است، ارسال می کنیم.

پس از اینکه "مالک" درخواست را ارسال کردیم، باید به گزارش خروجی داده شود - برای این ما متغیر را پیکربندی می کنیم log_line_prefix = ' %m [%p:%v] [%d] %r %a'. برای علاقه مندان، شاید در دفترچه راهنما نگاه کنیدهمه ی اینها چه معنایی میدهد. معلوم می شود که در گزارش می بینیم:

• زمان
• شناسه های فرآیند و تراکنش
• نام پایگاه داده
• IP شخصی که این درخواست را ارسال کرده است
• و نام روش

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

بنابراین این برش است "یک سرور - یک روز" برای هر تحلیلی برای ما کافی بود.

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

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

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

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

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

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

و بلافاصله می توانید برنامه های مختلفی را مشاهده کنید که با یک الگو از یک درخواست مانند ارائه می شوند SELECT * FROM users WHERE login = 'Vasya'. Frontend، Backend، Processing... و شما تعجب می کنید که چرا پردازش اگر کاربر با او تعامل نداشته باشد، می خواند.

راه مخالف این است که فوراً از برنامه ببینید چه کاری انجام می دهد. به عنوان مثال، فرانت اند این است، این، این، و این یک بار در ساعت (خط زمانی کمک می کند). و بلافاصله این سوال پیش می‌آید: به نظر می‌رسد این وظیفه پیشرو این نیست که کاری را یک بار در ساعت انجام دهد...

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

شما یک نمایه مناسب روی صفحه ندارید، از آن درخواست می کنید، از ایندکس می گذرد، به Seq Scan می افتد ... شما همه رکوردها را به جز یکی فیلتر کرده اید. چرا به 100 میلیون رکورد فیلتر شده در روز نیاز دارید؟ آیا بهتر نیست فهرست را جمع آوری کنید؟

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

همه کسانی که پرس و جو می‌نویسند احتمالاً با این الگو مواجه شده‌اند: «آخرین سفارش واسیا، تاریخ آن را به من بدهید». دقیقاً بر روی همان "شنل" قدم بگذارید.

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

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

منبع: www.habr.com