У цьому дослідженні я хотів подивитися, які покращення продуктивності можна отримати, використовуючи джерело даних 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
Контрольні тести
Замість того, щоб використовувати якийсь набір даних, згенерований машиною, для цього тесту ми використовували дані «Продуктивність за часом, що повідомляється про час роботи оператора» з 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 as 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 as 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 from 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) from (select Year,Month,count(*) as c1 from 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 c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;
Query Contains Joins
Q14
SELECT a.Year, c1/c2 FROM ( select Year, count()1000 as c1 from ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (select Year, count(*) as c2 from 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)
Натисніть Будинок
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 taken to 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: Без 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