一、测试环境

服务器配置：PostgreSQL和MySQL分别部署在一台：亚马逊m5.4xlarge（CPU 8cores 16 Threads,内存 64G，存储gp3, 通用型SSD卷 3000IOPS ，125 MB/s 吞吐量）上。

软件版本：

PostgreSQL：PostgreSQL 12.7 on x86_64-koji-linux-gn

MySQL: percona 8.0.26-16

数据库参数配置：

PostgreSQL：shared_buffers 8192MB

MySQL: innodb_buffer_pool_size 8192MB

测试背景：PostgreSQL和MySQL采用默认的安装配置，只调整了内存参数，整个测试过程PostgreSQL没有任何优化行为。

二、测试数据

表的信息：

1张事实表：lineorder

4张维度表：customer，part，supplier，dates

表占用操作系统存储空间：19G

数据查询：11条标准SQL查询测试语句（统计查询、多表关联、sum、复杂条件、group by、order by等组合方式）。

具体SQL语句：附录1 TPC-H测试SQL。

三、测试结果

SQL	查询时间(S)（PostgreSQL）	查询时间(S)（MySQL）
Q1.1	6.79	80.19
Q1.2	6.45	78.95
Q1.3	6.44	78.88
Q2.1	11.33	磁盘耗尽，跑不出结果优化后：113.72
Q2.2	10.39	执行错误
Q2.3	8.65	执行错误
Q3.1	12.42	执行错误
Q3.2	9.2	执行错误
Q3.3	8.3	执行错误
Q4.1	12.5	执行错误
Q4.2	12.105	执行错误

MySQL错误信息：ERROR 3 (HY000): Error writing file '/kunlun2/data10/6010/tmp/MYfd=332'(OS errno 28-No space left on device)

分析及总结：通过对比同一SQL语句在PostgreSQL和MySQL执行计划，会发现MySQL的执行计划没有采用优的join次序及并行操作，导致性能差。

譬如Q2.1 MySQL执行计划：

执行计划分析：上述执行计划首先将几个维度表做join（dates和supplier和part),得到的结果再与事实表lineorder join,因而得出了一个超级大的中间结果集，数据量达到10的15次方的数量（查询计划第五行返回的 rows），终导致临时文件耗尽磁盘空间而未能完成查询。

优化方案：通过在SQL语句中强制指定表join次序：首先与part表join得到一个小的数据子集，然后再与supplier和dates join,逐步缩小范围，查询语句及查询计划的效果如下：

explain format=tree  selectsum(lo_revenue) as lo_revenue, d_year as year, p_brand from ((lineorderstraight_join part on lo_partkey = p_partkey) straight_join  supplier on  lo_suppkey = s_suppkey)  straight_join dates ON  lo_orderdate = d_datekey where p_category ='MFGR#12' and s_region = 'AMERICA' group by year, p_brand order by year,p_brand;

在MySQL上指定join次序，

同时开启动并行查询：set local innodb_parallel_read_threads=16

而PostgreSQL的执行计划：

PostgreSQL自动采用优的join次序，大大减少了后排序的数据量，并且启动了3个并行分割数据集，因此执行的性能比手动优化后的MySQL还快10倍。

结论：MySQL执行TPC-H的测试，需要手动优化查询语句，即使如此，性能任然远远低于PostgreSQL，而PostgreSQL默认的配置就可以达到相对好的性能。

四、附录：测试SQL及查询计划

Q1.1

select sum(lo_revenue) as revenue from lineorder 
join dates on lo_orderdate = d_datekey
where d_year  = 1993 and lo_discount between 1 and 3 and lo_quantity < 25;

PostgreSQL执行计划：

MySQL执行计划：

Q1.2

select sum(lo_revenue) as revenue   from lineorder
join dates on lo_orderdate = d_datekey
where d_yearmonthnum = 199401
and lo_discount between 4 and 6
and lo_quantity between 26 and 35;

PostgreSQL执行计划：

MySQL执行计划：

Q1.3

select sum(lo_revenue) as revenue   from lineorder
join dates on lo_orderdate = d_datekey
where d_weeknuminyear = 6 and year(d_datekey)  = 1994
and lo_discount between 5 and 7
and lo_quantity between 26 and 35;

PostgreSQL执行计划：

MySQL执行计划：

Q2.1

select sum(lo_revenue) as lo_revenue, d_year as year, p_brand
from lineorder
join dates on lo_orderdate = d_datekey
join part on lo_partkey = p_partkey
join supplier on lo_suppkey = s_suppkey
where p_category = 'MFGR#12' and s_region = 'AMERICA'
group by year, p_brand
order by year, p_brand;

