چطوری سرعت کوئری های تحلیلی SQL رو بیش از ۱۰۰۰ برابر افزایش دادم!

اگر براتون پیش اومده که روی چند تا جدول بزرگ (منظورم از بزرگ اینه که حداقل ۱۰ میلیون رکورد داشته باشن) بخواید یه کوئری SQL اجرا کنید که چندین دقیقه یا حتی چندین ساعت طول بکشه و راه حلی براش پیدا نکردین، مطلب زیر به کارتون میاد.

میخوام راجع به تجربم در اجرای کوئری هایی بنویسم که بیش از ۲ ساعت زمان میبرد و الان در عرض ۳۰ ثانیه به جواب میرسم. قرار نیست راجع به Query optimization صحبت کنم، قراره همون کوئری رو ببریم روی بستر یه تکنولوژی دیگه که مخصوص اجرای کوئری های تحلیلی ساخته شده.

مسئله چی بود؟

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

دو جدول اصلی که ما استفاده می کردیم حدودا ۱۶۰ و ۹ میلیون رکورد داشتند و روی دیتابیس MySQL با ایندکس گذاری مناسب قرار داشتند. اجرای هرکوئری روی این دیتابیس چیزی نزدیک به ۲ ساعت زمان لازم داشت. مسئله وقتی خسته کننده تر میشد که برای هر ISP باید این کار جداگانه انجام میشد.

این شد که به دنبال راه حلی برای این تیپ کوئری ها افتادیم.

راه حل چیه؟

کوئری های این چنینی رو معمولا روی سیستم های OLAP که مخفف OnLine Analytical Processing هست اجرا میکنند. از طرف دیگه برای زیرساخت دیتابیس های OLAP هم معمولا از دیتابیس های مبتنی بر ستون (‌اسم فارسی جالب تری پیدا نکردم D: )‌ یا column oriented استفاده میکنند. در اکثر دیتابیس های رابطه ای مانند MySQL دیتاها به صورت row oriented ذخیره و پیمایش میشوند. اما اساسا در دیتابیس های column oriented داده های هر ستون در کنار یکدیگر ذخیره می شوند. به همین دلیل معمولا توابع aggregation مانند COUNT, AVG,SUM روی این دیتابیس ها فوق العاده سریعتر اجرا میشن. با توجه به همین رویکرد اساسا عملیات UPDATE , DELETE در دیتابیس های column oriented بسیار پرهزینه است.

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

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

تا اینجا نکته مهم اینه که اگر شما دیتایی از جنس لاگ دارید که قرار نیست عملیات UPDATE , DELETE داشته باشین و قصد دارین که روی اون کار تحلیلی انجام بدین، توصیه میشه که برین سراغ این دیتابیس ها.

من همین طور که دنبال دیتابیس های column oriented و سیستم های OLAP میگشتم رسیدم به این صفحه ویکپدیا که مقایسه نسبتا کاملی از سیستم های OLAP رو ارائه کرده که در ادامه بخشیش رو آوردم.

از اونجایی که ما به دنبال ۱) یک سیستم Opensource بودیم که روی ۲) سیستم عامل لینوکس نصب بشه، ۳)سرعت بالایی داشته باشه، ۴) زبان استاندار و ساده ای مثل SQL رو برای کوئری ها پشتیبانی کنه و همچنین ۵)‌قابلیت ساخت مدل MOLAP (‌نوعی ساختار داده های چند بعدی مثل cube ) رو داشته باشه و بتوان با ۶)‌ابزاهای مختلف Visualization مثل Metabase ( که ما ازش استفاده میکنیم و بعدا مفصل راجع بهش توضیح میدم) بهش متصل شد،‌سیستم Clickhouse رو انتخاب کردیم.

طبق توضیح سایت خودش، Clickhouse یک دیتابیس column oriented هست که مناسب اجرای کوئری های تحلیلی یا همون OLAP هست و قادره که یک میلیارد رکورد رو با بیش از ده ها گیگابیت داده روی یک سرور و در یک ثانیه پردازش کند.

ClickHouse's performance exceeds comparable column-oriented database management systems currently available on the market. It processes hundreds of millions to more than a billion rows and tens of gigabytes of data per single server per second

در اینجا مقایسه ای هم از سرعت اجرای انواع کوئری های SELECT در مقایسه با دیتابیس های Vertica , Greeplum آورده که دیدنش خالی از لطف نیست.

ما چیکار کردیم؟

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

توضیحات نحوه انجام اینکار به همراه کدهایی که برای تست و ایجاد replica از جداول MySQL روی Clickhouse میباشد رو در یک مطلب مفصل دیگه مینویسم. اگر لازم دارین که چنین کاری انجام بدین میتونید از مطلب این مقاله هم استفاده کنید.

در ادامه خروجی مقایسه تست هایی که روی MySQL و Clickhouse داشتیم رو آوردم.

تست اول

کوئری محاسبه جمع ترافیک استریم شده ۳ ماه گذشته.

• Clickhouse: 50 ms
• MySQL: 79 s

تست دوم

کوئری محاسبه تعداد رکورد لاگ ها، کاربران یونیکی که ویدیو دیده اند،‌ محصولات یونیکی که دیده شده و مجموع ترافیک استریم شده در ۳ ماهه اول سال ۲۰۲۰ سایت دیجیتون

• Clickhouse: 212 ms
• MySQL: 107 s

تست سوم - کوئری اصلی

کوئری محاسبه میزان ترافیک کاربران اینترنت داتک در مدت زمانی که بسته اشتراک یک ساله آنها فعال بوده است. این کوئری همان بود که بیش از ۲ ساعت زمان میبرد که اکنون حدودا ظرف مدت ۳۰ ثانیه جواب میدهد. اگر با Google Big Query کار کرده باشین، احتمالا خوشحال میشد که بدونید میشه کوئری ها را با clickhouse-client اجرا کنید و خروجی مشابه خروجی کوئری های Google Big Query دریافت کنید که شامل موراد زیر است.

• مدت زمان اجرای کوئری:‌ ۳۵ ثانیه
• تعداد رکوردهای پردازش شده: ۱۷۶ میلیون ردیف
• حجم پردازش شده: ۳.۶۴ گیگابایت
• سرعت اجرای کوئری: ۴.۹۸ میلیون رکورد در ثانیه و ۱۰۲ مگابایت در ثانیه

با فرض متوسط ۲ ساعت برای کوئری های تست سوم روی MySQL و متوسط ۳۰ ثانیه روی ClickHouse تقریبا افزایش سرعتی معادل ۲۴۰ برابری رو تجربه کردیم.

کلام آخر

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