PostgreSQL, ClickHouse आणि clickhousedb_fdw (PostgreSQL) मधील विश्लेषणात्मक प्रश्नांची कामगिरी तपासत आहे

या अभ्यासात, मला PostgreSQL ऐवजी ClickHouse डेटा स्रोत वापरून काय कामगिरी सुधारणा साध्य करता येतील हे पहायचे होते. मला क्लिकहाऊस वापरून मिळणारे उत्पादकता फायदे माहित आहेत. मी फॉरेन डेटा रॅपर (FDW) वापरून PostgreSQL वरून क्लिकहाऊसमध्ये प्रवेश केल्यास हे फायदे चालू राहतील का?

PostgreSQL v11, clickhousedb_fdw आणि ClickHouse डेटाबेसचा अभ्यास केलेला डेटाबेस वातावरण आहे. शेवटी, PostgreSQL v11 वरून आम्ही आमच्या clickhousedb_fdw द्वारे ClickHouse डेटाबेसकडे राउट केलेल्या विविध SQL क्वेरी चालवणार आहोत. मग आम्ही FDW चे कार्यप्रदर्शन मूळ PostgreSQL आणि मूळ क्लिकहाऊसमध्ये चालू असलेल्या समान क्वेरीशी कसे तुलना करते ते पाहू.

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

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

Clickhousedb_fdw

clickhousedb_fdw - क्लिकहाऊस डेटाबेस किंवा FDW साठी बाह्य डेटा रॅपर, Percona मधील एक मुक्त स्रोत प्रकल्प आहे. येथे प्रकल्पाच्या GitHub भांडाराची लिंक आहे.

मार्चमध्ये मी एक ब्लॉग लिहिला जो तुम्हाला आमच्या FDW बद्दल अधिक माहिती देतो.

तुम्ही पाहाल त्याप्रमाणे, हे ClickHouse साठी FDW प्रदान करते जे PostgreSQL v11 सर्व्हरवरील ClickHouse डेटाबेस मधून SELECT आणि INSERT INTO ला अनुमती देते.

FDW प्रगत वैशिष्ट्यांना समर्थन देते जसे की एकत्रित आणि सामील. हे या संसाधन-केंद्रित ऑपरेशन्ससाठी रिमोट सर्व्हरच्या संसाधनांचा वापर करून कार्यप्रदर्शनात लक्षणीय सुधारणा करते.

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

• सुपरमाइक्रो सर्व्हर:
  • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
  • 2 सॉकेट्स / 28 कोर / 56 धागे
  • मेमरी: 256 जीबी रॅम
  • स्टोरेज: 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
आठवड्याचा दिवस निवडा, ऑनटाइम पासून c म्हणून मोजा (*) जेथे वर्ष >= 2000 आणि वर्ष <= 2008 ग्रुप बाई ऑफ वीक ऑर्डर करून c DESC;

Q2
आठवड्याचा दिवस निवडा, ऑनटाइम पासून c म्हणून मोजा (*) जेथे DepDelay>10 आणि वर्ष >= 2000 आणि वर्ष <= 2008 ग्रुप बाई ऑफ वीक ऑर्डर c DESC द्वारे;

Q3
मूळ निवडा, गणना (*) ऑनटाइम पासून c म्हणून करा जेथे DepDelay>10 आणि वर्ष >= 2000 आणि वर्ष <= 2008 मूळ क्रमानुसार c DESC मर्यादा 10 नुसार गट;

Q4
वाहक निवडा, गणना() ऑनटाइम कुठे विलंब> 10 आणि वर्ष = 2007 गट वाहक क्रमानुसार गणना () DESC;

Q5
निवडा a.वाहक, c, c2, c1000/c2 c3 FROM म्हणून ( वाहक निवडा, गणना () ऑनटाइम पासून जेथे DepDelay>10 आणि वर्ष = 2007 वाहकाद्वारे गट ) एक अंतर्गत सामील व्हा ( वाहक निवडा, गणना (*) ऑनटाइमपासून c2 म्हणून जेथे वर्ष = वाहकाद्वारे 2007 गट) b a.Carrier=b.ORDERCarrier वर C3 DESC द्वारे;

