Šajā pētījumā es vēlējos apskatīt, kādas veiktspējas uzlabojumus var iegūt, izmantojot datu avotu ClickHouse, nevis PostgreSQL. Es zinu, kādas veiktspējas priekšrocības sniedz ClickHouse. Vai šīs priekšrocības saglabāsies, ja es piekļušu ClickHouse no PostgreSQL, izmantojot ārējo datu apvalku (FDW)?
Pētāmās datu bāzes vidi ir PostgreSQL v11, clickhousedb_fdw un ClickHouse datu bāze. Galu galā no PostgreSQL v11 mēs izpildīsim dažādus SQL vaicājumus, kas tiks maršrutēti caur mūsu clickhousedb_fdw uz ClickHouse datu bāzi. Pēc tam mēs redzēsim, kā FDW veiktspēja salīdzinās ar tiem pašiem vaicājumiem, kas tiek izpildīti nativajā PostgreSQL un nativajā ClickHouse.
Clickhouse datu bāze
ClickHouse ir atvērtā koda kolonnveida datu bāzes pārvaldības sistēma, kas var sasniegt veiktspēju no 100 līdz 1000 reizēm ātrāk nekā tradicionālās datu bāzes pieejas, spējot apstrādāt vairāk nekā miljardu rindu mazāk nekā sekundē.
Clickhousedb_fdw
clickhousedb_fdw ir ClickHouse datu bāzes ārējo datu apvalks (FDW), kas ir atvērtā koda projekts no Percona. .
.
Kā jūs redzēsiet, tas nodrošina FDW ClickHouse, kas ļauj SELECT no, un INSERT INTO ClickHouse datu bāzi no PostgreSQL v11 servera.
FDW atbalsta paplašinātas funkcijas, piemēram, agregāciju un apvienošanu. Tas ievērojami uzlabo veiktspēju, izmantojot attālās servera resursus šīm resursietilpīgajām operācijām.
Mērījumu vide
- Supermicro serveris:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2 soketi / 28 kodoli / 56 pavedieni
- Atmiņa: 256GB RAM
- Krātuve: Samsung SM863 1.9TB uzņēmuma SSD
- Failu sistēma: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: versija 11
Mērījumu testi
Pikļu izmantošanai šajā testā, nevis kādu mašīnas ģenerētu datu kopu, mēs izmantojām datus "Veiktspēja laikā, ziņojot par operatora darba laiku" no 1987. līdz 2018. gadam. Jūs varat piekļūt datiem .
Datu bāzes izmērs ir 85 GB, nodrošinot vienu tabulu ar 109 kolonnām.
Mērījumu vaicājumi
Šeit ir vaicājumi, kurus es izmantoju, lai salīdzinātu ClickHouse, clickhousedb_fdw un PostgreSQL.
Q#
Vaicājums satur agregātus un grupu pa
Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q2
IZVELIET DayOfWeek, count(*) AS c NO ontime KUR DepDelay>10 UN GADS >= 2000 UN GADS <= 2008 GRUPĒJET pēc DayOfWeek RINDĀ ar c DESC;
Q3
IZVELIET Origin, count(*) AS c NO ontime KUR DepDelay>10 UN GADS >= 2000 UN GADS <= 2008 GRUPĒJET pēc Origin RINDĀ ar c DESC IEROBEŽOT 10;
Q4
IZVELIET Carrier, count() NO ontime KUR DepDelay>10 UN GADS = 2007 GRUPĒJET pēc Carrier RINDĀ ar count() DESC;
Q5
IZVELIET a.Carrier, c, c2, c1000/c2 kā c3 NO ( IZVELIET Carrier, count() AS c NO ontime KUR DepDelay>10 UN GADS=2007 GRUPĒJET pēc Carrier ) a IESTATĪT ( IZVELIET Carrier,count(*) AS c2 NO ontime KUR GADS=2007 GRUPĒJET pēc Carrier)b ON a.Carrier=b.Carrier RINDĀ ar c3 DESC;
Q6
IZVELIET a.Carrier, c, c2, c1000/c2 kā c3 NO ( IZVELIET Carrier, count() AS c NO ontime KUR DepDelay>10 UN GADS >= 2000 UN GADS <= 2008 GRUPĒJET pēc Carrier) a IESTATĪT ( IZVELIET Carrier, count(*) AS c2 NO ontime KUR GADS >= 2000 UN GADS <= 2008 GRUPĒJET pēc Carrier ) b ON a.Carrier=b.Carrier RINDĀ ar c3 DESC;
Q7
IZVELIET Carrier, avg(DepDelay) * 1000 AS c3 NO ontime KUR GADS >= 2000 UN GADS <= 2008 GRUPĒJET pēc Carrier;
Q8
IZVELIET GADS, avg(DepDelay) NO ontime GRUPĒJET pēc GADS;
Q9
izvēlieties GADS, count(*) kā c1 no ontime grupēt pēc GADS;
Q10
IZVELIET avg(cnt) NO (IZVELIET GADS, Mēnesis,count(*) AS cnt NO ontime KUR DepDel15=1 GRUPĒJET pēc GADS, Mēnesis) a;
Q11
izvēlieties avg(c1) no (izvēlieties GADS, Mēnesis,count(*) kā c1 no ontime grupēt pēc GADS, Mēnesis) a;
Q12
IZVELIET OriginCityName, DestCityName, count(*) AS c NO ontime GRUPĒJET pēc OriginCityName, DestCityName RINDĀ ar c DESC IEROBEŽOT 10;
Q13
IZVELIET OriginCityName, count(*) AS c NO ontime GRUPĒJET pēc OriginCityName RINDĀ ar c DESC IEROBEŽOT 10;
Query Satur Navē
Q14
IZVELIET a.GADS, c1/c2 NO ( izvēlieties GADS, count()1000 kā c1 NO ontime KUR DepDelay>10 GRUPĒJET pēc GADS) a IESTATĪT (izvēlieties GADS, count(*) kā c2 no ontime grupēt pēc GADS ) b ON a.GADS=b.GADS RINDĀ ar a.GADS;
Q15
IZVELIET a.”GADS”, c1/c2 NO ( izvēlieties “GADS”, count()1000 kā c1 NO fontime KUR “DepDelay”>10 GRUPĒJET pēc “GADS”) a IESTATĪT (izvēlieties “GADS”, count(*) kā c2 no fontime grupēt pēc “GADS” ) b ON a.”GADS”=b.”GADS”;
Tabula-1: Vaicājumi, kas izmantoti salīdzinājumā
Vaicājumu izpildes
Šeit ir katra vaicājuma rezultāti, veicot dažadas datubāzes iestatījumus: PostgreSQL ar indeksiem un bez tiem, pašu ClickHouse un clickhousedb_fdw. Laiks tiek rādīts milisekundēs.
Q#
PostgreSQL
PostgreSQL (indeksēts)
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
Tabula-1: Laiks, kas nepieciešams, lai izpildītu vaicājumus, kas izmantoti salīdzinājumā
Pārbaudiet rezultātus
Grafiks parāda vaicājuma izpildes laiku milisekundēs, X ass parāda vaicājuma numuru no augstāk minētajām tabulām, bet Y ass parāda izpildes laiku milisekundēs. ClickHouse rezultāti un dati, kas iegūti no postgres ar clickhousedb_fdw, tiek parādīti. Tabulā redzams, ka pastāv milzīga atšķirība starp PostgreSQL un ClickHouse, bet minimāla atšķirība starp ClickHouse un clickhousedb_fdw.

