Benchmark database performance with sysbench and fio integration

Update system packages

Start by updating your package manager to ensure you get the latest versions of dependencies.

sudo apt update && sudo apt upgrade -y
sudo apt install -y curl wget build-essential git

sudo dnf update -y
sudo dnf groupinstall -y "Development Tools"
sudo dnf install -y curl wget git

Install fio for disk I/O benchmarking

Install fio 3.37 from repositories or compile from source for the latest features.

sudo apt install -y fio
fio --version

sudo dnf install -y fio
fio --version

Install sysbench dependencies

Install required libraries for compiling sysbench with MySQL and PostgreSQL support.

sudo apt install -y libmysqlclient-dev libpq-dev libaio-dev pkg-config autotools-dev autoconf automake libtool

sudo dnf install -y mysql-devel postgresql-devel libaio-devel pkgconfig autotools automake libtool

Compile and install sysbench 1.0.20

Download and compile sysbench from source to get version 1.0.20 with all database drivers.

cd /tmp
wget https://github.com/akopytov/sysbench/archive/1.0.20.tar.gz
tar -xzf 1.0.20.tar.gz
cd sysbench-1.0.20
./autogen.sh
./configure --with-mysql --with-pgsql
make -j$(nproc)
sudo make install
sudo ldconfig

Verify sysbench installation

Check that sysbench is installed correctly with database support enabled.

sysbench --version
sysbench --help | grep -E "mysql|pgsql"

Install database systems for testing

Install MySQL and PostgreSQL servers for benchmarking. Skip if you're testing existing databases.

sudo apt install -y mysql-server postgresql postgresql-contrib
sudo systemctl enable --now mysql postgresql

sudo dnf install -y mysql-server postgresql-server postgresql-contrib
sudo systemctl enable --now mysqld postgresql
sudo postgresql-setup --initdb

Configure MySQL test database

Create a dedicated database and user for sysbench testing with proper permissions.

sudo mysql -e "CREATE DATABASE sysbenchdb;"
sudo mysql -e "CREATE USER 'sysbench'@'localhost' IDENTIFIED BY 'StrongPassword123!';"
sudo mysql -e "GRANT ALL PRIVILEGES ON sysbenchdb.* TO 'sysbench'@'localhost';"
sudo mysql -e "FLUSH PRIVILEGES;"

Configure PostgreSQL test database

Set up PostgreSQL database and user for sysbench testing.

sudo -u postgres createdb sysbenchdb
sudo -u postgres psql -c "CREATE USER sysbench WITH PASSWORD 'StrongPassword123!';"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE sysbenchdb TO sysbench;"
sudo -u postgres psql -c "ALTER USER sysbench CREATEDB;"

Configure PostgreSQL authentication

Update pg_hba.conf to allow password authentication for the sysbench user.

# Add this line before other local entries
local   sysbenchdb      sysbench                md5

sudo systemctl reload postgresql

Create directory for test data

Create a dedicated directory for benchmark data files with proper permissions.

sudo mkdir -p /var/lib/benchmark-data
sudo chown $(whoami):$(whoami) /var/lib/benchmark-data
cd /var/lib/benchmark-data

Run sequential read/write tests

Test sequential I/O performance to establish baseline storage throughput.

# Sequential write test
fio --name=seq-write --ioengine=libaio --iodepth=32 --rw=write --bs=64k --direct=1 --size=4G --numjobs=1 --runtime=300 --group_reporting --filename=/var/lib/benchmark-data/test-seq-write

Sequential read test
fio --name=seq-read --ioengine=libaio --iodepth=32 --rw=read --bs=64k --direct=1 --size=4G --numjobs=1 --runtime=300 --group_reporting --filename=/var/lib/benchmark-data/test-seq-read

Run random I/O tests

Test random I/O patterns that simulate database workloads more accurately.

# Random write IOPS test
fio --name=rand-write --ioengine=libaio --iodepth=16 --rw=randwrite --bs=4k --direct=1 --size=2G --numjobs=4 --runtime=300 --group_reporting --filename=/var/lib/benchmark-data/test-rand-write

Random read IOPS test
fio --name=rand-read --ioengine=libaio --iodepth=16 --rw=randread --bs=4k --direct=1 --size=2G --numjobs=4 --runtime=300 --group_reporting --filename=/var/lib/benchmark-data/test-rand-read

