PostgreSQL, ClickHouse र clickhousedb_fdw (PostgreSQL) मा विश्लेषणात्मक प्रश्नहरूको प्रदर्शन परीक्षण गर्दै

यस अध्ययनमा, म PostgreSQL को सट्टा ClickHouse डाटा स्रोत प्रयोग गरेर के प्रदर्शन सुधारहरू हासिल गर्न सकिन्छ भनेर हेर्न चाहन्थें। मलाई क्लिकहाउस प्रयोग गरेर प्राप्त हुने उत्पादकत्व लाभहरू थाहा छ। यदि मैले विदेशी डाटा र्यापर (FDW) प्रयोग गरेर PostgreSQL बाट ClickHouse पहुँच गरेमा यी लाभहरू जारी रहन्छ?

अध्ययन गरिएका डाटाबेस वातावरणहरू PostgreSQL v11, clickhousedb_fdw र ClickHouse डाटाबेस हुन्। अन्ततः, PostgreSQL v11 बाट हामी हाम्रो clickhousedb_fdw मार्फत ClickHouse डाटाबेसमा विभिन्न SQL क्वेरीहरू चलाउनेछौं। हामी त्यसपछि कसरी FDW को कार्यसम्पादन नेटिभ PostgreSQL र नेटिभ ClickHouse मा चलिरहेको समान प्रश्नहरूसँग तुलना गर्छ देख्नेछौं।

क्लिकहाउस डाटाबेस

ClickHouse एक खुला स्रोत स्तम्भ डाटाबेस व्यवस्थापन प्रणाली हो जसले परम्परागत डाटाबेस दृष्टिकोणहरू भन्दा 100-1000 गुणा छिटो प्रदर्शन हासिल गर्न सक्छ, एक सेकेन्ड भन्दा कममा एक अरब पङ्क्तिहरू प्रशोधन गर्न सक्षम छ।

Clickhousedb_fdw

clickhousedb_fdw - ClickHouse डाटाबेस, वा FDW को लागि बाह्य डाटा र्यापर, Percona को एक खुला स्रोत परियोजना हो। यहाँ परियोजनाको GitHub भण्डारको लिङ्क छ.

मार्चमा मैले एउटा ब्लग लेखें जसले तपाईंलाई हाम्रो FDW बारे थप बताउँछ.

तपाईले देख्नुहुनेछ, यसले ClickHouse को लागि FDW प्रदान गर्दछ जसले PostgreSQL v11 सर्भरबाट ClickHouse डाटाबेस बाट SELECT, र INSERT INTO लाई अनुमति दिन्छ।

FDW ले उन्नत सुविधाहरूलाई समर्थन गर्दछ जस्तै समग्र र सामेल। यसले यी स्रोत-गहन अपरेशनहरूको लागि रिमोट सर्भरको स्रोतहरू प्रयोग गरेर कार्यसम्पादनमा उल्लेखनीय सुधार गर्दछ।

बेन्चमार्क वातावरण

• सुपरमाइक्रो सर्भर:
  • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
  • 2 सकेटहरू / 28 कोर / 56 थ्रेडहरू
  • मेमोरी: GB जीबीको र्याम
  • भण्डारण: Samsung SM863 1.9TB Enterprise SSD
  • फाइल प्रणाली: ext4/xfs
• OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
• PostgreSQL: संस्करण 11

बेन्चमार्क परीक्षणहरू

यस परीक्षणको लागि केही मेसिन-उत्पन्न डेटा सेट प्रयोग गर्नुको सट्टा, हामीले 1987 देखि 2018 सम्मको "समय रिपोर्ट गरिएको अपरेटर समय द्वारा उत्पादकता" डेटा प्रयोग गर्यौं। तपाईं डाटा पहुँच गर्न सक्नुहुन्छ यहाँ उपलब्ध हाम्रो लिपि प्रयोग गरेर.

डाटाबेस आकार 85 GB छ, 109 स्तम्भहरूको एउटा तालिका प्रदान गर्दछ।

बेन्चमार्क प्रश्नहरू

यहाँ मैले क्लिकहाउस, clickhousedb_fdw र PostgreSQL तुलना गर्न प्रयोग गरेको प्रश्नहरू छन्।

Q#
क्वेरीमा समुह र समूह समावेश छ

Q1
DayOfWeek चयन गर्नुहोस्, गणना (*) बाट c बाट ontime जहाँ वर्ष >= 2000 र वर्ष <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

Q2
DayOfWeek चयन गर्नुहोस्, गन्नुहोस्

Q3
उत्पत्ति चयन गर्नुहोस्, गणना (*) को रूपमा c FROM ontime WHERE DepDelay>10 र वर्ष >= 2000 र वर्ष <= 2008 GROUP Origin ORDER BY c DESC LIMIT 10;

Q4
क्यारियर चयन गर्नुहोस्, गणना () बाट ओनटाइम कहाँ DepDelay> 10 र वर्ष = 2007 समूह द्वारा वाहक क्रम गणना द्वारा () DESC;

Q5
चयन गर्नुहोस् a. क्यारियर, c, c2, c1000/c2 बाट c3 को रूपमा ( चयन गर्नुहोस् क्यारियर, गणना () AS c FROM ontime HERE DepDelay>10 र वर्ष = वाहक द्वारा समूह ) एक भित्री सामेल हुनुहोस् ( चयन गर्नुहोस् क्यारियर, गणना (*) को रूपमा c2007 बाट ओनटाइम जहाँ वर्ष = 2 वाहक द्वारा समूह) b मा a.Carrier=b.ORDERCarrier C2007 DESC द्वारा;

