ProHoster > Blog > Administrim > Testimi i performancës së pyetjeve analitike në PostgreSQL, ClickHouse dhe clickhousedb_fdw (PostgreSQL)

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

Në këtë studim, dëshiroja të shikoja se cilat përmirësime në performancë mund të arrihen duke përdorur një burim të dhënash ClickHouse në vend të PostgreSQL. E di se çfarë përfitimesh performance kam duke përdorur ClickHouse. A do të ruaj këto përfitime nëse aksesoj ClickHouse nga PostgreSQL përmes një mbështetjeje të jashtme të të dhënave (FDW)?

Mjediset e bazave të të dhënave që po studiohen janë PostgreSQL v11, clickhousedb_fdw dhe baza e të dhënave ClickHouse. Në fund, ne do të ekzekutojmë pyetje të ndryshme SQL nga PostgreSQL v11, të marra përmes klikhuese db_fdw në bazën e të dhënave ClickHouse. Pastaj do të shohim se si performanca e FDW krahasohet me të njëjtat pyetje që ekzekutohen në PostgreSQL-nativ dhe ClickHouse-nativ.

Baza e të dhënave ClickHouse

ClickHouse është një sistem menaxhimi të dhënash me burim të hapur, i bazuar në kolona, i cili mund të arrijë performancë nga 100 deri në 1000 herë më shpejt se qasjet tradhitionale ndaj bazave të të dhënave, i aftë për të përpunuar më shumë se një miliard rreshta në më pak se një sekondë.

Clickhousedb_fdw

clickhousedb_fdw është një mbështetje e jashtme për të dhënat e bazës së të dhënave ClickHouse, ose FDW, është një projekt me burim të hapur nga Percona. Ja linku për depozitat e projektit në GitHub.

Në mars, shkrova një blog që tregon më shumë për FDW-në tonë.

Siç do të shihni, kjo ofron 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 aggregate dhe join. Kjo rrit ndjeshëm performancën duke shfrytëzuar burimet e serverit të largët për këto operacione që kërkojnë shumë burime.

Mjedisi i Benchmarkut

  • Serveri Supermicro:
    • IntelÂź XeonÂź CPU E5-2683 v3 @ 2.00GHz
    • 2 socketĂ« / 28 bĂ«rthama / 56 thread-e
    • Memoria: 256GB RAM
    • Ruajtja: Samsung SM863 1.9TB Enterprise SSD
    • Sistemi i SkedarĂ«ve: ext4/xfs
  • OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
  • PostgreSQL: versioni 11

Testet e Benchmarkut

Në vend që të përdorim ndonjë grup të dhënash të gjeneruara nga makina për këtë test, ne përdorëm të dhënat "Performanca e kohës, e raportuar për korespondencën e operatorit" nga viti 1987 deri në 2018. Mund të qaseni në të dhënat me anë të skriptit tonë, i cili është në dispozicion këtu.

Madhësia e databazës është 85 GB, duke ofruar një tabelë me 109 kolona.

Pyetjet e Benchmarkut

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

Q#
KĂ«rkesa PĂ«rmban Agregate dhe Grupim Nga

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;

KĂ«rkesa PĂ«rmban Bashkime

Q14
SELECT a.Year, c1/c2 FROM ( select Year, count()1000 as c1 from ontime WHERE DepDelay > 10 GROUP BY Year) a INNER JOIN (select Year, count(*) as c2 from ontime GROUP BY Year ) b on a.Year = b.Year ORDER BY a.Year;

Q15
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";

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

Ekzekutimet e kërkesave

Këtu janë 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 vetjak dhe clickhousedb_fdw. Koha tregohet në milisekonda.

Q#
PostgreSQL
PostgreSQL (Me Indekse)
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 shpenzuar për të ekzekutuar kërkesat e përdorura në benchmark

Shiko rezultatet