Q6
निवडा a.वाहक, c, c2, c1000/c2 c3 FROM म्हणून ( वाहक निवडा, गणना () c ऑनटाइम पासून जेथे Depdelay>10 आणि वर्ष >= 2000 आणि वर्ष <= वाहक द्वारे 2008 गट) एक अंतर्गत सामील व्हा ( वाहक निवडा, गणना करा वाहक ) b वर a.Carrier=b.Carrier ORDER by c2 DESC;

Q7
वाहक निवडा, सरासरी(DepDelay) * 1000 AS c3 पासून ऑनटाइम जेथे वर्ष >= 2000 आणि वर्ष <= वाहकाद्वारे 2008 गट;

Q8
वर्ष निवडा, सरासरी (उशीर विलंब) ऑनटाइम गटानुसार वर्ष;

Q9
वर्ष निवडा, वर्षानुसार ऑनटाइम गटातून c1 म्हणून मोजा(*);

Q10
मधून सरासरी(cnt) निवडा (वर्ष, महिना,गणना (*) ऑनटाइम पासून cnt म्हणून निवडा जेथे DepDel15=1 गटानुसार वर्ष, महिना) a;

Q11
मधून सरासरी(c1) निवडा (वर्ष,महिना,गणना(*) ऑनटाइम गटातून c1 म्हणून वर्ष,महिना निवडा) a;

Q12
OriginCityName, DestCityName, गणना(*) निवडा ओरिजिनसिटीनेम नुसार ग्रुप, C DESC मर्यादा 10 द्वारे DestCityName ऑर्डर करा;

Q13
OriginCityName सिलेक्ट करा, गणना(*) वरून c मधून C DESC LIMIT 10 द्वारे OriginCityName क्रमाने गट करा;

क्वेरीमध्ये सामील आहेत

Q14
a.Year, c1/c2 FROM निवडा (वर्ष निवडा, गणना()ऑनटाइम पासून c1000 म्हणून 1 जेथे DepDelay>वर्षानुसार 10 गट) एक अंतर्गत सामील व्हा (वर्ष निवडा, गणना (*) ऑनटाइम ग्रुप मधून c2 म्हणून वर्षानुसार ) b रोजी a.Year=b.Year ORDER BY a.Year;

Q15
निवडा a.”वर्ष”, c1/c2 FROM ( “वर्ष” निवडा, गणना()1000 फॉन्टाईम पासून c1 म्हणून जेथे “DepDelay”>10 ग्रुप बाय “वर्ष”) एक इनर जॉईन (“वर्ष” निवडा, मोजा(*) फॉन्टाईम ग्रुप मधून “वर्ष” ) b वर a.”Year”=b. "वर्ष";

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

चौकशी अंमलबजावणी

वेगवेगळ्या डेटाबेस सेटिंग्जमध्ये चालवल्या जातात तेव्हा प्रत्येक क्वेरीचे परिणाम येथे आहेत: अनुक्रमणिकासह आणि त्याशिवाय PostgreSQL, मूळ क्लिकहाऊस आणि 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 अक्ष मिलिसेकंदांमध्ये एक्झिक्यूशन वेळ दाखवतो. 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 प्रश्न रन केले. 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 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 पोस्टग्रेएसक्यूएल वरून क्लिकहाऊसचे कार्यप्रदर्शन लाभ देते. clickhousedb_fdw वापरताना काही ओव्हरहेड असले तरी, क्लिकहाऊस डेटाबेसवर नेटिव्हली चालवून साध्य केलेल्या कामगिरीशी ते नगण्य आणि तुलनेने योग्य आहे. हे देखील पुष्टी करते की PostgreSQL मधील fdw उत्कृष्ट परिणाम प्रदान करते.

क्लिकहाऊस द्वारे टेलीग्राम चॅट https://t.me/clickhouse_ru
PostgreSQL वापरून टेलीग्राम चॅट https://t.me/pgsql

स्त्रोत: www.habr.com