پرو هوسٽر > بلاگ > انتظاميه > PostgreSQL، ClickHouse ۽ clickhousedb_fdw (PostgreSQL) ۾ تجزياتي سوالن جي ڪارڪردگي کي جانچڻ

PostgreSQL، ClickHouse ۽ clickhousedb_fdw (PostgreSQL) ۾ تجزياتي سوالن جي ڪارڪردگي کي جانچڻ

هن مطالعي ۾، مان ڏسڻ چاهيان ٿو ته ڪارڪردگي بهتري حاصل ڪري سگهجي ٿي هڪ ClickHouse ڊيٽا ماخذ کي استعمال ڪندي PostgreSQL بجاءِ. مان ڄاڻان ٿو پيداواري فائدا جيڪي مون کي ڪلڪ هاؤس استعمال ڪرڻ مان حاصل ٿين ٿا. ڇا اھي فائدا جاري رھندا جيڪڏھن مان ClickHouse تائين PostgreSQL کان فارين ڊيٽا ريپر (FDW) استعمال ڪريان؟

ڊيٽابيس ماحول جو اڀياس ڪيو ويو آهي PostgreSQL v11، clickhousedb_fdw ۽ ClickHouse ڊيٽابيس. آخرڪار، PostgreSQL v11 کان اسان مختلف SQL سوالن کي هلائيندا سين جيڪي اسان جي clickhousedb_fdw ذريعي ClickHouse ڊيٽابيس ڏانهن موڪليا ويندا. اسان پوءِ ڏسنداسين ته ڪيئن FDW جي ڪارڪردگي ساڳيو سوالن سان مقابلو ڪري ٿي ڏيهي PostgreSQL ۽ اصلي ClickHouse ۾.

ڪلڪ هائوس ڊيٽابيس

ClickHouse هڪ اوپن سورس ڪالمنر ڊيٽابيس مينيجمينٽ سسٽم آهي جيڪو ڪارڪردگي حاصل ڪري سگهي ٿو 100-1000 ڀيرا وڌيڪ تيزيءَ سان روايتي ڊيٽابيس جي طريقن کان، هڪ سيڪنڊ کان به گهٽ وقت ۾ هڪ ارب قطارن تي پروسيسنگ ڪرڻ جي قابل.

Clickhousedb_fdw

clickhousedb_fdw - ڪلڪ هاؤس ڊيٽابيس لاءِ خارجي ڊيٽا ريپر، يا FDW، پرڪونا کان هڪ اوپن سورس پروجيڪٽ آهي. هتي منصوبي جي GitHub مخزن جي هڪ لنڪ آهي.

مارچ ۾ مون هڪ بلاگ لکيو جيڪو توهان کي اسان جي FDW بابت وڌيڪ ٻڌائي ٿو.

جيئن توهان ڏسندا، هي هڪ FDW مهيا ڪري ٿو ClickHouse لاءِ جيڪا اجازت ڏئي ٿي SELECT from، ۽ INSERT INTO، ClickHouse ڊيٽابيس PostgreSQL v11 سرور کان.

FDW ترقي يافته خاصيتن کي سپورٽ ڪري ٿو جهڙوڪ مجموعي ۽ شامل ٿيڻ. اهو خاص طور تي ڪارڪردگي کي بهتر بڻائي ٿو ريموٽ سرور جي وسيلن کي استعمال ڪندي انهن وسيلن جي شدت واري عملن لاءِ.

معيار جو ماحول

  • Supermicro سرور:
    • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
    • 2 ساکٽ / 28 ڪور / 56 موضوع
    • يادگيري: 256GB ريم
    • اسٽوريج: 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 ڪالمن جي هڪ ٽيبل مهيا ڪري ٿي.

معيار جا سوال

هتي اهي سوال آهن جن کي مان استعمال ڪرڻ لاءِ استعمال ڪيو ويو ClickHouse، clickhousedb_fdw ۽ PostgreSQL.

Q#
سوال تي مشتمل آهي مجموعي ۽ گروپ طرفان

Q1
هفتي جو ڏينهن چونڊيو، ڳڻيو (*) جيئن ته وقت کان وٺي جتي سال >= 2000 ۽ سال <= 2008 گروپ ڊي آف هفتي جي ترتيب سان سي ڊي سي پاران؛

Q2
ھفتي جو ڏينھن چونڊيو، ڳڻيو (*) جيئن ته وقت کان پوءِ جتي DepDelay>10 ۽ سال >= 2000 ۽ سال <= 2008 گروپ ھفتي جي ڏينھن جي ترتيب سان DESC پاران؛

Q3
منتخب ڪريو اصلي، شمار (*) جيئن وقت کان وٺي وقت تي جتي DepDelay>10 ۽ سال >= 2000 ۽ سال <= 2008 گروپ اصل ترتيب جي ترتيب سان سي ڊي سي جي حد 10؛

