Šajā pētījumā es vēlējos noskaidrot, kādus veiktspējas uzlabojumus var panākt, izmantojot ClickHouse datu avotu, nevis PostgreSQL. Es zinu produktivitātes priekšrocības, ko gūstu, izmantojot ClickHouse. Vai šīs priekšrocības turpināsies, ja es piekļūšu ClickHouse no PostgreSQL, izmantojot svešu datu iesaiņotāju (FDW)?
Izpētītās datu bāzes vides 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 redzēsim, kā FDW veiktspēja ir salīdzināma ar tiem pašiem vaicājumiem, kas darbojas vietējā PostgreSQL un vietējā ClickHouse.
Clickhouse datu bāze
ClickHouse ir atvērtā pirmkoda kolonnu datu bāzes pārvaldības sistēma, kas var sasniegt veiktspēju 100–1000 reižu ātrāk nekā tradicionālās datu bāzes pieejas, un kas spēj apstrādāt vairāk nekā miljardu rindu mazāk nekā sekundē.
Clickhousedb_fdw
clickhousedb_fdw — ClickHouse datu bāzes jeb FDW ārējais datu iesaiņojums ir Percona atvērtā koda projekts. .
.
Kā jūs redzēsit, tas nodrošina ClickHouse FDW, kas ļauj SELECT un INSERT INTO no ClickHouse datu bāzes no PostgreSQL v11 servera.
FDW atbalsta uzlabotas funkcijas, piemēram, apkopošanu un pievienošanos. Tas ievērojami uzlabo veiktspēju, izmantojot attālā servera resursus šīm resursietilpīgajām darbībām.
Etalona vide
- Supermicro serveris:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00 GHz
- 2 ligzdas / 28 serdeņi / 56 vītnes
- Atmiņa: 256 GB RAM
- Krātuve: Samsung SM863 1.9TB Enterprise SSD
- Failu sistēma: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: versija 11
Etalona testi
Tā vietā, lai šim testam izmantotu dažas mašīnas ģenerētas datu kopas, mēs izmantojām datus “Produktivitāte pēc laika ziņotā operatora laika” no 1987. gada 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.
Etalona vaicājumi
Šeit ir vaicājumi, kurus izmantoju, lai salīdzinātu ClickHouse, clickhousedb_fdw un PostgreSQL.
Q#
Vaicājums satur apkopojumus un grupēt pēc
Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Gads >= 2000 UN gads <= 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 UN gads >= 2000 UN gads <= 2008 GRUPA PĒC Izcelsmes PĒC c DESC LIMIT 10;
Q4
ATLASĪT pārvadātāju, skaitīt() FROM ontime WHERE DepDelay>10 UN gads = 2007 GROUP BY Carrier ORDER BY count() DESC;
Q5
ATLASĪT a.Carrier, c, c2, c1000/c2 kā c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 UN gads=2007 GROUP BY Carrier ) a INNER JOIN ( SELECT Carrier,count(*) AS c2 FROM ontime WHERE Year=2007 GROUP BY Carrier)b uz a.Carrier=b.Carrier ORDER BY c3 DESC;
Q6
ATLASĪT a.Carrier, c, c2, c1000/c2 kā c3 FROM ( SELECT Carrier, count() AS c NO ontime WHERE DepDelay>10 UN gads >= 2000 UN gads <= 2008 GROUP BY Carrier) a INNER JOIN ( SELECT Carrier, count (*) AS c2 FROM ontime WHERE gads >= 2000 UN gads <= 2008 GROUP BY Carrier ) b on a.Carrier=b.Carrier ORDER BY c3 DESC;
Q7
SELECT Carrier, vid.(DepDelay) * 1000 AS c3 FROM ontime WHERE Gads >= 2000 UN gads <= 2008 GROUP BY Carrier;
Q8
SELECT Gads, vid.(DepDelay) FROM ontime GROUP BY Year;
Q9
atlasiet Gads, skaitiet(*) kā c1 no ontime grupas pēc gada;
Q10
SELECT avg(cnt) FROM (SELECT Year, Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY BY Year, Month) a;
Q11
atlasiet vid.(c1) no (izvēlieties gads, mēnesis, skaits(*) kā c1 no ontime grupas pēc gada, mēnesis) 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;
Vaicājums satur pievienošanos
Q14
SELECT a.Year, c1/c2 FROM ( izvēlieties Year, count()1000 kā c1 no sākuma WHERE DepDelay>10 GROUP BY Gada) a IEKŠĒJĀ PIEVIENOŠANĀS (atlasiet Gads, skaitiet (*) kā c2 no sākuma GROUP BY Year ) b uz a.Year=b.Year ORDER BY a.Year;
Q15
SELECT a."Gads", c1/c2 NO ( izvēlieties "Gads", skaitiet()1000 kā c1 NO fontime WHERE “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (atlasiet “Gads”, skaitiet (*) kā c2 FROM fontime GROUP BY BY “Year”) b uz a.”Gads”=b. "Gads";
1. tabula: etalonā izmantotie vaicājumi
Vaicājuma izpildes
Šeit ir katra vaicājuma rezultāti, ja tie tiek izpildīti dažādos datu bāzes iestatījumos: PostgreSQL ar un bez indeksiem, vietējā ClickHouse un clickhousedb_fdw. Laiks tiek rādīts milisekundēs.
Q#
PostgreSQL
PostgreSQL (indeksēts)
Noklikšķiniet uz Māja
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. tabula: laiks, kas nepieciešams etalonā izmantoto vaicājumu izpildei
Skatīt rezultātus
Diagrammā ir parādīts vaicājuma izpildes laiks milisekundēs, X ass parāda vaicājuma numuru no iepriekš minētajām tabulām, bet Y ass parāda izpildes laiku milisekundēs. Tiek rādīti ClickHouse rezultāti un dati, kas iegūti no postgres, izmantojot clickhousedb_fdw. No tabulas var redzēt, ka pastāv milzīga atšķirība starp PostgreSQL un ClickHouse, bet minimāla atšķirība starp ClickHouse un clickhousedb_fdw.
Šajā diagrammā parādīta atšķirība starp ClickhouseDB un clickhousedb_fdw. Lielākajā daļā vaicājumu FDW pieskaitāmās izmaksas nav tik lielas un ir gandrīz nozīmīgas, izņemot Q12. Šis vaicājums ietver pievienošanos un ORDER BY klauzulu. ORDER BY GROUP/BY klauzulas dēļ ORDER BY nenolaižas uz ClickHouse.
2. tabulā redzams laika lēciens vaicājumos Q12 un Q13. To atkal izraisa ORDER BY klauzula. Lai to apstiprinātu, es izpildīju vaicājumus Q-14 un Q-15 ar un bez klauzulas ORDER BY. Bez ORDER BY klauzulas pabeigšanas laiks ir 259 ms, bet ar ORDER BY klauzulu tas ir 1364212. Lai atkļūdotu šo vaicājumu, es izskaidroju gan vaicājumus, gan šeit ir paskaidrojuma rezultāti.
Q15: bez ORDER BY klauzulas
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 klauzulas
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. jautājums: vaicājums ar ORDER BY klauzulu
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. jautājums: vaicājumu plāns ar ORDER BY klauzulu
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)secinājums
Šo eksperimentu rezultāti liecina, ka ClickHouse piedāvā patiešām labu veiktspēju, un clickhousedb_fdw piedāvā PostgreSQL ClickHouse veiktspējas priekšrocības. Lai gan, izmantojot clickhousedb_fdw, rodas papildu izmaksas, tās ir niecīgas un salīdzināmas ar veiktspēju, kas tiek sasniegta, izmantojot ClickHouse datu bāzi. Tas arī apstiprina, ka fdw programmā PostgreSQL nodrošina izcilus rezultātus.
Telegrammas tērzēšana, izmantojot Clickhouse
Telegrammas tērzēšana, izmantojot PostgreSQL
Avots: www.habr.com