mysql5.7.27单实例与tidb性能压测对比

[yulei7633]

没有对比就滑伤害，这种对比还是有一定的参考价值的，要从mysql迁移到tidb，总得对比一下性能。

[yulei7633]

经过测试，prepare阶段就忽略了，mysql单机插入的速度要比tidb快：
1、tidb运行命令，
sysbench oltp_read_write.lua --mysql-host=192.168.201.1 --mysql-port=4000 --mysql-db=sbtest --mysql-user=root --mysql-password=‘’ --table_size=50000 --tables=16 --time=600 --threads=16 --report-interval=10 --db-driver=mysql --db-ps-mode=auto --rand-type=uniform run
结果如下：
queries performed:
read: 880628
write: 250731
other: 126681
total: 1258040
transactions: 62902 (104.82 per sec.)
queries: 1258040 (2096.30 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

Throughput:
events/s (eps): 104.8151
time elapsed: 600.1236s
total number of events: 62902

Latency (ms):
min: 39.43
avg: 152.63
max: 628.93
95th percentile: 262.64
sum: 9600467.42

Threads fairness:
events (avg/stddev): 3931.3750/10.45
execution time (avg/stddev): 600.0292/0.03

mysql单机：
运行的命令：
sysbench oltp_read_write.lua --mysql-host=192.168.201.1 --mysql-port=3306 --mysql-db=sbtest --mysql-user=yanfa --mysql-password=12345678 --table_size=50000 --tables=16 --tables=16 --time=600 --threads=16 --report-interval=10 --db-driver=mysql --db-ps-mode=auto --rand-type=uniform run
运行的结果如下：
queries performed:
read: 2453668
write: 701048
other: 350524
total: 3505240
transactions: 175262 (291.95 per sec.)
queries: 3505240 (5838.98 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

Throughput:
events/s (eps): 291.9492
time elapsed: 600.3167s
total number of events: 175262

Latency (ms):
min: 5.73
avg: 54.78
max: 1927.62
95th percentile: 116.80
sum: 9600915.39

Threads fairness:
events (avg/stddev): 10953.8750/51.10
execution time (avg/stddev): 600.0572/0.14

从上面对比的结果来看：
tidb比mysql单机的性能要差，当然这是在数据量特别小的情况下对比，16个表，每个表5万条数据，后续再跟踪大数据量的情况下对比一下。

[yulei7633]

当数据量是16个表，每个表50万的时候：
tidb的表现如下：
SQL statistics:
queries performed:
read: 97972
write: 27730
other: 14258
total: 139960
transactions: 6998 (11.65 per sec.)
queries: 139960 (233.02 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

Throughput:
events/s (eps): 11.6512
time elapsed: 600.6240s
total number of events: 6998

Latency (ms):
min: 34.04
avg: 1372.71
max: 24201.79
95th percentile: 4437.27
sum: 9606254.39

Threads fairness:
events (avg/stddev): 437.3750/9.56
execution time (avg/stddev): 600.3909/0.16

mysql单实例的表现如下：
SQL statistics:
queries performed:
read: 149422
write: 42692
other: 21346
total: 213460
transactions: 10673 (17.76 per sec.)
queries: 213460 (355.19 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

Throughput:
events/s (eps): 17.7595
time elapsed: 600.9752s
total number of events: 10673

Latency (ms):
min: 181.05
avg: 900.04
max: 6295.18
95th percentile: 1648.20
sum: 9606173.21

Threads fairness:
events (avg/stddev): 667.0625/8.53
execution time (avg/stddev): 600.3858/0.24

可以看出，数据量在增加的时候，性能越来越近，当数据量再大些，tidb的优势就能体现出来

[yulei7633]

当数据量是16个表，每个表100万的时候：
SQL statistics:
queries performed:
read: 159418
write: 41445
other: 26877
total: 227740
transactions: 11387 (11.51 per sec.)
queries: 227740 (230.29 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

Throughput:
events/s (eps): 11.5144
time elapsed: 988.9384s
total number of events: 11387

Latency (ms):
min: 47.14
avg: 1388.70
max: 552689.29
95th percentile: 1304.21
sum: 15813179.84

Threads fairness:
events (avg/stddev): 711.6875/6.80
execution time (avg/stddev): 988.3237/0.34

mysql：
SQL statistics:
queries performed:
read: 181902
write: 51972
other: 25986
total: 259860
transactions: 12993 (21.63 per sec.)
queries: 259860 (432.58 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

Throughput:
events/s (eps): 21.6291
time elapsed: 600.7174s
total number of events: 12993

Latency (ms):
min: 185.32
avg: 739.27
max: 3630.90
95th percentile: 1149.76
sum: 9605309.97

Threads fairness:
events (avg/stddev): 812.0625/10.15
execution time (avg/stddev): 600.3319/0.24

数据量增加的时候，差距在缩小。目前这数据量还是很少。

[yulei7633]

是的，一台物理机，安装了vm，虚拟出10台虚拟机

[yulei7633]

可以，只有在数据量非常大的情况下，才能体现tidb的性能

[有猫万事足]

我说说我走过的路。
1年前，我给老板说可以考虑tidb换mysql。
当时老板自己随手装了一台mysql8，对比tidb的结果是，tps明显不如mysql，成本也大。给我否了。

1年后，mysql某日志表超过6000w，这样的表还有3个，分布在10+服上，查询怎么优化都难以接受。我用dm工具直接把10+个mysql库拖到tidb上做查询，相当于给原有的mysql整体做了个读写分离，查询快多了。现在老板把tidb替换mysql提上日程了。

没有那种东西是任何场景通用的解决方案。根据发展的需要选择就可以了。当时没这个数据量，我也不能说老板没有前瞻性。没这个数据量，确实没有必要付出额外的成本。现在有这个需要了，tidb突然就香了。

[yulei7633]

tidb在数据量很大的情况下，优势才能体现出来，我现在公司用的都是mysql异步主从，数据量大的惊人，现在最大的单表都有12T了，单实例上跑的数据量，普通都在2-6T左右。光维护数据库的实例，大概有500+实例，不敢轻易迁移到tidb，目前 对tidb也只是在皮毛阶段。还得加强学习

[yulei7633]

VMware vSphere Client 是用这个虚拟机，不是vm

[孤君888]

我们混合业务的项目我全都上了TIDB，维护轻松的很

[yulei7633]

目前还在学习阶段，以后有意向使用。

[yulei7633]

等掌握了tidb的相关技术，才能替换

[yulei7633]

能熟悉性能优化、备份、还原等