Q4
ڪيريئر چونڊيو، شمار (() DESC؛

Q5
چونڊيو a. ڪيريئر، ج، سي2، ج1000/c2 جيئن c3 کان ( چونڊيو ڪيريئر، شمار () جيئن وقت کان پوءِ ڊيپ ڊيلي C10 DESC پاران؛

Q6
چونڊيو a. ڪيريئر، ج، سي2، ج1000/c2 جيئن c3 کان ( چونڊيو ڪيريئر، شمار () جيئن وقت کان وٺي ڊيپ ڊيلي> 10 ۽ سال >= 2000 ۽ سال <= ڪيريئر طرفان 2008 گروپ) هڪ اندروني شموليت ( چونڊيو ڪيريئر، شمار (*) جي طور تي c2 کان وقت تي جتي سال >= 2000 ۽ سال <= 2008 سال ڪيريئر ) b on a.Carrier=b.Carrier ORDER by c3 DESC؛

Q7
ڪيريئر چونڊيو، avg(DepDelay) * 1000 AS c3 وقت کان وٺي جتي سال >= 2000 ۽ سال <= 2008 گروپ طرفان ڪيريئر؛

Q8
سال چونڊيو، سراسري (ڊيپ ڊيلي) وقت کان وٺي گروپ طرفان سال؛

Q9
سال چونڊيو، شمار (*) c1 طور تي وقت جي گروپ مان سال جي لحاظ کان؛

ايڪسڪسڪس
منتخب ڪريو avg(cnt) منجهان (منتخب سال، مهينو، ڳڻپ (*) جيئن ته cnt کان وقت تي جتي DepDel15=1 گروپ بائي سال، مهينو) a؛

ايڪسڪسڪس
منتخب ڪريو avg(c1) مان (منتخب ڪريو سال، مهينو، ڳڻپ (*) جيئن c1 آن ٽائم گروپ مان سال،مهيني) a؛

ايڪسڪسڪس
منتخب ڪريو OriginCityName، DestCityName، ڳڻپ(*) AS C FROM FROM GROUP BY OriginCityName، DestCityName ORDER BY C DESC LIMIT 10؛

ايڪسڪسڪس
منتخب ڪريو اصلي شهر جو نالو، شمار (*) جيئن وقت تي گروپ طرفان اصل شهر جو نالو آرڊر سي ڊي سي جي حد 10 ذريعي؛

سوال شامل آهن

ايڪسڪسڪس
چونڊيو a.Year, c1/c2 FROM (سال چونڊيو، شمار ()1000 جي طور تي c1 کان وقت تي جتي DepDelay>10 گروپ سال تائين) هڪ اندروني شموليت (سال چونڊيو، شمار (*) c2 طور تي وقت کان گروپ طرفان سال) b تي a.Year=b.year ORDER BY a.Year؛

ايڪسڪسڪس
چونڊيو a.”سال”، c1/c2 FROM (منتخب “سال”، شمار ()1000 جيئن c1 FROM fonttime HERE “DepDelay”>10 GROUP BY “Year”) هڪ اندروني شموليت (منتخب ڪريو “سال”، ڳڻيو (*) جيئن c2 کان فونٽائم گروپ بائي “سال” ) b تي a.”Year”=b. "سال"؛

جدول-1: بينچ مارڪ ۾ استعمال ٿيل سوال

پڇا ڳاڇا ڪرڻ

هتي هر هڪ سوالن جا نتيجا آهن جڏهن مختلف ڊيٽابيس سيٽنگن ۾ هلن ٿا: 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

ايڪسڪسڪس
34990
20102
56
148

ايڪسڪسڪس
30489
51658
37
155

ايڪسڪسڪس
39357
33742
186
1333

ايڪسڪسڪس
29912
30709
101
384

ايڪسڪسڪس
54126
39913
124
1364212

ايڪسڪسڪس
97258
30211
245
259

جدول-1: بينچ مارڪ ۾ استعمال ڪيل سوالن کي انجام ڏيڻ لاءِ ورتو ويو وقت

نتيجا ڏسو

گراف ڏيکاري ٿو پڇا ڳاڇا جي عمل جو وقت مليس سيڪنڊن ۾، ايڪس محور ڏيکاري ٿو سوال نمبر مٿي ڏنل جدولن مان، ۽ Y محور ملائي سيڪنڊن ۾ عملدرآمد وقت ڏيکاري ٿو. Clickhousedb_fdw استعمال ڪندي پوسٽ گريس مان حاصل ڪيل ڪلڪ هائوس جا نتيجا ۽ ڊيٽا ڏيکاريل آھن. ٽيبل مان توهان ڏسي سگهو ٿا ته PostgreSQL ۽ ClickHouse جي وچ ۾ وڏو فرق آهي، پر ClickHouse ۽ clickhousedb_fdw جي وچ ۾ گهٽ ۾ گهٽ فرق آهي.

PostgreSQL، ClickHouse ۽ clickhousedb_fdw (PostgreSQL) ۾ تجزياتي سوالن جي ڪارڪردگي کي جانچڻ

هي گراف 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 شق جي بغير مڪمل ٿيڻ جو وقت 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: شق جي ترتيب سان سوال

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

تبصرو شامل ڪريو