benchmark之sysbench-cpu\fileio\memory\threads\mutex\mysql

官网：https://github.com/akopytov/sysbench

【安装】

方法一：

Debian/Ubuntu环境下：   

    curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash

    sudo apt -y install sysbench

RHEL/CentOS环境下：

    curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash

    sudo yum -y install sysbench

方法二，如法一失败：

git clone https://github.com/akopytov/sysbench.git

git checkout remotes/origin/1.0

./autogen.sh

./configure --prefix=/home/cq/library/sysbench --with-mysql-includes=/home/cq/library/mysql-5.7/include --with-mysql-libs=/home/cq/library/mysql-5.7/lib

make –j8

make install

若报链接库的问题，则需要导入mysql库目录：

sudo vim /etc/ld.so.conf  加入以下代码后，sudo ldconfig

/home/cq/library/mysql-5.7/lib

 

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

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

2 CPU Benchmark
You can benchmark your CPU performance as follows:

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

root@server1:~# sysbench --test=cpu --cpu-max-prime=20000 run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Doing CPU performance benchmark

Threads started!
Done.

Maximum prime number checked in CPU test: 20000


Test execution summary:
    total time:                          23.8724s
    total number of events:              10000
    total time taken by event execution: 23.8716
    per-request statistics:
         min:                                  2.31ms
         avg:                                  2.39ms
         max:                                  6.39ms
         approx.  95 percentile:               2.44ms

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   23.8716/0.00

root@server1:~#

You see a lot of numbers, the most important of it is the total time:

    total time:                          23.8724s

Of course, you must compare benchmarks across multiple systems to know what these numbers are worth.

3 File IO Benchmark
To measure file IO performance, we first need to create a test file that is much bigger than your RAM (because otherwise, the system will use RAM for caching which tampers with the benchmark results) - 150GB is a good value:

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

Afterwards, we can run the benchmark:

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

root@server1:~# sysbench --test=fileio --file-total-size=150G --file-test-mode=rndrw --init-rng=on --max-time=300 --max-requests=0 run
sysbench: /usr/lib/libmysqlclient.so.18: no version information available (required by sysbench)
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1
Initializing random number generator from timer.


Extra file open flags: 0
128 files, 1.1719Gb each
150Gb total file size
Block size 16Kb
Number of random requests for random IO: 0
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed:  600 Read, 400 Write, 1186 Other = 2186 Total
Read 9.375Mb  Written 6.25Mb  Total transferred 15.625Mb  (53.316Kb/sec)
    3.33 Requests/sec executed

Test execution summary:
    total time:                          300.0975s
    total number of events:              1000
    total time taken by event execution: 158.7611
    per-request statistics:
         min:                                  0.01ms
         avg:                                158.76ms
         max:                               2596.96ms
         approx.  95 percentile:             482.29ms

Threads fairness:
    events (avg/stddev):           1000.0000/0.00
    execution time (avg/stddev):   158.7611/0.00

root@server1:~#

The important number is the Kb/sec value:

Read 9.375Mb  Written 6.25Mb  Total transferred 15.625Mb  (53.316Kb/sec)

After the benchmark, you can delete the 150GB test file from the system:

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

4 MySQL Benchmark
To measure MySQL performance, we first create a test table in the database test with 1,000,000 rows of data:

sysbench --test=oltp --oltp-table-size=1000000 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=yourrootsqlpassword prepare

root@server1:~# sysbench --test=oltp --oltp-table-size=1000000 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=yourrootsqlpassword prepare
sysbench 0.4.12: multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Creating table 'sbtest'...
Creating 1000000 records in table 'sbtest'...
root@server1:~#

Replace the word yourrootsqlpassword with your MySQL root password in the above command. Do the same in the next commands.

Afterwards, you can run the MySQL benchmark as follows:

sysbench --test=oltp --oltp-table-size=1000000 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=yourrootsqlpassword --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run

