Testimi i performancës së kërkesave analitike në PostgreSQL, ClickHouse dhe clickhousedb_fdw (PostgreSQL)

Në këtë studim, doja të shqyrtoja se cilat përmirësime të performancës mund të arrihen duke përdorur burimin e të dhënave ClickHouse, në vend të PostgreSQL. E di se cilat janë avantazhet e performancës që kam duke përdorur ClickHouse. A do të ruhet kjo përparësi nëse accessohem në ClickHouse nga PostgreSQL përmes një mbulojës të jashtme të të dhënave (FDW)?

Mjediset e studiuara të bazave të të dhënave janë PostgreSQL v11, clickhousedb_fdw dhe databaza ClickHouse. Në fund, do të ekzekutojmë shqetësime të ndryshme SQL nga PostgreSQL v11, të marra përmes clickhousedb_fdw në databazën ClickHouse. Pastaj, do të shohim si performanca e FDW krahasohet me të njëjtat të dhëna të ekzekutuara në PostgreSQL-në native dhe në ClickHouse-në native.

Baza e të dhënave Clickhouse

ClickHouse është një sistem menaxhimi të dhënash me bazë kolone me burim të hapur, i cili mund të arrijë performancën 100-1000 herë më të shpejtë se qasjet tradicionale të bazave të të dhënave, në gjendje të përpunojë më shumë se një miliard rreshta në më pak se një sekondë.

Clickhousedb_fdw

clickhousedb_fdw është një mbulues i jashtëm i të dhënave për databazën ClickHouse, ose FDW, një projekt me burim të hapur nga Percona. Këtu është lidhja për repository-n e projektit GitHub.

Në mars, unë shkrova një blog që ju tregon më shumë rreth FDW tonë.

Siç do ta shihni, kjo siguron FDW për ClickHouse, i cili lejon SELECT from, dhe INSERT INTO, bazën e të dhënave ClickHouse nga serveri PostgreSQL v11.

FDW mbështet funksione të avancuara, si agregat dhe bashkime. Kjo e rrit ndjeshëm performancën duke shfrytëzuar burimet e serverit të largët për këto operacione që kërkojnë burime.

Mjedisi i Benchmark

• Server Supermicro:
  • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
  • 2 socketë / 28 bërthama / 56 thithje
  • Memoria: 256 GB RAM
  • Ruajtja: Samsung SM863 1.9TB Enterprise SSD
  • Sistemi i Files: ext4/xfs
• OS: Linux smblade01 4.15.0-42-gjenerik #45~16.04.1-Ubuntu
• PostgreSQL: versioni 11

Testet e Benchmark

Në vend që të përdorim ndonjë grup të dhënash të gjeneruar nga makina, për këtë test, ne përdorëm të dhënat 'Performanca e kohës, e raportuar nga koha e punës së operatorit' nga 1987 deri në 2018. Ju mund të qaseni në të dhënat përmes skenarit tonë, i disponueshëm këtu.

Madhësia e bazës së të dhënave është 85 GB, duke ofruar një tabelë prej 109 kolonash.

Kërkesat e Benchmark

Këtu janë kërkesat që kam përdorur për të krahasuar ClickHouse, clickhousedb_fdw dhe PostgreSQL.

Q#
Kërkesa përmban agregat dhe Grupim

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

Q2
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek 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
SELECT Carrier, count() FROM ontime WHERE DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count() DESC;

Q5
SELECT a.Carrier, c, c2, c1000/c2 as 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.Carrier ORDER BY c3 DESC;

Q6
SELECT a.Carrier, c, c2, c1000/c2 as c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND 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 Year;

Q9
select Year, count(*) as c1 from ontime group by Year;

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

Q11
select avg(c1) from (select Year,Month,count(*) as c1 from ontime group by Year,Month) 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;

Query Contains Joins

Q14
SELECT a.Year, c1/c2 FROM ( select Year, count()1000 si c1 nga ontime KU DepDelay>10 GRUPOH Shtatorit nga Viti) a BASHKOHUNI (select Viti, count(*) si c2 nga ontime GRUPOH Viti) b mbi a.Viti=b.Viti RENDIT sipas a.Viti;

Q15
SELECT a."Viti", c1/c2 NGA ( select "Viti", count()1000 si c1 NGA fontime KU "DepDelay">10 GRUPOH "Viti") a BASHKOHUNI (select "Viti", count(*) si c2 NGA fontime GRUPOH "Viti") b mbi a."Viti"=b."Viti";

Tabela-1: Kërkimet e përdorura në benchmark

Ekzekutimet e kërkimeve

Ja rezultatet e çdo kërkese gjatë ekzekutimit në konfigurime të ndryshme të bazës së të dhënave: PostgreSQL me indekse dhe pa to, ClickHouse i vetëdijshëm dhe clickhousedb_fdw. Koha tregohet në milisekonda.

Q#
PostgreSQL
PostgreSQL (E Indeksuar)
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

Tabela-1: Koha e marrë për të ekzekutuar kërkimet e përdorura në benchmark

Shikoni rezultatet

Grafiku tregon kohën e ekzekutimit të kërkesës në milisekonda, aksisi X tregon numrin e kërkesës nga tabelat e mësipërme, ndërsa aksisi Y tregon kohën e ekzekutimit në milisekonda. Rezultatet e ClickHouse dhe të dhënat që janë marrë nga postgres përmes clickhousedb_fdw, janë paraqitur. Nga tabela shihet se ka një ndryshim të madh midis PostgreSQL dhe ClickHouse, por një ndryshim minimal midis ClickHouse dhe clickhousedb_fdw.

