Performance Comparison Between TiDB and MySQL

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidb和mysql的性能对比

| username: tidb狂热爱好者

【TiDB Usage Environment】Production
【TiDB Version】7.2
【Reproduction Path】Operations performed that caused the issue
【Encountered Issue: Problem Phenomenon and Impact】
Huobi’s fee: Monthly fee of $6000

tph test: Data analysis test

tiup is checking updates for component bench …timeout(2s)!
Starting component bench: /var/root/.tiup/components/bench/v1.12.0/tiup-bench tpch --sf=1 run -P4001
[Current] Q1: 78.89s
[Current] Q2: 3.79s
[Current] Q3: 26.81s
[Current] Q4: 6.14s
[Current] Q5: 6.48s
[Current] Q6: 10.23s
[Current] Q7: 26.21s
[Current] Q8: 39.76s
[Current] Q9: 98.75s
[Current] Q10: 13.25s
[Current] Q11: 10.37s
[Current] Q12: 15.94s
[Current] Q13: 12.72s
[Current] Q14: 32.78s
[Current] Q15: 31.24s
[Current] Q16: 4.06s

MySQL cannot perform data analysis and relies on Redshift, unable to perform Q17 test.

[Current] Q3: 0.30s
[Current] Q4: 1.31s
[Current] Q5: 0.57s
[Current] Q6: 0.17s
[Current] Q7: 0.30s
[Current] Q8: 0.50s
[Current] Q9: 0.97s
[Current] Q1: 0.57s
[Current] Q10: 0.44s
[Current] Q11: 0.17s
[Current] Q12: 0.44s
[Current] Q13: 0.70s
[Current] Q14: 0.17s
[Current] Q15: 0.50s
[Current] Q16: 0.23s
[Current] Q17: 0.44s
[Current] Q18: 0.84s
[Current] Q2: 0.17s
[Current] Q21: 0.84s
[Current] Q22: 0.10s
[Current] Q3: 0.30s
[Current] Q4: 1.38s
[Current] Q5: 0.44s
[Current] Q6: 0.17s
[Current] Q7: 0.37s
[Current] Q8: 0.50s
[Current] Q9: 0.91s
[Current] Q1: 0.57s
[Current] Q10: 0.44s
[Current] Q11: 0.17s
[Current] Q12: 0.30s
[Current] Q13: 0.57s
[Current] Q14: 0.17s
[Current] Q15: 0.64s
[Current] Q16: 0.23s
[Current] Q17: 0.50s
[Current] Q19: 0.64s
[Current] Q2: 0.17s
[Current] Q20: 0.23s
[Current] Q21: 0.91s
[Current] Q22: 0.10s
[Current] Q3: 0.37s
[Current] Q4: 1.17s
[Current] Q5: 0.44s
[Current] Q6: 0.17s
[Current] Q7: 0.37s
[Current] Q8: 0.50s
[Current] Q9: 0.84s
[Current] Q1: 0.64s
[Current] Q10: 0.44s
[Current] Q11: 0.17s
[Current] Q12: 0.23s
[Current] Q13: 0.57s
[Current] Q14: 0.10s
[Current] Q15: 0.37s
[Current] Q16: 0.17s
[Current] Q17: 0.30s
[Current] Q18: 0.67s
[Current] Q19: 0.64s
[Current] Q2: 0.17s
[Current] Q20: 0.30s
[Current] Q21: 0.77s
[Current] Q22: 0.10s
[Current] Q3: 0.30s
[Current] Q4: 1.17s
[Current] Q5: 0.44s
[Current] Q6: 0.10s
[Current] Q7: 0.37s
[Current] Q8: 0.30s
[Current] Q9: 0.70s

TiDB’s data analysis performance is hundreds of times faster than MySQL, completing in just a few hundred milliseconds.

tpc test: Transaction system test

Starting component bench: /var/root/.tiup/components/bench/v1.12.0/tiup-bench ycsb run tidb -p tidb.instances=127.0.0.1:4000 -p operationcount=10000

***************** properties *****************

“operationcount”=“10000”

“dotransactions”=“true”

“command”=“run”

“tidb.instances”=“127.0.0.1:4000”


Run finished, takes 2.257779708s

READ - Takes(s): 2.3, Count: 9493, OPS: 4208.6, Avg(us): 216, Min(us): 140, Max(us): 2057, 99th(us): 415, 99.9th(us): 743, 99.99th(us): 1956

UPDATE - Takes(s): 2.3, Count: 507, OPS: 224.7, Avg(us): 370, Min(us): 256, Max(us): 1228, 99th(us): 722, 99.9th(us): 1036, 99.99th(us): 1228

