mysql vs_MySQL 5.7 vs 8.0，哪个性能更牛？网友吵开了锅！

阳光穿透心脏的1/2处 2023-01-12 04:18 95阅读 0赞

背景

前提

环境

测试

双1模式下

0 2 模式下

结论

背景

测试mysql5.7和mysql8.0 分别在读写、只读、只写模式下不同并发时的性能(tps，qps)

前提

测试使用版本为mysql5.7.22和mysql8.0.15

sysbench测试前先重启mysql服务，并清空os的cache(避免多次测试时命中缓存)

每次进行测试都是新生成测试数据后再进行mysql5.7和mysql8.0的测试

每次测试时保证mysql5.7和mysql8.0的配置参数一致

环境

myql5.7.22

5.7.22-log

innodb\_buffer\_pool\_size 128M

innodb\_log\_buffer\_size 64M

innodb\_log\_file\_size 48M

binlog\_format ROW

log\_bin ON

transaction\_isolation REPEATABLE-READ

复制代码

mysql8.0.15

8.0.15

innodb\_buffer\_pool\_size 128M

innodb\_log\_buffer\_size 64M

innodb\_log\_file\_size 48M

binlog\_format ROW

log\_bin ON

transaction\_isolation REPEATABLE-READ

复制代码

sysbench

sysbench -V

sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

复制代码

测试

在不同的持久化策略下(binlog, redo log持久化)mysql5.7和mysql8.0 在读写模式、只读模式、只写模式(oltp\_read\_write，oltp\_read\_only，oltp\_write\_only)下的性能表现

sysbench 测试时间为60s，测试的表数量为20

测试分别在双1模式(安全性)和0 2模式(高性能)下进行

![86d43929b031533a92d119a96d02fe6d.png][]

双1模式下

SHOW GLOBAL VARIABLES WHERE Variable\_name IN('sync\_binlog','innodb\_flush\_log\_at\_trx\_commit');

\+--------------------------------+-------+

| Variable\_name | Value |

\+--------------------------------+-------+

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

| sync\_binlog | 1 |

\+--------------------------------+-------+

复制代码

mysql5.7和mysql8.0 在读写模式下的表现

![6e09290e9ee8536594cc414a924ffe91.png][]

双1 配置，读写模式下，mysql5.7.22 和mysql8.0.15 tps 、qps 性能差不多，mysql8.0.15 在120 线程并发时，性能出现了下降抖动

mysql5.7和mysql8.0 在只读模式下的表现

![40dfca78d67a20d314b44e2203d80d74.png][]

双1 配置，只读模式下，mysql5.7.22 的tps、qps比mysql8.0.15 好1/3 左右；并发线程数增加后，tps、qps并没有随着增加，反而出现了下降的趋势

mysql5.7和mysql8.0 在只写模式下的表现

![935877bc4d30f454bb8476902f82bf08.png][]

双1 配置，只写模式下，随着并发数的上升，mysql5.7.22 的性能比mysql8.0.15 好1/4左右

0 2 模式下

SHOW GLOBAL VARIABLES WHERE Variable\_name IN('sync\_binlog','innodb\_flush\_log\_at\_trx\_commit');

\+--------------------------------+-------+

| Variable\_name | Value |

\+--------------------------------+-------+

| innodb\_flush\_log\_at\_trx\_commit | 2 |

| sync\_binlog | 0 |

\+--------------------------------+-------+

复制代码

mysql5.7和mysql8.0 在读写模式下的表现

![390413c93f322921cbc2791d0dbcc152.png][]

0 2配置，读写模式下，并发数低时，mysql5.7.22性能好于mysql8.0.15; 并发数比较高时，mysql8.0.15 性能好于mysql5.7.22；在80 线程的并发以上时，性能开始下降

mysql5.7和mysql8.0 在只读模式下的表现

![2fa8de9d5f8557f8bd2922b4bde45e93.png][]

0 2配置，只读模式下，mysql5.7.22性能比mysql8.0.15 好1/3左右；随着并发数的上升，性能也没有上升，反而有下降的趋势

mysql5.7和mysql8.0 在只写模式下的表现

![4f464d9a4efdeeeef1124b9350fd49a8.png][]

0 2 配置，只写模式下，mysql5.7.22的tps 抖动比较大；mysql5.7.22 的qps比mysql8.0.15好1/3左右

结论

整体来看，mysql5.7.22在读写模式、只读模式、只写模式下的表现是优于mysql8.0.15的

随着并行数的增加，性能表现不会也跟着增加，还会出现下降

本次测试结果是在配置很低的情况下进行的，不代表绝对

