PostgreSQL vs MySQL TPC-H 测试

一、测试环境

服务器配置：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。

三、测试结果

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;