Install and use sysbench.

Document directory

• Question 1:
• Question 2:
• Test environment:
• 2. thread testing
• 3. Io Testing
• 4. Memory Test
• 5. MySQL OLTP Test

I. Introduction

Sysbench is a modular, cross-platform, multi-thread benchmark testing tool used to evaluate the database load of different system parameters.
The following system parameters can be tested for the current function:
File I/O performance (file I/O performance)
Scheduler Performance)
Memory allocation and transfer speed (memory allocation and transmission speed)
POSIX Threads implementation performance (POSIX thread execution performance)
Database server performance (OLTP benchmark) (database server performance)
Sysbench runs a specified number of threads, all of which are parallel execution requests. The actual workload of requests depends on the specified test mode (add the test module to the compilation ). You can limit the total number or total time of requests as the benchmark, or both.

Ii. Installation

wget http://downloads.sourceforge.net/project/sysbench/sysbench/0.4.12/sysbench-0.4.12.tar.gz# tar -zxvf  sysbench-0.4.12.tar.gz# cd sysbench-0.4.12# ./autogen.sh# ./configure --with-mysql-includes=/usr/local/perconamysql/include/ --with-mysql-libs=/usr/local/perconamysql/lib/#make && make install

Errors during installation and solutions:

Question 1:

../libtool: line 838: X--tag=CC: command not found../libtool: line 871: libtool: ignoring unknown tag : command not found../libtool: line 838: X--mode=link: command not found../libtool: line 1004: *** Warning: inferring the mode of operation is deprecated.: command not found../libtool: line 1005: *** Future versions of Libtool will require --mode=MODE be specified.: command not found../libtool: line 2231: X-g: command not found../libtool: line 2231: X-O2: command not found../libtool: line 1951: X-L/usr/local/mysql-5.5.8/lib: No such file or directory../libtool: line 2400: Xsysbench: command not found

Solution:
1. Run autogen. Sh before compilation.
2. Install the latest version of libtool. Note that you must first configure and then copy libtool to the sysbench directory.

Question 2:

# Sysbench: Error while loading shared libraries: libmysqlclient. so.16: cannot open shared object file: no such file or directory solution: # In-S/usr/local/mysql-5.5.8/lib/libmysqlclient. so.16/lib64/libmysqlclient. so.16

Iii. test options

