PostgreSQL၊ ClickHouse နှင့် clickhousedb_fdw (PostgreSQL) တွင် ခွဲခြမ်းစိတ်ဖြာမေးမြန်းချက်များ၏ စွမ်းဆောင်ရည်ကို စမ်းသပ်ခြင်း

ဤလေ့လာမှုတွင်၊ PostgreSQL ထက် ClickHouse ဒေတာရင်းမြစ်ကို အသုံးပြုခြင်းဖြင့် ကျွန်ုပ်ရရှိနိုင်သည့် စွမ်းဆောင်ရည်မြှင့်တင်မှုများကို ကြည့်ရှုလိုပါသည်။ ClickHouse ကိုအသုံးပြုတဲ့အခါ ဘယ်လိုစွမ်းဆောင်ရည် အကျိုးကျေးဇူးတွေ ရနိုင်လဲဆိုတာ သိပါတယ်။ External Data Wrapper (FDW) ကို အသုံးပြု၍ PostgreSQL မှ ClickHouse သို့ဝင်ရောက်ပါက ထိုအကျိုးကျေးဇူးများကို ထိန်းသိမ်းနိုင်ပါမည်လား။

ကျွန်ုပ်တို့ စုံစမ်းစစ်ဆေးမည့် ဒေတာဘေ့စ်ပတ်ဝန်းကျင်များသည် PostgreSQL v11၊ clickhousedb_fdw နှင့် ClickHouse database တစ်ခုဖြစ်သည်။ နောက်ဆုံးတွင်၊ ကျွန်ုပ်တို့သည် ကျွန်ုပ်တို့၏ clickhousedb_fdw မှတဆင့် ClickHouse ဒေတာဘေ့စ်သို့ ဖြတ်သန်းသွားသော PostgreSQL v11 မှ အမျိုးမျိုးသော SQL queries များကို လုပ်ဆောင်ပါမည်။ ထို့နောက်တွင် FDW ၏ စွမ်းဆောင်ရည်သည် မူရင်း PostgreSQL နှင့် မူရင်း ClickHouse တွင် လုပ်ဆောင်နေသည့် တူညီသောမေးခွန်းများနှင့် နှိုင်းယှဉ်ကြည့်ပါမည်။

Clickhouse ဒေတာဘေ့စ်

ClickHouse သည် ရိုးရာဒေတာဘေ့စ်ချဉ်းကပ်မှုများထက် အဆ 100 မှ 1000 အဆပိုမြန်သော စွမ်းဆောင်ရည်ကို ရရှိနိုင်သည့် ပွင့်လင်းရင်းမြစ်ကော်လံအခြေခံဒေတာဘေ့စ် စီမံခန့်ခွဲမှုစနစ်တစ်ခုဖြစ်ပြီး တစ်စက္ကန့်အတွင်း အတန်းတစ်ဘီလီယံကျော်ကို လုပ်ဆောင်နိုင်စွမ်းရှိသည်။

Clickhousedb_fdw

clickhousedb_fdw - ClickHouse Database External Data Wrapper သို့မဟုတ် FDW သည် Percona မှ open source ပရောဂျက်တစ်ခုဖြစ်သည်။ ဤသည်မှာ ပရောဂျက်၏ GitHub သိုလှောင်မှုသို့ လင့်ခ်တစ်ခုဖြစ်သည်။.

မတ်လတွင်ကျွန်ုပ်တို့၏ FDW အကြောင်းပိုမိုပြောပြသောဘလော့ဂ်တစ်ခုရေးခဲ့သည်။.

သင်တွေ့မြင်ရသည့်အတိုင်း၊ ၎င်းသည် SELECT မှခွင့်ပြုပြီး PostgreSQL v11 ဆာဗာမှ ClickHouse ဒေတာဘေ့စ်ကို ထည့်သွင်းသည့် ClickHouse အတွက် FDW ကို ထောက်ပံ့ပေးသည်။

FDW သည် အစုလိုက်နှင့် ပါဝင်ခြင်းကဲ့သို့သော အဆင့်မြင့်အင်္ဂါရပ်များကို ပံ့ပိုးပေးသည်။ ၎င်းသည် အရင်းအမြစ်-အလေးပေးလုပ်ဆောင်မှုများအတွက် အဝေးထိန်းဆာဗာရင်းမြစ်များကို အသုံးပြုခြင်းဖြင့် စွမ်းဆောင်ရည်ကို သိသိသာသာ တိုးတက်စေသည်။

စံသတ်မှတ်ချက်ပတ်ဝန်းကျင်