Run mixed workload test

Simulate realistic database I/O patterns with mixed read/write operations.

fio --name=mixed-rw --ioengine=libaio --iodepth=16 --rw=randrw --rwmixread=70 --bs=8k --direct=1 --size=2G --numjobs=4 --runtime=600 --group_reporting --filename=/var/lib/benchmark-data/test-mixed-rw

Prepare MySQL test data

Create test tables and populate with data for sysbench OLTP workloads.

sysbench oltp_read_write --table-size=1000000 --tables=8 --mysql-db=sysbenchdb --mysql-user=sysbench --mysql-password=StrongPassword123! --mysql-host=localhost prepare

Run MySQL OLTP benchmark

Execute comprehensive OLTP workload test against MySQL with various thread counts.

# Run with increasing thread counts
for threads in 1 4 8 16 32; do
    echo "Testing MySQL with $threads threads"
    sysbench oltp_read_write --table-size=1000000 --tables=8 --threads=$threads --time=300 --mysql-db=sysbenchdb --mysql-user=sysbench --mysql-password=StrongPassword123! --mysql-host=localhost run > mysql_benchmark_${threads}t.txt
    sleep 30
done

Run MySQL read-only benchmark

Test read-heavy workloads to measure query performance and caching effectiveness.

sysbench oltp_read_only --table-size=1000000 --tables=8 --threads=16 --time=300 --mysql-db=sysbenchdb --mysql-user=sysbench --mysql-password=StrongPassword123! --mysql-host=localhost run > mysql_readonly_benchmark.txt

Prepare PostgreSQL test data

Set up PostgreSQL test environment with the same data structure.

sysbench oltp_read_write --table-size=1000000 --tables=8 --pgsql-db=sysbenchdb --pgsql-user=sysbench --pgsql-password=StrongPassword123! --pgsql-host=localhost prepare

Run PostgreSQL OLTP benchmark

Execute the same workload against PostgreSQL for comparison.

for threads in 1 4 8 16 32; do
    echo "Testing PostgreSQL with $threads threads"
    sysbench oltp_read_write --table-size=1000000 --tables=8 --threads=$threads --time=300 --pgsql-db=sysbenchdb --pgsql-user=sysbench --pgsql-password=StrongPassword123! --pgsql-host=localhost run > pgsql_benchmark_${threads}t.txt
    sleep 30
done

Memory performance test

Test memory allocation and access patterns to identify memory bottlenecks.

sysbench memory --memory-block-size=1M --memory-total-size=10G --threads=4 run > memory_benchmark.txt

CPU performance test

Benchmark CPU performance to establish system computational capacity.

sysbench cpu --cpu-max-prime=20000 --threads=$(nproc) run > cpu_benchmark.txt

Custom Lua script benchmark

Create custom workload patterns using sysbench Lua scripting capabilities.

#!/usr/bin/env sysbench

function thread_init()
   drv = sysbench.sql.driver()
   con = drv:connect()
end

function event()
   local table_id = sysbench.rand.uniform(1, sysbench.opt.tables)
   local id = sysbench.rand.uniform(1, sysbench.opt.table_size)
   
   con:query("SELECT c FROM sbtest" .. table_id .. " WHERE id=" .. id)
   con:query("SELECT c FROM sbtest" .. table_id .. " WHERE id BETWEEN " .. id .. " AND " .. (id + 100))
   con:query("UPDATE sbtest" .. table_id .. " SET k=k+1 WHERE id=" .. id)
end

function thread_done()
   con:disconnect()
end

sysbench /var/lib/benchmark-data/custom_workload.lua --table-size=1000000 --tables=8 --threads=16 --time=300 --mysql-db=sysbenchdb --mysql-user=sysbench --mysql-password=StrongPassword123! --mysql-host=localhost run > custom_workload_benchmark.txt

Extract key I/O metrics

Parse fio results to extract important performance indicators.

# Create analysis script
cat > analyze_fio.sh << 'EOF'
#!/bin/bash
echo "=== Disk I/O Performance Summary ==="
echo "Sequential Write Throughput:"
grep -A 5 "WRITE:" fio_seq_write.log | grep "bw="
echo "Sequential Read Throughput:"
grep -A 5 "READ:" fio_seq_read.log | grep "bw="
echo "Random Write IOPS:"
grep -A 5 "WRITE:" fio_rand_write.log | grep "iops="
echo "Random Read IOPS:"
grep -A 5 "READ:" fio_rand_read.log | grep "iops="
EOF
chmod +x analyze_fio.sh