Starting component bench: /var/root/.tiup/components/bench/v1.12.0/tiup-bench ycsb run mysql -p tidb.instances=127.0.0.1:4000 -p operationcount=10000

***************** properties *****************

“tidb.instances”=“127.0.0.1:4000”

“operationcount”=“10000”

“dotransactions”=“true”

“command”=“run”


Run finished, takes 2.301933666s

READ - Takes(s): 2.3, Count: 9476, OPS: 4118.7, Avg(us): 220, Min(us): 141, Max(us): 2051, 99th(us): 412, 99.9th(us): 908, 99.99th(us): 2041

UPDATE - Takes(s): 2.3, Count: 524, OPS: 227.8, Avg(us): 372, Min(us): 251, Max(us): 883, 99th(us): 717, 99.9th(us): 833, 99.99th(us): 883

tpc test: TiDB and MySQL single-node performance is almost identical.

Non-high availability solution: Cold disk 250m peak read

cpu instance hourly cost 20t cold hhd (1 month) total cost
pd c5.2xlarge 8 0.192 0
tidb c5.4xlarge 16 0.856 0
kv r5.4xlarge 16 1.096 30
monthly cost 1543.68 30 1573
BACKUP TABLE `test`.`sbtest01` TO 'local:///mnt/backup/sbtest01?access-key={YOUR_ACCESS_KEY}&secret-access-key={YOUR_SECRET_KEY}';
Unnecessary backups use s3, s3 costs are negligible
Restore data when needed, usually within 1 minute. Backup data recovery speed on TiKV storage nodes can reach 100 MiB/s. Backup data recovery speed on TiKV storage nodes can reach 100 MiB/s.
RESTORE TABLE `test`.`sbtest01`, `test`.`sbtest02` FROM 'local:///mnt/backup/2020/04/';
Run this command to restore table data
Backup data recovery speed on a single TiKV storage node can reach 100 MiB/s.
Generally, a table can be restored within a minute

Non-high availability solution: st1 disk 500m peak read

cpu instance hourly cost 20t st1 hhd (1 month) total cost
pd c5.2xlarge 8 0.192 0
tidb c5.4xlarge 16 0.856 0
kv r5.4xlarge 16 1.096 900
monthly cost 1543.68 900 2443

Non-high availability solution: ssd gp2 disk 500m peak read

cpu instance hourly cost 20t ssd (1 month) total cost
pd c5.2xlarge 8 0.192 0
tidb c5.4xlarge 16 0.856 0
kv r5.4xlarge 16 1.096 2000
monthly cost 1543.68 2000 3443

Non-high availability can also use s3 for backup to ensure data integrity.

High availability three replicas: Huobi’s solution is this

cpu instance hourly cost 20t cold hhd (1 month) total cost
pd c5.2xlarge 8 0.192 0
pd c5.2xlarge 8 0.192 0
pd c5.2xlarge 8 0.192 0
tidb c5.4xlarge 16 0.856 0
tidb c5.4xlarge 16 0.856 0
kv r5.4xlarge 16 1.096 300
kv r5.4xlarge 16 1.096 300
kv r5.4xlarge 16 1.096 300
monthly cost 4631.04 900 5531.04

High availability three replicas ssd solution

cpu instance hourly cost ssd total cost
pd c5.2xlarge 8 0.192 0
pd c5.2xlarge 8 0.192 0
pd c5.2xlarge 8 0.192 0
tidb c5.4xlarge 16 0.856 0
tidb c5.4xlarge 16 0.856 0
kv r5.4xlarge 16 1.096 2000
kv r5.4xlarge 16 1.096 2000
kv r5.4xlarge 16 1.096 2000
monthly cost 4631.04 6000 10631
| username: xfworld | Original post link

No conclusion? So many solutions…

| username: zhanggame1 | Original post link

What about the MySQL configuration?

| username: cassblanca | Original post link

Is there any comparability without configuration?

| username: redgame | Original post link

Customer letter is fine, haha.

| username: tidb狂热爱好者 | Original post link

The configuration is on the same computer.

| username: MrSylar | Original post link

Sure enough, a fanatic.

| username: cy6301567 | Original post link

Under the same configuration, TiDB’s superiority is definitely better than MySQL. There are so many test cases, just missing a summary, haha.

| username: 托马斯滑板鞋 | Original post link

Is there a specific version of MySQL? I’m wondering if TiDB can be used to replace MGR on a low-spec cluster (8c16g 16g32g) :upside_down_face:

| username: zhaokede | Original post link

What size of data was used for the analysis? Could you please provide some details?

| username: BraveChen | Original post link

Just put the summary for TPC-H.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.