🥇Վերլուծական հարցումների կատարման փորձարկում 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 տվյալների բազայի արտաքին տվյալների փաթաթումը կամ FDW-ն բաց կոդով նախագիծ է Percona-ից: Ահա նախագծի GitHub պահեստի հղումը.

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

Ինչպես կտեսնեք, սա ClickHouse-ի համար ապահովում է FDW, որը թույլ է տալիս 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
ՕՀ՝ Linux smblade01 4.15.0-42-generic #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.ORDER C3 DESC-ով;

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 <= 2008 GROUP BY Carrier ) b on 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 Տարեկան) ՆԵՐՔԻՆ ՄԻԱՑՈՒՄ (ընտրել Տարին, հաշվել (*) որպես 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”) ա ՆԵՐՔԻՆ ՄԻԱՑՈՒՄ (ընտրեք «Տարի», հաշվեք(*) որպես c2 FROM fontime GROUP BY «Year» ) b on a.”Year”=b: «Տարի»;

Աղյուսակ-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-ի միջև:

Այս գրաֆիկը ցույց է տալիս 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 տվյալների բազայի վրա ինքնուրույն աշխատելու միջոցով: Սա նաև հաստատում է, որ fdw-ն PostgreSQL-ում ապահովում է գերազանց արդյունքներ:

Telegram զրույցը Clickhouse-ի միջոցով https://t.me/clickhouse_ru
Telegram զրույց՝ օգտագործելով PostgreSQL https://t.me/pgsql

Source: www.habr.com

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