В това проучване исках да видя какви подобрения на производителността могат да бъдат постигнати чрез използване на източник на данни 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 от и INSERT INTO базата данни на ClickHouse от сървъра PostgreSQL v11.
FDW поддържа разширени функции като агрегиране и присъединяване. Това значително подобрява производителността чрез използване на ресурсите на отдалечения сървър за тези ресурсоемки операции.
Бенчмарк среда
- Supermicro сървър:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00 GHz
- 2 гнезда / 28 ядра / 56 нишки
- Памет: 256GB на RAM
- Съхранение: Samsung SM863 1.9TB Enterprise SSD
- Файлова система: ext4/xfs
- ОС: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: версия 11
Бенчмарк тестове
Вместо да използваме набор от машинно генерирани данни за този тест, ние използвахме данните за „Продуктивност по време, отчетено време на оператора“ от 1987 до 2018 г. Можете да получите достъп до данните .
Размерът на базата данни е 85 GB, осигурявайки една таблица от 109 колони.
Сравнителни заявки
Ето заявките, които използвах, за да сравня ClickHouse, clickhousedb_fdw и PostgreSQL.
Q#
Заявката съдържа агрегати и групиране по
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
ИЗБЕРЕТЕ 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 на a.Carrier=b.Carrier ORDER ПО c3 DESC;
Q6
ИЗБЕРЕТЕ 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 BY 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
ИЗБЕРЕТЕ Година, ср.(DepDelay) ОТ ontime ГРУПИРАНЕ ПО Година;
Q9
изберете Year, count(*) като c1 от ontime група по Year;
Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;
Q11
изберете avg(c1) от (изберете 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;
Заявката съдържа съединения
Q14
ИЗБЕРЕТЕ a.Year, c1/c2 FROM ( изберете Year, count()1000 като c1 от ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (изберете Year, count(*) като c2 от ontime GROUP BY Year ) b on a.Year=b.Year ORDER BY a.Year;
Q15
ИЗБЕРЕТЕ a.”Year”, c1/c2 FROM ( изберете “Year”, count()1000 като c1 FROM fonttime WHERE “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (изберете “Year”, count(*) като c2 FROM fontime GROUP BY “Year” ) b на a.”Year”=b. "Година";
Таблица-1: Заявки, използвани в бенчмарк
Изпълнения на заявки
Ето резултатите от всяка от заявките, когато се изпълняват в различни настройки на базата данни: PostgreSQL със и без индекси, собствен ClickHouse и clickhousedb_fdw. Времето се показва в милисекунди.
Q#
PostgreSQL
PostgreSQL (индексиран)
Щракнете върху Къща
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: Време, необходимо за изпълнение на заявките, използвани в бенчмарка
Вижте резултатите
Графиката показва времето за изпълнение на заявката в милисекунди, оста 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 времето за завършване е 259ms, а с клаузата ORDER BY е 1364212. За отстраняване на грешки в тази заявка обяснявам и двете заявки и ето резултатите от обяснението.
Q15: Без клауза ORDER BY
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: Запитване без клауза ORDER BY
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: Запитване с клауза ORDER BY
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: План за заявка с клауза ORDER BY
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 предоставя отлични резултати.
Чат в Telegram чрез Clickhouse
Чат в Telegram с помощта на PostgreSQL
Източник: www.habr.com