How Do MySQL 5.7 and 8.0 Compare Under Read/Write, Read‑Only, and Write‑Only Loads?

Background

Test the performance (TPS, QPS) of MySQL 5.7.22 and MySQL 8.0.15 under read‑write, read‑only, and write‑only modes.

Test Setup

Versions: MySQL 5.7.22 and MySQL 8.0.15

Before each run, restart MySQL and clear OS caches to avoid cache effects.

Generate fresh test data for each run.

Keep configuration parameters identical between the two versions.

Environment

Machine : CentOS Linux 7.5.1804 (Core), KVM virtualized, 4 CPU cores.

MySQL 5.7.22 configuration

5.7.22
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

MySQL 8.0.15 configuration

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

System platform

sysbench -V
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

Test Cases

Measure performance of MySQL 5.7 and 8.0 under different persistence strategies (sync_binlog, innodb_flush_log_at_trx_commit) and workloads (oltp_read_write, oltp_read_only, oltp_write_only).

Each sysbench run lasts 60 seconds with 20 tables.

Tests are performed in two configurations: “dual‑1” (high safety) and “0‑2” (high performance).

Parameter Matrix

Parameter

Value

Meaning

sync_binlog

0

Binlog flush handled by OS, better performance but risk of loss.

sync_binlog

1

Flush after each transaction, safest.

sync_binlog

n

Flush after every n transactions.

innodb_flush_log_at_trx_commit

0

Write redo log each second and flush.

innodb_flush_log_at_trx_commit

1

Write and flush redo log at each commit, safest.

innodb_flush_log_at_trx_commit

2

Write redo log at each commit, flush twice.

Dual‑1 Mode Results

Read‑write workload:

Under dual‑1 settings, MySQL 5.7.22 and MySQL 8.0.15 achieve similar TPS/QPS; MySQL 8.0.15 shows a performance drop at 120 threads.

Read‑only workload:

In dual‑1 read‑only mode, MySQL 5.7.22 outperforms MySQL 8.0.15 by roughly one‑third; increasing concurrency does not improve TPS/QPS and even declines.

Write‑only workload:

In dual‑1 write‑only mode, MySQL 5.7.22 performs about 25 % better than MySQL 8.0.15 as concurrency increases.

0‑2 Mode Results

Read‑write workload:

At low concurrency, MySQL 5.7.22 is faster; at higher concurrency, MySQL 8.0.15 overtakes, but performance degrades beyond 80 threads.

Read‑only workload:

In 0‑2 read‑only mode, MySQL 5.7.22 is about one‑third faster; higher concurrency does not improve performance.

Write‑only workload:

In 0‑2 write‑only mode, MySQL 5.7.22 reaches a higher TPS peak and its QPS is about one‑third better than MySQL 8.0.15.

Summary

Overall, MySQL 5.7.22 outperforms MySQL 8.0.15 in read‑write, read‑only, and write‑only workloads under the tested conditions.

Performance does not scale linearly with concurrency; it eventually declines.

The results were obtained on a low‑spec configuration and may not represent all scenarios.

Important Note

Sysbench must be run with --db-ps-mode=disable to disable prepared statements; otherwise, high‑thread runs may fail with errors such as “Can't create more than max_prepared_stmt_count statements”.

Test Script

cat sysbench_test_mysql5.7_8.0_tps_qps.sh
#!/bin/bash
# Used for sysbench testing of MySQL 5.7 and 8.0 in read‑write, read‑only, and write‑only modes
#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_last_comm() {
    if [ $1 -ne 0 ]; then
        red_echo $2
        exit 1
    fi
}

function restart_mysqld() {
    service mysqld$1 restart
    sleep 2
}

function purge_binlog() {
    port=$1
    mysql -u$user -p$passwd -P$port -h$host <<EOF
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_last_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
            done
        done
    done
}

main