• စူပါမိုက်ခရိုဆာဗာ-
  • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
  • 2 sockets / 28 cores / 56 threads
  • Memory: 256GB RAM
  • သိုလှောင်မှု- 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 ခုနှစ်အထိ အသုံးပြုထားပါသည်။ ဒေတာကို သင်ဝင်ရောက်ကြည့်ရှုနိုင်ပါသည်။ ကျွန်ုပ်တို့၏ script ကိုအသုံးပြု၍ ဤနေရာတွင် ရရှိနိုင်ပါသည်။.

ဒေတာဘေ့စ်အရွယ်အစားမှာ 85GB ဖြစ်ပြီး ဇယားတစ်ခုတွင် ကော်လံ 109 ခုပါရှိသည်။

စံပြမေးခွန်းများ

ဤသည်မှာ ClickHouse၊ clickhousedb_fdw နှင့် PostgreSQL တို့ကို နှိုင်းယှဉ်ရန် အသုံးပြုခဲ့သော မေးခွန်းများဖြစ်သည်။

Q#
Query တွင် အစုလိုက်နှင့် အုပ်စုအလိုက် ပါရှိသည်။

Q1
DayOfWeek ကိုရွေးချယ်ပါ၊ count(*) အတိုင်း c မှ အချိန်မှန်ဘယ်မှာရှိသနည်း >= 2000 နှင့် Year <= 2008 Group BY DayOfWeek ORDER BY c DESC;

Q2
DayOfWeek ကိုရွေးချယ်ပါ၊ count(*) as c မှ အချိန်မှန် DepDelay>10 နှင့် Year >= 2000 နှင့် Year <= 2008 Group BY DayOfWeek Order BY c DESC;

Q3
ဇာစ်မြစ်ကို SELECT၊ count(*) အဖြစ် c အချိန်ပေါ်နေရာမှ DepDelay>10 နှင့် Year >= 2000 နှင့် Year <= 2008 မူရင်းမှာယူမှုအလိုက် အုပ်စုဖွဲ့ c DESC LIMIT 10;

Q4
SELECT ဝန်ဆောင်မှုပေးသူ၊ ရေတွက်() အချိန်မှန်နေရာမှ DepDelay> 10 နှင့် Year = 2007 ကယ်ရီယာ မှာယူမှုအလိုက် အုပ်စုဖွဲ့ခြင်း() DESC;

Q5
a.Carrier၊c၊c2၊c ကိုရွေးပါ။1000/c2 မှ c3 အဖြစ် ( SELECT Carrier ၊ count() အချိန်ပေါ်အချိန်မှစသည့်အတိုင်း c10 WHERE WHERE Year=2007 AND Year=2 GROUP BY Carrier ) တစ်ဦးအတွင်းပိုင်းပူးပေါင်းခြင်း ( SELECT Carrier၊count(*) AS c2007 မှ အချိန်မှန် WHERE Year=3 GROUP BY Carrier)b တွင် a.Carrier=b.CarrierXNUMX DE

