官网: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.