У гэтым даследаванні я хацеў паглядзець, якія паляпшэнні прадукцыйнасці можна атрымаць, выкарыстоўваючы крыніцу дадзеных ClickHouse, а не PostgreSQL. Я ведаю, якія перавагі прадукцыйнасці пры выкарыстанні ClickHouse я атрымліваю. Ці будуць гэтыя перавагі захаваны, калі я атрымаю доступ да ClickHouse з PostgreSQL з дапамогай знешняй абалонкі дадзеных (FDW)?
Доследнымі асяроддзямі баз дадзеных з'яўляюцца PostgreSQL v11, clickhousedb_fdw і база дадзеных ClickHouse. У канчатковым рахунку, з PostgreSQL v11 мы будзем запускаць розныя SQL-запыты, якія маршрутызуюцца праз наш clickhousedb_fdw у базу дадзеных ClickHouse. Затым мы ўбачым, як прадукцыйнасць FDW параўноўваецца з тымі ж запытамі, якія выконваюцца ў натыўным PostgreSQL і натыўным ClickHouse.
База даных Clickhouse
ClickHouse - гэта сістэма кіравання базамі дадзеных на аснове калонак з адкрытым зыходным кодам, якая можа дасягаць прадукцыйнасці ў 100-1000 разоў хутчэй, чым традыцыйныя падыходы да баз дадзеных, здольная апрацоўваць больш за мільярд радкоў менш чым за секунду.
Clickhousedb_fdw
clickhousedb_fdw – абалонка вонкавых дадзеных базы дадзеных ClickHouse, ці FDW, з'яўляецца праектам з адчыненым зыходным кодам ад Percona. .
.
Як вы ўбачыце, гэта забяспечвае FDW для ClickHouse, які дазваляе SELECT from, і INSERT INTO, базу дадзеных ClickHouse з сервера PostgreSQL v11.
FDW падтрымлівае пашыраныя функцыі, такія як aggregate і join. Гэта значна падвышае прадукцыйнасць за кошт выкарыстання рэсурсаў выдаленага сервера для гэтых рэсурсаёмістых аперацый.
Benchmark environment
- Supermicro server:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2 sockets / 28 cores / 56 threads
- Памяць: 256 Гб аператыўнай памяці
- Storage: Samsung SM863 1.9TB Enterprise SSD
- Filesystem: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: version 11
Benchmark tests
Замест таго, каб выкарыстоўваць нейкі набор дадзеных, згенераваны машынай, для гэтага тэсту, мы выкарыстоўвалі дадзеныя "Прадукцыйнасць па часе, якая паведамляецца аб часе працы аператара" з 1987 па 2018 год. Вы можаце атрымаць доступ да дадзеных .
Памер базы дадзеных складае 85 ГБ, забяспечваючы адну табліцу са 109 слупкоў.
Benchmark Queries
Вось запыты, якія я выкарыстоўваў для параўнання ClickHouse, clickhousedb_fdw і PostgreSQL.
Q#
Query Contains Aggregates and Group By
Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 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 ORDER BY c DESC LIMIT 10;
Q4
SELECT Carrier, count() FROM ontime WHERE DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count() DESC;
Q5
SELECT a.Carrier, c, c2, c1000/c2 c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year=2007 GROUP BY Carrier ) a INNER JOIN ( SELECT Carrier,count(*) AS c2 FROM ontime WHERE Year=2007 GROUP BY Carrier)b on a.Carrier=b. BY c3 DESC;
Q6
SELECT a.Carrier, c, c2, c1000/c2 c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Carrier) a INNER JOIN ( SELECT Carrier, count(*) AS c2 FROM ontime WHERE Year >= 2000 AND Year <= 2008 Carrier ) b па a.Carrier=b.Carrier ORDER BY c3 DESC;
Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier;
Q8
SELECT Year, avg(DepDelay) FROM ontime GROUP BY Year;
Q9
select Year, count(*) as c1 з ontime group by Year;
Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;
Q11
select avg(c1) ад (select Year,Month,count(*) as c1 ад ontime group by Year,Month) 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 з FROM ontime GROUP BY OriginCityName ORDER BY з DESC LIMIT 10;
Query Contains Joins
Q14
SELECT a.Year, c1/c2 FROM ( select Year, count()1000 як c1 ад ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (select Year, count(*) as c2 ад ontime GROUP BY Year ) b on a.Year=b.Year ORDER BY a.Year;
Q15
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”;
Table-1: Queries used in benchmark
Query executions
Вось вынікі кожнага з запытаў пры выкананні ў розных наладах базы дадзеных: PostgreSQL з індэксамі і без іх, уласны ClickHouse і clickhousedb_fdw. Час паказваецца ў мілісекундах.
Q#
PostgreSQL
PostgreSQL (Indexed)
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
Table-1: Time execute the queries used in benchmark
Прагляд вынікаў
Графік паказвае час выканання запыту ў мілісекундах, вось X паказвае нумар запыту з табліц вышэй, а вось Y паказвае час выканання ў мілісекундах. Вынікі ClickHouse і дадзеныя, атрыманыя з postgres з дапамогай clickhousedb_fdw, паказаны. З табліцы бачна, што існуе велізарная розніца паміж PostgreSQL і ClickHouse, але мінімальная розніца паміж ClickHouse і clickhousedb_fdw.
Гэты графік паказвае розніцу паміж ClickhouseDB і clickhousedb_fdw. У большасці запытаў накладныя выдаткі FDW не так вялікія і ці ледзь значныя, акрамя Q12. Гэты запыт уключае ў сябе аб'яднанні і прапанову ORDER BY. З-за прапановы ORDER BY GROUP/BY і ORDER BY не апускаюцца да ClickHouse.
У табліцы 2 мы бачым скачок часу ў запытах Q12 і Q13. Паўтаруся, гэта выклікана прапановай ORDER BY. Каб пацвердзіць гэта, я выканаў запыты Q-14 і Q-15 з прапановай ORDER BY і без яе. Без прапановы ORDER BY час завяршэння складае 259 мс, а з прапановай ORDER BY — 1364212. Для адладкі гэтага запыту я тлумачу абодва запыты, а тут прыведзены вынікі тлумачэння.
Q15: Without ORDER BY Clause
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: Query Without ORDER BY Clause
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: Query With ORDER BY Clause
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: Query Plan with ORDER BY Clause
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)Выснова
Вынікі гэтых эксперыментаў паказваюць, што ClickHouse прапануе сапраўды добрую прадукцыйнасць, а clickhousedb_fdw прапануе перавагі прадукцыйнасці ClickHouse з PostgreSQL. Хоць пры выкарыстанні clickhousedb_fdw ёсць некаторыя накладныя выдаткі, яны малаважныя і супастаўныя з прадукцыйнасцю, дасягнутай пры натуральным запуску ў базе дадзеных ClickHouse. Гэта таксама пацвярджае, што fdw у PostgreSQL забяспечвае выдатныя вынікі.
Тэлеграм чат па Clickhouse
Тэлеграм чат па PostgreSQL
Крыніца: habr.com