Performance Comparison of MySQL 5.7 and MySQL 8.0 under Various Workloads Using Sysbench

Background

Test MySQL 5.7 and MySQL 8.0 performance (TPS, QPS) under read‑write, read‑only, and write‑only modes with varying concurrency.

Prerequisites

MySQL versions: 5.7.22 and 8.0.15

Restart MySQL and clear OS cache before each test

Generate fresh test data for each run

Ensure identical configuration parameters for both versions

Environment

Machine details:

cat /etc/redhat-release | xargs echo '版本' && dmidecode -s system-product-name | xargs echo '是否虚拟化' && cat /proc/cpuinfo |grep "processor"|wc -l | xargs echo 'cpu核数'
版本 CentOS Linux release 7.5.1804 (Core)
是否虚拟化 KVM
cpu核数 4

MySQL 5.7.22 configuration:

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

MySQL 8.0.15 configuration (same values):

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 version:

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

Test Plan

Compare MySQL 5.7 and 8.0 under three workloads (oltp_read_write, oltp_read_only, oltp_write_only) with two persistence strategies (binlog/redo‑log durability).

Each test runs for 60 seconds, using 20 tables.

Execute tests in two configurations: "double‑1" (high safety) and "0‑2" (high performance).

Example command to show current durability settings:

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     |
+--------------------------------+-------+

Results

Double‑1 Configuration

Read‑write mode: MySQL 5.7.22 and 8.0.15 have similar TPS/QPS, but MySQL 8.0.15 shows performance jitter at 120 threads.

Read‑only mode: MySQL 5.7.22 outperforms MySQL 8.0.15 by roughly one‑third; increasing threads does not improve TPS/QPS and may cause decline.

Write‑only mode: MySQL 5.7.22 performs about 25 % better than MySQL 8.0.15 as concurrency rises.

0‑2 Configuration

Read‑write mode: At low concurrency MySQL 5.7.22 is faster; at higher concurrency MySQL 8.0.15 becomes faster, with performance dropping after 80 threads.

Read‑only mode: MySQL 5.7.22 is about one‑third faster; performance does not scale with threads and may decline.

Write‑only mode: MySQL 5.7.22 shows larger TPS jitter but its QPS is roughly one‑third higher than MySQL 8.0.15.

Conclusion

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

Increasing concurrency does not linearly improve performance; beyond a certain point it degrades.

Results are specific to the low‑memory settings used and should not be taken as absolute.

Notes

Sysbench must be run with --db-ps-mode=disable to avoid "max_prepared_stmt_count" errors under high concurrency.

FATAL: mysql_stmt_prepare() failed
FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)"

Below is the Bash script used for the benchmark (run with appropriate permissions):

#!/bin/bash
# Benchmark script for MySQL 5.7 and 8.0 read/write, read‑only, write‑only modes
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} <
/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