ScAlAn

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'...

Leave a Reply

Your email address will not be published. Required fields are marked *