Šiame tyrime norėjau sužinoti, kokius našumo patobulinimus galima pasiekti naudojant ClickHouse duomenų šaltinį, o ne PostgreSQL. Žinau, kokią naudą našumui gaunu naudodamas „ClickHouse“. Ar šie pranašumai išliks, jei „ClickHouse“ pasieksiu iš „PostgreSQL“ naudodamas užsienio duomenų įvyniotuvą (FDW)?
Tirtos duomenų bazės aplinkos yra PostgreSQL v11, clickhousedb_fdw ir ClickHouse duomenų bazė. Galiausiai iš PostgreSQL v11 vykdysime įvairias SQL užklausas, nukreiptas per mūsų clickhousedb_fdw į ClickHouse duomenų bazę. Tada pamatysime, kaip FDW našumas lyginamas su tomis pačiomis užklausomis, vykdomomis vietinėje PostgreSQL ir savojoje ClickHouse.
Clickhouse duomenų bazė
ClickHouse yra atvirojo kodo stulpelių duomenų bazių valdymo sistema, kuri gali pasiekti našumą 100–1000 kartų greičiau nei tradiciniai duomenų bazės metodai, galinti apdoroti daugiau nei milijardą eilučių greičiau nei per sekundę.
Clickhousedb_fdw
clickhousedb_fdw – „ClickHouse“ duomenų bazės arba FDW išorinis duomenų paketas yra „Percona“ atvirojo kodo projektas. .
.
Kaip matysite, tai suteikia ClickHouse FDW, leidžiantį SELECT ir INSERT INTO iš ClickHouse duomenų bazės iš PostgreSQL v11 serverio.
FDW palaiko išplėstines funkcijas, tokias kaip agregavimas ir prisijungimas. Tai žymiai pagerina našumą naudojant nuotolinio serverio išteklius šioms daug išteklių reikalaujančioms operacijoms.
Etaloninė aplinka
- Supermicro serveris:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00 GHz
- 2 lizdai / 28 branduoliai / 56 sriegiai
- Atmintis: 256 GB RAM
- Saugykla: Samsung SM863 1.9TB Enterprise SSD
- Failų sistema: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: 11 versija
Lyginamieji testai
Užuot naudoję tam tikrą mašininiu būdu sukurtą duomenų rinkinį šiam bandymui, naudojome 1987–2018 m. duomenis „Produktyvumas pagal operatoriaus laiką“. Galite pasiekti duomenis .
Duomenų bazės dydis yra 85 GB, joje yra viena 109 stulpelių lentelė.
Lyginamosios užklausos
Štai užklausos, kurias naudojau palygindamas ClickHouse, clickhousedb_fdw ir PostgreSQL.
Q#
Užklausoje yra suvestiniai duomenys ir grupavimas pagal
Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Metai >= 2000 IR metai <= 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
PASIRINKITE kilmę, skaičiuokite (*) AS c FROM ontime WHERE DepDelay>10 IR Metai >= 2000 IR metai <= 2008 GRUPĖ PAGAL kilmę UŽSAKYTI BY c DESC RIBA 10;
Q4
PASIRINKITE vežėją, skaičiuokite () FROM ontime WHERE DepDelay>10 AND Metai = 2007 GROUP BY Vežėjas UŽSAKYMAS PAGAL SKAIČIUS() DESC;
Q5
PASIRINKITE a.Carrier, c, c2, c1000/c2 kaip c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Metai = 2007 GROUP BY Carrier ) a VIDINIS PRISIJUNGTI ( SELECT Carrier,count(*) AS c2 FROM ontime WHERE Metai = 2007 GROUP BY Carrier)b ant a.Carrier = b. Carrier ORDER BY c3 DESC;
Q6
PASIRINKITE a.Carrier, c, c2, c1000/c2 kaip c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 IR metai >= 2000 IR metai <= 2008 GROUP BY Carrier) a VIDINIS PRISIJUNGTI ( PASIRINKITE vežėją, skaičiuokite (*) AS c2 FROM ontime WHERE Metai >= 2000 IR metai <= 2008 GROUP BY Vežėjas ) b ant a.Carrier=b.Carrier ORDER BY c3 DESC;
Q7
PASIRINKITE vežėją, vid.(DepDelay) * 1000 AS c3 FROM ontime WHERE Metai >= 2000 IR metai <= 2008 GROUP BY Carrier;
Q8
PASIRINKTI Metus, vid.(DepDelay) FROM ontime GROUP BY Year;
Q9
pasirinkite Metai, skaičiuokite (*) kaip c1 iš ontime grupės pagal metus;
Q10
SELECT avg(cnt) FROM (SELECT Metai,Mėnuo,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;
Q11
pasirinkite avg(c1) iš (pasirinkite metai, mėnuo, skaičius (*) kaip c1 iš ontime grupės pagal metus, mėnesį) 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;
Užklausoje yra prisijungimų
Q14
SELECT a.Year, c1/c2 FROM ( pasirinkite Metai, skaičius ()1000 kaip c1 nuo ontime WHERE DepDelay>10 GROUP BY Year) a VIDINIS JOIN (pasirinkite Metus, skaičiuokite (*) kaip c2 nuo ontime GROUP BY Year ) b ant a.Year=b.Year UŽSAKYMAS PAGAL a.Metus;
Q15
PASIRINKITE a."Metai", c1/c2 FROM ( pasirinkite "Year", count()1000 kaip c1 FROM fontime WHERE "DepDelay">10 GROUP BY "Year") a INNER JOIN (pasirinkite "Year", skaičiuokite (*) kaip c2 FROM fontime GROUP BY "Year") b ant a."Year"=b. "Metai";
1 lentelė: palyginimui naudojamos užklausos
Užklausos vykdymas
Čia pateikiami kiekvienos užklausos rezultatai, kai jie vykdomi naudojant skirtingus duomenų bazės parametrus: PostgreSQL su indeksais ir be jų, vietinė ClickHouse ir clickhousedb_fdw. Laikas rodomas milisekundėmis.
Q#
PostgreSQL
PostgreSQL (indeksuotas)
„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
1 lentelė: laikas, per kurį atliekamos palyginime naudojamos užklausos
Žiūrėti rezultatus
Grafike rodomas užklausos vykdymo laikas milisekundėmis, X ašyje – užklausos numeris iš aukščiau pateiktų lentelių, o Y ašyje – vykdymo laikas milisekundėmis. Rodomi ClickHouse rezultatai ir duomenys, gauti iš postgres naudojant clickhousedb_fdw. Iš lentelės matote, kad yra didžiulis skirtumas tarp PostgreSQL ir ClickHouse, bet minimalus skirtumas tarp ClickHouse ir clickhousedb_fdw.
Šioje diagramoje parodytas skirtumas tarp ClickhouseDB ir clickhousedb_fdw. Daugumoje užklausų FDW pridėtinės išlaidos nėra tokios didelės ir vargu ar yra reikšmingos, išskyrus Q12. Ši užklausa apima sujungimus ir sąlygą ORDER BY. Dėl ORDER BY GROUP/BY sąlygos, ORDER BY nenukrenta į ClickHouse.
2 lentelėje matome laiko šuolį Q12 ir Q13 užklausose. Vėlgi, tai sukelia sąlyga ORDER BY. Norėdamas tai patvirtinti, vykdžiau užklausas Q-14 ir Q-15 su sąlyga ORDER BY ir be jos. Be ORDER BY sąlygos užbaigimo laikas yra 259 ms, o su ORDER BY - 1364212. Norėdami derinti šią užklausą, aš paaiškinu tiek užklausas, tiek čia yra paaiškinimo rezultatai.
15 klausimas: be ORDER BY sąlygos
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";15 klausimas: užklausa be ORDER BY sąlygos
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)14 klausimas: užklausa naudojant ORDER BY sąlygą
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";14 klausimas: užklausos planas su punktu 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)Produkcija
Šių eksperimentų rezultatai rodo, kad ClickHouse siūlo tikrai gerą našumą, o clickhousedb_fdw siūlo PostgreSQL ClickHouse našumo pranašumus. Nors naudojant clickhousedb_fdw yra šiek tiek papildomų išlaidų, jos yra nereikšmingos ir palyginamos su našumu, pasiekiamu naudojant „ClickHouse“ duomenų bazę. Tai taip pat patvirtina, kad fdw PostgreSQL suteikia puikių rezultatų.
Telegramos pokalbis per Clickhouse
Telegramos pokalbis naudojant PostgreSQL
Šaltinis: www.habr.com