Docker MySQL Stress test
Task: Create environment to run series of read/write queries to real MySQL server.
Before make real experiments I tried to implement this tests in the Docker containers. As a test tool I use sysbench.
Environment:
docker-compose.yml
version: '3'
services:
mysqldb:
image: mysql:5.7.22
restart: always
command: mysqld --sql_mode=""
environment:
- MYSQL_DATABASE=testdb
- MYSQL_ROOT_PASSWORD=root
- MYSQL_USER=testuser
- MYSQL_PASSWORD=testuser
ports:
- "8989:3306"
volumes:
- "./data/mysqldb:/var/lib/mysql"
stresstest:
depends_on:
- mysqldb
build:
context: .
dockerfile: Dockerfile
volumes:
- ./z:/home/ubuntu/z
Dockerfile
FROM ubuntu:18.04 RUN apt-get update -y RUN apt-get install -y sysbench mc mysql-client WORKDIR / RUN mkdir /home/ubuntu RUN mkdir /home/ubuntu/z WORKDIR /home/ubuntu/z
Command to build docker environment:
docker-compose up -d --force-recreate --build
Test script (test.sh):
#!/bin/bash -xv
prepare
sysbench --mysql-host=mysqldb --mysql-db=testdb --mysql-user=testuser \
--db-driver=mysql --mysql-password=testuser \
--mysql-port=3306 /usr/share/sysbench/$1 prepare
run tests
sysbench --mysql-host=mysqldb --mysql-db=testdb --mysql-user=testuser \
--max-requests=0 --db-driver=mysql --mysql-password=testuser \
--time=15 --report-interval=5 \
--mysql-port=3306 --threads=128 /usr/share/sysbench/$1 run
cleanup
sysbench --mysql-host=mysqldb --mysql-db=testdb --mysql-user=testuser \
--db-driver=mysql --mysql-password=testuser \
--mysql-port=3306 /usr/share/sysbench/$1 cleanup
List of sysbench tests:
bulk_insert.lua oltp_insert.lua oltp_read_write.lua oltp_write_only.lua oltp_point_select.lua oltp_update_index.lua select_random_points.lua oltp_delete.lua oltp_read_only.lua oltp_update_non_index.lua select_random_ranges.lua
How to run test script with parameter:
./test.sh oltp_write_only.lua ./test.sh oltp_delete.lua ...
Result working log:
root@637de3326558:/home/ubuntu/z# ./test.sh oltp_read_only.lua
#!/bin/bash -xv
#prepare
sysbench --mysql-host=mysqldb --mysql-db=testdb --mysql-user=testuser \
--max-requests=0 --db-driver=mysql --mysql-password=testuser \
--time=60 --report-interval=10 \
--db-ps-mode=disable \
--mysql-port=3306 --threads=128 /usr/share/sysbench/$1 prepare
+ sysbench --mysql-host=mysqldb --mysql-db=testdb --mysql-user=testuser --max-requests=0 --db-driver=mysql --mysql-password=testuser --time=60 --report-interval=10 --db-ps-mode=disable --mysql-port=3306 --threads=128 /usr/share/sysbench/oltp_read_only.lua prepare
sysbench 1.0.11 (using system LuaJIT 2.1.0-beta3)
Initializing worker threads...
Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
#run tests
sysbench --mysql-host=mysqldb --mysql-db=testdb --mysql-user=testuser \
--max-requests=0 --db-driver=mysql --mysql-password=testuser \
--time=60 --report-interval=10 \
--db-ps-mode=disable \
--mysql-port=3306 --threads=128 /usr/share/sysbench/$1 run
+ sysbench --mysql-host=mysqldb --mysql-db=testdb --mysql-user=testuser --max-requests=0 --db-driver=mysql --mysql-password=testuser --time=60 --report-interval=10 --db-ps-mode=disable --mysql-port=3306 --threads=128 /usr/share/sysbench/oltp_read_only.lua run
sysbench 1.0.11 (using system LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 128
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 128 tps: 332.81 qps: 5439.29 (r/w/o: 4761.28/0.00/678.01) lat (ms,95%): 511.33 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 128 tps: 352.41 qps: 5644.42 (r/w/o: 4939.49/0.00/704.93) lat (ms,95%): 530.08 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 128 tps: 260.60 qps: 4176.62 (r/w/o: 3655.33/0.00/521.29) lat (ms,95%): 893.56 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 128 tps: 279.16 qps: 4448.89 (r/w/o: 3890.87/0.00/558.02) lat (ms,95%): 831.46 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 128 tps: 272.82 qps: 4368.04 (r/w/o: 3822.50/0.00/545.54) lat (ms,95%): 802.05 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 128 tps: 337.02 qps: 5404.05 (r/w/o: 4729.81/0.00/674.23) lat (ms,95%): 601.29 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 258678
write: 0
other: 36954
total: 295632
transactions: 18477 (306.33 per sec.)
queries: 295632 (4901.20 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.3163s
total number of events: 18477
Latency (ms):
min: 5.03
avg: 416.79
max: 9970.68
95th percentile: 719.92
sum: 7701116.00
Threads fairness:
events (avg/stddev): 144.3516/5.82
execution time (avg/stddev): 60.1650/0.09
#cleanup
sysbench --mysql-host=mysqldb --mysql-db=testdb --mysql-user=testuser \
--max-requests=0 --db-driver=mysql --mysql-password=testuser \
--time=60 --report-interval=10 \
--db-ps-mode=disable \
--mysql-port=3306 --threads=128 /usr/share/sysbench/$1 cleanup
+ sysbench --mysql-host=mysqldb --mysql-db=testdb --mysql-user=testuser --max-requests=0 --db-driver=mysql --mysql-password=testuser --time=60 --report-interval=10 --db-ps-mode=disable --mysql-port=3306 --threads=128 /usr/share/sysbench/oltp_read_only.lua cleanup
sysbench 1.0.11 (using system LuaJIT 2.1.0-beta3)
Dropping table 'sbtest1'...