[Root @ localhost bin] # sysbench test case: sysbench [General-options]... -- test = <Test-Name> [test-options]... command common options: -- num-threads = N number of test threads created. The default value is 1. -- Max-Requests = n. The default value is limit. 0 indicates no limit. -- Max-time = n maximum execution time, in seconds. The default value is 0. -- Forced-Shutdown = string is forcibly interrupted when the value of max-time is exceeded. The default value is off.] -- Thread-Stack-size = size the stack size of each thread. The default value is 32 KB. -- Init-RNG = [ON | off] whether to initialize the random number generator at the beginning of the test. The default value is off. -- Test = string specifies the name of the test project. -- DEBUG = [ON | off]: whether to display more debugging information. The default value is off. -- Validate = [ON | off] perform a verification check if possible. The default value is off. Test Project: fileio-file I/O test CPU-CPU performance test memory-memory functions speed test threads-threads subsystem Performance Test mutex-mutex Performance Test (mutex performance test) OLTP-OLTP test, the main character of our test. Command: Prepare (preparations before test) Run (formal test) cleanup (delete test data after test) help versionsee 'sysbench -- test = <Name> help' for a list of options for each test. view the list of more options for each test item. [Root @ localhost bin] # sysbench -- test = fileio help -- file-num = n create the number of test files. The default value is 128 -- file-block-size = n. The default value is 16384 (16 k) -- file-total-size = size. The default value is 2 GB -- file-test-mode = string file test mode {seqwr (sequential write), seqrewr (sequential read/write), seqrd (sequential read), rndrd (random read ), rndwr (random write), rndrw (random read/write)} -- file-io-mode = string file operation mode {sync (synchronous), async (asynchronous ), faw.map (fast map ing), slowmmap (slow map ing )}. The default value is sync -- file-extra-flags = string. The extra sign is used to open the file {sync, dsync, direct }. The default value is null. -- file-fsync-freq = N: the frequency at which fsync () is executed. (0-do not use fsync ()). The default value is 100 -- file-fsync-All = [ON | off] fsync is executed every time a write operation is executed. The default value is off -- file-fsync-end = [ON | off]. fsync is executed at the end of the test. The default value is on -- file-fsync-mode = string. which method is used for synchronization? {fsync, fdatasync }. The default value is fsync -- file-merged-Requests = n. If yes, the maximum number of I/O requests to be merged (0 indicates that the request is not merged ). The default value is 0 -- file-RW-ratio = n. The default value is 1.5 [root @ localhost bin] # sysbench -- test = CPU help -- CPU-max-prime = n. The default value is 10000 [root @ localhost bin] #./sysbench -- test = memory help -- memory-block-size = size. The default value is 1 k -- memory-total-size = size. The default value is 100 GB -- memory-scope = string memory access range {Global, local }. The default value is global -- memory-hugetlb = [ON | off. The default value is off -- memory-memory = string memory operation type. {Read, write, none} is the default write -- memory-access-mode = string Storage Access Mode {seq, RND} is seq [root @ localhost bin] # sysbench -- test = threads help -- thread-yields = n. How many threads are generated for each request. The default value is 1000 -- thread-locks = n. The number of locks for each thread. The default value is 8 [root @ localhost bin] # sysbench -- test = mutex help -- mutex-num = the total size of the n Array mutex. The default value is 4096 -- mutex-locks = n. The number of mutex locks for each thread. The default value is 50000 -- mutex-loops = n. The number of empty cycles of internal mutex locks. The default value is 10000 [root @ localhost bin] # sysbench -- test = OLTP helpoltp options: -- OLTP-test-mode = string Execution Mode {simple, complex (Advanced transactional ), nontrx (non-transactional), SP }. The default value is complex -- OLTP-Reconnect-mode = string reconnect mode {SESSION (reconnect is not used. Each thread is disconnected only at the end of the test), transaction (reconnect after each transaction ends), and query (reconnect after each SQL statement is executed ), random (select the above reconnection mode randomly for each transaction )}. The default value is session -- OLTP-SP-name = string stored procedure name. The default value is null. -- OLTP-read-only = [ON | off] Read-only mode. The update, delete, and insert statements cannot be executed. The default value is off -- OLTP-Skip-Trx = [ON | off]. The begin/commit statement is omitted. The default value is off -- OLTP-range-size = n. Default Value: 100 -- OLTP-point-Selects = N number of point selects [10] -- OLTP-simple-ranges = N number of simple ranges [1] -- OLTP-sum-ranges = N number of sum ranges [1] -- OLTP-order-ranges = N number of ordered ranges [1] -- OLTP-distinct-ranges = N number of distinct ranges [1] -- OLTP -index-Updates = N number of Index Update [1] -- OLTP-non-index-Updates = N number of non-index updates [1] -- OLTP-nontrx-mode = string query type pair In non-transaction execution mode {select, update_key, update_nokey, insert, delete} [select] -- OLTP-auto-Inc = [ON | off] auto_increment whether to enable or not. The default value is on -- OLTP-connect-delay = n. The default value is 10000 -- OLTP-user-delay-min = n. The shortest waiting time for each request. The Unit is ms. The default value is 0 -- OLTP-user-delay-max = n. The maximum waiting time for each request. The Unit is ms. The default value is 0 -- OLTP-table-name = string. The default value is sbtest -- OLTP-table-size = n. The number of records in the test table. The default value is 10000 -- OLTP-dist-type = Random Number of string distribution {uniform (uniform distribution), Gaussian (Gaussian distribution), special (spatial distribution )}. The default value is special -- OLTP-dist-iter = n. The default value is 12 -- the percentage of OLTP-dist-PCT = N is considered as 'special '(for special distribution ). The default value is 1 -- percentage of OLTP-dist-res = n 'special. The default value is 75 General Database options: -- DB-driver = string. Specify the database Driver ('help' to get list of available drivers) -- DB-PS-mode = string report usage mode {auto, disable} [auto] compiled-in database drivers: mysql-MySQL drivermysql options: -- mysql-host = [list,...] mySQL server host [localhost] -- mysql-Port = n MySQL server port [3306] -- mysql-socket = string MySQL socket -- mysql-user = string MySQL user [sbtest] -- mysql- password = string MySQL password [] -- mysql-DB = string MySQL database name [sbtest] -- mysql-table-engine = string storage engine to use for the test table {MyISAM, innoDB, bdb, heap, ndbcluster, federated} [InnoDB] -- mysql-engine-Trx = string whether storage engine used is transactional or not {yes, no, auto} [auto] -- mysql-SSL = [ON | off] use SSL connections, if available in the client library [off] -- MyISAM-max-rows = n max-rows parameter for MyISAM tables [1000000] -- mysql-create-Options = string additional options passed to create table []

Iv. Test the instance test environment:

CPU: Name: Intel (r) Xeon (r) CPU e5606 @ 2.13 GHz
Memory: 4 GB
System: rhel5.4 x86
DB: percona-5.5.18

1. Test CPU

# sysbench --test=cpu --cpu-max-prime=2000 runMaximum prime number checked in CPU test: 2000Test execution summary:    total time:                          2.8035s    total number of events:              10000    total time taken by event execution: 2.7988    per-request statistics:         min:                                  0.28ms         avg:                                  0.28ms         max:                                  0.51ms         approx.  95 percentile:               0.28msThreads fairness:    events (avg/stddev):           10000.0000/0.00    execution time (avg/stddev):   2.7988/0.00