Ky this grafik tregon diferencën midis ClickhouseDB dhe clickhousedb_fdw. Në shumicën e pyetjeve, kostot e FDW nuk janë aq të mëdha dhe gati as që kanë rëndësi, përveç Q12. Kjo pyetje përfshin bashkime dhe nje propozim ORDER BY. Për shkak të propozimit ORDER BY GROUP/BY dhe ORDER BY nuk hidhet poshtë në ClickHouse.

Në tabelën 2 shohim një skak në kohën e pyetjeve Q12 dhe Q13. Të ndjej se, kjo shkaktohet nga propozimi ORDER BY. Për të konfirmuar këtë, kam ekzekutuar pyetjet Q-14 dhe Q-15 me propozimin ORDER BY dhe pa të. Pa propozimin ORDER BY, koha e përfundimit është 259 ms, ndërsa me propozimin ORDER BY është 1364212. Për të debug-uar këtë pyetje, unë shpjegoj të dyja pyetjet, dhe këtu janë rezultatet e shpjegimit.

Q15: Pa Klauzolën ORDER BY

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: Pyetje pa Klauzolën ORDER BY

PLANINQTËS                                                      
Hash Join  (kost=2250.00..128516.06 rreshta=50000000 gjerësia=12)  
Dërgimi: fontime."Viti", (((count(*) * 1000)) / b.c2)  
Unike Brenda: true   Hash Cond: (fontime."Viti" = b."Viti")  
->  Skano të Huaj  (kost=1.00..-1.00 rreshta=100000 gjerësia=12)        
Dërgimi: fontime."Viti", ((count(*) * 1000))        
Marrëdhëniet: Agregat në (fontime)        
SQL i Largët: SELECT "Viti", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Viti"  
->  Hash  (kost=999.00..999.00 rreshta=100000 gjerësia=12)        
Dërgimi: b.c2, b."Viti"        
->  Skano nënkërkime mbi b  (kost=1.00..999.00 rreshta=100000 gjerësia=12)              
Dërgimi: b.c2, b."Viti"              
->  Skano të Huaj  (kost=1.00..-1.00 rreshta=100000 gjerësia=12)                    
Dërgimi: fontime_1."Viti", (count(*))                    
Marrëdhëniet: Agregat në (fontime)                    
SQL i Largët: SELECT "Viti", count(*) FROM "default".ontime GROUP BY "Viti"(16 rreshta)

Q14: Kërkesa me Klauzolën ORDER BY

bm=# EXPLAIN VERBOSE SELECT a."Viti", c1/c2 FROM(SELECT "Viti", count(*)*1000 AS c1 FROM fontime WHERE "DepDelay" > 10 GROUP BY "Viti") a 
     INNER JOIN(SELECT "Viti", count(*) as c2 FROM fontime GROUP BY "Viti") b  ON a."Viti"= b."Viti" 
     ORDER BY a."Viti";

Q14: Plani i Kërkesës me Klauzolën ORDER BY

PLANI I KËRKESËS 
Bashkim i Fusionit  (kostot=2.00..628498.02 rreshta=50000000 gjerësia=12)   
Dalja: fontime."Viti", (((numri(*) * 1000)) / (numri(*)))   
Unike e Brendshme: e vërtetë   Kushti i Bashkimit: (fontime."Viti" = fontime_1."Viti")   
->  Grup Agregat  (kostot=1.00..499.01 rreshta=1 gjerësia=12)        
Dalja: fontime."Viti", (numri(*) * 1000)         
Çelësi i Grupit: fontime."Viti"         
->  Skane të Morte të huaj në publik.fontime  (kostot=1.00..-1.00 rreshta=100000 gjerësia=4)               
SQL i Largët: SELECT "Viti" FROM "default".ontime WHERE (("DepDelay" > 10)) 
            Rregullo me "Viti" Rritës  ASC   
->  Grup Agregat  (kostot=1.00..499.01 rreshta=1 gjerësia=12)         
Dalja: fontime_1."Viti", numri(*)         Çelësi i Grupit: fontime_1."Viti"         
->  Skane të Morte të huaj në publik.fontime fontime_1  (kostot=1.00..-1.00 rreshta=100000 gjerësia=4) 
              
SQL i Largët: SELECT "Viti" FROM "default".ontime Rregullo me "Viti" Rritës ASC(16 rreshta)

Përfundim

Rezultatet e këtyre eksperimenteve tregojnë se ClickHouse ofron vërtet performancë shumë të mirë, dhe clickhousedb_fdw sjell avantazhe të performancës së ClickHouse nga PostgreSQL. Megjithëse përdorimi i clickhousedb_fdw ka disa shpenzime, ato janë të papërfillshme dhe të krahasueshme me performancën e arritur gjatë ekzekutimit të natyrshëm në bazën e të dhënave ClickHouse. Kjo gjithashtu konfirmon se fdw në PostgreSQL ofron rezultate të shkëlqyera.

Grupi në Telegram për Clickhouse https://t.me/clickhouse_ru
Grupi në Telegram për PostgreSQL https://t.me/pgsql

Burimi: habr.com