Extract database performance metrics

Parse sysbench results to compare database performance across configurations.

cat > analyze_db.sh << 'EOF'
#!/bin/bash
echo "=== Database Performance Summary ==="
for file in mysql_benchmark_*.txt; do
    threads=$(echo $file | grep -o '[0-9]*t' | tr -d 't')
    tps=$(grep "transactions:" $file | awk '{print $3}' | tr -d '(')
    latency=$(grep "95th percentile:" $file | awk '{print $3}')
    echo "MySQL $threads threads: $tps TPS, 95th percentile: $latency ms"
done

for file in pgsql_benchmark_*.txt; do
    threads=$(echo $file | grep -o '[0-9]*t' | tr -d 't')
    tps=$(grep "transactions:" $file | awk '{print $3}' | tr -d '(')
    latency=$(grep "95th percentile:" $file | awk '{print $3}')
    echo "PostgreSQL $threads threads: $tps TPS, 95th percentile: $latency ms"
done
EOF
chmod +x analyze_db.sh
./analyze_db.sh

Generate comprehensive report

Create detailed performance report combining I/O and database metrics.

cat > generate_report.sh << 'EOF'
#!/bin/bash
REPORT_FILE="performance_report_$(date +%Y%m%d_%H%M%S).txt"

echo "Database Performance Benchmark Report" > $REPORT_FILE
echo "Generated on: $(date)" >> $REPORT_FILE
echo "========================================" >> $REPORT_FILE
echo "" >> $REPORT_FILE

echo "System Information:" >> $REPORT_FILE
echo "CPU: $(lscpu | grep 'Model name' | awk -F: '{print $2}' | sed 's/^[ \t]*//')" >> $REPORT_FILE
echo "Memory: $(free -h | grep '^Mem:' | awk '{print $2}')" >> $REPORT_FILE
echo "Storage: $(df -h / | tail -1 | awk '{print $2, $4}')" >> $REPORT_FILE
echo "" >> $REPORT_FILE

./analyze_fio.sh >> $REPORT_FILE 2>/dev/null || echo "FIO results not available" >> $REPORT_FILE
echo "" >> $REPORT_FILE

./analyze_db.sh >> $REPORT_FILE

echo "Report saved to: $REPORT_FILE"
EOF
chmod +x generate_report.sh
./generate_report.sh

Identify performance bottlenecks

Compare results to identify whether limitations are I/O, CPU, memory, or database configuration related.

# Check I/O wait time during benchmarks
iostat -x 1 5

Monitor memory usage patterns
free -h
cat /proc/meminfo | grep -E "MemAvailable|Buffers|Cached"

Check database-specific metrics
mysqladmin -u sysbench -pStrongPassword123! extended-status | grep -E "Innodb_buffer_pool|Query_cache"
psql -U sysbench -d sysbenchdb -c "SELECT * FROM pg_stat_database WHERE datname='sysbenchdb';"

Apply MySQL optimizations

Configure MySQL parameters based on benchmark findings. This configuration works for systems with 8GB+ RAM.

[mysqld]
Buffer pool size (70-80% of available RAM)
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4

I/O optimizations
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000

Connection and thread settings
max_connections = 200
thread_cache_size = 50
table_open_cache = 4000

Query cache (if read-heavy workload)
query_cache_type = 1
query_cache_size = 256M

Binary logging for replication
log_bin = /var/log/mysql/mysql-bin.log
binlog_cache_size = 1M
max_binlog_cache_size = 128M

sudo systemctl restart mysql

Apply PostgreSQL optimizations

Configure PostgreSQL for better performance based on benchmark results.

# Memory settings
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 64MB
maintenance_work_mem = 512MB

Checkpoint and WAL settings
wal_buffers = 64MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min
max_wal_size = 4GB
min_wal_size = 1GB

Connection settings
max_connections = 200

Query planner
random_page_cost = 1.1
effective_io_concurrency = 200

Logging for analysis
log_min_duration_statement = 1000
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on

sudo systemctl restart postgresql