ProHoster > Օրագիր > Վարչակազմը > Վերլուծական հարցումների կատարման փորձարկում PostgreSQL-ում, ClickHouse-ում և clickhousedb_fdw-ում (PostgreSQL)

Վերլուծական հարցումների կատարման փորձարկում PostgreSQL-ում, ClickHouse-ում և clickhousedb_fdw-ում (PostgreSQL)

Այս ուսումնասիրության մեջ ես ուզում էի տեսնել, թե ինչ կատարողականի բարելավումներ կարելի է ձեռք բերել՝ օգտագործելով ClickHouse տվյալների աղբյուրը, այլ ոչ PostgreSQL: Ես գիտեմ կատարողականի առավելությունները, որոնք ես ստանում եմ ClickHouse-ի օգտագործումից: Արդյո՞ք այս առավելությունները կպահպանվեն, եթե ես մուտք գործեմ ClickHouse PostgreSQL-ից՝ օգտագործելով արտաքին տվյալների փաթաթան (FDW):

Ուսումնասիրվող տվյալների բազան են PostgreSQL v11, clickhousedb_fdw և ClickHouse տվյալների բազան: Ի վերջո, PostgreSQL v11-ից մենք կգործարկենք տարբեր SQL հարցումներ՝ ուղղորդված մեր clickhousedb_fdw-ի միջոցով դեպի ClickHouse տվյալների բազա: Այնուհետև մենք կտեսնենք, թե ինչպես է FDW-ի կատարումը համեմատվում նույն հարցումների հետ, որոնք առաջադրվել են բնիկ PostgreSQL-ում և մայրենի ClickHouse-ում:

Clickhouse Database

ClickHouse-ը բաց կոդով սյունակի վրա հիմնված տվյալների բազայի կառավարման համակարգ է, որը կարող է հասնել 100-1000 անգամ ավելի արագ կատարողականի, քան ավանդական տվյալների բազայի մոտեցումները, որը կարող է մեկ վայրկյանից պակաս ժամանակում մշակել ավելի քան միլիարդ տող:

Clickhousedb_fdw

clickhousedb_fdw - ClickHouse Database External Data Wrapper-ը կամ FDW-ն բաց կոդով նախագիծ է Percona-ից: Ահա նախագծի GitHub պահեստի հղումը.

Մարտին ես գրեցի բլոգ, որը ձեզ ավելին է պատմում մեր FDW-ի մասին.

Ինչպես կտեսնեք, սա ապահովում է FDW ClickHouse-ի համար, որը թույլ է տալիս SELECT-ից և INSERT INTO-ից ClickHouse տվյալների բազա PostgreSQL v11 սերվերից:

FDW-ն աջակցում է առաջադեմ առանձնահատկություններ, ինչպիսիք են ագրեգատը և միանալը: Սա զգալիորեն բարելավում է կատարողականությունը՝ օգտագործելով հեռավոր սերվերի ռեսուրսները այս ռեսուրսների ինտենսիվ գործողությունների համար:

Հենանիշ միջավայր

  • Սուպերմիկրո սերվեր.
    • Intel® Xeon® CPU E5-2683 v3 @ 2.00 ԳՀց
    • 2 վարդակ / 28 միջուկ / 56 թել
    • Հիշողություն ՝ 256 ԳԲ հիշողություն
    • Պահպանում՝ Samsung SM863 1.9TB Enterprise SSD
    • Ֆայլային համակարգ՝ ext4/xfs
  • OS: Linux smblade01 4.15.0-42-ընդհանուր #45~16.04.1-Ubuntu
  • PostgreSQL: տարբերակ 11

Հենանիշային թեստեր

Այս թեստի համար մեքենաների կողմից ստեղծված տվյալներից որոշ օգտագործելու փոխարեն մենք օգտագործեցինք «Օպերատորի ժամանակի կողմից հաղորդված ժամանակի ընթացքում արտադրողականություն» տվյալները 1987-ից մինչև 2018 թվականը: Դուք կարող եք մուտք գործել տվյալներ: օգտագործելով մեր սցենարը, որը հասանելի է այստեղ.

Տվյալների բազայի չափը 85 ԳԲ է՝ տրամադրելով մեկ աղյուսակ՝ 109 սյունակով:

Հենանիշի հարցումներ

