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

इस अध्ययन में, मैं यह देखना चाहता था कि PostgreSQL के बजाय ClickHouse डेटा स्रोत का उपयोग करके क्या प्रदर्शन सुधार प्राप्त किए जा सकते हैं। मैं 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 के लिए बाहरी डेटा रैपर, पेरकोना का एक ओपन सोर्स प्रोजेक्ट है। यहां प्रोजेक्ट के GitHub रिपॉजिटरी का लिंक दिया गया है.

मार्च में मैंने एक ब्लॉग लिखा था जो आपको हमारे FDW के बारे में और अधिक बताता है.

जैसा कि आप देखेंगे, यह ClickHouse के लिए एक FDW प्रदान करता है जो PostgreSQL v11 सर्वर से ClickHouse डेटाबेस को SELECT from और INSERT INTO की अनुमति देता है।

FDW एग्रीगेट और जॉइन जैसी उन्नत सुविधाओं का समर्थन करता है। यह इन संसाधन-गहन संचालनों के लिए दूरस्थ सर्वर के संसाधनों का उपयोग करके प्रदर्शन में उल्लेखनीय सुधार करता है।

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

• सुपरमाइक्रो सर्वर:
  • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
  • 2 सॉकेट / 28 कोर / 56 धागे
  • मेमोरी: 256GB RAM
  • भंडारण: सैमसंग SM863 1.9TB एंटरप्राइज SSD
  • फ़ाइल सिस्टम: ext4/xfs
• ओएस: लिनक्स sblade01 4.15.0-42-जेनेरिक #45~16.04.1-उबंटू
• पोस्टग्रेएसक्यूएल: संस्करण 11

बेंचमार्क परीक्षण

इस परीक्षण के लिए कुछ मशीन-जनित डेटा सेट का उपयोग करने के बजाय, हमने 1987 से 2018 तक "समय रिपोर्ट ऑपरेटर समय द्वारा उत्पादकता" डेटा का उपयोग किया। आप डेटा तक पहुंच सकते हैं यहां उपलब्ध हमारी स्क्रिप्ट का उपयोग करना.

डेटाबेस का आकार 85 जीबी है, जो 109 कॉलम की एक तालिका प्रदान करता है।

बेंचमार्क प्रश्न

यहां वे प्रश्न हैं जिनका उपयोग मैंने ClickHouse, clickhousedb_fdw और PostgreSQL की तुलना करने के लिए किया था।

Q#
क्वेरी में समुच्चय और समूह शामिल हैं

Q1
सप्ताह के दिन का चयन करें, ऑनटाइम से (*) को सी के रूप में गिनें जहां वर्ष >= 2000 और वर्ष <= 2008 सप्ताह के दिन के अनुसार समूह, सी डीईएससी द्वारा आदेश;

Q2
डेऑफ़वीक का चयन करें, ऑनटाइम से गिनें (*) सी के रूप में जहां डेपडिले>10 और वर्ष >= 2000 और वर्ष <= 2008 ग्रुप बाय डेऑफ़वीक ऑर्डर बाय सी डीईएससी;

Q3
उत्पत्ति का चयन करें, गिनें (*) AS c ऑनटाइम से जहां DepDelay>10 और वर्ष >= 2000 और वर्ष <= 2008 उत्पत्ति के अनुसार समूह, C DESC LIMIT 10 द्वारा समूह;

Q4
वाहक का चयन करें, गिनती करें() समय से जहां DepDelay>10 और वर्ष = 2007 ग्रुप बाय कैरियर ऑर्डर बाय काउंट() डीईएससी;

Q5
ए.कैरियर, सी, सी2, सी चुनें1000/c2 को c3 के रूप में FROM (वाहक चुनें, गिनती करें() AS c समय से जहां DepDelay>10 और वर्ष = 2007 वाहक द्वारा समूह) एक आंतरिक जोड़ (वाहक का चयन करें, गिनती (*) AS c2 से ontime से जहां वर्ष = 2007 वाहक द्वारा समूह)b पर a.Carrier=b.वाहक आदेश सी3 डीईएससी द्वारा;

Q6
ए.कैरियर, सी, सी2, सी चुनें1000/c2 को c3 के रूप में FROM (वाहक चुनें, गिनती करें() एएस सी फ्रॉम ऑनटाइम जहां डेपडिले>10 और वर्ष >= 2000 और वर्ष <= 2008 ग्रुप बाय कैरियर) एक इनर जॉइन (सेलेक्ट कैरियर, गिनती(*) एएस सी2 फ्रॉम ऑनटाइम जहां ईयर >= 2000 और वर्ष <= 2008 ग्रुप बाय कैरियर ) बी ऑन ए.कैरियर=बी.कैरियर ऑर्डर बाय सी3 डीईएससी;

