Tässä tutkimuksessa halusin nähdä, mitä suorituskykyparannuksia voitaisiin saavuttaa käyttämällä ClickHouse-tietolähdettä PostgreSQL:n sijaan. Tiedän ClickHousen käytön tuottavuusedut. Jatkuvatko nämä edut, jos käytän ClickHousea PostgreSQL:stä ulkomaisen datakääreen (FDW) avulla?
Tutkittavat tietokantaympäristöt ovat PostgreSQL v11, clickhousedb_fdw ja ClickHouse-tietokanta. Viime kädessä PostgreSQL v11:stä suoritamme erilaisia SQL-kyselyitä, jotka ohjataan clickhousedb_fdw:n kautta ClickHouse-tietokantaan. Sen jälkeen näemme, kuinka FDW:n suorituskykyä verrataan samoihin kyselyihin, jotka suoritetaan alkuperäisessä PostgreSQL:ssä ja natiivissa ClickHousessa.
Clickhouse-tietokanta
ClickHouse on avoimen lähdekoodin saraketietokannan hallintajärjestelmä, joka voi saavuttaa suorituskyvyn 100-1000 kertaa nopeammin kuin perinteiset tietokantamenetelmät ja joka pystyy käsittelemään yli miljardi riviä alle sekunnissa.
Clickhousedb_fdw
clickhousedb_fdw - ClickHouse-tietokannan tai FDW:n ulkoinen datakääre on Perconan avoimen lähdekoodin projekti. .
.
Kuten näette, tämä tarjoaa ClickHouselle FDW:n, joka mahdollistaa SELECT- ja INSERT INTO -tietokannan ClickHouse-tietokannan PostgreSQL v11 -palvelimelta.
FDW tukee edistyneitä ominaisuuksia, kuten yhdistämistä ja liittymistä. Tämä parantaa merkittävästi suorituskykyä käyttämällä etäpalvelimen resursseja näihin resurssiintensiivisiin toimintoihin.
Benchmark-ympäristö
- Supermicro-palvelin:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00 GHz
- 2 kantaa / 28 ydintä / 56 kierrettä
- Muisti: 256GB RAM
- Tallennustila: Samsung SM863 1.9TB Enterprise SSD
- Tiedostojärjestelmä: ext4/xfs
- Käyttöjärjestelmä: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: versio 11
Vertailutestit
Sen sijaan, että olisimme käyttäneet koneella luotuja tietojoukkoja tähän testiin, käytimme "Tuottavuus aikaraportoidun kuljettajan ajan mukaan" -tietoja vuosilta 1987–2018. Pääset käsiksi tietoihin .
Tietokannan koko on 85 Gt, mikä tarjoaa yhden 109 sarakkeen taulukon.
Vertailukyselyt
Tässä ovat kyselyt, joita käytin vertaamaan ClickHousea, clickhousedb_fdw:tä ja PostgreSQL:ää.
Q#
Kysely sisältää aggregaatteja ja ryhmittelyperusteita
Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Vuosi >= 2000 AND Vuosi <= 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 c DESC RAJA 10;
Q4
SELECT Operaattori, count() FROM ontime WHERE DepDelay>10 AND Year = 2007 GROUP BY Operaattorin TILAUS count() DESC;
Q5
VALITSE a.Kantaja, c, c2, c1000/c2 as c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year=2007 GROUP BY Carrier ) a SISÄINEN LIITTYMINEN ( SELECT Carrier,count(*) AS c2 FROM ontime WHERE Vuosi=2007 GROUP BY Carrier)b a.Carrier=b.Crier ORDER C3 DESC:llä;
Q6
VALITSE a.Kantaja, c, c2, c1000/c2 as c3 FROM ( SELECT Carrier, count() AS c ontimesta WHERE DepDelay>10 JA vuosi >= 2000 JA vuosi <= 2008 GROUP BY Carrier) a SISÄINEN LIITTYMINEN ( SELECT Operaattori, count(*) AS c2 ontime WHERE Vuosi >= 2000 JA vuosi <= 2008 GROUP BY Kantoaalto ) b on a.Kantaja=b.Kantaja ORDER BY c3 DESC;
Q7
SELECT Kantoaalto, keskiarvo (DepDelay) * 1000 AS c3 FROM ontime WHERE Vuosi >= 2000 JA Vuosi <= 2008 GROUP BY Kantaja;
Q8
SELECT Vuosi, keskiarvo(DepDelay) FROM ontime GROUP BY Year;
Q9
valitse Vuosi, laske(*) c1:ksi ontime-ryhmästä vuoden mukaan;
Q10
SELECT avg(cnt) FROM (SELECT Vuosi,kuukausi,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;
Q11
valitse avg(c1) from (valitse Vuosi,kuukausi,count(*) as c1 from ontime ryhmä Year,Month) a;
Q12
SELECT OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC RAJA 10;
Q13
SELECT OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC RAJA 10;
Kysely sisältää liittymiä
Q14
SELECT a.Year, c1/c2 FROM ( valitse vuosi, määrä()1000 kuin c1 ON-ajasta WHERE DepDelay>10 GROUP BY BY) a SISÄINEN LIITTYMINEN (valitse Vuosi, laske (*) c2:ksi ON-ajasta RYHMÄ VUOTTA ) b on a.Year=b.Year ORDER BY a.Year;
Q15
SELECT a."Year", c1/c2 FROM ( valitse "Vuosi", laske()1000 as c1 FROM fonttime WHERE "DepDelay">10 GROUP BY "Year") a INNER JOIN (valitse "Vuosi", laske (*) c2 FROM fonttimesta GROUP BY "Year" ) b on a."Year"=b. "Vuosi";
Taulukko 1: Vertailussa käytetyt kyselyt
Kyselyn teloitukset
Tässä ovat kunkin kyselyn tulokset, kun ne suoritetaan eri tietokanta-asetuksissa: PostgreSQL indekseillä ja ilman, natiivi ClickHouse ja clickhousedb_fdw. Aika näytetään millisekunteina.
Q#
PostgreSQL
PostgreSQL (indeksoitu)
Napsauta taloa
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
Taulukko 1: Vertailussa käytettyjen kyselyiden suorittamiseen kulunut aika
Näytä tulokset
Kaavio näyttää kyselyn suoritusajan millisekunteina, X-akselilla kyselyn numero yllä olevista taulukoista ja Y-akselilla suoritusaika millisekunteina. ClickHouse-tulokset ja postgresista clickhousedb_fdw:n avulla haetut tiedot näytetään. Taulukosta näet, että PostgreSQL:n ja ClickHousen välillä on valtava ero, mutta pieni ero ClickHousen ja clickhousedb_fdw:n välillä.
Tämä kaavio näyttää eron ClickhouseDB:n ja clickhousedb_fdw:n välillä. Useimmissa kyselyissä FDW-yleiskustannukset eivät ole niin korkeat ja tuskin merkittäviä lukuun ottamatta Q12:ta. Tämä kysely sisältää liitokset ja ORDER BY -lauseen. ORDER BY GROUP/BY -lausekkeen vuoksi ORDER BY ei pudota ClickHouseen.
Taulukossa 2 näkyy aikahyppy kyselyissä Q12 ja Q13. Tämä taas johtuu ORDER BY -lauseesta. Tämän vahvistamiseksi suoritin kyselyt Q-14 ja Q-15 ORDER BY -lausekkeen kanssa ja ilman sitä. Ilman ORDER BY -lausetta valmistumisaika on 259 ms ja ORDER BY -lauseella 1364212. Tämän kyselyn virheenkorjausta varten selitän molemmat kyselyt ja tässä ovat selityksen tulokset.
Q15: Ilman ORDER BY -lauseketta
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: Kysely ilman ORDER BY -lausetta
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)Q14: Kysely ORDER BY -lausekkeella
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";Kysymys 14: Kyselysuunnitelma ORDER BY -lausekkeella
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)johtopäätös
Näiden kokeiden tulokset osoittavat, että ClickHouse tarjoaa todella hyvän suorituskyvyn, ja clickhousedb_fdw tarjoaa PostgreSQL:n ClickHousen suorituskykyedut. Vaikka clickhousedb_fdw:n käyttäminen aiheuttaa jonkin verran lisäkustannuksia, se on mitätön ja verrattavissa ClickHouse-tietokannan natiivisti suoritettuun suorituskykyyn. Tämä vahvistaa myös sen, että fdw PostgreSQL:ssä tarjoaa erinomaisia tuloksia.
Telegram-chat Clickhousen kautta
Telegram-chat PostgreSQL:llä
Lähde: will.com