How to Benchmark Your System (CPU, File IO, MySQL) with Sysbench

To Nha Notes | Sept. 30, 2022, 4:19 p.m.

Installing sysbench

sudo apt-get install sysbench

rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY*
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
amazon-linux-extras install epel
yum -y update
yum install -y mysql-community-client
yum install -y sysbench

CPU Benchmark

sysbench --test=cpu --cpu-max-prime=20000 run

File IO Benchmark

sysbench --test=fileio --file-total-size=150G prepare

sysbench --test=fileio --file-total-size=150G --file-test-mode=rndrw --init-rng=on --max-time=300 --max-requests=0 run

sysbench --test=fileio --file-total-size=150G cleanup

MySQL Benchmark

Use below bash script to measure MySQL performance

# -- load_test_db.sh --

#!/bin/bash

display_usage() { 
    echo "Load testing MySQL database with sysbench. SELECT/INSERT/UPDATE/DELETE DMLs are auto-generated."
    echo "Usage: $0 {rds_host} {rds_user} {rds_pass} {rds_db} {cmd}";
    echo "          cmd: prepare, run, cleanup";
    exit 1 
} 

if [ $# -lt 5 ]
then
  display_usage
fi

rds_host=$1
rds_user=$2
rds_pass=$3
rds_db=$4
cmd=$5

# Manually tuning below params to adjust DB workload
TABLES=3
TABLE_ROWS=1000000
THREADS=8
RPS=50
# Total execution time in seconds
TIME=60

prepare () {
    sysbench oltp_read_write \
    --db-driver=mysql \
    --mysql-host=$rds_host \
    --mysql-user=$rds_user \
    --mysql-password=$rds_pass \
    --mysql-db=$rds_db \
    --tables=$TABLES \
    --table-size=$TABLE_ROWS  \
    prepare
}

run () {
    sysbench oltp_read_write \
    --db-driver=mysql \
    --mysql-host=$rds_host \
    --mysql-user=$rds_user \
    --mysql-password=$rds_pass \
    --mysql-db=$rds_db \
    --tables=$TABLES \
    --table-size=$TABLE_ROWS  \
    --threads=$THREADS \
    --events=0 \
    --rate=$RPS \
    --time=$TIME \
    --report-interval=1 \
    run
}

cleanup () {
    sysbench oltp_read_write \
    --db-driver=mysql \
    --mysql-host=$rds_host \
    --mysql-user=$rds_user \
    --mysql-password=$rds_pass \
    --mysql-db=$rds_db \
    --tables=$TABLES \
    --table-size=$TABLE_ROWS  \
    cleanup
}

if [[ $cmd == "prepare" ]]; then
    prepare
elif [[ $cmd == "run" ]]; then
    run
elif [[ $cmd == "cleanup" ]]; then
    cleanup
fi

if [ $? -eq 0 ]; then
    result="Database load test success."
else
    result="Database load test failed."
fi

References

https://www.howtoforge.com/how-to-benchmark-your-system-cpu-file-io-mysql-with-sysbench

https://ittutorial.org/how-to-benchmark-performance-of-mysql-using-sysbench/