فهرست مطالب
1. معرفی
1.1. OLTP
1.2. OLAP
1.3. MySQL
1.4. کلیک هاوس
2. مقایسه مطالعه موردی
2.5. مشخصات سیستم
2.6. جریان معیار
2.7. پرس و جوها
3. نتایج
3.8. بار مجموعه داده
3.9. اندازه جدول
3.10. اجرای کوئری ها را بخوانید
3.11. به روز رسانی اجرای پرس و جو
4. نتیجه گیری
معرفی
هیچ چیز کامل نیست. از نظر پایگاه های داده، نمی توانید بهترین عملکرد را برای هر کار و پرس و جو از پایگاه داده مستقر شده خود انتظار داشته باشید. با این حال، گام حیاتی به عنوان یک توسعه‌دهنده نرم‌افزار، شناخت نقاط قوت و ضعف و نحوه برخورد با آنهاست.
در این پست کلیک هاوس را به عنوان نماینده پایگاه داده OLAP و MySQL OLTP مقایسه خواهم کرد. این به ما کمک می کند تا با توجه به شرایط و خواسته های خود راه حل های بهتری برای چالش های خود انتخاب کنیم. قبل از پرش به زمینه اصلی، اجازه دهید OLTP، OLAP، MySQL و ClickHouse را مورد بحث قرار دهیم.
OLTP
OLTP مخفف Online Transaction Processing است و برای عملیات روزانه مانند پردازش سفارشات و به روز رسانی اطلاعات مشتری استفاده می شود. OLTP برای تراکنش های کوتاه و سریع بهترین است و برای زمان پاسخگویی سریع بهینه شده است. اطمینان از صحت و سازگاری داده ها و ارائه راهی کارآمد برای دسترسی به داده ها ضروری است.
OLAP
OLAP مخفف Online Analytical Processing است و برای داده کاوی و تجزیه و تحلیل استفاده می شود. این سازمان ها را قادر می سازد تا مقادیر زیادی از داده ها را از منظرهای متعدد تجزیه و تحلیل کنند و روندها و الگوها را شناسایی کنند. OLAP برای پرس و جوهای پیچیده و داده کاوی بهترین است و می تواند بینش غیرممکنی را با ابزارهای گزارش سنتی ارائه دهد.
MySQL
MySQL یک سیستم مدیریت پایگاه داده منبع باز محبوب است. برای ذخیره و مدیریت داده ها استفاده می شود و توسط وب سایت ها و برنامه ها برای ذخیره و مدیریت اطلاعات استفاده می شود. MySQL یک سیستم مدیریت پایگاه داده رابطه‌ای است که داده‌ها را در جداول نگهداری می‌کند و به کاربران اجازه می‌دهد تا داده‌ها را پرس و جو کنند. همچنین ویژگی هایی مانند محرک ها، رویه های ذخیره شده و نماها را فراهم می کند. MySQL برای استفاده آسان است و دارای طیف گسترده ای از ویژگی ها است که می توان از آنها برای ایجاد برنامه های کاربردی قدرتمند و کارآمد استفاده کرد.
کلیک هاوس
ClickHouse یک سیستم مدیریت پایگاه داده ستون گرا منبع باز است که توسط Yandex توسعه یافته است. برای ارائه عملکرد بالا برای پرس و جوهای تحلیلی طراحی شده است.
ClickHouse از یک زبان پرس و جو مانند SQL برای جست و جوی داده ها استفاده می کند و از انواع مختلف داده از جمله اعداد صحیح، رشته ها، تاریخ ها و شناورها پشتیبانی می کند. ویژگی های مختلفی مانند خوشه بندی، پردازش پرس و جو توزیع شده و تحمل خطا را ارائه می دهد. همچنین از تکرار و به اشتراک گذاری داده ها پشتیبانی می کند. با مراجعه به قسمت اول این مجموعه می توانید اطلاعات بیشتری در مورد این پایگاه داده کسب کنید:
اکنون می توانیم در مورد مقایسه عملکرد صحبت کنیم.
مطالعه موردی مقایسه
من از روش مخزن Clickbench برای مطالعه موردی پیروی کرده ام. از بازدید مجموعه داده به دست آمده از ثبت ترافیک واقعی یکی از بزرگترین پلتفرم های تحلیل وب جهان. hits شامل حدود 100 میلیون ردیف به عنوان یک میز تخت. این مخزن بیش از 20 پایگاه داده را در مورد زمان بارگذاری مجموعه داده، زمان سپری شده برای 43 پرس و جو OLAP و فضای ذخیره سازی اشغال شده مطالعه می کند. در اینجا می توانید به نتایج تجسم شده آنها دسترسی پیدا کنید.
ClickBench: معیاری برای پایگاه داده های تحلیلی
https://benchmark.clickhouse.com/
بحث: https://news.ycombinator.com/item?id=32084571
بررسی اجمالی
این معیار حجم کار معمولی را در حوزه‌های زیر نشان می‌دهد: تحلیل جریان کلیک و ترافیک، تجزیه و تحلیل وب، داده‌های تولید شده توسط ماشین، گزارش‌های ساختاری، و داده‌های رویدادها. این پرس و جوهای معمولی در تجزیه و تحلیل موقت و داشبوردهای بلادرنگ را پوشش می دهد.
مجموعه داده این معیار از ثبت ترافیک واقعی یکی از بزرگترین پلتفرم های تحلیل وب در جهان به دست آمده است. در حالی که تمام توزیع های ضروری داده ها را حفظ می کند، ناشناس است. مجموعه پرس و جوها برای منعکس کردن بارهای کاری واقع بینانه بداهه شده است، در حالی که پرس و جوها مستقیماً از تولید نیستند.
اهداف
اهداف اصلی این معیار عبارتند از:
تکرارپذیری
شما می توانید به سرعت هر تست را در کمتر از 20 دقیقه (اگرچه برخی از سیستم ها ممکن است چندین ساعت طول بکشد) را به صورت نیمه خودکار تکرار کنید. راه اندازی آزمایشی مستند شده است و از ماشین های مجازی ابری ارزان قیمت استفاده می کند. فرآیند تست در قالب یک پوسته اسکریپت مستند شده است که…
برای بررسی عملکرد ClickHouse و MySQL به طور خاص، من 10 میلیون ردیف از جدول را جدا کردم و برخی از پرس و جوهای از پیش تعریف شده را انتخاب کردم که می تواند نظر ما را واضح تر کند. این پرس‌و‌جوها عمدتاً به روش OLAP هستند، بنابراین آنها فقط نقاط قوت ClickHouse را در مقایسه با MySQL نشان می‌دهند (یعنی MySQL در تمام آن کوئری‌ها ضرر می‌کند). از این رو، من پرس و جوهای دیگری را اضافه کردم که عکس آن را نشان می دهند (پرس و جوهای OLTP). اگرچه من معیار را به این دو پایگاه داده محدود کرده ام، اما می توانید این مفهوم را به سایر DBMS های ردیف گرا و ستون گرا تعمیم دهید.
سلب مسئولیت: این معیار تنها تفاوت اصلی بین پایگاه های داده ستون محور و ردیف گرا را در مورد عملکرد و موارد استفاده آنها روشن می کند. نباید به عنوان مرجعی برای موارد استفاده شما در نظر گرفته شود. از این رو، برای دستیابی به بهترین تصمیم، باید معیارهای خود را با پرس و جوهای خود انجام دهید.
مشخصات سیستم
پایگاه های داده بر روی اوبونتو 22.04 LTS روی سیستمی با مشخصات زیر نصب می شوند:
- CPU: Intel® Core™ i7-10510U CPU @ 1.80GHz × 8
- رم: 16 گیگابایت
- فضای ذخیره سازی: 256 گیگابایت SSD
جریان معیار
- پایگاه داده ایجاد می شود.
- جدول با DDL تعریف شده ایجاد می شود.
- داده ها (
hits.tsv) در جدول بارگذاری می شود و زمان آن اندازه گیری می شود. - کوئری ها اجرا می شوند و زمان سپری شده هر پرس و جو اندازه گیری می شود.
پرس و جوها
| شماره پرس و جو | بیانیه | تایپ کنید |
|---|---|---|
| 1 | SELECT COUNT(*) FROM hits; |
OLAP |
| 2 | SELECT SUM(AdvEngineID), COUNT(*), AVG(ResolutionWidth) FROM hits; |
OLAP |
| 3 | SELECT URL, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND DontCountHits = 0 AND IsRefresh = 0 AND URL <> '' GROUP BY URL ORDER BY PageViews DESC LIMIT 10; |
OLAP |
| 4 | SELECT WatchID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM hits GROUP BY WatchID, ClientIP ORDER BY c DESC LIMIT 10; |
OLAP |
| 5 | SELECT EventTime, WatchID FROM hits WHERE CounterID = 38 AND EventDate="2013-07-15" AND UserID = '1387668437822950552' AND WatchID = '8899477221003616239'; |
OLTP |
| 6 | SELECT Title, URL, Referer FROM hits WHERE CounterID = 38 AND EventDate="2013-07-15" AND UserID = '1387668437822950552' AND WatchID = '8899477221003616239'; |
OLTP |
| 7 | UPDATE hits SET Title="my title", URL='my url', Referer="my referer" WHERE CounterID = 38 AND EventDate="2013-07-15" AND UserID = '1387668437822950552' AND WatchID = '8899477221003616239'; |
OLTP |
نتایج
من نتایج را در چهار دسته مطالعه خواهم کرد:
- بار مجموعه داده
- اندازه جدول
- اجرای کوئری ها را بخوانید
- اجرای درخواست به روز رسانی: من در مورد درخواست به روز رسانی (پرسمان شماره 7) به طور جداگانه بحث کرده ام زیرا نیاز به بحث و توجه بیشتری دارد.
بار مجموعه داده
| کلیک هاوس | MySQL | نسبت |
|---|---|---|
| دهه 65 | 11 متر 35 ثانیه | x10.7 |
به لطف LSM و شاخص های پراکنده، زمان بارگذاری ClickHouse بسیار سریعتر از MySQL است که از BTree استفاده می کند. با این حال، کارایی درج های ClickHouse در درج های انبوه به جای بسیاری از درج های منفرد قابل مشاهده است. این رفتار از این واقعیت ناشی می شود که برای هر درج قطعات تغییرناپذیر ایجاد می کند و تمایلی به تغییر، حذف یا ایجاد داده های آن برای چند ردیف ندارد.
اندازه جدول
| کلیک هاوس (گیگابایت) | MySQL (GiB) | نسبت |
|---|---|---|
| 1.3 | 6.32 | x4.86 |
ساختار ستون گرا این توانایی را می دهد متراکم سازی داده ها، چیزی که در پایگاه داده های ردیف گرا موجود نیست. به همین دلیل است که ClickHouse می تواند به تیم هایی که حجم بالایی از داده را ذخیره می کنند، لطف عملی کند و هزینه ذخیره سازی را کاهش دهد.
اجرای کوئری ها را بخوانید
| شماره پرس و جو | کلیک هاوس (ها) | MySQL (های) | نسبت |
|---|---|---|---|
| 1 | 0.005 | 7.79 | x1558 |
| 2 | 0.030 | 16.0 | x533.3 |
| 3 | 0.193 | 4.35 | x22.5 |
| 4 | 2.600 | 180.93 | x69.58 |
| 5 | 0.01 | 0.00 | x0 |
| 6 | 0.011 | 0.00 | x0 |
شاخص پراکنده ClickHouse و ساختار ستون گرا در تمام پرس و جوهای OLAP (اعداد 1 تا 4) از MySQL بهتر عمل کرده است. به همین دلیل است که BI و تحلیلگران داده از ClickHouse برای گزارش های روزانه خود راضی هستند.
با این حال، MySQL در نبرد پیروز می شود که نوبت به پرس و جوهای OLTP می رسد (شماره 5 و 6). Btree (تجهیز شده توسط MySQL) در واقع برای پرس و جوهای نقطه ای که در آن تراکنش های کوتاهی که نیاز به ردیف های کمی دارند، بهتر عمل می کند.
به روز رسانی اجرای پرس و جو
برای درخواست به روز رسانی (شماره 7)، باید یک پرس و جو متفاوت را در ClickHouse اجرا کنیم زیرا از به روز رسانی ها به روش ساده ای پشتیبانی نمی کند. Alter دستور باید استفاده شود:
ALTER TABLE hits UPDATE JavaEnable=0 WHERE CounterID = 38 AND EventDate = '2013-07-15' AND UserID = '1387668437822950552' AND WatchID = '8899477221003616239';
علاوه بر این، ClickHouse به روز رسانی را به صورت ناهمزمان اعمال می کند. برای اینکه فوراً به نتیجه برسید، باید یک را انجام دهید optimize دستور:
OPTIMIZE TABLE hits FINAL;
با اجرای عبارت پرس و جو شماره 7 نشان داده شده در جدول پرس و جو برای MySQL و دو عبارت SQL بالا برای ClickHouse، به نتایج زیر می رسیم:
| شماره پرس و جو | کلیک هاوس (ها) | MySQL (های) | نسبت |
|---|---|---|---|
| 7 | 26 | 0.00 | 0 |
باز هم، تنفر از جهش ClickHouse باعث می‌شود در مقایسه با MySQL، برای به‌روزرسانی‌های هم‌زمان (و به طور مشابه حذف‌ها) بازنده باشد. در نتیجه، روش‌های دیگری مانند deduplication با استفاده از ReplacingMergeTree می‌تواند برای مدیریت به‌روزرسانی‌ها استفاده شود. منابع ارزشمند را می توانید در لینک های زیر بیابید:
نتیجه
در این پست پایگاه داده های MySQL و ClickHouse را محک زدم تا برخی از نقاط قوت و ضعف آنها را بررسی کنم که ممکن است به ما در انتخاب راه حل مناسب کمک کند. به طور خلاصه:
- MySQL در پرس و جوهای pointy و OLTP بهتر عمل می کند.
- ClickHoues در پرس و جوهای OLAP بهتر عمل می کند.
- ClickHouse برای به روز رسانی و حذف مکرر طراحی نشده است. شما باید آنها را با روش های deduplication مدیریت کنید.
- ClickHouse به لطف ساختار ستون گرا، هزینه ذخیره سازی را کاهش می دهد.
- زمان بارگذاری انبوه درج‌های ClickHouse به مراتب بهتر از MySQL عمل می‌کند.