PostgreSQL执行计划：

MySQL执行计划：

Q2.2

select sum(lo_revenue) as lo_revenue, year(d_datekey) as year, p_brand
from lineorder
join dates on lo_orderdate = d_datekey
join part on lo_partkey = p_partkey
join supplier on lo_suppkey = s_suppkey
where p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA'
group by year, p_brand
order by year, p_brand;

PostgreSQL执行计划：

MySQL执行计划：

Q2.3

select sum(lo_revenue) as lo_revenue, year(d_datekey) as year, p_brand
from lineorder
join dates on lo_orderdate = d_datekey
join part on lo_partkey = p_partkey
join supplier on lo_suppkey = s_suppkey
where p_brand = 'MFGR#2239' and s_region = 'EUROPE'
group by year, p_brand
order by year, p_brand;

PostgreSQL执行计划：

MySQL执行计划：

Q3.1

select c_nation, s_nation, year(d_datekey) as year, sum(lo_revenue) as lo_revenue
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
where c_region = 'ASIA' and s_region = 'ASIA' and year(d_datekey) between 1992 and 1997
group by c_nation, s_nation, year
order by year asc, lo_revenue desc;

PostgreSQL执行计划：

MySQL执行计划：

Q3.2

select c_city, s_city, year(d_datekey) as year, sum(lo_revenue) as lo_revenue
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
where c_nation = 'UNITED STATES' and s_nation = 'UNITED STATES'
and year(d_datekey) between 1992 and 1997
group by c_city, s_city, year
order by year asc, lo_revenue desc;

PostgreSQL执行计划：

MySQL执行计划：

Q3.3

select c_city, s_city, year(d_datekey) as year, sum(lo_revenue) as lo_revenue
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
where (c_city='UNITED KI1' or c_city='UNITED KI5')
and (s_city='UNITED KI1' or s_city='UNITED KI5')
and year(d_datekey) between 1992 and 1997
group by c_city, s_city, year
order by year asc, lo_revenue desc;

PostgreSQL执行计划：

MySQL执行计划：

Q4.1

select year(d_datekey) as year, c_nation, sum(lo_revenue) - sum(lo_supplycost) as profit
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
join part on lo_partkey = p_partkey
where c_region = 'AMERICA' and s_region = 'AMERICA' and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2')
group by year, c_nation
order by year, c_nation;

PostgreSQL执行计划：

MySQL执行计划：

Q4.2

select year(d_datekey) as year, s_nation, p_category, sum(lo_revenue) - sum(lo_supplycost) as profit
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
join part on lo_partkey = p_partkey
where c_region = 'AMERICA'and s_region = 'AMERICA'
and (year(d_datekey) = 1997 or year(d_datekey) = 1998)
and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2')
group by year, s_nation, p_category
order by year, s_nation, p_category;

PostgreSQL执行计划：

MySQL执行计划：

点击阅读原文

END

昆仑数据库是一个HTAP NewSQL分布式数据库管理系统，可以满足用户对海量关系数据的存储管理和利用的全方位需求。
应用开发者和DBA的使用昆仑数据库的体验与单机MySQL和单机PostgreSQL几乎完全相同，因为首先昆仑数据库支持PostgreSQL和MySQL双协议，支持标准SQL:2011的 DML 语法和功能以及PostgreSQL和MySQL对标准 SQL的扩展。同时，昆仑数据库集群支持水平弹性扩容，数据自动拆分，分布式事务处理和分布式查询处理，健壮的容错容灾能力，完善直观的监测分析告警能力，集群数据备份和恢复等常用的DBA 数据管理和操作。所有这些功能无需任何应用系统侧的编码工作，也无需DBA人工介入，不停服不影响业务正常运行。
昆仑数据库具备全面的OLAP 数据分析能力，通过了TPC-H和TPC-DS标准测试集，可以实时分析最新的业务数据，帮助用户发掘出数据的价值。昆仑数据库支持公有云和私有云环境的部署，可以与docker，k8s等云基础设施无缝协作，可以轻松搭建云数据库服务。
请访问 http://www.kunlunbase.com/ 获取更多信息并且下载昆仑数据库软件、文档和资料。
KunlunBase项目已开源
【GitHub：】
https://github.com/zettadb
【Gitee：】
https://gitee.com/zettadb

ITPub博客

PostgreSQL vs MySQL TPC-H 测试

一、测试环境

二、测试数据

三、测试结果

四、附录：测试SQL及查询计划

END

最新文章