Database System Test Tool sysbench

Sysbench is a modular, cross-platform, multi-thread benchmark testing tool used to evaluate the database load of different system parameters. For details about this project, see: http://sysbench.sourceforge.net.

It mainly includes the following testing methods:
1. CPU Performance
2. Disk I/O performance
3. SchedulingProgramPerformance
4. Memory Allocation and transmission speed
5. POSIX thread Performance
6. database performance (OLTP benchmark test)
Currently, sysbench mainly supports MySQL, pgsql, and Oracle databases.

1. install
first, download the source package in the http://sourceforge.net/projects/sysbench.
next, follow the steps below to install:

tar zxf sysbench-0.4.8.tar.gz
Cd sysbench-0.4.8
. /configure & make install
strip/usr/local/bin/sysbench

the preceding method is applicable to MySQL installation in the standard default directory. under, if MySQL is not installed in the standard directory, you must specify the MySQL path. For example, if MySQL is installed in/usr/local/MySQL, compile it as follows:

/configure -- With-mysql-related des =/usr/local/MySQL/include -- With-mysql-libs =/usr/local/MySQL/ lib & make install

of course, to compile with the above parameters, make sure that there is a corresponding so file under your MySQL lib directory. If not, you can download devel or share package for installation.
In addition, If you want sysbench to support pgsql/Oracle, you need to add the parameter

-- With-pgsql
or
-- With-Oracle

these two parameters are disabled by default, only MySQL is supported by default.

2. Start testing
After the compilation is successful, it is necessary to test various performances. The test methods are also mentioned on the website, but the OLTP test is not accurate enough. Here I will roughly mention:
1. CPU performance test

Sysbench -- test = CPU -- CPU-max-prime = 20000 run

In the preceding example, the maximum prime number is 20000. You can adjust the value based on the CPU performance.

2. thread testing

Sysbench -- test = threads -- num-threads = 64 -- thread-yields = 100 -- thread-locks = 2 run

3. Disk I/O performance test

Sysbench -- test = fileio -- num-threads = 16 -- file-total-size = 3G -- file-test-mode = rndrw prepare
Sysbench -- test = fileio -- num-threads = 16 -- file-total-size = 3G -- file-test-mode = rndrw run
Sysbench -- test = fileio -- num-threads = 16 -- file-total-size = 3G -- file-test-mode = rndrw cleanup

The above Parameters specify a maximum of 16 threads, the total size of the created files is 3 GB, and the file read/write mode is random read.

4. Memory Test

Sysbench -- test = memory -- memory-block-size = 8 K -- memory-total-size = 4G run

The above parameter specifies that the entire test process is to transfer 4 GB of data in the memory, each block size is 8 K.

5. OLTP Test

Sysbench -- test = OLTP -- mysql-table-engine = MyISAM -- OLTP-table-size = 1000000/
-- Mysql-socket =/tmp/MySQL. Sock -- mysql-user = test -- mysql-host = localhost/
-- Mysql-Password = test prepare

The above Parameters specify the table storage engine type for this test as MyISAM. Note that the parameters on the official website are incorrect, that is,-mysql-table-engine, -mysql-table-type is written on the official website. This should be caused by the absence of timely updates. In addition, if the maximum number of records in a table is 1000000, other parameters are well understood, mainly specifying the logon mode. When testing OLTP, you can create a database sbtest first, or use the parameter-mysql-dB to specify other databases. -Mysql-table-engine can also be specified as a table storage engine supported by MySQL, such as InnoDB.

Appendix: Use sysbench to test MySQL Performance

1. Prepare data
# Sysbench-DEBUG = OFF-test = OLTP-mysql-host = 10.15.2.20.- mysql-user = test-mysql-Password = test-OLTP-table-size = 1000000-mysql-DB = test-OLTP-table-name = stest-num-threads = 20-max-Requests = 10000-OLTP-auto-Inc = OFF-mysql-engine-Trx = Yes prepare

2. Test
# Sysbench-DEBUG = OFF-test = OLTP-mysql-host = 10.15.2.20.- mysql-user = test-mysql-Password = test-OLTP-table-size = 1000000-mysql-DB = test-OLTP-table-name = stest-num-threads = 20-max-Requests = 10000-OLTP-auto-Inc = OFF-mysql-engine-Trx = Yes run

3. delete data
# Sysbench-DEBUG = OFF-test = OLTP-mysql-host = 10.15.2.20.- mysql-user = test-mysql-Password = test-OLTP-table-size = 1000000-mysql-DB = test-OLTP-table-name = stest-num-threads = 20-max-Requests = 10000-OLTP-auto-Inc = OFF-mysql-engine-Trx = Yes cleanup

Okay, the main thing is this. If you want to know more, visitSysbenchHome page of the project.