چگونه می توان 10 برابر تعداد پرس و جوها را در پایگاه داده افزایش داد بدون اینکه به یک سرور سازنده تر منتقل شود و عملکرد سیستم را حفظ کند؟ من به شما خواهم گفت که چگونه با کاهش عملکرد پایگاه داده خود برخورد کردیم، چگونه پرس و جوهای SQL را بهینه سازی کردیم تا به بیشترین تعداد ممکن کاربران خدمت رسانی کنیم و هزینه منابع محاسباتی را افزایش ندادیم.
من یک سرویس برای مدیریت فرآیندهای تجاری در شرکت های ساختمانی ایجاد می کنم. حدود 3 هزار شرکت با ما کار می کنند. روزانه بیش از 10 هزار نفر به مدت 4-10 ساعت با سیستم ما کار می کنند. مشکلات مختلف برنامه ریزی، اطلاع رسانی، هشدار، اعتبارسنجی را حل می کند... ما از PostgreSQL 9.6 استفاده می کنیم. ما حدود 300 جدول در پایگاه داده داریم و روزانه تا 200 میلیون پرس و جو (10 هزار مورد مختلف) دریافت می شود. ما به طور متوسط 3-4 هزار درخواست در ثانیه داریم، در فعال ترین لحظات بیش از 10 هزار درخواست در ثانیه. اکثر پرس و جوها OLAP هستند. افزودن ها، اصلاحات و حذف ها بسیار کمتر است، به این معنی که بار OLTP نسبتاً سبک است. من همه این اعداد را ارائه کردم تا بتوانید مقیاس پروژه ما را ارزیابی کنید و درک کنید که تجربه ما چقدر می تواند برای شما مفید باشد.
تصویر یک بزمی، غزلی
وقتی توسعه را شروع کردیم، واقعاً به این فکر نمی‌کردیم که چه نوع باری روی پایگاه داده می‌افتد و اگر کارکرد سرور متوقف شود، چه کاری انجام خواهیم داد. هنگام طراحی پایگاه داده، توصیه‌های کلی را دنبال کردیم و سعی کردیم به پای خود شلیک نکنیم، اما فراتر از توصیه‌های عمومی مانند «از الگو استفاده نکنید» رفتیم. ما داخل نشدیم ما بر اساس اصول عادی سازی، اجتناب از افزونگی داده ها طراحی کردیم و به سرعت بخشیدن به درخواست های خاص اهمیتی ندادیم. به محض ورود اولین کاربران، با مشکل عملکرد مواجه شدیم. طبق معمول برای این کار کاملاً ناآماده بودیم. اولین مشکلات ساده بود. به عنوان یک قاعده، همه چیز با اضافه کردن یک شاخص جدید حل شد. اما زمانی فرا رسید که وصله های ساده از کار افتادند. با درک اینکه تجربه کافی نداریم و درک اینکه چه چیزی باعث این مشکلات می شود برایمان سخت تر می شود، متخصصانی را استخدام کردیم که به ما کمک کردند سرور را به درستی تنظیم کنیم، نظارت را متصل کنیم و به ما نشان دادند که کجا باید جستجو کنیم. .
تصویر دو آماری
بنابراین ما روزانه حدود 10 هزار کوئری مختلف داریم که در پایگاه داده ما اجرا می شوند. از این 10 هزار، هیولاهایی وجود دارند که 2-3 میلیون بار با میانگین زمان اجرای 0.1-0.3 میلی ثانیه اجرا می شوند و کوئری هایی با میانگین زمان اجرای 30 ثانیه وجود دارند که 100 بار در روز فراخوانی می شوند.
بهینه سازی تمام 10 هزار پرس و جو امکان پذیر نبود، بنابراین تصمیم گرفتیم که تلاش خود را برای بهبود عملکرد صحیح پایگاه داده به کجا هدایت کنیم. پس از چندین بار تکرار، ما شروع به تقسیم درخواست ها به انواع کردیم.
درخواست های برتر
اینها سنگین ترین پرسش هایی هستند که بیشترین زمان را می گیرند (کل زمان). اینها پرس و جوهایی هستند که یا خیلی اوقات فراخوانی می شوند یا پرس و جوهایی هستند که اجرای آنها زمان بسیار زیادی می برد (پرسش های طولانی و مکرر در اولین تکرارهای مبارزه برای سرعت بهینه شده بودند). در نتیجه سرور بیشترین زمان را برای اجرای آنها صرف می کند. علاوه بر این، مهم است که درخواست های برتر را بر اساس زمان کل اجرا و به طور جداگانه بر اساس زمان IO جدا کنید. روش های بهینه سازی چنین پرس و جوها کمی متفاوت است.
رویه معمول همه شرکت ها کار با درخواست های TOP است. تعداد کمی از آنها وجود دارد؛ بهینه سازی حتی یک پرس و جو می تواند 5-10٪ از منابع را آزاد کند. با این حال، همانطور که پروژه بالغ می شود، بهینه سازی پرس و جوهای TOP به یک کار غیر ضروری تبدیل می شود. همه روش های ساده قبلاً کار شده اند و "سنگین ترین" درخواست "فقط" 3-5٪ از منابع را می گیرد. اگر پرس و جوهای برتر در مجموع کمتر از 30 تا 40 درصد زمان را می گیرند، به احتمال زیاد قبلاً تلاش کرده اید تا آنها را سریع کار کنید و زمان آن رسیده است که به بهینه سازی پرس و جوها از گروه بعدی بروید.
باقی مانده است که به این سوال پاسخ دهیم که چه تعداد از پرس و جوهای برتر باید در این گروه قرار گیرند. من معمولاً حداقل 10 می گیرم، اما نه بیشتر از 20. سعی می کنم اطمینان حاصل کنم که زمان اولین و آخرین در گروه TOP بیش از 10 برابر نباشد. یعنی اگر زمان اجرای پرس و جو به شدت از رتبه اول به 1 کاهش پیدا کند، TOP-10 را می گیرم، اگر افت تدریجی تر باشد، اندازه گروه را به 10 یا 15 افزایش می دهم.
دهقانان میانه
اینها همه درخواست هایی هستند که بلافاصله پس از TOP ارائه می شوند، به استثنای 5-10٪ آخر. معمولاً در بهینه‌سازی این پرس‌و‌جوها فرصتی برای افزایش عملکرد سرور وجود دارد. این درخواست ها می تواند تا 80 درصد وزن داشته باشد. اما حتی اگر سهم آنها از 50٪ فراتر رفته باشد، وقت آن است که با دقت بیشتری به آنها نگاه کنید.
دم
همانطور که گفته شد، این پرس و جوها در پایان می آیند و 5 تا 10 درصد زمان را می گیرند. تنها در صورتی که از ابزارهای تحلیل خودکار پرس و جو استفاده نکنید، می توانید آنها را فراموش کنید، پس بهینه سازی آنها نیز می تواند ارزان باشد.
چگونه هر گروه را ارزیابی کنیم؟
من از یک پرس و جوی SQL استفاده می کنم که به انجام چنین ارزیابی برای PostgreSQL کمک می کند (مطمئن هستم که یک پرس و جو مشابه را می توان برای بسیاری از DBMS های دیگر نوشت)
پرس و جوی SQL برای تخمین اندازه گروه های TOP-MEDIUM-TAIL
SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
SELECT CASE WHEN rn <= 20 THEN tt_percent ELSE 0 END AS time_top,
CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
CASE WHEN rn > 800 THEN tt_percent ELSE 0 END AS time_tail
FROM (
SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
FROM pg_stat_statements
ORDER BY total_time DESC
) AS t
)
AS ts
نتیجه پرس و جو سه ستون است که هر کدام شامل درصد زمان پردازش پرس و جوهای این گروه است. در داخل درخواست دو عدد وجود دارد (در مورد من 20 و 800 است) که درخواست های یک گروه را از گروه دیگر جدا می کند.
به این صورت است که سهم درخواست‌ها در زمان شروع کار بهینه‌سازی و اکنون با هم مقایسه می‌شود.
این نمودار نشان می دهد که سهم درخواست های TOP به شدت کاهش یافته است، اما "دهقانان متوسط" افزایش یافته اند.
در ابتدا، درخواست های TOP شامل اشتباهات فاحش بود. با گذشت زمان، بیماری های دوران کودکی ناپدید شدند، سهم درخواست های TOP کاهش یافت و تلاش های بیشتری برای تسریع در درخواست های دشوار باید انجام می شد.
برای دریافت متن درخواست ها از درخواست زیر استفاده می کنیم
SELECT * FROM (
SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
FROM pg_stat_statements
ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800 -- TAIL
در اینجا لیستی از متداول ترین تکنیک های استفاده شده است که به ما کمک می کند تا پرس و جوهای برتر را سرعت بخشیم:
- طراحی مجدد سیستم، به عنوان مثال، کار مجدد منطق اعلان با استفاده از یک واسطه پیام به جای درخواست های دوره ای به پایگاه داده
- افزودن یا تغییر نمایه ها
- بازنویسی پرس و جوهای ORM به SQL خالص
- بازنویسی منطق بارگذاری داده های تنبل
- ذخیره سازی از طریق غیرعادی سازی داده ها به عنوان مثال، ما یک اتصال جدول تحویل -> فاکتور -> درخواست -> برنامه داریم. یعنی هر تحویل از طریق جداول دیگر با یک برنامه مرتبط است. برای اینکه همه جداول در هر درخواست پیوند داده نشود، پیوند را به درخواست در جدول تحویل کپی کردیم.
- ذخیره جداول استاتیک با کتاب های مرجع و به ندرت تغییر جداول در حافظه برنامه.
گاهی اوقات تغییرات به یک طراحی مجدد چشمگیر منجر می شد، اما آنها 5-10٪ بار سیستم را فراهم کردند و توجیه شدند. با گذشت زمان، اگزوز کوچکتر و کوچکتر شد و نیاز به طراحی مجدد بیشتر و جدی تری داشت.
سپس توجه خود را به گروه دوم درخواست ها معطوف کردیم - گروه دهقانان متوسط. سوالات بسیار بیشتری در آن وجود دارد و به نظر می رسید که تجزیه و تحلیل کل گروه زمان زیادی می برد. با این حال، بهینه سازی اکثر پرس و جوها بسیار ساده بود و بسیاری از مشکلات ده ها بار در تغییرات مختلف تکرار شدند. در اینجا نمونه‌هایی از بهینه‌سازی‌های معمولی وجود دارد که برای ده‌ها پرس‌و‌جوی مشابه اعمال کردیم و هر گروه از پرس‌و‌جوهای بهینه‌سازی شده پایگاه داده را 3 تا 5 درصد تخلیه کردند.
- به جای بررسی وجود سوابق با استفاده از COUNT و اسکن کامل جدول، استفاده از EXISTS شروع شد.
- از DISTINCT خلاص شد (هیچ دستور العمل کلی وجود ندارد، اما گاهی اوقات می توانید به راحتی با افزایش سرعت درخواست 10-100 برابر از شر آن خلاص شوید).
به عنوان مثال، به جای درخواست برای انتخاب همه درایورها از یک جدول بزرگ تحویل (DELIVERY)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDیک پرس و جو در یک جدول نسبتا کوچک PERSON انجام داد
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)به نظر می رسد که ما از یک زیرپرس و جوی همبسته استفاده کرده ایم، اما سرعت آن بیش از 10 برابر است.
- در بسیاری از موارد، COUNT به طور کلی رها شد و
- به جای
UPPER(s) LIKE JOHN%’استفاده
s ILIKE “John%”
هر درخواست خاص گاهی اوقات بین 3 تا 1000 برابر افزایش می یابد. علیرغم عملکرد چشمگیر، در ابتدا به نظرمان رسید که بهینه سازی پرس و جوی که تکمیل آن 10 میلی ثانیه طول می کشد، یکی از صدمین پرس و جوی سنگین است و صدم درصد زمان بارگذاری کلی پایگاه داده را به خود اختصاص می دهد، فایده ای ندارد. اما با اعمال همان دستور العمل برای گروهی از جستارهای مشابه، چند درصد برنده شدیم. برای اینکه زمان را برای مرور دستی صدها پرس و جو تلف نکنیم، چندین اسکریپت ساده نوشتیم که از عبارات منظم برای یافتن پرس و جوهایی از یک نوع استفاده می کردند. در نتیجه، جستجوی خودکار گروه‌های پرسش‌ها به ما این امکان را می‌دهد تا با تلاشی کم، عملکرد خود را بیشتر بهبود بخشیم.
در نتیجه ما سه سال است که روی همان سخت افزار کار می کنیم. میانگین بار روزانه حدود 30 درصد است، در پیک ها به 70 درصد می رسد. تعداد درخواست ها و همچنین تعداد کاربران تقریباً 10 برابر شده است. و همه اینها به لطف نظارت مداوم بر همین گروه های درخواست های TOP-MEDIUM است. به محض اینکه یک درخواست جدید در گروه TOP ظاهر شد، بلافاصله آن را تجزیه و تحلیل می کنیم و سعی می کنیم سرعت آن را افزایش دهیم. ما هفته ای یک بار گروه MEDIUM را با استفاده از اسکریپت های تحلیل پرس و جو مرور می کنیم. اگر به پرس و جوهای جدیدی برخورد کنیم که از قبل می دانیم چگونه بهینه سازی کنیم، به سرعت آنها را تغییر می دهیم. گاهی اوقات روش‌های بهینه‌سازی جدیدی را می‌یابیم که می‌توان آنها را به طور همزمان روی چندین کوئری اعمال کرد.
طبق پیش بینی های ما، سرور فعلی در برابر افزایش تعداد کاربران 3-5 برابر دیگر مقاومت خواهد کرد. درست است، ما یک ACE دیگر در آستین خود داریم - همانطور که توصیه می شود هنوز پرس و جوهای SELECT را به آینه منتقل نکرده ایم. اما ما این کار را آگاهانه انجام نمی‌دهیم، زیرا می‌خواهیم ابتدا امکانات بهینه‌سازی «هوشمند» را قبل از روشن کردن «توپخانه سنگین» به‌طور کامل از بین ببریم.
یک نگاه انتقادی به کار انجام شده ممکن است استفاده از مقیاس عمودی را پیشنهاد کند. به جای اتلاف وقت متخصصان، سرور قدرتمندتری بخرید. سرور ممکن است آنقدر هزینه نداشته باشد، به خصوص که ما هنوز محدودیت های مقیاس عمودی را تمام نکرده ایم. با این حال، تنها تعداد درخواست ها 10 برابر افزایش یافته است. در طول چندین سال، عملکرد سیستم افزایش یافته است و در حال حاضر انواع بیشتری از درخواست ها وجود دارد. به لطف حافظه پنهان، عملکردی که وجود داشت در درخواست‌های کمتر و درخواست‌های کارآمدتر انجام می‌شود. این بدان معناست که می توانید با خیال راحت در 5 دیگر ضرب کنید تا ضریب شتاب واقعی را بدست آورید. بنابراین، طبق محافظه کارانه ترین تخمین ها، می توان گفت که شتاب 50 برابر یا بیشتر بوده است. چرخش عمودی یک سرور 50 برابر هزینه بیشتری دارد. به خصوص با توجه به این که یک بار بهینه سازی انجام می شود، همیشه کار می کند و صورتحساب سرور اجاره ای هر ماه می آید.
منبع: www.habr.com