Ահա այն հարցումները, որոնք ես օգտագործել եմ ClickHouse-ը, clickhousedb_fdw-ը և PostgreSQL-ը համեմատելու համար:

Q#
Հարցումը պարունակում է ագրեգատներ և խմբեր ըստ

Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY OFSheek ORDER BY c DESC;

Q2
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY OfSheek ORDER BY c DESC;

Q3
SELECT Origin, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;

Q4
Ընտրեք օպերատոր, հաշվեք() FROM ontime WHERE DepDelay>10 AND Year = 2007 ԽՈՒՄԲ ԸՍՏ Օպերատորի ՊԱՏՎԵՐԸ ԸՍՏ հաշվառման () DESC;

Q5
SELECT a.Carrier, c, c2, c1000/c2 որպես c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year=2007 GROUP BY Carrier ) a INNER JOIN ( SELECT Carrier,count(*) AS c2 FROM ontime WHERE Year=2007 GROUP BY Carrier)b on a.Carrier=b.B.Carrier=b.Carrier;

Q6
SELECT a.Carrier, c, c2, c1000/c2 որպես c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Carrier) a INNER JOIN ( SELECT Carrier, count(*) AS c2 FROM ontime WHERE Year >= 2000 AND Year <= BY 2008 a.Carrier=b.Carrier ORDER BY c3 DESC;

Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier;

Q8
SELECT Year, avg(DepDelay) FROM ontime GROUP BY BY;

Q9
ընտրել Տարի, հաշվել (*) որպես c1 ժամանակի խմբից ըստ Տարի;

Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year, Month) a;

Q11
ընտրել avg(c1)-ից (ընտրել Տարի,Ամիս,հաշվարկ(*) որպես c1 ժամանակի խմբից ըստ Տարի,Ամիս) a;

Q12
SELECT OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;

Q13
SELECT OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;

Հարցումը պարունակում է միացումներ

Q14
SELECT a.Year, c1/c2 FROM (ընտրել Տարի, հաշվում()1000 որպես c1՝ ժամանակին WHERE DepDelay>10 GROUP BY Year) ՆԵՐՔԻՆ ՄԻԱՑՈՒՄ (ընտրել Տարին, հաշվել (*) որպես c2՝ ժամանակին GROUP BY BY ) b on a.Year=b.Year ORDER BY A.Year;

Q15
SELECT a.”Year”, c1/c2 FROM (ընտրեք “Year”, count()1000 որպես c1 FROM fontime WHERE “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (ընտրեք «Տարին», հաշվեք(*) որպես c2 FROM fontime GROUP BY «Year» ) b on a.”Year”=b.”Year”;

Աղյուսակ-1. Հենանիշներում օգտագործվող հարցումները

Հարցումների կատարումներ

Ահա յուրաքանչյուր հարցման արդյունքները, երբ գործարկվում է տվյալների բազայի տարբեր կարգավորումներում. PostgreSQL ինդեքսներով և առանց ինդեքսների, տեղական ClickHouse և clickhousedb_fdw: Ժամանակը ցուցադրվում է միլիվայրկյաններով:

Q#
PostgreSQL
PostgreSQL (ինդեքսավորված)
clickhouse
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-ի արդյունքները և տվյալները, որոնք վերցված են postgres-ից՝ օգտագործելով 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: Այս հարցումը վրիպազերծելու համար ես բացատրում եմ երկու հարցումները և ահա բացատրության արդյունքները:

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 կետով

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-ն առաջարկում է ClickHouse-ի կատարողական առավելությունները PostgreSQL-ից: Թեև clickhousedb_fdw-ն օգտագործելիս որոշակի գերավճար կա, այն աննշան է և համեմատելի է այն կատարողականի հետ, որը ձեռք է բերվել ClickHouse տվյալների բազայի վրա ինքնուրույն աշխատելու ժամանակ: Սա նաև հաստատում է, որ PostgreSQL-ում fdw-ն հիանալի արդյունքներ է տալիս:

Telegram զրույցը Clickhouse-ում https://t.me/clickhouse_ru
Telegram զրույց PostgreSQL-ում https://t.me/pgsql

Source: www.habr.com

Գնեք հուսալի հոստինգ DDoS պաշտպանությամբ կայքերի, VPS VDS սերվերների համար 🔥 Գնեք հուսալի կայքերի հոսթինգ՝ DDoS պաշտպանությամբ, VPS VDS սերվերներով | ProHoster