TiDB TPC-H 100GB Performance Test Report -- TiDB v5.0 MPP mode vs. Greenplum 6.15.0 and Apache Spark 3.1.1

Test overview

This test aims at comparing the TPC-H 100GB performance of TiDB v5.0 in the MPP mode with that of Greenplum and Apache Spark, two mainstream analytics engines, in their latest versions. The test result shows that the performance of TiDB v5.0 in the MPP mode is two to three times faster than that of the other two solutions under TPC-H workload.

In v5.0, TiDB introduces the MPP mode for TiFlash, which significantly enhances TiDB's Hybrid Transactional and Analytical Processing (HTAP) capabilities. Test objects in this report are as follows:

• TiDB v5.0 columnar storage in the MPP mode
• Greenplum 6.15.0
• Apache Spark 3.1.1 + Parquet

Test environment

Hardware prerequisite

• Node count: 3
• CPU: Intel(R) Xeon(R) CPU E5-2630 v4 @ 2.20GHz, 40 cores
• Memory: 189 GB
• Disks: NVMe 3TB * 2

Software version

Parameter configuration

TiDB v5.0

For the v5.0 cluster, TiDB uses the default parameter configuration except for the following configuration items.

In the configuration file users.toml of TiFlash, configure max_memory_usage as follows:

[profiles.default]
max_memory_usage = 10000000000000

Set session variables with the following SQL statements:

set @@tidb_isolation_read_engines='tiflash';
set @@tidb_allow_mpp=1;
set @@tidb_mem_quota_query = 10 << 30;

All TPC-H test tables are replicated to TiFlash in columnar format, with no additional partitions or indexes.

Greenplum

Except for the initial 3 nodes, the Greenplum cluster is deployed using an additional master node. Each segment server contains 8 segments, which means 4 segments per NVMe SSD. So there are 24 segments in total. The storage format is append-only/column-oriented storage and partition keys are used as primary keys.

log_statement = all
gp_autostats_mode = none
statement_mem = 2048MB
gp_vmem_protect_limit = 16384

Apache Spark

The test of Apache Spark uses Apache Parquet as the storage format and stores the data on HDFS. The HDFS system consists of three nodes. Each node has two assigned NVMe SSD disks as the data disks. The Spark cluster is deployed in standalone mode, using NVMe SSD disks as the local directory of spark.local.dir to speed up the shuffle spill, with no additional partitions or indexes.

--driver-memory 20G
--total-executor-cores 120
--executor-cores 5
--executor-memory 15G

Test result

In the performance diagram above:

• Blue lines represent TiDB v5.0;
• Red lines represent Greenplum 6.15.0;
• Yellow lines represent Apache Spark 3.1.1.
• The y-axis represents the execution time of the query. The less the time is, the better the performance is.