Q7
कैरियर का चयन करें, औसत (डिपेले) * 1000 एएस सी3 ऑनटाइम से जहां वर्ष >= 2000 और वर्ष <= 2008 कैरियर द्वारा समूह;

Q8
वर्ष दर वर्ष समूह में से वर्ष, औसत (डिपडिले) का चयन करें;

Q9
वर्ष चुनें, वर्ष के अनुसार ऑनटाइम समूह से (*) को c1 के रूप में गिनें;

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

Q11
(वर्ष, माह, गिनती (*) को वर्ष, माह के अनुसार ऑनटाइम समूह से सी1 के रूप में चुनें) से औसत (सी1) चुनें;

Q12
ओरिजिनसिटीनाम, डेस्टसिटीनाम, काउंट(*) को ऑनटाइम ग्रुप से ओरिजिनसिटीनाम, डेस्टसिटीनाम ऑर्डर द्वारा सी डीईएससी लिमिट 10 से चुनें;

Q13
ओरिजिन सिटी नाम का चयन करें, ओरिजिन सिटी नाम ऑर्डर द्वारा सी डीईएससी सीमा 10 द्वारा ऑनटाइम ग्रुप से सी के रूप में गिनती (*) करें;

क्वेरी में जोड़ शामिल हैं

Q14
a.Year, c1/c2 FROM चुनें (वर्ष चुनें, गिनती करें()1000 ऑनटाइम से सी1 के रूप में जहां डेपडिले>10 ग्रुप बाय ईयर) एक इनर जॉइन (वर्ष का चयन करें, ऑनटाइम ग्रुप बाय ईयर से सी2 के रूप में गिनती (*)) बी पर ए.ईयर=बी.ईयर ऑर्डर बाय ए.ईयर;

Q15
a.'वर्ष', c1/c2 FROM चुनें ('वर्ष' चुनें'', गिनती करें()फॉन्टटाइम से सी1000 के रूप में 1 जहां "डेपडिले">10 ग्रुप बाय "ईयर") एक इनर जॉइन ("ईयर" ​​चुनें, फॉन्टटाइम ग्रुप से सी2 के रूप में (*) को "ईयर" ​​के हिसाब से गिनें) बी ऑन ए।"ईयर"=बी। "वर्ष";

तालिका-1: बेंचमार्क में प्रयुक्त प्रश्न

क्वेरी निष्पादन

विभिन्न डेटाबेस सेटिंग्स में चलाने पर प्रत्येक क्वेरी के परिणाम यहां दिए गए हैं: इंडेक्स के साथ और बिना पोस्टग्रेएसक्यूएल, मूल ClickHouse और clickhousedb_fdw। समय मिलीसेकेंड में दिखाया गया है.

Q#
पोस्टग्रेएसक्यूएल
पोस्टग्रेएसक्यूएल (अनुक्रमित)
क्लिकहाउस
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 परिणाम और clickhousedb_fdw का उपयोग करके पोस्टग्रेज से प्राप्त डेटा दिखाया गया है। तालिका से आप देख सकते हैं कि PostgreSQL और ClickHouse के बीच बहुत बड़ा अंतर है, लेकिन ClickHouse और clickhousedb_fdw के बीच न्यूनतम अंतर है।

यह ग्राफ ClickhouseDB और clickhousedb_fdw के बीच अंतर दिखाता है। अधिकांश प्रश्नों में, FDW ओवरहेड उतना अधिक नहीं है और Q12 को छोड़कर शायद ही महत्वपूर्ण है। इस क्वेरी में जॉइन्स और ऑर्डर बाय क्लॉज शामिल है। ORDER BY GROUP/BY क्लॉज के कारण, ORDER BY ClickHouse पर नहीं जाता है।

तालिका 2 में हम प्रश्न Q12 और Q13 में समय वृद्धि देखते हैं। पुनः, यह ORDER BY खंड के कारण होता है। इसकी पुष्टि करने के लिए, मैंने ORDER BY क्लॉज के साथ और उसके बिना Q-14 और Q-15 क्वेरीज़ चलाईं। ऑर्डर बाय क्लॉज के बिना पूरा होने का समय 259 एमएस है और ऑर्डर बाय क्लॉज के साथ यह 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: क्लॉज द्वारा ऑर्डर के साथ क्वेरी योजना

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

क्लिकहाउस के माध्यम से टेलीग्राम चैट https://t.me/clickhouse_ru
PostgreSQL का उपयोग करके टेलीग्राम चैट https://t.me/pgsql

स्रोत: www.habr.com