Grafiku tregon kohën e ekzekutimit të kërkesës në milisekonda, aksin X e tregon numrin e kërkesës nga tabelat më lart, ndërsa aksin Y e tregon kohën e ekzekutimit në milisekonda. Rezultatet e ClickHouse dhe të dhënat e marra nga postgres me ndihmën e clickhousedb_fdw janë treguar. Nga tabela, vihet re një ndryshim i madh midis PostgreSQL dhe ClickHouse, por një ndryshim minimal midis ClickHouse dhe clickhousedb_fdw.

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

Ky grafik tregon ndryshimin midis ClickhouseDB dhe clickhousedb_fdw. Në shumicën e kërkesave, shpenzimet e FDW nuk janë aq të mëdha dhe janë pak të dukshme, përveç Q12. Kjo kërkesë përfshin bashkime dhe propozimin ORDER BY. Për shkak të propozimit ORDER BY, GROUP/BY dhe ORDER BY nuk hiqen në ClickHouse.

Në tabelën 2 shohim një rritje të kohës për kërkesat Q12 dhe Q13. Po e përsëris, kjo shkaktohet nga propozimi ORDER BY. Për ta konfirmuar këtë, kam ekzekutuar kërkesat 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 debugin e kësaj kërkese, po shpjegoj të dyja kërkesat, 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: Kërkesa Pa Klauzolën ORDER BY

PLAN KËRKESËS                                                      
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")  
->  Skanime të Huaja  (cost=1.00..-1.00 rows=100000 width=12)        
Output: fontime."Year", ((count(*) * 1000))        
Marrëdhënie: Agregat mbi (fontime)        
SQL i Largët: 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"        
->  Skanime në Nënkërkesë mbi b  (cost=1.00..999.00 rows=100000 width=12)              
Output: b.c2, b."Year"              
->  Skanime të Huaja  (cost=1.00..-1.00 rows=100000 width=12)                    
Output: fontime_1."Year", (count(*))                    
Marrëdhënie: Agregat mbi (fontime)                    
SQL i Largët: SELECT "Year", count(*) FROM "default".ontime GROUP BY "Year"(16 rows)

Q14: Kërkesa Me 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" 
     ORDER BY a."Year";

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

PLAN KËRKESËS 
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")   
->  Grumbullim Agregat  (cost=1.00..499.01 rows=1 width=12)        
Output: fontime."Year", (count(*) * 1000)         
ÇelĂ«si i Grumbullimit: fontime."Year"         
->  Skanime të Huaja mbi public.fontime  (cost=1.00..-1.00 rows=100000 width=4)               
SQL i Largët: SELECT "Year" FROM "default".ontime WHERE (("DepDelay" > 10)) 
            ORDER BY "Year" ASC   
->  Grumbullim Agregat  (cost=1.00..499.01 rows=1 width=12)         
Output: fontime_1."Year", count(*)         ÇelĂ«si i Grumbullimit: fontime_1."Year"         
->  Skanime të Huaja mbi public.fontime fontime_1  (cost=1.00..-1.00 rows=100000 width=4) 
              
SQL i Largët: SELECT "Year" FROM "default".ontime ORDER BY "Year" ASC(16 rows)

PĂ«rfundimi

Rezultatet e këtyre eksperimentëve tregojnë se ClickHouse ofron të vërtetë mirë performancë, dhe clickhousedb_fdw ofron avantazhe të performancës së ClickHouse nga PostgreSQL. Megjithëse ka disa shpenzime të lidhura me përdorimin e clickhousedb_fdw, ato janë të papërfillshme dhe të krahasueshme me performancën e arritur gjatë ekzekutimit natyror në databasën ClickHouse. Kjo gjithashtu konfirmon se fdw në PostgreSQL siguron rezultate të shkëlqyera.

Grupi i Telegramit për Clickhouse https://t.me/clickhouse_ru
Grupi i Telegramit për PostgreSQL https://t.me/pgsql

Burimi: habr.com

Blini hosting tĂ« besueshĂ«m pĂ«r faqe interneti me mbrojtje nga DDoS, serverĂ« VPS VDS đŸ”„ Blini hosting tĂ« besueshĂ«m pĂ«r faqe interneti me mbrojtje nga DDoS, serverĂ« VPS VDS | ProHoster