Q6
a.Carrier၊c၊c2၊c ကိုရွေးပါ။1000/c2 မှ c3 အဖြစ် ( SELECT Carrier ၊ count() အချိန်ပေါ်အချိန်မှစသည့်အတိုင်း C သည် အချိန်မှန်နေရာမှ DepDelay>10 နှင့် Year >= 2000 နှင့် Year <= 2008 အဖွဲ့မှ သယ်ဆောင်သူ) တစ်ဦးအတွင်းပိုင်းပူးပေါင်းခြင်း ( SELECT Carrier၊ count(*) AS c2 AS on time WHERE Year >= 2000 AND Year <= 2008 နှင့် B ပေါ်ရှိ Carrier 3 a.Carrier=b.ကယ်ရီယာ မှာယူမှု cXNUMX DESC;

Q7
SELECT ဝန်ဆောင်မှုပေးသူ၊ avg(DepDelay) * 1000 AS c3 သည် အချိန်ပေါ်နေရာမှ နှစ်တစ်နှစ် >= 2000 နှင့် နှစ် <= 2008 ကယ်ရီယာအဖွဲ့ဖြင့် အဖွဲ့လိုက်၊

Q8
SELECT Year၊ avg(DepDelay) အချိန်ပေါ်အုပ်စုအလိုက် နှစ်အလိုက်၊

Q9
နှစ်အလိုက် အချိန်မှန်အုပ်စုမှ တစ်နှစ်၊ ရေတွက်(*) ကို ရွေးချယ်ပါ။

Q10
SELECT avg(cnt) FROM (SELECT Year၊Month,count(*) AS cnt AS cnt မှ အချိန်မှန် DEPDel15=1 အုပ်စုအလိုက် တစ်နှစ်၊လ) a;

Q11
a;

Q12
OriginCityName၊ DestCityName၊ count(*) အတိုင်း c ကို မူရင်းမြို့အမည်ဖြင့် အချိန်ပေါ်အုပ်စု၊ DestCityName မှမှာယူမှု c DESC ကန့်သတ်ချက် 10;

Q13
OriginCityName ကို SELECT၊ count(*) အနေဖြင့် c မှ အချိန်မီ အုပ်စုဖွဲ့ခြင်း မူရင်းမြို့အမည် မှာယူမှု c DESC LIMIT 10;

Query တွင် Joins များပါရှိသည်။

Q14
SELECT a.Year၊ c1/c2 FROM (နှစ်ကိုရွေးပါ၊ ရေတွက်ပါ()1000 အချိန်ပေါ်မှ C1 အဖြစ် 10 WHERE DepDelay>2 GROUP BY Year) a Inner Join (တစ်နှစ်ကို ရွေးချယ်ပါ၊ အချိန်အလိုက် GROUP BY Year မှ cXNUMX အဖြစ် cXNUMX အဖြစ်) b on a.Year=b.Year မှာသည် BY a.Year;

Q15
ရွေး a”Year”၊ c1/c2 မှ (“နှစ်” ကိုရွေးပါ၊ ရေတွက်ပါ()1000 အဖြစ် c1 ထဲမှ “DepDelay”> “Year” အလိုက် အုပ်စု 10) အတွင်းပိုင်း Join (“Year” ကိုရွေးချယ်ပါ၊ count(*) အဖြစ် c2 အဖြစ် “Year” ဖြင့် အုပ်စုဖွဲ့ပါ) b တွင် a.”Year”=b.”Year”;

ဇယား-၁- စံသတ်မှတ်ချက်တွင် အသုံးပြုထားသော မေးခွန်းများ

မေးခွန်းကို ကွပ်မျက်ခြင်း။

မတူညီသော ဒေတာဘေ့စ်ဆက်တင်များတွင် လုပ်ဆောင်သည့်အခါ မေးခွန်းတစ်ခုစီ၏ ရလဒ်များဖြစ်သည်- 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 ဝင်ရိုးသည် လုပ်ဆောင်ချိန်ကို မီလီစက္ကန့်ဖြင့်ပြသသည်။ ClickHouse ရလဒ်များနှင့် clickhousedb_fdw ကို အသုံးပြု၍ postgres မှရယူသောဒေတာကိုပြသထားသည်။ ဇယားမှ၊ PostgreSQL နှင့် ClickHouse အကြား ကြီးမားသော ခြားနားချက် ရှိသည်ကို ကျွန်ုပ်တို့ တွေ့နိုင်သော်လည်း ClickHouse နှင့် clickhousedb_fdw အကြား အနည်းငယ်သာ ကွာခြားချက်ရှိသည်။

ဤဂရပ်သည် ClickhouseDB နှင့် clickhousedb_fdw အကြား ခြားနားချက်ကို ပြသသည်။ မေးခွန်းအများစုတွင်၊ FDW သည် Q12 မှလွဲ၍ ထိုမျှကြီးမားပြီး သိသာထင်ရှားခြင်းမရှိပါ။ ဤမေးမြန်းချက်တွင် ပါဝင်မှုများနှင့် ORDER BY clause ပါဝင်သည်။ ORDER BY clause ကြောင့်၊ Group/BY နှင့် ORDER BY တို့သည် ClickHouse သို့ မဆင်းသွားပါ။

ဇယား 2 တွင်၊ မေးခွန်းများ Q12 နှင့် Q13 တွင် အချိန်တစ်ခုခုန်သွားသည်ကိုတွေ့မြင်ရသည်။ တဖန်၊ ဤသည်မှာ ORDER BY clause ကြောင့်ဖြစ်သည်။ ၎င်းကိုအတည်ပြုရန်၊ ကျွန်ုပ်သည် Q-14 နှင့် Q-15 တို့ကို ORDER BY clause မပါဘဲ စုံစမ်းမေးမြန်းထားပါသည်။ ORDER BY clause မပါဘဲ၊ ပြီးဆုံးချိန်သည် 259 ms ဖြစ်ပြီး၊ ORDER BY clause ဖြင့်၊ ၎င်းသည် 1364212 ms ဖြစ်သည်။ ဤမေးခွန်းကို အမှားရှာရန်၊ ကျွန်ုပ်သည် မေးခွန်းနှစ်ခုလုံးကို ရှင်းပြပြီး ဤသည်မှာ ရှင်းပြချက်၏ ရလဒ်များဖြစ်သည်။

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- အပိုဒ်အလိုက် မှာယူမှုဖြင့် မေးမြန်းမှု

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 Clause ဖြင့် မေးမြန်းမှု အစီအစဉ်

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 သည် ထူးထူးခြားခြားရလဒ်များကို ပေးဆောင်ကြောင်းလည်း အတည်ပြုပါသည်။

Clickhouse တွင် Telegram chat https://t.me/clickhouse_ru
PostgreSQL တွင် Telegram chat https://t.me/pgsql

source: www.habr.com