Ebben a tanulmányban azt szerettem volna látni, hogy a PostgreSQL helyett egy ClickHouse adatforrás használatával milyen teljesítményjavulás érhető el. Ismerem a ClickHouse használatából származó termelékenységi előnyöket. Továbbra is megmaradnak ezek az előnyök, ha a PostgreSQL-ből egy idegen adatcsomagoló (FDW) segítségével érem el a ClickHouse-t?
A vizsgált adatbázis-környezetek a következők: PostgreSQL v11, clickhousedb_fdw és ClickHouse adatbázis. Végső soron a PostgreSQL v11-től kezdve különféle SQL-lekérdezéseket fogunk futtatni a clickhousedb_fdw-n keresztül a ClickHouse adatbázisba. Ezután meglátjuk, hogy az FDW teljesítménye hogyan viszonyul ugyanazokhoz a lekérdezésekhez, amelyek a natív PostgreSQL-ben és a natív ClickHouse-ban futnak.
Clickhouse adatbázis
A ClickHouse egy nyílt forráskódú oszlopos adatbázis-kezelő rendszer, amely a hagyományos adatbázis-megközelítéseknél 100-1000-szer gyorsabb teljesítményt képes elérni, és több mint egymilliárd sort képes kevesebb mint egy másodperc alatt feldolgozni.
Clickhousedb_fdw
clickhousedb_fdw – A ClickHouse adatbázis vagy az FDW külső adatcsomagolója a Percona nyílt forráskódú projektje. .
.
Amint látni fogja, ez egy FDW-t biztosít a ClickHouse számára, amely lehetővé teszi a SELECT-et a ClickHouse adatbázisból, és INSERT INTO-t a PostgreSQL v11 szerverről.
Az FDW támogatja a fejlett funkciókat, például az összesítést és a csatlakozást. Ez jelentősen javítja a teljesítményt azáltal, hogy a távoli kiszolgáló erőforrásait használja ezekhez az erőforrás-igényes műveletekhez.
Benchmark környezet
- Supermicro szerver:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00 GHz
- 2 foglalat / 28 mag / 56 menet
- Memória: 256GB RAM
- Tárhely: Samsung SM863 1.9TB Enterprise SSD
- Fájlrendszer: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: 11-es verzió
Benchmark tesztek
Ahelyett, hogy ehhez a teszthez gépileg generált adatkészletet használtunk volna, a „Termékenység a kezelői idő szerint jelentett idő szerint” adatokat használtuk 1987 és 2018 között. Hozzáférhet az adatokhoz .
Az adatbázis mérete 85 GB, amely egy 109 oszlopból álló táblázatot biztosít.
Összehasonlító lekérdezések
Itt vannak azok a lekérdezések, amelyeket a ClickHouse, a clickhousedb_fdw és a PostgreSQL összehasonlításához használtam.
Q#
A lekérdezés összesítéseket és csoportosítást tartalmaz
Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Év >= 2000 ÉS év <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q2
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 ÉS év >= 2000 ÉS év <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q3
SELECT Origin, count(*) AS c FROM ontime WHERE DepDelay>10 ÉS év >= 2000 ÉS év <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;
Q4
SELECT szolgáltató, count() FROM ontime WHERE DepDelay>10 ÉS év = 2007 GROUP BY Carrier ORDER BY count() DESC;
Q5
SELECT a.Carrier, c, c2, c1000/c2 mint c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND év=2007 GROUP BY Carrier ) a BELSŐ CSATLAKOZÁS ( SELECT Carrier,count(*) AS c2 FROM ontime WHERE Év=2007 GROUP BY Carrier)b on a.Carrier=b.Carrier ORDER BY c3 DESC;
Q6
SELECT a.Carrier, c, c2, c1000/c2 mint c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 ÉS év >= 2000 ÉS év <= 2008 GROUP BY Carrier) a BELSŐ CSATLAKOZÁS ( VÁLASSZA SZÁLLÍTÓT, számolj (*) AS c2 FROM ontime WHERE Év >= 2000 ÉS év <= 2008 GROUP BY Carrier ) b on a.Carrier=b.Carrier ORDER BY c3 DESC;
Q7
SELECT Carrier, átlag (Megkésettség) * 1000 AS c3 FROM ontime WHERE Év >= 2000 ÉS év <= 2008 GROUP BY Carrier;
Q8
VÁLASZTÁS Év, átlag(Megkésés) FROM ontime GROUP BY Year;
Q9
válasszuk az Évet, a count(*) c1-et az ontime csoportból év szerint;
Q10
SELECT avg(cnt) FROM (SELECT Év,Hónap,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Hónap) a;
Q11
válasszon avg(c1) from (Válasszon Év,Hónap,count(*) as c1 from ontime csoport Év,Hónap szerint) 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;
A lekérdezés csatlakozásokat tartalmaz
Q14
SELECT a.Year, c1/c2 FROM ( Select Year, count()1000, mint c1 az időponttól WHERE DepDelay>10 GROUP BY Year) a BELSŐ CSATLAKOZÁS (Válassza ki az Évet, számolja (*) c2-ként az időponttól GROUP BY Year ) b on a.Year=b.Year ORDER BY a.Year;
Q15
SELECT a."Year", c1/c2 FROM ( válassza az "Év", count()1000, mint c1 FROM fontime WHERE “DepDelay”>10 GROUP BY “Year”) a BELSŐ JOIN (válassza az “Év”-t, számoljon (*)-ként c2-ként FROM fontime-tól GROUP BY “Year”) b on a.”Year”=b. "Év";
1. táblázat: A benchmarkban használt lekérdezések
Végrehajtások lekérdezése
Íme az egyes lekérdezések eredményei, amikor különböző adatbázis-beállításokban futnak: PostgreSQL indexekkel és anélkül, natív ClickHouse és clickhousedb_fdw. Az idő ezredmásodpercben jelenik meg.
Q#
PostgreSQL
PostgreSQL (indexelt)
Kattintson a Ház gombra
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. táblázat: A benchmarkban használt lekérdezések végrehajtásához szükséges idő
Eredmények megtekintése
A grafikon a lekérdezés végrehajtási idejét mutatja ezredmásodpercben, az X tengely a fenti táblázatokból származó lekérdezés számát, az Y tengely pedig a végrehajtási időt ezredmásodpercben. Megjelennek a ClickHouse eredményei és a postgres-ből a clickhousedb_fdw használatával lekért adatok. A táblázatból látható, hogy óriási különbség van a PostgreSQL és a ClickHouse között, de minimális a különbség a ClickHouse és a clickhousedb_fdw között.
Ez a grafikon a ClickhouseDB és a clickhousedb_fdw közötti különbséget mutatja. A legtöbb lekérdezésben az FDW rezsi nem olyan magas, és aligha jelentős, kivéve a Q12-t. Ez a lekérdezés csatlakozásokat és egy ORDER BY záradékot tartalmaz. Az ORDER BY GROUP/BY záradék miatt az ORDER BY nem jelenik meg a ClickHouse-ban.
A 2. táblázatban láthatjuk a Q12 és Q13 lekérdezések időugrását. Ezt ismét az ORDER BY záradék okozza. Ennek megerősítésére lefuttattam a Q-14 és Q-15 lekérdezéseket az ORDER BY záradékkal és anélkül. Az ORDER BY záradék nélkül a befejezési idő 259 ms, az ORDER BY záradékkal pedig 1364212. A lekérdezés hibakereséséhez mindkét lekérdezést elmagyarázom, és itt vannak a magyarázat eredményei.
15. kérdés: ORDER BY záradék nélkül
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. kérdés: Lekérdezés ORDER BY záradék nélkül
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. kérdés: Lekérdezés ORDER BY záradékkal
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. kérdés: Lekérdezési terv ORDER BY záradékkal
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)Teljesítmény
A kísérletek eredményei azt mutatják, hogy a ClickHouse igazán jó teljesítményt nyújt, a clickhousedb_fdw pedig a PostgreSQL ClickHouse teljesítménybeli előnyeit kínálja. Bár a clickhousedb_fdw használatakor van némi többletköltség, ez elhanyagolható és összehasonlítható a ClickHouse adatbázison való natív futtatással elért teljesítménnyel. Ez is megerősíti, hogy az fdw a PostgreSQL-ben kiváló eredményeket biztosít.
Telegram chat a Clickhouse-on keresztül
Telegram chat PostgreSQL használatával
Forrás: will.com