注意 sysbench 需要设置--db-ps-mode=disable 禁用预编译语句，不然并发测试线程多时会报下面的错误 FATAL: mysql\_stmt\_prepare() failed FATAL: MySQL error: 1461 "Can't create more than max\_prepared\_stmt\_count statements (current value: 16382)" FATAL: mysql\_stmt\_prepare() failed FATAL: MySQL error: 1461 "Can't create more than max\_prepared\_stmt\_count statements (current value: 16382)" FATAL: thread\_init' function failed: /usr/local/share/sysbench/oltp\_common.lua:288: SQL API error FATAL: mysql\_stmt\_prepare() failed FATAL: MySQL error: 1461 "Can't create more than max\_prepared\_stmt\_count statements (current value: 16382)" FATAL:thread\_init' function failed: /usr/local/share/sysbench/oltp\_common.lua:288: SQL API error FATAL: mysql\_stmt\_prepare() failed

使用脚本

cat sysbench\_test\_mysql5.7\_8.0\_tps\_qps.sh

\#!/bin/bash

\#用于sysbench 测试在读写模式、只读模式、只写模式下 mysql5.7和mysql8.0 的tps,qps

\#nohup bash $0 >/tmp/sysbench\_test 2>& 1 &

user=admin

passwd=admin

ports="8015 57222"

host=127.0.0.1

sysbench\_test\_mode="oltp\_read\_write oltp\_read\_only oltp\_write\_only"

sysbench\_test\_info\_path=/tmp/sysbench-test

function red\_echo () \{

local what="$\*"

echo -e "$(date +%F-%T) \\e\[1;31m $\{what\} \\e\[0m"

function check\_las\_comm()\{

if \[ $1 -ne 0 \];then

red\_echo $2

exit 1

function restart\_mysqld()\{

service mysqld$\{1\} restart

sleep 2

function purge\_binlog()\{

port=$1

mysql -u$user -p$passwd -P$port -h$host<

purge binary logs before now();

EOF

function clean\_os\_cache()\{

echo 3 > /proc/sys/vm/drop\_caches

function sysbench\_with\_diff\_thread()\{

thread\_num=$1

port=$2

order=$3

test\_mode=$4

sysbench /usr/local/share/sysbench/$\{test\_mode\}.lua --mysql\_storage\_engine=innodb --table-size=100000 --tables=20 --mysql-db=test\_1 --mysql-user=$user --mysql-password=$passwd --mysql-port=$port --mysql-host=$host --threads=$thread\_num --time=60 --report-interval=2 --db-ps-mode=disable --events=0 --db-driver=mysql $order

function main()\{

for test\_mode in $sysbench\_test\_mode;do

for port in $ports;do

for thread\_num in \{5,10,20,30,40,80,120,200\};do

restart\_mysqld "$port"

check\_las\_comm "$?" "restart mysqld$\{port\} failed "

clean\_os\_cache

purge\_binlog "$port"

red\_echo "sysbench $thread\_num threads cleanup mysqld$\{port\}"

sysbench\_with\_diff\_thread "$thread\_num" "$port" "cleanup" "$test\_mode">/dev/null

red\_echo "sysbench $thread\_num threads prepare mysqld$\{port\}"

sysbench\_with\_diff\_thread "$thread\_num" "$port" "prepare" "$test\_mode">/dev/null

mkdir -p $sysbench\_test\_info\_path

red\_echo "sysbench $thread\_num threads run mysqld$\{port\} $test\_mode"

sysbench\_with\_diff\_thread "$thread\_num" "$port" "run" "$test\_mode" > $sysbench\_test\_info\_path/$\{test\_mode\}\_$\{thread\_num\}\_$port

\# service mysqld\{port\} stop

done

main

复制代码

[86d43929b031533a92d119a96d02fe6d.png]: /images/20221119/2a295c507e474e3e96d51856b44220a0.png
[6e09290e9ee8536594cc414a924ffe91.png]: /images/20221119/b4c73439af624560b6bbfb4e9dd5e4c3.png
[40dfca78d67a20d314b44e2203d80d74.png]: /images/20221119/e64fad35c70b42b699944351d040fb0b.png
[935877bc4d30f454bb8476902f82bf08.png]: /images/20221119/75bba72528e249cebfe39f9b3bd19a9a.png
[390413c93f322921cbc2791d0dbcc152.png]: /images/20221119/6c4c05a3a75c45dc950895a132eccb4b.png
[2fa8de9d5f8557f8bd2922b4bde45e93.png]: /images/20221119/22494418673940eaa3126091fb97fa20.png
[4f464d9a4efdeeeef1124b9350fd49a8.png]: /images/20221119/2dd0b87ab2db411fb08facff6e72b2bc.png