🥇تست عملکرد کوئری های تحلیلی در PostgreSQL، ClickHouse و clickhousedb_fdw (PostgreSQL)

در این مطالعه، من می‌خواستم ببینم با استفاده از منبع داده ClickHouse به جای PostgreSQL، چه پیشرفت‌هایی در عملکرد می‌توان به دست آورد. من از مزایای بهره وری که از استفاده از ClickHouse بدست می آورم می دانم. اگر با استفاده از یک بسته داده خارجی (FDW) از PostgreSQL به ClickHouse دسترسی پیدا کنم، آیا این مزایا ادامه خواهند داشت؟

محیط های پایگاه داده مورد مطالعه عبارتند از PostgreSQL v11، clickhousedb_fdw و پایگاه داده ClickHouse. در نهایت، از PostgreSQL v11، ما پرس و جوهای مختلف SQL را اجرا خواهیم کرد که از طریق clickhousedb_fdw خود به پایگاه داده ClickHouse هدایت می شوند. سپس خواهیم دید که عملکرد FDW چگونه با همان جستارهای اجرا شده در PostgreSQL بومی و ClickHouse بومی مقایسه می شود.

پایگاه داده کلیک هاوس

ClickHouse یک سیستم مدیریت پایگاه داده ستونی منبع باز است که می تواند عملکردی 100 تا 1000 برابر سریعتر از رویکردهای پایگاه داده سنتی داشته باشد و قادر است بیش از یک میلیارد ردیف را در کمتر از یک ثانیه پردازش کند.

Clickhousedb_fdw

clickhousedb_fdw - پوشش داده خارجی برای پایگاه داده ClickHouse یا FDW، یک پروژه منبع باز از Percona است. در اینجا پیوندی به مخزن GitHub پروژه وجود دارد.

در ماه مارس وبلاگی نوشتم که در مورد FDW ما بیشتر به شما می گوید.

همانطور که خواهید دید، این یک FDW را برای ClickHouse فراهم می کند که به پایگاه داده ClickHouse از سرور PostgreSQL v11 اجازه انتخاب و INSERT INTO را می دهد.

FDW از ویژگی های پیشرفته ای مانند aggregate و join پشتیبانی می کند. این به طور قابل توجهی عملکرد را با استفاده از منابع سرور راه دور برای این عملیات منابع فشرده بهبود می بخشد.

محیط معیار

سرور سوپرمیکرو:
- CPU Intel® Xeon® E5-2683 v3 @ 2.00GHz
- 2 سوکت / 28 هسته / 56 رشته
- حافظه: 256 گیگابایت رم
- فضای ذخیره سازی: Samsung SM863 1.9TB Enterprise SSD
- سیستم فایل: ext4/xfs
سیستم عامل: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
PostgreSQL: نسخه 11

تست های معیار

به‌جای استفاده از مجموعه داده‌های تولید شده توسط ماشین برای این آزمایش، از داده‌های «بهره‌وری بر اساس زمان گزارش شده زمان اپراتور» از سال 1987 تا 2018 استفاده کردیم. می توانید به داده ها دسترسی داشته باشید با استفاده از اسکریپت ما در اینجا موجود است.

حجم پایگاه داده 85 گیگابایت است که یک جدول 109 ستونی را ارائه می دهد.

پرس و جوهای محک

در اینجا پرس و جوهایی هستند که من برای مقایسه ClickHouse، clickhousedb_fdw و PostgreSQL استفاده کردم.

Q#
پرس و جو شامل مجموعه ها و گروه بر اساس است

