TiDB TPC-H 100GB Performance Test Report -- TiDB v5.1 MPP mode vs. Greenplum 6.15.0 and Apache Spark 3.1.1
This test aims at comparing the TPC-H 100GB performance of TiDB v5.1 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.1 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.1 columnar storage in the MPP mode
- Greenplum 6.15.0
- Apache Spark 3.1.1 + Parquet
- Node count: 3
- CPU: Intel(R) Xeon(R) CPU E5-2630 v4 @ 2.20GHz, 40 cores
- Memory: 189 GB
- Disks: NVMe 3TB * 2
|Service type||Software version|
For the v5.1 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.
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
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
The following test results are the average data of three tests. All numbers are in seconds.
|Query ID||TiDB v5.1||Greenplum 6.15.0||Apache Spark 3.1.1 + Parquet|
In the performance diagram above:
- Blue lines represent TiDB v5.1;
- 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.