Veiktspējas testēšana analītiskajiem vaicājumiem PostgreSQL, ClickHouse un clickhousedb_fdw (PostgreSQL)

Šajā pētījumā es vēlējos apskatīt, kādas veiktspējas uzlabojumus var iegūt, izmantojot datu avotu ClickHouse, nevis PostgreSQL. Es zinu, kādas veiktspējas priekšrocības sniedz ClickHouse. Vai šīs priekšrocības saglabāsies, ja es piekļušu ClickHouse no PostgreSQL, izmantojot ārējo datu apvalku (FDW)?

Pētāmās datu bāzes vidi ir PostgreSQL v11, clickhousedb_fdw un ClickHouse datu bāze. Galu galā no PostgreSQL v11 mēs izpildīsim dažādus SQL vaicājumus, kas tiks maršrutēti caur mūsu clickhousedb_fdw uz ClickHouse datu bāzi. Pēc tam mēs redzēsim, kā FDW veiktspēja salīdzinās ar tiem pašiem vaicājumiem, kas tiek izpildīti nativajā PostgreSQL un nativajā ClickHouse.

Clickhouse datu bāze

ClickHouse ir atvērtā koda kolonnveida datu bāzes pārvaldības sistēma, kas var sasniegt veiktspēju no 100 līdz 1000 reizēm ātrāk nekā tradicionālās datu bāzes pieejas, spējot apstrādāt vairāk nekā miljardu rindu mazāk nekā sekundē.

Clickhousedb_fdw

clickhousedb_fdw ir ClickHouse datu bāzes ārējo datu apvalks (FDW), kas ir atvērtā koda projekts no Percona. Šeit ir saite uz projekta GitHub repozitoriju.

Martā es uzrakstīju emuāru, kas stāsta vairāk par mūsu FDW.

Kā jūs redzēsiet, tas nodrošina FDW ClickHouse, kas ļauj SELECT no, un INSERT INTO ClickHouse datu bāzi no PostgreSQL v11 servera.

FDW atbalsta paplašinātas funkcijas, piemēram, agregāciju un apvienošanu. Tas ievērojami uzlabo veiktspēju, izmantojot attālās servera resursus šīm resursietilpīgajām operācijām.

Mērījumu vide

  • Supermicro serveris:
    • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
    • 2 soketi / 28 kodoli / 56 pavedieni
    • Atmiņa: 256GB RAM
    • Krātuve: Samsung SM863 1.9TB uzņēmuma SSD
    • Failu sistēma: ext4/xfs
  • OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
  • PostgreSQL: versija 11

Mērījumu testi

Pikļu izmantošanai šajā testā, nevis kādu mašīnas ģenerētu datu kopu, mēs izmantojām datus "Veiktspēja laikā, ziņojot par operatora darba laiku" no 1987. līdz 2018. gadam. Jūs varat piekļūt datiem izmantojot mūsu skriptu, kas pieejams šeit.

Datu bāzes izmērs ir 85 GB, nodrošinot vienu tabulu ar 109 kolonnām.

Mērījumu vaicājumi

Šeit ir vaicājumi, kurus es izmantoju, lai salīdzinātu ClickHouse, clickhousedb_fdw un PostgreSQL.

Q#
Vaicājums satur agregātus un grupu pa

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

Q2
IZVELIET DayOfWeek, count(*) AS c NO ontime KUR DepDelay>10 UN GADS >= 2000 UN GADS <= 2008 GRUPĒJET pēc DayOfWeek RINDĀ ar c DESC;

Q3
IZVELIET Origin, count(*) AS c NO ontime KUR DepDelay>10 UN GADS >= 2000 UN GADS <= 2008 GRUPĒJET pēc Origin RINDĀ ar c DESC IEROBEŽOT 10;

Q4
IZVELIET Carrier, count() NO ontime KUR DepDelay>10 UN GADS = 2007 GRUPĒJET pēc Carrier RINDĀ ar count() DESC;

Q5
IZVELIET a.Carrier, c, c2, c1000/c2 kā c3 NO ( IZVELIET Carrier, count() AS c NO ontime KUR DepDelay>10 UN GADS=2007 GRUPĒJET pēc Carrier ) a IESTATĪT ( IZVELIET Carrier,count(*) AS c2 NO ontime KUR GADS=2007 GRUPĒJET pēc Carrier)b ON a.Carrier=b.Carrier RINDĀ ar c3 DESC;