Q6
चयन गर्नुहोस् a. क्यारियर, c, c2, c1000/c2 बाट c3 को रूपमा ( चयन गर्नुहोस् क्यारियर, गणना () c FROM ontime कहाँ DepDelay> 10 र वर्ष >= 2000 र वर्ष <= 2008 GROUP BY वाहक) एक भित्री सामेल हुनुहोस् ( वाहक चयन गर्नुहोस्, गणना गर्नुहोस् (*) c2 बाट ontime जहाँ वर्ष >= 2000 र वर्ष <= BY 2008 क्यारियर ) b on a.Carrier=b.Carrier ORDER BY c3 DESC;

Q7
क्यारियर चयन गर्नुहोस्, औसत (DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 र वर्ष <= 2008 GROUP BY वाहक;

Q8
वर्ष चयन गर्नुहोस्, औसत (DepDelay) FROM ontime GROUP BY वर्ष;

Q9
वर्ष चयन गर्नुहोस्, वर्ष अनुसार अनटाइम समूहबाट c1 को रूपमा गणना (*);

Q10
बाट औसत (सीएनटी) चयन गर्नुहोस् (वर्ष, महिना, गणना (*) सीन्टको रूपमा एक समयबाट चयन गर्नुहोस् जहाँ DepDel15=1 वर्ष, महिना अनुसार समूह) a;

Q11
बाट औसत (c1) चयन गर्नुहोस् (वर्ष, महिना, गणना (*) c1 को रूपमा वर्ष, महिना द्वारा अनटाइम समूहबाट चयन गर्नुहोस्) a;

Q12
चयन गर्नुहोस् OriginCityName, DestCityName, गणना(*) को रूपमा c FROM FROM GROUP OriginCityName द्वारा, DestCityName ORDER by c DESC LIMIT 10;

Q13
OriginCityName चयन गर्नुहोस्, C DESC LIMIT 10 द्वारा OriginCityName ORDER BY OriginCityName बाट OriginCityName बाट c बाट गणना(*);

क्वेरीमा जोडिएको छ

Q14
चयन गर्नुहोस् a.Year, c1/c2 FROM ( वर्ष चयन गर्नुहोस्, गणना ()1000 c1 को रूपमा ontime बाट WHERE DepDelay>10 GROUP BY) भित्री सामेल हुनुहोस् (वर्ष चयन गर्नुहोस्, गणना गर्नुहोस् (*) c2 को रूपमा ontime GROUP BY Year ) b on a.Year=b.Year ORDER BY a.Year;

Q15
चयन गर्नुहोस् a."वर्ष", c1/c2 बाट ("वर्ष" चयन गर्नुहोस्, गणना ()1000 c1 को रूपमा फन्टटाइम बाट जहाँ "DepDelay"> "वर्ष" द्वारा 10 समूह) एक भित्री जोइन ("वर्ष" चयन गर्नुहोस्, गणना गर्नुहोस् (*) फन्टटाइम समूहबाट "वर्ष" बाट c2 को रूपमा ) b मा a.”Year”=b। "वर्ष";

तालिका-१: बेन्चमार्कमा प्रयोग गरिएका प्रश्नहरू

क्वेरी कार्यान्वयन

विभिन्न डाटाबेस सेटिङहरूमा चलाउँदा प्रत्येक प्रश्नका नतिजाहरू यहाँ छन्: 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

तालिका-१: बेन्चमार्कमा प्रयोग गरिएका प्रश्नहरू कार्यान्वयन गर्न लाग्ने समय

नतिजाहरू हेर्नुहोस्

ग्राफले क्वेरी कार्यान्वयन समय मिलिसेकेन्डमा देखाउँछ, X अक्षले माथिको तालिकाबाट क्वेरी नम्बर देखाउँछ, र Y अक्षले मिलिसेकेन्डमा कार्यान्वयन समय देखाउँछ। 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 खण्डको कारणले भएको हो। यो पुष्टि गर्न, मैले ORDER BY खण्डको साथ र बिना Q-14 र Q-15 प्रश्नहरू चलाएँ। ORDER BY खण्ड बिना पूरा हुने समय 259ms छ र ORDER BY खण्डको साथ यो 1364212 हो। यो क्वेरी डिबग गर्नको लागि म दुबै प्रश्नहरूको व्याख्या गर्दैछु र यहाँ स्पष्टीकरणको नतिजाहरू छन्।

Q15: क्लज द्वारा आदेश बिना

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 को साथ क्वेरी

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 ले PostgreSQL बाट ClickHouse को प्रदर्शन लाभहरू प्रदान गर्दछ। clickhousedb_fdw प्रयोग गर्दा केही ओभरहेड हुँदा, यो क्लिकहाउस डाटाबेसमा नेटिभ रूपमा चलाएर हासिल गरेको प्रदर्शनसँग नगण्य र तुलना गर्न मिल्ने छ। यसले PostgreSQL मा fdw ले उत्कृष्ट नतिजाहरू प्रदान गर्दछ भनेर पनि पुष्टि गर्छ।

Clickhouse मार्फत टेलिग्राम च्याट https://t.me/clickhouse_ru
PostgreSQL प्रयोग गरेर टेलिग्राम च्याट https://t.me/pgsql

स्रोत: www.habr.com