🥇اختبار أداء الاستعلامات التحليلية في PostgreSQL وClickHouse وclickhousedb_fdw (PostgreSQL)

في هذه الدراسة، أردت أن أرى تحسينات الأداء التي يمكن تحقيقها باستخدام مصدر بيانات ClickHouse بدلاً من PostgreSQL. أعرف فوائد الإنتاجية التي أحصل عليها من استخدام ClickHouse. هل ستستمر هذه الفوائد إذا قمت بالوصول إلى ClickHouse من PostgreSQL باستخدام برنامج تغليف البيانات الأجنبية (FDW)؟

بيئات قواعد البيانات التي تمت دراستها هي PostgreSQL v11 وclickhousedb_fdw وClickHouse. في النهاية، بدءًا من الإصدار 11 من PostgreSQL، سنقوم بتشغيل العديد من استعلامات SQL التي يتم توجيهها عبر Clickhousedb_fdw إلى قاعدة بيانات ClickHouse. سنرى بعد ذلك كيف يقارن أداء FDW بنفس الاستعلامات التي تعمل في PostgreSQL الأصلي وClickHouse الأصلي.

قاعدة بيانات كليكهاوس

ClickHouse هو نظام إدارة قواعد بيانات عمودي مفتوح المصدر يمكنه تحقيق أداء أسرع بمعدل 100-1000 مرة من أساليب قواعد البيانات التقليدية، وهو قادر على معالجة أكثر من مليار صف في أقل من ثانية.

Clickhousedb_fdw

Clickhousedb_fdw - مجمع البيانات الخارجية لقاعدة بيانات ClickHouse، أو FDW، هو مشروع مفتوح المصدر من Percona. فيما يلي رابط لمستودع GitHub الخاص بالمشروع.

في شهر مارس، قمت بكتابة مدونة تخبرك بالمزيد عن FDW لدينا.

كما سترى، يوفر هذا FDW لـ ClickHouse الذي يسمح بالاختيار من قاعدة بيانات ClickHouse وإدراجها في خادم PostgreSQL v11.

يدعم FDW الميزات المتقدمة مثل التجميع والانضمام. يؤدي هذا إلى تحسين الأداء بشكل ملحوظ باستخدام موارد الخادم البعيد لهذه العمليات كثيفة الاستخدام للموارد.

البيئة المرجعية

خادم سوبرمايكرو:
- وحدة المعالجة المركزية Intel® Xeon® E5-2683 v3 @ 2.00 جيجا هرتز
- 2 مقابس / 28 نواة / 56 خيطًا
- الذاكرة: 256GB من ذاكرة الوصول العشوائي
- التخزين: Samsung SM863 1.9TB Enterprise SSD
- نظام الملفات: ext4/xfs
نظام التشغيل: Linux sblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
PostgreSQL: الإصدار 11

الاختبارات المعيارية

بدلاً من استخدام بعض مجموعات البيانات التي تم إنشاؤها بواسطة الآلة لهذا الاختبار، استخدمنا بيانات "الإنتاجية حسب وقت المشغل المُبلغ عنه" من عام 1987 إلى عام 2018. يمكنك الوصول إلى البيانات باستخدام البرنامج النصي لدينا متاح هنا.

حجم قاعدة البيانات هو 85 جيجابايت، وتوفر جدولًا واحدًا مكونًا من 109 عمودًا.

الاستعلامات المعيارية

فيما يلي الاستعلامات التي استخدمتها لمقارنة ClickHouse وclickhousedb_fdw وPostgreSQL.

Q#
يحتوي الاستعلام على التجميعات والتجميع حسب

Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

Q2
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

Q3
SELECT Origin, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;

Q4
حدد الناقل، العد () من وقت التشغيل حيث DepDelay> 10 والسنة = 2007 المجموعة حسب الناقل ترتيب حسب العدد() DESC؛

Q5
حدد a.Carrier، c، c2، c1000/c2 كـ c3 FROM (اختر الناقل، العد() AS c من ontime حيث 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.Carrier ORDER بواسطة c3 DESC؛

Q6
حدد a.Carrier، c، c2، c1000/c2 كـ c3 FROM (اختر الناقل، العد() AS c FROM ontime حيث DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Carrier) a INNER JOIN ( SELECT Carrier، count(*) AS c2 FROM ontime حيث سنة >= 2000 AND Year <= 2008 GROUP BY الناقل ) ب على 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) من مجموعة ontime حسب السنة؛

Q9
حدد السنة، واحسب (*) كـ c1 من مجموعة ontime حسب السنة؛

Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) أ؛

Q11
حدد متوسط (c1) من (حدد سنة، شهر، العد (*) كـ c1 من مجموعة ontime حسب السنة، الشهر) أ؛

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

Q13
SELECT OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;

يحتوي الاستعلام على الصلات

Q14
حدد سنة، c1/c2 من (حدد سنة، عد()1000 كـ c1 من ontime WHERE DepDelay>10 GROUP BY Year) INNER JOIN (حدد السنة، عد (*) كـ c2 من ontime GROUP BY Year ) b على a.Year=b.Year ORDER BY a.Year؛

Q15
اختر أ."السنة"، c1/c2 من (حدد "السنة"، العد()1000 كـ c1 من الخط حيث "DepDelay">10 GROUP BY "Year") a INNER JOIN (اختر "Year"، عد (*) كـ c2 FROM Fontime GROUP BY "Year" ) b على a."Year"=b. "سنة"؛

الجدول 1: الاستعلامات المستخدمة في المعيار

عمليات تنفيذ الاستعلام

فيما يلي نتائج كل من الاستعلامات عند تشغيلها في إعدادات قاعدة بيانات مختلفة: PostgreSQL مع الفهارس وبدونها، وClickHouse الأصلي، وclickhousedb_fdw. يظهر الوقت بالمللي ثانية.

Q#
كيو
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. لتصحيح هذا الاستعلام، أقوم بشرح كلا الاستعلامين وهنا نتائج الشرح.

س15: بدون بند الترتيب

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";

س15: الاستعلام بدون ترتيب حسب البند

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)

س14: الاستعلام عن الترتيب حسب البند

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";

س14: خطة الاستعلام مع بند 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 يوفر نتائج ممتازة.

دردشة برقية عبر Clickhouse https://t.me/clickhouse_ru
دردشة برقية باستخدام PostgreSQL https://t.me/pgsql

المصدر: www.habr.com

اختبار أداء الاستعلامات التحليلية في PostgreSQL وClickHouse وclickhousedb_fdw (PostgreSQL)