2. thread testing

# sysbench  --test=threads --num-threads=500 --thread-yields=100 --thread-locks=4 runTest execution summary:    total time:                          1.6930s    total number of events:              10000    total time taken by event execution: 829.6164    per-request statistics:         min:                                  0.08ms         avg:                                 82.96ms         max:                                471.16ms         approx.  95 percentile:             206.11msThreads fairness:    events (avg/stddev):           20.0000/7.45    execution time (avg/stddev):   1.6592/0.01

3. Io Testing

(1) In the prepare stage, generate the required test file. After completion, many small files will be generated in the current directory. # Sysbench -- test = fileio -- num-threads = 16 -- file-total-size = 10g -- file-test-mode = rndrw prepare -- num-threads enabled thread -- file- total-size total file size (2) run stage # sysbench -- test = fileio -- num-threads = 200 -- file-total-size = 10g -- file-test-mode = rndrw run -- file-test-mode rndrw: merged random read/write rndwr: Random write rndrd: random read seqrewr: sequential rewrite seqwr: sequential write operations written med: 6013 read, 3994 write, 11540 Other = 21547 totalread 93.953 MB written 62.406 MB total transferred 156.36 MB (8.7984 MB/sec) 563.10 requests/sec executedtest execution Summary: Total time: 17.7712 s total number of events: 10007 total time taken by event execution: 881.7831 per-request statistics: min: 0.01 Ms AVG: 88.12 Ms MAX: 1363.57 Ms approx. 95 percentile: 419.12 msthreads fairness: Events (avg/stddev): 50.0350/12.93 execution time (avg/stddev): 4.4089/1.16 (3) clean the files generated during the test # sysbench -- test = fileio -- num-threads = 200 -- file-total-size = 10g -- file-test-mode = rndrw cleanup

4. Memory Test

#sysbench --test=memory --memory-block-size=8k --memory-total-size=4G run

5. MySQL OLTP Test

Mysql> Create Database sbtest; Prepare the data used for testing, I have prepared 1 million rows of data # sysbench -- test = OLTP -- mysql-user = root -- mysql-host = localhost -- mysql-socket =/tmp/MySQL. sock -- mysql-Password = -- mysql-table-engine = InnoDB -- OLTP-table-size = 10000000 prepare # use 16 threads to start the test and read/write mode. # Sysbench -- mysql-DB = sbtest -- Max-Requests = 0 -- test = OLTP -- mysql-engine-Trx = yes -- mysql-table-engine = InnoDB -- OLTP-table-size = 10000000 -- DB-PS-mode = Disable -- mysql-user = root -- mysql-host = localhost -- mysql-socket =/tmp/MySQL. sock -- mysql-Password = -- num-threads = 16 -- Max-time = 600 runoltp test statistics: queries saved med: Read: 5777898 write: 2063535 other: 825414 Total: 8666847 transactions: 412707 (687.83 per sec .) deadlocks: 0 (0.00 per sec .) read/write requests: 7841433 (13068.78 per sec .) other operations: 825414 (1375.66 per sec .) test execution Summary: Total time: 600.0128 s total number of events: 412707 total time taken by event execution: 9595.4495 per-request statistics: min: 3.87 Ms AVG: 23.25 Ms Max: 8699.18 Ms approx. 95 percentile: 44.69 msthreads fairness: Events (avg/stddev): 2579 4.1875/97.71 execution time (avg/stddev): 599.7156/0.00 # use 16 threads to start the test, read-only mode. # Sysbench -- mysql-DB = sbtest -- Max-Requests = 0 -- test = OLTP -- mysql-engine-Trx = yes -- mysql-table-engine = InnoDB -- OLTP-table-size = 10000000 -- DB-PS-mode = Disable -- mysql-user = root -- mysql-host = localhost -- mysql-socket =/tmp/MySQL. sock -- OLTP-read-only -- mysql-Password = -- num-threads = 16 -- Max-time = 600 runoltp test statistics: queries saved med: Read: 3617138 write: 0 Other: 516734 Total: 4133872 transactions: 258367 (430.59 per sec .) deadlocks: 0 (0.00 per sec .) read/write requests: 3617138 (6028.33 per sec .) other operations: 516734 (861.19 per sec .) test execution Summary: Total time: 600.0233 s total number of events: 258367 total time taken by event execution: 9597.2735 per-request statistics: min: 3.76 ms AVG: 37.15 Ms Max: 103.82 Ms approx. 95 percentile: 49.00 msthreads fairness: Events (avg/stddev): 16147.9375/20.32 execution time (avg/stddev ): 599.8296/0.01 # Clear the residual test information sysbench -- mysql-DB = sbtest -- Max-Requests = 0 -- test = OLTP -- mysql-engine-Trx = yes -- MYS