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 হল একটি ওপেন সোর্স কলামার ডাটাবেস ম্যানেজমেন্ট সিস্টেম যা প্রথাগত ডাটাবেস পদ্ধতির চেয়ে 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 থ্রেড
  • মেমোরি: র‌্যামের 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
সপ্তাহের দিন নির্বাচন করুন, যথাসময় থেকে গণনা করুন(*)

Q2
সপ্তাহের দিন নির্বাচন করুন, অনটাইম থেকে গণনা করুন(*)

Q3
অরিজিন নির্বাচন করুন, গণনা করুন

Q4
ক্যারিয়ার নির্বাচন করুন, গণনা() অনটাইম থেকে যেখানে বিলম্ব> 10 এবং বছর = 2007 গ্রুপ বাহক দ্বারা গণনা অনুসারে অর্ডার () DESC;

Q5
নির্বাচন করুন a. ক্যারিয়ার, c, c2, c1000/c2 থেকে c3 হিসাবে ( ক্যারিয়ার নির্বাচন করুন, গণনা () সি অনটাইম থেকে কোথায় DepDelay>10 এবং বছর = 2007 কেরিয়ার দ্বারা গ্রুপ C2 DESC দ্বারা;

Q6
নির্বাচন করুন a. ক্যারিয়ার, c, c2, c1000/c2 থেকে c3 হিসাবে ( ক্যারিয়ার নির্বাচন করুন, গণনা () সি অনটাইম থেকে যেখানে বিলম্ব> 10 এবং বছর >= 2000 এবং বছর <= ক্যারিয়ার দ্বারা 2008 গ্রুপ) একটি অভ্যন্তরীণ যোগদান (ক্যারিয়ার নির্বাচন করুন, গণনা করুন ক্যারিয়ার ) b on a. Carrier=b. Carrier ORDER BY c2 DESC;

Q7
ক্যারিয়ার নির্বাচন করুন, avg(DepDelay) * 1000 AS c3 থেকে ontime যেখানে বছর >= 2000 এবং বছর <= ক্যারিয়ার দ্বারা 2008 গ্রুপ;

Q8
নির্বাচন করুন বছর, গড় (DepDelay) অনটাইম থেকে বছর অনুসারে গ্রুপ;

Q9
বছর নির্বাচন করুন, বছর অনুসারে অনটাইম গ্রুপ থেকে c1 হিসাবে গণনা করুন;

Q10
থেকে avg(cnt) নির্বাচন করুন (নির্বাচন বছর,মাস,গণনা (*) cnt হিসাবে ontime থেকে যেখানে DepDel15=1 বছর, মাস অনুসারে গ্রুপ) a;

Q11
থেকে avg(c1) নির্বাচন করুন (বছর,মাস,অনটাইম গ্রুপ থেকে c1 হিসাবে বছর,মাস নির্বাচন করুন) a;

Q12
OriginCityName, DestCityName, গণনা (*) নির্বাচন করুন

Q13
OriginCityName নির্বাচন করুন, গণনা করুন

কোয়েরিতে যোগদান রয়েছে

Q14
নির্বাচন করুন a.Year, c1/c2 FROM ( বছর নির্বাচন করুন, গণনা()অনটাইম থেকে c1000 হিসাবে 1 যেখানে DepDelay>বছর অনুসারে 10 গ্রুপ) একটি অভ্যন্তরীণ যোগদান (বছর নির্বাচন করুন, গণনা করুন (*) অনটাইম গ্রুপ থেকে সি2 হিসাবে বছর অনুসারে ) 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 (সূচিবদ্ধ)
ক্লিকহাউস
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 ধারা রয়েছে। ORDER BY GROUP/BY ধারার কারণে, ORDER BY ক্লিক হাউসে নেমে যায় না।

সারণি 2-এ আমরা Q12 এবং Q13 প্রশ্নের মধ্যে সময় লাফানো দেখতে পাই। আবার, এটি ORDER BY ধারার কারণে হয়। এটি নিশ্চিত করার জন্য, আমি ORDER BY ধারা সহ এবং ছাড়া Q-14 এবং Q-15 প্রশ্নগুলি চালিয়েছি। ORDER BY ক্লজ ব্যতীত সমাপ্তির সময় 259ms এবং 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: ধারা দ্বারা 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";

প্রশ্ন 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 PostgreSQL থেকে ClickHouse-এর কর্মক্ষমতা সুবিধা প্রদান করে। clickhousedb_fdw ব্যবহার করার সময় কিছু ওভারহেড থাকলেও, এটি ক্লিকহাউস ডাটাবেসে নেটিভভাবে চালানোর মাধ্যমে অর্জিত কর্মক্ষমতার সাথে নগণ্য এবং তুলনীয়। এটি নিশ্চিত করে যে PostgreSQL-এ fdw চমৎকার ফলাফল প্রদান করে।

Clickhouse মাধ্যমে টেলিগ্রাম চ্যাট https://t.me/clickhouse_ru
PostgreSQL ব্যবহার করে টেলিগ্রাম চ্যাট https://t.me/pgsql

উত্স: www.habr.com