Q6
IZVELIET a.Carrier, c, c2, c1000/c2 kā c3 NO ( IZVELIET Carrier, count() AS c NO ontime KUR DepDelay>10 UN GADS >= 2000 UN GADS <= 2008 GRUPĒJET pēc Carrier) a IESTATĪT ( IZVELIET Carrier, count(*) AS c2 NO ontime KUR GADS >= 2000 UN GADS <= 2008 GRUPĒJET pēc Carrier ) b ON a.Carrier=b.Carrier RINDĀ ar c3 DESC;

Q7
IZVELIET Carrier, avg(DepDelay) * 1000 AS c3 NO ontime KUR GADS >= 2000 UN GADS <= 2008 GRUPĒJET pēc Carrier;

Q8
IZVELIET GADS, avg(DepDelay) NO ontime GRUPĒJET pēc GADS;

Q9
izvēlieties GADS, count(*) kā c1 no ontime grupēt pēc GADS;

Q10
IZVELIET avg(cnt) NO (IZVELIET GADS, Mēnesis,count(*) AS cnt NO ontime KUR DepDel15=1 GRUPĒJET pēc GADS, Mēnesis) a;

Q11
izvēlieties avg(c1) no (izvēlieties GADS, Mēnesis,count(*) kā c1 no ontime grupēt pēc GADS, Mēnesis) a;

Q12
IZVELIET OriginCityName, DestCityName, count(*) AS c NO ontime GRUPĒJET pēc OriginCityName, DestCityName RINDĀ ar c DESC IEROBEŽOT 10;

Q13
IZVELIET OriginCityName, count(*) AS c NO ontime GRUPĒJET pēc OriginCityName RINDĀ ar c DESC IEROBEŽOT 10;

Query Satur Navē

Q14
IZVELIET a.GADS, c1/c2 NO ( izvēlieties GADS, count()1000 kā c1 NO ontime KUR DepDelay>10 GRUPĒJET pēc GADS) a IESTATĪT (izvēlieties GADS, count(*) kā c2 no ontime grupēt pēc GADS ) b ON a.GADS=b.GADS RINDĀ ar a.GADS;

Q15
IZVELIET a.”GADS”, c1/c2 NO ( izvēlieties “GADS”, count()1000 kā c1 NO fontime KUR “DepDelay”>10 GRUPĒJET pēc “GADS”) a IESTATĪT (izvēlieties “GADS”, count(*) kā c2 no fontime grupēt pēc “GADS” ) b ON a.”GADS”=b.”GADS”;

Tabula-1: Vaicājumi, kas izmantoti salīdzinājumā

Vaicājumu izpildes

Šeit ir katra vaicājuma rezultāti, veicot dažadas datubāzes iestatījumus: PostgreSQL ar indeksiem un bez tiem, pašu ClickHouse un clickhousedb_fdw. Laiks tiek rādīts milisekundēs.

Q#
PostgreSQL
PostgreSQL (indeksēts)
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

Tabula-1: Laiks, kas nepieciešams, lai izpildītu vaicājumus, kas izmantoti salīdzinājumā

Pārbaudiet rezultātus

Grafiks parāda vaicājuma izpildes laiku milisekundēs, X ass parāda vaicājuma numuru no augstāk minētajām tabulām, bet Y ass parāda izpildes laiku milisekundēs. ClickHouse rezultāti un dati, kas iegūti no postgres ar clickhousedb_fdw, tiek parādīti. Tabulā redzams, ka pastāv milzīga atšķirība starp PostgreSQL un ClickHouse, bet minimāla atšķirība starp ClickHouse un clickhousedb_fdw.

Veiktspējas testēšana analītiskajiem vaicājumiem PostgreSQL, ClickHouse un clickhousedb_fdw (PostgreSQL)

Šis grafiks parāda atšķirības starp ClickhouseDB un clickhousedb_fdw. Lielākajā daļā vaicājumu FDW uzliktās izmaksas nav tik lielas un, iespējams, ir nenozīmīgas, izņemot Q12. Šis vaicājums ietver apvienojumus un pasūtījuma piedāvājumu. Pasūtījuma pēc GROUP/BY un ORDER BY tiek izlaisti līdz ClickHouse.