root@server1:~# sysbench --test=oltp --oltp-table-size=1000000 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=yourrootsqlpassword --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 8

Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 7 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            2253860
        write:                           0
        other:                           321980
        total:                           2575840
    transactions:                        160990 (2683.06 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 2253860 (37562.81 per sec.)
    other operations:                    321980 (5366.12 per sec.)

Test execution summary:
    total time:                          60.0024s
    total number of events:              160990
    total time taken by event execution: 479.3419
    per-request statistics:
         min:                                  0.81ms
         avg:                                  2.98ms
         max:                               3283.40ms
         approx.  95 percentile:               4.62ms

Threads fairness:
    events (avg/stddev):           20123.7500/63.52
    execution time (avg/stddev):   59.9177/0.00

root@server1:~#

The important number is the transactions per second value:

    transactions:                        160990 (2683.06 per sec.)

To clean up the system afterwards (i.e., remove the test table), run:

sysbench --test=oltp --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=yourrootsqlpassword cleanup

在测试mysql之前需要先create database

https://www.jamescoyle.net/how-to/1131-benchmark-mysql-server-performance-with-sysbench

flex001@flex001-desktop:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6307
Server version: 5.7.22-22 Percona Server (GPL), Release '22', Revision 'f62d93c'

Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test_innodb        |
| test_rocksdb       |
+--------------------+
7 rows in set (0.01 sec)

mysql> drop database test_innodb;
Query OK, 1 row affected (0.04 sec)

mysql> drop database test_rocksdb;
Query OK, 1 row affected (0.01 sec)

mysql> create database test_innodb;
Query OK, 1 row affected (0.00 sec)

mysql> create database test_rocksdb;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test_innodb        |
| test_rocksdb       |
+--------------------+
7 rows in set (0.00 sec)

mysql> exit
Bye

测试脚本：

flex001@flex001-desktop:~$ python3 test_innodb_and_rocksdb.py 
sysbench /usr/share/sysbench/oltp_point_select.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb prepare
sysbench /usr/share/sysbench/oltp_point_select.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_point_select.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_point_select.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_point_select.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_point_select.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_point_select.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb cleanup
sysbench /usr/share/sysbench/oltp_point_select.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb prepare
sysbench /usr/share/sysbench/oltp_point_select.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_point_select.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_point_select.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_point_select.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_point_select.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_point_select.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb cleanup
sysbench /usr/share/sysbench/oltp_read_only.lua        --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb prepare
sysbench /usr/share/sysbench/oltp_read_only.lua        --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_read_only.lua        --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_read_only.lua        --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_read_only.lua        --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_read_only.lua        --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_read_only.lua        --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb cleanup
sysbench /usr/share/sysbench/oltp_read_only.lua        --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb prepare
sysbench /usr/share/sysbench/oltp_read_only.lua        --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_read_only.lua        --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_read_only.lua        --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_read_only.lua        --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_read_only.lua        --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_read_only.lua        --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb cleanup
sysbench /usr/share/sysbench/oltp_write_only.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb prepare
sysbench /usr/share/sysbench/oltp_write_only.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_write_only.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_write_only.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_write_only.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_write_only.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_write_only.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb cleanup
sysbench /usr/share/sysbench/oltp_write_only.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb prepare
sysbench /usr/share/sysbench/oltp_write_only.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_write_only.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_write_only.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_write_only.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_write_only.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_write_only.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb cleanup
sysbench /usr/share/sysbench/oltp_read_write.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb prepare
sysbench /usr/share/sysbench/oltp_read_write.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_read_write.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_read_write.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_read_write.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_read_write.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_read_write.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb cleanup
sysbench /usr/share/sysbench/oltp_read_write.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb prepare
sysbench /usr/share/sysbench/oltp_read_write.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_read_write.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_read_write.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_read_write.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_read_write.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_read_write.lua       --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb cleanup
sysbench /usr/share/sysbench/oltp_update_non_index.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb prepare
sysbench /usr/share/sysbench/oltp_update_non_index.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_update_non_index.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_update_non_index.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_update_non_index.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_update_non_index.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_update_non_index.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb cleanup
sysbench /usr/share/sysbench/oltp_update_non_index.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb prepare
sysbench /usr/share/sysbench/oltp_update_non_index.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_update_non_index.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_update_non_index.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_update_non_index.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_update_non_index.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_update_non_index.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb cleanup
sysbench /usr/share/sysbench/oltp_update_index.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb prepare
sysbench /usr/share/sysbench/oltp_update_index.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_update_index.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_update_index.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_update_index.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_update_index.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_update_index.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb cleanup
sysbench /usr/share/sysbench/oltp_update_index.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb prepare
sysbench /usr/share/sysbench/oltp_update_index.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_update_index.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_update_index.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_update_index.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_update_index.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_update_index.lua     --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb cleanup
sysbench /usr/share/sysbench/oltp_insert.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb prepare
sysbench /usr/share/sysbench/oltp_insert.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_insert.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_insert.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_insert.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_insert.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_insert.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb cleanup
sysbench /usr/share/sysbench/oltp_insert.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb prepare
sysbench /usr/share/sysbench/oltp_insert.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_insert.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_insert.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_insert.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_insert.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_insert.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb cleanup
sysbench /usr/share/sysbench/oltp_delete.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb prepare
sysbench /usr/share/sysbench/oltp_delete.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_delete.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_delete.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_delete.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_delete.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run
sysbench /usr/share/sysbench/oltp_delete.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb cleanup
sysbench /usr/share/sysbench/oltp_delete.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb prepare
sysbench /usr/share/sysbench/oltp_delete.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_delete.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_delete.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_delete.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_delete.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run
sysbench /usr/share/sysbench/oltp_delete.lua           --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb cleanup

[root@localhost script]# cat DBPerf2.py 
#!/usr/bin/python3
# -*- coding: UTF-8 -*-

# Firstly, You must create the two database test_innodb and test_rocksdb in mysql

import os, sys
import time
import subprocess

table_size = "300000000"
#table_size =    "5000000"

cmd_innodb_params           = "--table-size=" + table_size + " --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb"
cmd_innodb_point_select     = "sysbench /usr/share/sysbench/oltp_point_select.lua     " + cmd_innodb_params
cmd_innodb_read_only        = "sysbench /usr/share/sysbench/oltp_read_only.lua        " + cmd_innodb_params
cmd_innodb_write_only       = "sysbench /usr/share/sysbench/oltp_write_only.lua       " + cmd_innodb_params
cmd_innodb_read_write       = "sysbench /usr/share/sysbench/oltp_read_write.lua       " + cmd_innodb_params
cmd_innodb_update_non_index = "sysbench /usr/share/sysbench/oltp_update_non_index.lua " + cmd_innodb_params
cmd_innodb_update_index     = "sysbench /usr/share/sysbench/oltp_update_index.lua     " + cmd_innodb_params
cmd_innodb_insert           = "sysbench /usr/share/sysbench/oltp_insert.lua           " + cmd_innodb_params
cmd_innodb_delete           = "sysbench /usr/share/sysbench/oltp_delete.lua           " + cmd_innodb_params

cmd_rocksdb_params           = "--table-size=" + table_size + " --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb"
cmd_rocksdb_point_select     = "sysbench /usr/share/sysbench/oltp_point_select.lua     " + cmd_rocksdb_params
cmd_rocksdb_read_only        = "sysbench /usr/share/sysbench/oltp_read_only.lua        " + cmd_rocksdb_params
cmd_rocksdb_write_only       = "sysbench /usr/share/sysbench/oltp_write_only.lua       " + cmd_rocksdb_params
cmd_rocksdb_read_write       = "sysbench /usr/share/sysbench/oltp_read_write.lua       " + cmd_rocksdb_params
cmd_rocksdb_update_non_index = "sysbench /usr/share/sysbench/oltp_update_non_index.lua " + cmd_rocksdb_params
cmd_rocksdb_update_index     = "sysbench /usr/share/sysbench/oltp_update_index.lua     " + cmd_rocksdb_params
cmd_rocksdb_insert           = "sysbench /usr/share/sysbench/oltp_insert.lua           " + cmd_rocksdb_params
cmd_rocksdb_delete           = "sysbench /usr/share/sysbench/oltp_delete.lua           " + cmd_rocksdb_params



def prepare_cmd(input_cmd):
    "prepare command"
    f = open(r"output.txt", "a")
    
    cmd = input_cmd + " prepare"
    print(cmd) 
    f.write(cmd + "\n")
    f.flush()
    pipe = subprocess.Popen(cmd, shell=True, stdout=f)
    pipe.wait()

    f.close()

def run_cmd(input_cmd):
    "run command"
    f = open(r"output.txt", "a")
    
    for _ in range(5):
        cmd = input_cmd + " run"
        print(cmd) 
        f.write(cmd + "\n")
        f.flush()
        pipe = subprocess.Popen(cmd, shell=True, stdout=f)
        pipe.wait()
        time.sleep(1)

    f.close()

def cleanup_cmd(input_cmd):
    "run command"
    f = open(r"output.txt", "a")
    
    cmd = input_cmd + " cleanup"
    print(cmd) 
    f.write(cmd + "\n")
    f.flush()
    pipe = subprocess.Popen(cmd, shell=True, stdout=f)
    pipe.wait()

    f.close()

def all_cmd():
    "all command"
    prepare_cmd(cmd_innodb_point_select)
    run_cmd(cmd_innodb_point_select)
    run_cmd(cmd_innodb_read_only)
    run_cmd(cmd_innodb_write_only)
    run_cmd(cmd_innodb_read_write)
    run_cmd(cmd_innodb_update_non_index)
    run_cmd(cmd_innodb_update_index)
    run_cmd(cmd_innodb_insert)
    run_cmd(cmd_innodb_delete)
    cleanup_cmd(cmd_innodb_point_select)

    #prepare_cmd(cmd_rocksdb_point_select)
    #run_cmd(cmd_rocksdb_point_select)
    #run_cmd(cmd_rocksdb_read_only)
    #run_cmd(cmd_rocksdb_write_only)
    #run_cmd(cmd_rocksdb_read_write)
    #run_cmd(cmd_rocksdb_update_non_index)
    #run_cmd(cmd_rocksdb_update_index)
    #run_cmd(cmd_rocksdb_insert)
    #run_cmd(cmd_rocksdb_delete)
    #cleanup_cmd(cmd_rocksdb_point_select)


all_cmd()

[root@localhost script]# cat rm_files.py 
#!/usr/bin/python3
# -*- coding: UTF-8 -*-

import os
import time

path = "/var/lib/mysql/"

while 1:
    files= os.listdir(path)
    for file in files:
        file = path + file
        print(file)
        if os.path.getsize(file) > 1073892320:
            os.system("rm " + file)
            print("rm " + file)
    time.sleep(10)

[root@localhost DBPerf]# cat README.md 
# DBPerf
Mysql performance test in innoDB and RocksDB

# Hardware condition

OS Version: ubuntu 16.04 LTS, 64-bit

Hardware: Intel® Core™ i3-6100U CPU @ 2.30GHz × 4, 4G DDR4, 120G SSD


# Mysql 5.7(innoDB and RocksDB) install
references https://www.percona.com/doc/percona-server/LATEST/myrocks/install.html#

wget https://repo.percona.com/apt/percona-release_0.1-6.$(lsb_release -sc)_all.deb

sudo dpkg -i percona-release_0.1-6.$(lsb_release -sc)_all.deb

sudo apt-get update

sudo apt-cache search percona

sudo apt-get install percona-server-rocksdb-5.7

# Mysql 8.0(innoDB) install
references https://www.percona.com/blog/2018/05/14/installing-mysql-8-on-ubuntu-16-04-lts/

wget https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb

sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb

sudo apt-get update

sudo apt-get install mysql-server

# Benchmark test used sysbench
references https://github.com/akopytov/sysbench
# Install

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash

sudo apt -y install sysbench

# Usage
before using sysbench to test mysql performance, you must create database, like test_innodb and test_rocksdb

mysql> create database test_innodb;

mysql> create database test_rocksdb;

# test
After you create database, you can use sysbench to test mysql performance in innoDB and rocksDB. 
You can use oltp script to test TPS/QPS/Latency, like:
oltp_point_select.lua, oltp_read_only.lua, oltp_write_only.lua, oltp_read_write.lua, 
oltp_update_non_index.lua, oltp_update_index.lua, oltp_insert.lua, oltp_delete.lua. 
Automated script test_innodb_and_rocksdb.py example:

sysbench /usr/share/sysbench/oltp_point_select.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb prepare

sysbench /usr/share/sysbench/oltp_point_select.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb run

sysbench /usr/share/sysbench/oltp_point_select.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_innodb --mysql-user=root --mysql-password=123 --mysql_storage_engine=innodb cleanup


sysbench /usr/share/sysbench/oltp_point_select.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb prepare

sysbench /usr/share/sysbench/oltp_point_select.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb run

sysbench /usr/share/sysbench/oltp_point_select.lua --table-size=5000000 --threads=100 --rand-type=special --time=180 --db-driver=mysql --mysql-db=test_rocksdb --mysql-user=root --mysql-password=123 --mysql_storage_engine=rocksdb cleanup


# Note
sysbench test 5 times so that you can select the median.