Šis grafiks parāda atšķirības starp ClickhouseDB un clickhousedb_fdw. Lielākajā daļā vaicājumu FDW uzliktās izmaksas nav tik lielas un, iespējams, ir nenozīmīgas, izņemot Q12. Šis vaicājums ietver apvienojumus un pasūtījuma piedāvājumu. Pasūtījuma pēc GROUP/BY un ORDER BY tiek izlaisti līdz ClickHouse.
2. tabulā redzam pieaugumu vaicājumu Q12 un Q13 laikā. Atkārtoju, tas ir saistīts ar ORDER BY frāzi. Lai to apstiprinātu, es izpildīju vaicājumus Q-14 un Q-15 ar ORDER BY frāzi un bez tās. Bez ORDER BY frāzes izpildes laiks ir 259 ms, bet ar ORDER BY frāzi — 1364212. Lai atkļūdotu šo vaicājumu, es paskaidroju abus vaicājumus, un šeit ir paskaidrojumu rezultāti.
Q15: Bez ORDER BY frāzes
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: Vaicājums bez ORDER BY frāzes
VAICĀJUMA PLĀNS
Hash Join (izmaksas=2250.00..128516.06 rindas=50000000 platums=12)
Izvade: fontime."Year", (((count(*) * 1000)) / b.c2)
Iekšējā unikāla: patiesi Hash Cond: (fontime."Year" = b."Year")
-> Ārējā skenēšana (izmaksas=1.00..-1.00 rindas=100000 platums=12)
Izvade: fontime."Year", ((count(*) * 1000))
Attiecības: Agregācija uz (fontime)
Attālinātā SQL: SELECT "Year", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Year"
-> Hash (izmaksas=999.00..999.00 rindas=100000 platums=12)
Izvade: b.c2, b."Year"
-> Apakšvaicājuma skenēšana uz b (izmaksas=1.00..999.00 rindas=100000 platums=12)
Izvade: b.c2, b."Year"
-> Ārējā skenēšana (izmaksas=1.00..-1.00 rindas=100000 platums=12)
Izvade: fontime_1."Year", (count(*))
Attiecības: Agregācija uz (fontime)
Attālinātā SQL: SELECT "Year", count(*) FROM "default".ontime GROUP BY "Year"(16 rindas)Q14: Vaicājums ar ORDER BY frāzi
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: Vaicājuma plāns ar ORDER BY frāzi
VAICĀJUMA PLĀNS
Merge Join (izmaksas=2.00..628498.02 rindas=50000000 platums=12)
Izvade: fontime."Year", (((count(*) * 1000)) / (count(*)))
Iekšējā unikāla: patiesi Merge Cond: (fontime."Year" = fontime_1."Year")
-> GroupAggregate (izmaksas=1.00..499.01 rindas=1 platums=12)
Izvade: fontime."Year", (count(*) * 1000)
Grupas atslēga: fontime."Year"
-> Ārējā skenēšana uz public.fontime (izmaksas=1.00..-1.00 rindas=100000 platums=4)
Attālinātā SQL: SELECT "Year" FROM "default".ontime WHERE (("DepDelay" > 10))
ORDER BY "Year" ASC
-> GroupAggregate (izmaksas=1.00..499.01 rindas=1 platums=12)
Izvade: fontime_1."Year", count(*) Grupas atslēga: fontime_1."Year"
-> Ārējā skenēšana uz public.fontime fontime_1 (izmaksas=1.00..-1.00 rindas=100000 platums=4)
Attālinātā SQL: SELECT "Year" FROM "default".ontime ORDER BY "Year" ASC(16 rindas)Izvade
Šo eksperimentu rezultāti rāda, ka ClickHouse nodrošina patiešām labu veiktspēju, savukārt clickhousedb_fdw piedāvā ClickHouse veiktspējas priekšrocības no PostgreSQL. Lai gan, izmantojot clickhousedb_fdw, ir daži pārvaldības izdevumi, tie ir nenozīmīgi un salīdzināmi ar veiktspēju, kas tiek sasniegta, dabiski darbinot datubāzē ClickHouse. Tas arī apstiprina, ka fdw PostgreSQL nodrošina ievērojamus rezultātus.
Telegram grupas čats par Clickhouse
Telegram grupas čats par PostgreSQL
Avots: habr.com