2. tabulā redzam pieaugumu vaicājumu Q12 un Q13 laikā. Atkārtoju, tas ir saistīts ar ORDER BY frāzi. Lai to apstiprinātu, es izpildīju vaicājumus Q-14 un Q-15 ar ORDER BY frāzi un bez tās. Bez ORDER BY frāzes izpildes laiks ir 259 ms, bet ar ORDER BY frāzi — 1364212. Lai atkļūdotu šo vaicājumu, es paskaidroju abus vaicājumus, un šeit ir paskaidrojumu rezultāti.

Q15: Bez ORDER BY frāzes

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: Vaicājums bez ORDER BY frāzes

VAICĀJUMA PLĀNS                                                      
Hash Join  (izmaksas=2250.00..128516.06 rindas=50000000 platums=12)  
Izvade: fontime."Year", (((count(*) * 1000)) / b.c2)  
Iekšējā unikāla: patiesi   Hash Cond: (fontime."Year" = b."Year")  
->  Ārējā skenēšana  (izmaksas=1.00..-1.00 rindas=100000 platums=12)        
Izvade: fontime."Year", ((count(*) * 1000))        
Attiecības: Agregācija uz (fontime)        
Attālinātā SQL: SELECT "Year", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Year"  
->  Hash  (izmaksas=999.00..999.00 rindas=100000 platums=12)        
Izvade: b.c2, b."Year"        
->  Apakšvaicājuma skenēšana uz b  (izmaksas=1.00..999.00 rindas=100000 platums=12)              
Izvade: b.c2, b."Year"              
->  Ārējā skenēšana  (izmaksas=1.00..-1.00 rindas=100000 platums=12)                    
Izvade: fontime_1."Year", (count(*))                    
Attiecības: Agregācija uz (fontime)                    
Attālinātā SQL: SELECT "Year", count(*) FROM "default".ontime GROUP BY "Year"(16 rindas)

Q14: Vaicājums ar ORDER BY frāzi

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: Vaicājuma plāns ar ORDER BY frāzi

VAICĀJUMA PLĀNS 
Merge Join  (izmaksas=2.00..628498.02 rindas=50000000 platums=12)   
Izvade: fontime."Year", (((count(*) * 1000)) / (count(*)))   
Iekšējā unikāla: patiesi   Merge Cond: (fontime."Year" = fontime_1."Year")   
->  GroupAggregate  (izmaksas=1.00..499.01 rindas=1 platums=12)        
Izvade: fontime."Year", (count(*) * 1000)         
Grupas atslēga: fontime."Year"         
->  Ārējā skenēšana uz public.fontime  (izmaksas=1.00..-1.00 rindas=100000 platums=4)               
Attālinātā SQL: SELECT "Year" FROM "default".ontime WHERE (("DepDelay" > 10)) 
            ORDER BY "Year" ASC   
->  GroupAggregate  (izmaksas=1.00..499.01 rindas=1 platums=12)         
Izvade: fontime_1."Year", count(*)         Grupas atslēga: fontime_1."Year"         
->  Ārējā skenēšana uz public.fontime fontime_1  (izmaksas=1.00..-1.00 rindas=100000 platums=4) 
              
Attālinātā SQL: SELECT "Year" FROM "default".ontime ORDER BY "Year" ASC(16 rindas)

Izvade

Šo eksperimentu rezultāti rāda, ka ClickHouse nodrošina patiešām labu veiktspēju, savukārt clickhousedb_fdw piedāvā ClickHouse veiktspējas priekšrocības no PostgreSQL. Lai gan, izmantojot clickhousedb_fdw, ir daži pārvaldības izdevumi, tie ir nenozīmīgi un salīdzināmi ar veiktspēju, kas tiek sasniegta, dabiski darbinot datubāzē ClickHouse. Tas arī apstiprina, ka fdw PostgreSQL nodrošina ievērojamus rezultātus.

Telegram grupas čats par Clickhouse https://t.me/clickhouse_ru
Telegram grupas čats par PostgreSQL https://t.me/pgsql

Avots: habr.com

Iegādājieties uzticamu tīmekļa hostingu ar DDoS aizsardzību, VPS VDS serveriem 🔥 Iegādājieties uzticamu tīmekļa hostingu ar DDoS aizsardzību, VPS VDS serveriem | ProHoster