ProHoster > Блог > Администриране > Тестване на производителността на аналитични заявки в PostgreSQL, ClickHouse и clickhousedb_fdw (PostgreSQL)

Тестване на производителността на аналитични заявки в PostgreSQL, ClickHouse и clickhousedb_fdw (PostgreSQL)

В това проучване исках да разгледам какви подобрения в производителността можем да постигнем, използвайки данни от ClickHouse вместо PostgreSQL. Знам какви предимства в производителността получавам от ClickHouse. Ще запазят ли тези предимства, ако получа достъп до ClickHouse от PostgreSQL с помощта на външна обвивка за данни (FDW)?

Изучаваните среди за бази данни са PostgreSQL v11, clickhousedb_fdw и базата данни ClickHouse. В крайна сметка ще изпълняваме различни SQL заявки от PostgreSQL v11, маршрутизирани през нашия clickhousedb_fdw до базата данни ClickHouse. След това ще видим как производителността на FDW се сравнява с идентични заявки, изпълнявани в нативен PostgreSQL и нативен ClickHouse.

База данни Clickhouse

ClickHouse е система за управление на бази данни с отворен код, основана на колони, която може да достигне производителност от 100 до 1000 пъти по-бързо от традиционните подходи към бази данни, способна да обработва повече от милиард реда за по-малко от секунда.

Clickhousedb_fdw

clickhousedb_fdw е обвивка за външни данни на базата данни ClickHouse, или FDW, продуктов проект с отворен код от Percona. Ето линк към репозитория на проекта GitHub.

През март написах блог, който ви разказва повече за нашия FDW.

Както ще видите, това осигурява FDW за ClickHouse, което позволява SELECT from и INSERT INTO базата данни ClickHouse от сървър PostgreSQL v11.

FDW поддържа разширени функции като агрегации и обединения. Това значително увеличава производителността, използвайки ресурсите на отдалечения сървър за тези ресурсоемки операции.

Бенчмарк среда

  • Supermicro сървър:
    • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
    • 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
ИЗБЕРИ ДенСедмица, брой(*) КАТО c ОТ ontime КЪДЕ DepDelay>10 И Година >= 2000 И Година <= 2008 ГРУПИРАЙ ПО ДенСедмица НАРЕДИ ПО c НОВА;

Q3
ИЗБЕРИ Произход, брой(*) КАТО c ОТ ontime КЪДЕ DepDelay>10 И Година >= 2000 И Година <= 2008 ГРУПИРАЙ ПО Произход НАРЕДИ ПО c НОВА ОГРАНИЧИ 10;

Q4
ИЗБЕРИ Превозвач, брой() ОТ ontime КЪДЕ DepDelay>10 И Година = 2007 ГРУПИРАЙ ПО Превозвач НАРЕДИ ПО брой() НОВА;

Q5
ИЗБЕРИ a.Превозвач, c, c2, c1000/c2 КАТО c3 ОТ ( ИЗБЕРИ Превозвач, брой() КАТО c ОТ ontime КЪДЕ DepDelay>10 И Година=2007 ГРУПИРАЙ ПО Превозвач ) a ВНУТРИ СЪЕДИНИ ( ИЗБЕРИ Превозвач, брой(*) КАТО c2 ОТ ontime КЪДЕ Година=2007 ГРУПИРАЙ ПО Превозвач) b на a.Превозвач=b.Превозвач НАРЕДИ ПО c3 НОВА;

Q6
ИЗБЕРИ a.Превозвач, c, c2, c1000/c2 КАТО c3 ОТ ( ИЗБЕРИ Превозвач, брой() КАТО c ОТ ontime КЪДЕ DepDelay>10 И Година >= 2000 И Година = 2000 И Година <= 2008 ГРУПИРАЙ ПО Превозвач ) b на a.Превозвач=b.Превозвач НАРЕДИ ПО c3 НОВА;

Q7
ИЗБЕРИ Превозвач, avg(DepDelay) * 1000 КАТО c3 ОТ ontime КЪДЕ Година >= 2000 И Година <= 2008 ГРУПИРАЙ ПО Превозвач;

Q8
ИЗБЕРИ Година, avg(DepDelay) ОТ ontime ГРУПИРАЙ ПО Година;

Q9
избери Година, брой(*) КАТО c1 ОТ ontime ГРУПИРАЙ ПО Година;

Q10
ИЗБЕРИ avg(cnt) ОТ (ИЗБЕРИ Година,Месец,брой(*) КАТО cnt ОТ ontime КЪДЕ DepDel15=1 ГРУПИРАЙ ПО Година,Месец) a;

Q11
избери avg(c1) от (избери Година,Месец,брой(*) КАТО c1 от ontime групирай по Година,Месец) a;

Q12
ИЗБЕРИ ИмеНаГрадПроизход, ИмеНаГрадДестинация, брой(*) КАТО c ОТ ontime ГРУПИРАЙ ПО ИмеНаГрадПроизход, ИмеНаГрадДестинация НАРЕДИ ПО c НОВА ОГРАНИЧИ 10;

Q13
ИЗБЕРИ ИмеНаГрадПроизход, брой(*) КАТО c ОТ ontime ГРУПИРАЙ ПО ИмеНаГрадПроизход НАРЕДИ ПО c НОВА ОГРАНИЧИ 10;

Запитването Съдържа Съединения

Q14
ИЗБЕРИ a.Година, c1/c2 ОТ ( избери Година, брой()1000 КАТО c1 от ontime КЪДЕ DepDelay>10 ГРУПИРАЙ ПО Година) a ВНУТРИ СЪЕДИНИ (избери Година, брой(*) КАТО c2 от ontime GROUP BY Година ) b на a.Година=b.Година НАРЕДИ ПО a.Година;

Q15
ИЗБЕРИ a.”Година”, c1/c2 ОТ ( избери “Година”, брой()1000 КАТО c1 ОТ fontime КЪДЕ “DepDelay”>10 ГРУПИРАЙ ПО “Година”) a ВНУТРИ СЪЕДИНИ (избери “Година”, брой(*) КАТО c2 ОТ fontime ГРУПИРАЙ ПО “Година” ) b на a.”Година”=b.”Година”;

Таблица-1: Запитвания, използвани в бенчмарка

Изпълнения на запитванията

Ето резултатите от всяко запитване при изпълнение в различни настройки на базата данни: PostgreSQL с индекси и без тях, собствен ClickHouse и clickhousedb_fdw. Времето се показва в милисекунди.

Q#
PostgreSQL
PostgreSQL (Индексиран)
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

Таблица-1: Времето, необходимо за изпълнение на запитванията, използвани в бенчмарка

Преглед на резултатите

Графикът показва времето за изпълнение на запитването в милисекунди, оста X показва номера на запитването от таблиците по-горе, а оста Y показва времето за изпълнение в милисекунди. Резултатите от ClickHouse и данните, получени от postgres чрез clickhousedb_fdw, са показани. От таблицата е видно, че съществува огромна разлика между PostgreSQL и ClickHouse, но минимална разлика между ClickHouse и clickhousedb_fdw.

Тестване на производителността на аналитични заявки в PostgreSQL, ClickHouse и clickhousedb_fdw (PostgreSQL)

Тази графика показва разликата между 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

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 осигурява забележителни резултати.

Телеграм чат за Clickhouse https://t.me/clickhouse_ru
Телеграм чат за PostgreSQL https://t.me/pgsql

Източник: habr.com

Купете надежден хостинг за сайтове със защита от DDoS, VPS и VDS сървъри 🔥 Купете надежден хостинг за сайтове със защита от DDoS, VPS и VDS сървъри | ProHoster