主持人 > 博客 > 管理 > 测试 PostgreSQL、ClickHouse 和 clickhousedb_fdw (PostgreSQL) 中分析查询的性能

测试 PostgreSQL、ClickHouse 和 clickhousedb_fdw (PostgreSQL) 中分析查询的性能

在这项研究中，我想看看通过使用 ClickHouse 数据源而不是 PostgreSQL 可以实现哪些性能改进。 我知道使用 ClickHouse 可以提高工作效率。 如果我使用外部数据包装器 (FDW) 从 PostgreSQL 访问 ClickHouse，这些好处还会继续吗？

研究的数据库环境是PostgreSQL v11、clickhousedb_fdw和ClickHouse数据库。 最终，从 PostgreSQL v11 开始，我们将运行通过 clickhousedb_fdw 路由到 ClickHouse 数据库的各种 SQL 查询。 然后，我们将了解 FDW 的性能与在本机 PostgreSQL 和本机 ClickHouse 中运行的相同查询相比如何。

Clickhouse数据库

ClickHouse 是一种开源列式数据库管理系统，其性能比传统数据库方法快 100-1000 倍，能够在不到一秒的时间内处理超过 XNUMX 亿行。

Clickhousedb_fdw

clickhousedb_fdw -ClickHouse 数据库的外部数据包装器（FDW）是 Percona 的一个开源项目。 这是该项目的 GitHub 存储库的链接.

三月份，我写了一篇博客，向您介绍更多关于我们的外籍女佣的信息.

正如您将看到的，这为 ClickHouse 提供了一个 FDW，允许从 PostgreSQL v11 服务器中对 ClickHouse 数据库进行 SELECT 和 INSERT INTO。

FDW 支持聚合和连接等高级功能。 通过使用远程服务器的资源来执行这些资源密集型操作，可以显着提高性能。

基准环境

  • 超微服务器：
    • 英特尔® 至强® CPU E5-2683 v3 @ 2.00GHz
    • 2 个插槽/28 核/56 线程
    • 内存：256GB的RAM
    • 存储：三星 SM863 1.9TB 企业级 SSD
    • 文件系统：ext4/xfs
  • 操作系统：Linux sblade01 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
选择运营商，计数（) 从准时开始，其中 DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count() 降序；

Q5
SELECT a.Carrier, c, c2, c1000/c2 ​​as c3 FROM ( 选择运营商, 计数() 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.Carrier ORDER按 c3 描述；

Q6
SELECT a.Carrier, c, c2, c1000/c2 ​​as c3 FROM ( 选择运营商, 计数() 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 GROUP BY承运人 ) b on 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;

查询包含连接

Q14
SELECT a.Year, c1/c2 FROM ( 选择 Year, count()1000 as c1 from ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (选择 Year, count(*) as c2 from ontime GROUP BY Year ) b on a.Year=b.Year ORDER BY a.Year;

Q15
SELECT a.“年份”, c1/c2 FROM ( 选择“年份”, count()1000 as c1 FROM fonttime WHERE “DepDelay”>10 GROUP BY “Year”) a INNER JOIN（选择“Year”，count(*) as c2 FROM fonttime GROUP BY “Year”）b on 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 结果和使用 clickhousedb_fdw 从 postgres 检索的数据。 从表中可以看出，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 子句引起的。 为了确认这一点，我运行了带或不带 ORDER BY 子句的查询 Q-14 和 Q-15。 如果没有 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 提供了 PostgreSQL 中 ClickHouse 的性能优势。 虽然使用 clickhousedb_fdw 时会产生一些开销，但它可以忽略不计，并且与在 ClickHouse 数据库上本地运行所获得的性能相当。 这也证实了 PostgreSQL 中的 fdw 提供了出色的结果。

通过 Clickhouse 进行 Telegram 聊天 https://t.me/clickhouse_ru
使用 PostgreSQL 进行电报聊天 https://t.me/pgsql

来源： habr.com

添加评论