Në këtë studim, doja të shihja se cilat përmirësime të performancës mund të arriheshin duke përdorur një burim të dhënash ClickHouse në vend të PostgreSQL. Unë i di përfitimet e produktivitetit që marr nga përdorimi i ClickHouse. A do të vazhdojnë këto përfitime nëse hyj në ClickHouse nga PostgreSQL duke përdorur një mbështjellës të të dhënave të huaja (FDW)?
Mjediset e bazës së të dhënave të studiuara janë PostgreSQL v11, clickhousedb_fdw dhe bazës së të dhënave ClickHouse. Në fund të fundit, nga PostgreSQL v11 ne do të ekzekutojmë pyetje të ndryshme SQL të drejtuara përmes clickhousedb_fdw tonë në bazën e të dhënave ClickHouse. Më pas do të shohim se si performanca e FDW krahasohet me të njëjtat pyetje që ekzekutohen në PostgreSQL dhe në ClickHouse amtare.
Baza e të dhënave Clickhouse
ClickHouse është një sistem i menaxhimit të bazës së të dhënave me kod të hapur me kod të hapur që mund të arrijë performancën 100-1000 herë më shpejt se qasjet tradicionale të bazës së të dhënave, i aftë për të përpunuar mbi një miliardë rreshta në më pak se një sekondë.
Clickhousedb_fdw
clickhousedb_fdw - Mbështjellësi i jashtëm i të dhënave për bazën e të dhënave ClickHouse, ose FDW, është një projekt me burim të hapur nga Percona. .
.
Siç do ta shihni, kjo siguron një FDW për ClickHouse që lejon SELECT nga, dhe INSERT INTO, bazën e të dhënave ClickHouse nga serveri PostgreSQL v11.
FDW mbështet veçori të avancuara si agregati dhe bashkimi. Kjo përmirëson ndjeshëm performancën duke përdorur burimet e serverit në distancë për këto operacione me burime intensive.
Mjedisi standard
- Serveri supermikro:
- CPU Intel® Xeon® E5-2683 v3 @ 2.00 GHz
- 2 priza / 28 bërthama / 56 fije
- Kujtesa: 256 GB RAM
- Hapësira ruajtëse: Samsung SM863 1.9 TB 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 standarde
Në vend që të përdornim disa grupe të dhënash të krijuara nga makina për këtë test, ne përdorëm të dhënat "Produktiviteti sipas kohës së raportuar të operatorit" nga viti 1987 deri në 2018. Ju mund të përdorni të dhënat .
Madhësia e bazës së të dhënave është 85 GB, duke siguruar një tabelë me 109 kolona.
Pyetjet e standardeve
Këtu janë pyetjet që kam përdorur për të krahasuar ClickHouse, clickhousedb_fdw dhe PostgreSQL.
Q#
Pyetja përmban agregate dhe grupe sipas
Q1
ZGJIDH Ditën e Javës, numëro(*) SI c NGA kohore WHERE Viti >= 2000 DHE Viti <= 2008 GRUPI PËR DITË TËJAVËS RENDIT NGA c DESC;
Q2
ZGJIDH Ditën e Javës, numëro(*) SI c NGA kohore WHERE DepDelay>10 DHE Viti >= 2000 DHE Viti <= 2008 GRUPI PËR DITË TË Javës RENDIT NGA c DESC;
Q3
ZGJIDH Origjinën, numëro(*) AS c NGA kohëzgjatja WHERE DepDelay>10 DHE Viti >= 2000 DHE Viti <= 2008 GRUPI SIPAS Origjinës RENDOSJE SIPAS c KUFIZIMI I DESC 10;
Q4
ZGJIDH operatorin, numëro() FROM or time WHERE DepDelay>10 DHE VIT = 2007 GRUPI SIPAS operatorit ORDER SIPAS numrit () DESC;
Q5
ZGJIDH a.Transportues, c, c2, c1000/c2 si c3 FROM ( SELECT operatorin, numëro() AS c FROM ontime WHERE DepDelay>10 AND Year=2007 GROUP BY Carrier ) një BASHKIM I BRENDSHËM ( SELECT operator, numëro(*) AS c2 FROM ontime WHERE Year=2007 GROUP BY Carrier)b në a.Transportues=b.ORDER NGA c3 DESC;
Q6
ZGJIDH a.Transportues, c, c2, c1000/c2 si c3 FROM ( SELECT operatorin, numëro() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Carrier) një BASHKIMI I BRENDSHËM ( SELECT operator, numëro(*) AS c2 FROM ontime WHERE Viti >= 2000 AND Viti <= 2008 GROUP Transportuesi ) b në a.Transportuesi=b.RENDI I Transportuesit NGA c3 DESC;
Q7
SELECT operatorin, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Viti >= 2000 DHE Viti <= 2008 GROUP BY Carrier;
Q8
ZGJIDH Vitin, mesatarisht (DepDelay) NGA kohore GROUP BY BY;
Q9
zgjidhni Viti, numëroni (*) si c1 nga grupi në kohë sipas Vitit;
Q10
SELECT avg(cnt) FROM (ZGJIDH Viti, Muaj, numërimi(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY VIT, Muaj) a;
Q11
zgjidhni mesataren (c1) nga (zgjidh Viti, Muaj, numërimi (*) si c1 nga grupi në kohë sipas Vitit, Muajit) a;
Q12
SELECT OriginCityName, DestCityName, count(*) AS c NGA kohëzgjatja GROUP BY OriginCityName, DestCityName RENDITJA NGA c KUFIZIMI DESC 10;
Q13
ZGJIDH OriginCityName, numëro(*) AS c NGA kohëzgjatja GRUPI SIPAS OriginCityName RENDITJA SIPAS c KUFIZIMI I DESC 10;
Pyetja përmban bashkime
Q14
SELECT një.Vit, c1/c2 FROM ( zgjidhni Viti, numëroni()1000 si c1 nga koha e hershme KU DepDelay>10 GRUPI PËR VIT) një BASHKIM I BRENDSHËM (zgjidh Viti, numëro(*) si c2 nga kohëzgjatja GRUPI BY VIT ) b në a.Vit=b.Vit RENDIT ME një vit;
Q15
SELECT a"Year", c1/c2 FROM ( zgjidhni "Vit", numëroni()1000 si c1 FROM fontime WHERE "DepDelay">10 GROUP BY "Year") një BASHKIM I BRENDSHËM (zgjidhni "Year", numëroni (*) si c2 FROM fontime GROUP BY BY "Year" ) b në a."Vit"=b. "Viti";
Tabela-1: Pyetjet e përdorura në pikë referimi
Ekzekutimet e pyetjeve
Këtu janë rezultatet e secilit prej pyetjeve kur ekzekutohen në cilësime të ndryshme të bazës së të dhënave: PostgreSQL me dhe pa indekse, ClickHouse amtare dhe clickhousedb_fdw. Koha tregohet në milisekonda.
Q#
PostgreSQL
PostgreSQL (i indeksuar)
Shtëpi Kliko
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 nevojshme për të ekzekutuar pyetjet e përdorura në standard
Shiko rezultatet
Grafiku tregon kohën e ekzekutimit të pyetjes në milisekonda, boshti X tregon numrin e pyetjes nga tabelat e mësipërme dhe boshti Y tregon kohën e ekzekutimit në milisekonda. Rezultatet e ClickHouse dhe të dhënat e marra nga postgres duke përdorur clickhousedb_fdw janë shfaqur. Nga tabela mund të shihni se ka një ndryshim të madh midis PostgreSQL dhe ClickHouse, por ndryshim minimal midis ClickHouse dhe clickhousedb_fdw.
Ky grafik tregon ndryshimin midis ClickhouseDB dhe clickhousedb_fdw. Në shumicën e pyetjeve, shpenzimet e përgjithshme të FDW nuk janë aq të larta dhe vështirë se janë të rëndësishme përveç Q12. Ky pyetës përfshin bashkime dhe një klauzolë RENDI SIPAS. Për shkak të klauzolës ORDER BY GROUP/BY, ORDER BY nuk zbret në ClickHouse.
Në Tabelën 2 shohim kërcimin në kohë në pyetjet Q12 dhe Q13. Përsëri, kjo shkaktohet nga klauzola ORDER BY. Për ta konfirmuar këtë, unë drejtova pyetjet Q-14 dhe Q-15 me dhe pa klauzolën ORDER BY. Pa klauzolën ORDER BY koha e përfundimit është 259ms dhe me klauzolën ORDER BY është 1364212. Për të korrigjuar këtë pyetje unë po shpjegoj të dyja pyetjet dhe këtu janë rezultatet e shpjegimit.
P15: Pa Klauzola të Urdhrit
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";P15: Pyetje pa porosi sipas klauzolës
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)Pyetja 14: Pyetje me porosi sipas klauzolës
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";Pyetja 14: Plani i pyetjeve me Klauzola 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)Prodhim
Rezultatet e këtyre eksperimenteve tregojnë se ClickHouse ofron performancë vërtet të mirë dhe clickhousedb_fdw ofron përfitimet e performancës së ClickHouse nga PostgreSQL. Ndërsa ka pak shpenzime kur përdorni clickhousedb_fdw, ai është i papërfillshëm dhe i krahasueshëm me performancën e arritur duke ekzekutuar në mënyrë origjinale në bazën e të dhënave ClickHouse. Kjo gjithashtu konfirmon se fdw në PostgreSQL ofron rezultate të shkëlqyera.
Biseda në telegram përmes Clickhouse
Biseda në telegram duke përdorur PostgreSQL
Burimi: www.habr.com