Q1
SELECT DayOfWeek، شمارش(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

Q2
SELECT DayOfWeek، شمارش(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

Q3
SELECT Origin، شمارش(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;

Q4
انتخاب حامل، شمارش(() DESC;

Q5
SELECT a.Carrier, c, c2, c1000/c2 به عنوان c3 FROM (انتخاب حامل، شمارش() AS c FROM ontime WHERE DepDelay>10 AND Year=2007 GROUP BY Carrier ) a INNER JOIN ( SELECT Carrier,count(*) AS c2 FROM ontime WHERE Year=2007 GROUP BY Carrier)b on a.Carrier=b.ORDERa توسط c3 DESC;

Q6
SELECT a.Carrier, c, c2, c1000/c2 به عنوان c3 FROM (انتخاب حامل، شمارش() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Carrier) a INNER JOIN (انتخاب حامل، شمارش(*) AS c2 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY حامل ) b در a.Carrier=b.Carrier ORDER BY C3 DESC;

Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier;

Q8
انتخاب سال، میانگین (DepDelay) FROM ontime GROUP BY Year.

Q9
سال را انتخاب کنید، (*) را به عنوان c1 از گروه زمان بر اساس سال انتخاب کنید.

Q10
SELECT avg(cnt) FROM (انتخاب سال، ماه، شمارش(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY BY،Month) a;

Q11
میانگین (c1) را از (انتخاب سال، ماه، شمارش(*) به عنوان c1 از گروه زمانی بر اساس سال، ماه انتخاب کنید) a;

Q12
OriginCityName، DestCityName، count(*) AS c FROM ontime GROUP BY OriginCityName، DestCityName ORDER BY c DESC LIMIT 10;

Q13
OriginCityName را انتخاب کنید، count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;

Query شامل Joins است

Q14
SELECT a.Year، c1/c2 FROM (انتخاب سال، شمارش()1000 به عنوان c1 از زمانی که در آن زمان DepDelay> 10 گروه به سال) یک پیوستن داخلی (سال را انتخاب کنید، (*) را به عنوان c2 از زمان زمانی GROUP BY سال شمارش کنید) b در a.Year=b.Year ORDER BY BYYear;

Q15
SELECT a."Year", c1/c2 FROM ("Year" را انتخاب کنید، شمارش()1000 به عنوان c1 FROM fontime WHERE "DepDelay">10 GROUP BY "Year") یک پیوست داخلی ("Year" را انتخاب کنید، (*) را به عنوان c2 FROM FROM fontime GROUP BY "Year" بشمارید) b on a."Year"=b. "سال"؛

جدول-1: پرس و جوهای مورد استفاده در معیار

اجرای پرس و جو

در اینجا نتایج هر یک از پرس و جوها هنگام اجرا در تنظیمات مختلف پایگاه داده آمده است: PostgreSQL با و بدون ایندکس، ClickHouse بومی و clickhousedb_fdw. زمان بر حسب میلی ثانیه نشان داده می شود.

Q#
PostgreSQL و
PostgreSQL (نمایه شده)
کلیک هاوس
clickhousedb_fdw

Q1
27920
19634
23
57

Q2
35124
17301
50
80

Q3
34046
15618
67
115

Q4
31632
7667
25
37

Q5
47220
8976
27
60

Q6
58233
24368
55
153

Q7
30566
13256
52
91

Q8
38309
60511
112
179

Q9
20674
37979
31
81

Q10
34990
20102
56
148

Q11
30489
51658
37
155

Q12
39357
33742
186
1333

Q13
29912
30709
101
384

Q14
54126
39913
124
1364212

Q15
97258
30211
245
259

جدول-1: زمان صرف شده برای اجرای پرس و جوهای مورد استفاده در معیار

مشاهده نتایج

نمودار زمان اجرای پرس و جو را بر حسب میلی ثانیه، محور X عدد پرس و جو را از جداول بالا و محور Y زمان اجرا را بر حسب میلی ثانیه نشان می دهد. نتایج ClickHouse و داده های بازیابی شده از postgres با استفاده از clickhousedb_fdw نشان داده شده است. از جدول می بینید که تفاوت زیادی بین PostgreSQL و ClickHouse وجود دارد، اما تفاوت بسیار کمی بین ClickHouse و clickhousedb_fdw وجود دارد.

این نمودار تفاوت بین ClickhouseDB و clickhousedb_fdw را نشان می دهد. در اکثر پرس و جوها، سربار FDW چندان زیاد نیست و به جز برای Q12 به سختی قابل توجه است. این پرس و جو شامل اتصالات و یک بند ORDER BY است. به دلیل بند ORDER BY GROUP/BY، ORDER BY به ClickHouse نمی رسد.

در جدول 2 جهش زمانی را در پرس و جوهای Q12 و Q13 مشاهده می کنیم. باز هم، این توسط بند ORDER BY ایجاد می شود. برای تأیید این موضوع، پرسش‌های Q-14 و Q-15 را با و بدون بند ORDER BY اجرا کردم. بدون عبارت ORDER BY زمان تکمیل 259 میلی‌ثانیه و با بند ORDER BY 1364212 است. برای اشکال‌زدایی این پرس و جو، من هر دو پرس‌و‌جو را توضیح می‌دهم و در اینجا نتایج توضیح آمده است.

Q15: بدون ORDER BY بند

bm=# EXPLAIN VERBOSE SELECT a."Year", c1/c2 
     FROM (SELECT "Year", count(*)*1000 AS c1 FROM fontime WHERE "DepDelay" > 10 GROUP BY "Year") a
     INNER JOIN(SELECT "Year", count(*) AS c2 FROM fontime GROUP BY "Year") b ON a."Year"=b."Year";

Q15: درخواست بدون ترتیب توسط بند

QUERY PLAN                                                      
Hash Join  (cost=2250.00..128516.06 rows=50000000 width=12)  
Output: fontime."Year", (((count(*) * 1000)) / b.c2)  
Inner Unique: true   Hash Cond: (fontime."Year" = b."Year")  
->  Foreign Scan  (cost=1.00..-1.00 rows=100000 width=12)        
Output: fontime."Year", ((count(*) * 1000))        
Relations: Aggregate on (fontime)        
Remote SQL: SELECT "Year", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Year"  
->  Hash  (cost=999.00..999.00 rows=100000 width=12)        
Output: b.c2, b."Year"        
->  Subquery Scan on b  (cost=1.00..999.00 rows=100000 width=12)              
Output: b.c2, b."Year"              
->  Foreign Scan  (cost=1.00..-1.00 rows=100000 width=12)                    
Output: fontime_1."Year", (count(*))                    
Relations: Aggregate on (fontime)                    
Remote SQL: SELECT "Year", count(*) FROM "default".ontime GROUP BY "Year"(16 rows)

Q14: پرس و جو با ORDER BY بند

bm=# EXPLAIN VERBOSE SELECT a."Year", c1/c2 FROM(SELECT "Year", count(*)*1000 AS c1 FROM fontime WHERE "DepDelay" > 10 GROUP BY "Year") a 
     INNER JOIN(SELECT "Year", count(*) as c2 FROM fontime GROUP BY "Year") b  ON a."Year"= b."Year" 
     ORDER BY a."Year";

Q14: طرح پرس و جو با ORDER BY بند

QUERY PLAN 
Merge Join  (cost=2.00..628498.02 rows=50000000 width=12)   
Output: fontime."Year", (((count(*) * 1000)) / (count(*)))   
Inner Unique: true   Merge Cond: (fontime."Year" = fontime_1."Year")   
->  GroupAggregate  (cost=1.00..499.01 rows=1 width=12)        
Output: fontime."Year", (count(*) * 1000)         
Group Key: fontime."Year"         
->  Foreign Scan on public.fontime  (cost=1.00..-1.00 rows=100000 width=4)               
Remote SQL: SELECT "Year" FROM "default".ontime WHERE (("DepDelay" > 10)) 
            ORDER BY "Year" ASC   
->  GroupAggregate  (cost=1.00..499.01 rows=1 width=12)         
Output: fontime_1."Year", count(*)         Group Key: fontime_1."Year"         
->  Foreign Scan on public.fontime fontime_1  (cost=1.00..-1.00 rows=100000 width=4) 
              
Remote SQL: SELECT "Year" FROM "default".ontime ORDER BY "Year" ASC(16 rows)

نتیجه

نتایج این آزمایش ها نشان می دهد که ClickHouse عملکرد واقعاً خوبی را ارائه می دهد و clickhousedb_fdw مزایای عملکرد ClickHouse را از PostgreSQL ارائه می دهد. در حالی که هنگام استفاده از clickhousedb_fdw مقداری سربار وجود دارد، اما قابل مقایسه با عملکردی است که با اجرای بومی در پایگاه داده ClickHouse به دست می آید. این همچنین تأیید می کند که fdw در PostgreSQL نتایج عالی ارائه می دهد.

چت تلگرام از طریق کلیک هاوس https://t.me/clickhouse_ru
چت تلگرام با استفاده از PostgreSQL https://t.me/pgsql

منبع: www.habr.com

تست عملکرد پرس و جوهای تحلیلی در PostgreSQL، ClickHouse و clickhousedb_fdw (PostgreSQL)