MySQL8.0与MySQL5.7 OLTP 性能测试对比

约定不等于承诺〃 2023-02-14 05:47 84阅读 0赞

MySQL8.0出来已经有很长时间了，目前最新版本是MySQL8.0.20 ，那么MySQL8.0到底比MySQL5.7 性能有多大的提升呢，今天就做了一组压力测试来实际测试一下。为了使结果更具有说服性，测试环境是完全一样，具体测试结果如下:

**测试内容**

*  测试mysql5.7和mysql8.0 分别在读写、只读、只写模式（（oltp\_read\_write，oltp\_read\_only，oltp\_write\_only））下不同并发时的性能（tps，qps）

**测试环境**

*  测试使用版本分别为mysql8.0.20和mysql5.7.30
 *  Sysbench测试前先重启mysql服务，并清空OS的cache(避免多次测试时命中缓存)
 *  每次进行测试都是新生成测试数据后再进行mysql8.0和mysql5.7的测试
 *  每次测试时保证mysql8.0和mysql5.7参数一致
 *  sysbench 测试数据: --table-size=2000000 --tables=20  --time=600

**系统环境**

*  操作系统都是CentOS Linux release 7.4.1708 (Core),内存32G
 *  主要配置参数都是：

innodb\_buffer\_pool\_size 8GB

innodb\_log\_buffer\_size  16M

innodb\_log\_file\_size    256M

innodb\_flush\_log\_at\_trx\_commit 1

sync\_binlog 1

binlog\_format   ROW

log\_bin ON

transaction\_isolation   REPEATABLE-READ

*  sysbench 版本:

\[root@cpe-172-100-1-35 jsunicom\]\# sysbench --version

sysbench 1.1.0

**测试数据**

1.  读写模式

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70 1][]

1.  只读模式

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70 3][]

1.  只写模式

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70 4][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70 5][]

测试下来发现MySQL8.0性能还没MySQL5.7 性能好，不知道其他的小伙伴测试结果是否有不一致的，欢迎沟通交流！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200601095446728.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200601095446771.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200601095446743.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200601095446735.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200601095446775.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200601095446770.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ==,size_16,color_FFFFFF,t_70