Selles uuringus tahtsin näha, milliseid jõudluse täiustusi on võimalik saavutada, kasutades PostgreSQL-i asemel ClickHouse'i andmeallikat. Ma tean, millist kasu tootlikkusele ClickHouse'i kasutamisest saan. Kas need eelised jätkuvad, kui pääsen PostgreSQL-ist ClickHouse'i juurde välisandmete ümbrise (FDW) abil?
Uuritavad andmebaasikeskkonnad on PostgreSQL v11, clickhousedb_fdw ja ClickHouse andmebaas. Lõppkokkuvõttes käivitame PostgreSQL v11-st erinevaid SQL-päringuid, mis suunatakse meie clickhousedb_fdw kaudu ClickHouse'i andmebaasi. Seejärel näeme, kuidas FDW jõudlus on võrreldav samade päringutega, mis töötavad natiivses PostgreSQL-is ja natiivses ClickHouse'is.
Clickhouse'i andmebaas
ClickHouse on avatud lähtekoodiga veergude andmebaasihaldussüsteem, mis suudab saavutada jõudlust 100–1000 korda kiiremini kui traditsioonilised andmebaasipõhised lähenemisviisid ja mis on võimeline töötlema üle miljardi rea vähem kui sekundiga.
Clickhousedb_fdw
clickhousedb_fdw – ClickHouse'i andmebaasi ehk FDW väline andmeümbris on Percona avatud lähtekoodiga projekt. .
.
Nagu näete, pakub see ClickHouse'i jaoks FDW-d, mis võimaldab valida PostgreSQL v11 serverist ClickHouse'i andmebaasist ja INSERT INTO.
FDW toetab täiustatud funktsioone, nagu koondamine ja liitumine. See parandab oluliselt jõudlust, kasutades nende ressursimahukate toimingute jaoks kaugserveri ressursse.
Võrdluskeskkond
- Supermicro server:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00 GHz
- 2 pistikupesa / 28 südamikku / 56 keermega
- Mälu: 256 GB muutmälu
- Salvestus: Samsung SM863 1.9TB Enterprise SSD
- Failisüsteem: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: versioon 11
Võrdlusuuringud
Selle testi jaoks mõne masinaga loodud andmekogumi kasutamise asemel kasutasime andmeid "Tootlikkus aja järgi teatatud operaatori aja järgi" aastatel 1987–2018. Saate andmetele juurde pääseda .
Andmebaasi suurus on 85 GB, mis annab ühe 109 veeruga tabeli.
Võrdluspäringud
Siin on päringud, mida kasutasin ClickHouse'i, clickhousedb_fdw ja PostgreSQL võrdlemiseks.
Q#
Päring sisaldab koondandmeid ja rühmitamist
Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Aasta >= 2000 JA Aasta <= 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 BY ORDER BY c DESC LIMIT 10;
Q4
SELECT operaator, count() FROM ontime WHERE DepDelay>10 AND Year = 2007 GROUP BY Vedaja ORDER BY count() DESC;
Q5
VALI a.Kandja, c, c2, c1000/c2 kui 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 Aasta=2007 GROUP BY Carrier)b on a.Carrier=b. Carrier ORDER BY c3 DESC;
Q6
VALI a.Kandja, c, c2, c1000/c2 kui c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 JA Aasta >= 2000 JA Aasta <= 2008 GROUP BY Vedaja järgi) a SISEMINE LIITUMINE ( SELECT operaator, arv(*) AS c2 FROM ontime WHERE Aasta >= 2000 JA aasta <= 2008 GROUP BY Vedaja ) b kohta a.Kandja=b.Kandja ORDER BY c3 DESC;
Q7
SELECT kandja, keskmine (DepDelay) * 1000 AS c3 FROM ontime WHERE Aasta >= 2000 JA Aasta <= 2008 GROUP BY vedaja;
Q8
SELECT Aasta, keskmine (DepDelay) FROM ontime GROUP BY Year;
Q9
vali Aasta, loenda (*) c1-ks ontime rühmast Aasta järgi;
Q10
SELECT avg(cnt) FROM (SELECT Aasta,Kuu,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;
Q11
vali avg(c1) alates (vali Aasta,Kuu,count(*) kui c1 ontime grupist Aasta,Kuu järgi) 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;
Päring sisaldab liitumisi
Q14
SELECT a.Year, c1/c2 FROM ( vali Aasta, count()1000 kui c1 alates ontime WHERE DepDelay>10 GROUP BY BY) a SISEMINE LIITUMINE (valige Aasta, loendage (*) kui c2 alates ajast GROUP BY Year ) b kohta a.Year=b.Year ORDER BY a.Year;
Q15
SELECT a."Aasta", c1/c2 FROM ( valige "Aasta", loenda)1000 kui c1 FROM fonttime WHERE "DepDelay">10 GROUP BY "Year") a INNER JOIN (valige "Aasta", loendage (*) kui c2 FROM fontime'ist GROUP BY "Year" ) b kohta a."Aasta"=b. "Aasta";
Tabel-1: võrdlusuuringus kasutatud päringud
Päringu hukkamised
Siin on iga päringu tulemused, kui need on käivitatud erinevates andmebaasisätetes: PostgreSQL indeksitega ja ilma, natiivne ClickHouse ja clickhousedb_fdw. Aega näidatakse millisekundites.
Q#
PostgreSQL
PostgreSQL (indekseeritud)
Klõpsake nuppu Maja
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
Tabel-1: võrdlusuuringus kasutatud päringute täitmiseks kuluv aeg
Vaata tulemusi
Graafik näitab päringu täitmisaega millisekundites, X-teljel on päringu number ülaltoodud tabelitest ja Y-teljel on täitmisaeg millisekundites. Kuvatakse ClickHouse'i tulemused ja postgres'ist, kasutades clickhousedb_fdw, ammutatud andmed. Tabelist näete, et PostgreSQL-i ja ClickHouse'i vahel on tohutu erinevus, kuid ClickHouse'i ja clickhousedb_fdw vahel on minimaalne erinevus.
See graafik näitab erinevust ClickhouseDB ja clickhousedb_fdw vahel. Enamiku päringute puhul ei ole FDW üldkulud nii suured ja vaevalt märkimisväärsed, välja arvatud Q12 puhul. See päring sisaldab liitumisi ja ORDER BY klauslit. Tellimuse ORDER BY GROUP/BY tõttu ei langeta ORDER BY menüüsse ClickHouse.
Tabelis 2 näeme ajahüpet päringutes Q12 ja Q13. Selle põhjuseks on jällegi klausel ORDER BY. Selle kinnitamiseks käivitasin päringud Q-14 ja Q-15 koos ja ilma ORDER BY klauslita. Ilma ORDER BY klauslita on valmimisaeg 259 ms ja ORDER BY klausliga 1364212. Selle päringu silumiseks selgitan mõlemat päringut ja siin on selgituse tulemused.
K15: Ilma ORDER BY klauslita
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";K15: päring ilma ORDER BY klauslita
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)K14: päring ORDER BY klausliga
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";K14: päringuplaan koos klausliga 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)Väljund
Nende katsete tulemused näitavad, et ClickHouse pakub tõesti head jõudlust ja clickhousedb_fdw pakub PostgreSQL-i ClickHouse'i jõudluse eeliseid. Kuigi clickhousedb_fdw kasutamisel on mõningaid lisakulusid, on see tühine ja võrreldav ClickHouse'i andmebaasis natiivse käitamisega saavutatud jõudlusega. See kinnitab ka seda, et fdw PostgreSQL-is annab suurepäraseid tulemusi.
Telegrami vestlus Clickhouse'i kaudu
Telegrami vestlus PostgreSQL-i abil
Allikas: www.habr.com