sysbench manual
SysBench manual
Alexey Kopytov
<kaamos@users.sourceforge.net>
Copyright ©2004-2009 MySQL AB
Table of Contents
Chapter 1. Introduction.. 2
1. Features of SysBench. 2
2. Design. 2
3. Links. 2
4. Installation. 3
Chapter 2. Usage.. 4
1. General syntax. 4
2. Common command line options. 4
3. Batch mode. 6
4. Test modes. 6
4.1. cpu. 6
4.2. threads. 7
4.3. mutex. 7
4.4. memory. 8
4.5. fileio. 8
4.6. oltp. 11
Chapter 1. Introduction
SysBench is amodular, cross-platform and multi-threaded benchmark tool for evaluating OSparameters that are important for a system running a database under intensiveload.
The idea of thisbenchmark suite is to quickly get an impression about system performancewithout setting up complex database benchmarks or even without installing adatabase at all.
Current featuresallow to test the following system parameters:
- file I/O performance
- scheduler performance
- memory allocation and transfer speed
- POSIX threads implementation performance
- database server performance
The design is verysimple. SysBench runs a specified number of threads and they all executerequests in parallel. The actual workload produced by requests depends on thespecified test mode. You can limit either the total number of requests or thetotal time for the benchmark, or both.
Available testmodes are implemented by compiled-in modules, and SysBench was designed to makeadding new test modes an easy task. Each test mode may have additional (orworkload-specific) options.
Home page
Download
http://sf.net/projects/sysbench/.
Mailing lists
Web forums
· Help
Bug tracking system
If you arebuilding SysBench from a Bazaar repository rather than from a release tarball,you should run ./autogen.sh before building.
The followingstandart procedure will be sufficient to build SysBench in most cases:
| ./configure make make install |
The aboveprocedure will try to compile SysBench with MySQL support by default. If youhave MySQL headers and libraries in non-standard locations (and nomysql_config can be found inthe PATH environmental variable), then you can specify them explicitly with --with-mysql-includes and --with-mysql-libs options to./configure.
To compileSysBench without MySQL support, use --without-mysql. In this case alldatabase-related test modes will be unavailable.
If you are runningon a 64-bit platform, make sure to build a 64-bit binary by passing the propertarget platform and compiler options to configure script. You can also consult the INSTALLfile for generic installation instructions.
Chapter 2. Usage
The general syntaxfor SysBench is as follows:
| sysbench [common-options] --test=name [test-options] command |
See Section 2, “Commoncommand line options” for a descriptionof common options and documentation for particular test mode for a list oftest-specific options.
Below is a briefdescription of available commands and their purpose:
prepare
Performs preparative actions for thosetests which need them, e.g. creating the necessary files on disk for the fileio test, or fillingthe test database for theoltp test.
run
Runs the actual test specified with the --test=name option.
cleanup
Removes temporary data after the test runin those tests which create one.
help
Displays usage information for a testspecified with the --test=name option.
Also you can use sysbench help to display the brief usage summary and thelist of available test modes.
2. Common command line options
The table belowlists the supported common options, their descriptions and default values:
| Option | Description | Default value |
| --num-threads | The total number of worker threads to create | 1 |
| --max-requests | Limit for total number of requests. 0 means unlimited | 10000 |
| --max-time | Limit for total execution time in seconds. 0 (default) means unlimited | 0 |
| --forced-shutdown | Amount of time to wait after --max-time before forcing shutdown. The value can be either an absolute number of seconds or as a percentage of the --max-time value by specifying a number of percents followed by the '%' sign. "off" (the default value) means that no forced shutdown will be performed. | off |
| --thread-stack-size | Size of stack for each thread | 32K |
| --init-rng | Specifies if random numbers generator should be initialized from timer before the test start | off |
| --test | Name of the test mode to run | Required |
| --debug | Print more debug info | off |
| --validate | Perform validation of test results where possible | off |
| --help | Print help on general syntax or on a test mode specified with --test, and exit | off |
| --verbosity | Verbosity level (0 - only critical messages, 5 - debug) | 4 |
| --percentile | SysBench measures execution times for all processed requests to display statistical information like minimal, average and maximum execution time. For most benchmarks it is also useful to know a request execution time value matching some percentile (e.g. 95% percentile means we should drop 5% of the most long requests and choose the maximal value from the remaining ones). This option allows to specify a percentile rank of query execution times to count | 95 |
| --batch | Dump current results periodically (see Section 3, “Batch mode”) | off |
| --batch-delay | Delay between batch dumps in secods (see Section 3, “Batch mode”) | 300 |
| --validate | Perform validation of test results where possible | off |
Note thatnumerical values for all size options (like --thread-stack-size in this table) maybe specified by appending the corresponding multiplicative suffix (K forkilobytes, M for megabytes, G for gigabytes and T for terabytes).
In some cases itis useful to have not only the final benchmarks statistics, but also periodicaldumps of current stats to see how they change over the test run. For thispurpose SysBench has a batch execution mode which is turned on by the --batch option. You mayspecify the delay in seconds between the consequent dumps with the --batch-delay option. Example:
| sysbench --batch --batch-delay=5 --test=threads run |
This will runSysBench in a threads test mode, with the current values of minimum, average,maximum and percentile for request execution times printed every 5 seconds.
This section givesa detailed description for each test mode available in SysBench.
4.1. cpu
The cpu is one of the mostsimple benchmarks in SysBench. In this mode each request consists incalculation of prime numbers up to a value specified by the --cpu-max-primes option. All calculationsare performed using 64-bit integers.
Each threadexecutes the requests concurrently until either the total number of requests orthe total execution time exceed the limits specified with the common commandline options.
Example:
| sysbench --test=cpu --cpu-max-prime=20000 run |
4.2. threads
This test mode waswritten to benchmark scheduler performance, more specifically the cases when ascheduler has a large number of threads competing for some set of mutexes.
SysBench creates aspecified number of threads and a specified number of mutexes. Then each threadstarts running the requests consisting of locking the mutex, yielding the CPU,so the thread is placed in the run queue by the scheduler, then unlocking themutex when the thread is rescheduled back to execution. For each request, theabove actions are run several times in a loop, so the more iterations isperformed, the more concurrency is placed on each mutex.
The followingoptions are available in this test mode:
| Option | Description | Default value |
| --thread-yields | Number of lock/yield/unlock loops to execute per each request | 1000 |
| --thread-locks | Number of mutexes to create | 8 |
Example:
| sysbench --num-threads=64 --test=threads --thread-yields=100 --thread-locks=2 run |
4.3. mutex
This test mode waswritten to emulate a situation when all threads run concurrently most of thetime, acquiring the mutex lock only for a short period of time (incrementing aglobal variable). So the purpose of this benchmarks is to examine theperformance of mutex implementation.
The followingoptions are available in this test mode:
| Option | Description | Default value |
| --mutex-num | Number of mutexes. The actual mutex to lock is chosen randomly before each lock | 4096 |
| --mutex-locks | Number of mutex locks to acquire per each request | 50000 |
| --mutex-loops | Number of iterations for an empty loop to perform before acquiring the lock | 10000 |
4.4. memory
This test mode canbe used to benchmark sequential memory reads or writes. Depending on commandline options each thread can access either a global or a local block for allmemory operations.
The followingoptions are available in this test mode:
| Option | Description | Default value |
| --memory-block-size | Size of memory block to use | 1K |
| --memory-scope | Possible values: global, local. Specifies whether each thread will use a globally allocated memory block, or a local one. | global |
| --memory-total-size | Total size of data to transfer | 100G |
| --memory-oper | Type of memory operations. Possible values: read, write. | 100G |
4.5. fileio
This test mode canbe used to produce various kinds of file I/O workloads. At the prepare stage SysBenchcreates a specified number of files with a specified total size, then at the run stage, each threadperforms specified I/O operations on this set of files.
When the global --validate option is usedwith the fileio test mode, SysBench performs checksums validation on all data read fromthe disk. On each write operation the block is filled with random values, thenthe checksum is calculated and stored in the block along with the offset ofthis block within a file. On each read operation the block is validated bycomparing the stored offset with the real offset, and the stored checksum withthe real calculated checksum.
The following I/Ooperations are supported:
seqwr
sequential write
seqrewr
sequential rewrite
seqrd
sequential read
rndrd
random read
rndwr
random write
rndrw
combined random read/write
Also, thefollowing file access modes can be specified, if the underlying platformsupports them:
Asynchronous I/O mode
At the moment only Linux AIOimplementation is supported. When running in asynchronous mode, SysBench queuesa specified number of I/O requests using Linux AIO API, then waits for at leastone of submitted requests to complete. After that a new series of I/O requestsis submitted.
Slow mmap() mode
In this mode SysBench will use mmap'ed I/O. However, a separate mmap will be used for each I/O request due to the limitation of 32-bitarchitectures (we cannotmmap() the whole file, asits size migth possibly exceed the maximum of 2 GB of the process addressspace).
Fast mmap() mode
On 64-bit architectures it is possible to mmap() the whole fileinto the process address space, avoiding the limitation of 2 GB on 32-bitplatforms.
Using fdatasync() instead of fsync()
Additional flags to open(2)
SysBench can use additional flags to open(2), such as O_SYNC, O_DSYNC and O_DIRECT.
Below is a list oftest-specific option for the fileio mode:
| Option | Description | Default value |
| --file-num | Number of files to create | 128 |
| --file-block-size | Block size to use in all I/O operations | 16K |
| --file-total-size | Total size of files | 2G |
| --file-test-mode | Type of workload to produce. Possible values: seqwr, seqrewr, seqrd, rndrd, rndwr, rndwr (see above) | required |
| --file-io-mode | I/O mode. Possible values: sync, async, fastmmap, slowmmap (only if supported by the platform, see above). | sync |
| --file-async-backlog | Number of asynchronous operations to queue per thread (only for --file-io-mode=async, see above) | 128 |
| --file-extra-flags | Additional flags to use with open(2) |
|
| --file-fsync-freq | Do fsync() after this number of requests (0 - don't use fsync()) | 100 |
| --file-fsync-all | Do fsync() after each write operation | no |
| --file-fsync-end | Do fsync() at the end of the test | yes |
| --file-fsync-mode | Which method to use for synchronization. Possible values: fsync, fdatasync (see above) | fsync |
| --file-merged-requests | Merge at most this number of I/O requests if possible (0 - don't merge) | 0 |
| --file-rw-ratio | reads/writes ration for combined random read/write test | 1.5 |
Usage example:
| $ sysbench --num-threads=16 --test=fileio --file-total-size=3G --file-test-mode=rndrw prepare $ sysbench --num-threads=16 --test=fileio --file-total-size=3G --file-test-mode=rndrw run $ sysbench --num-threads=16 --test=fileio --file-total-size=3G --file-test-mode=rndrw cleanup |
In the aboveexample the first command creates 128 files with the total size of 3 GB in thecurrent directory, the second command runs the actual benchmark and displaysthe results upon completion, and the third one removes the files used for thetest.
4.6. oltp
This test mode waswritten to benchmark a real database performance. At the prepare stage the following table is created inthe specified database (sbtest by default):
| CREATE TABLE `sbtest` ( `id` int(10) unsigned NOT NULL auto_increment, `k` int(10) unsigned NOT NULL default '0', `c` char(120) NOT NULL default '', `pad` char(60) NOT NULL default '', PRIMARY KEY (`id`), KEY `k` (`k`));
|
Then this table isfilled with a specified number of rows.
The followingexecution modes are available at the run stage:
Simple
In this mode eachthread runs simple queries of the following form:
| SELECT c FROM sbtest WHERE id=N |
where N takes a random value in range 1..<table size>
Advanced transactional
Each threadperforms transactions on the test table. If the test table and database supporttransactions (e.g. InnoDB engine in MySQL), then BEGIN/COMMITstatements will beused to start/stop a transaction. Otherwise, SysBench will use LOCK TABLES/UNLOCK TABLES statements (e.g. for MyISAM engine in MySQL). If some rows are deleted ina transaction, the same rows will be inserted within the same transaction, sothis test mode does not destruct any data in the test table and can be runmultiple times on the same table.
Depending on the command line options,each transaction may contain the following statements:
· Point queries:
| SELECT c FROM sbtest WHERE id=N |
· Range queries:
| SELECT c FROM sbtest WHERE id BETWEEN N AND M |
· Range SUM() queries:
| SELECT SUM(K) FROM sbtest WHERE id BETWEEN N and M |
· Range ORDER BY queries:
| SELECT c FROM sbtest WHERE id between N and M ORDER BY c |
· Range DISTINCT queries:
| SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY c |
· UPDATEs on index column:
| UPDATE sbtest SET k=k+1 WHERE id=N |
· UPDATEs on non-index column:
| UPDATE sbtest SET c=N WHERE id=M |
· DELETE queries:
| DELETE FROM sbtest WHERE id=N |
· INSERT queries:
| INSERT INTO sbtest VALUES (...) |
Non-transactional
This mode is similarto Simple, but you can alsochoose the query to run. Note that unlike the Advanced transactional mode, this onedoes not preserve the test table between requests, so you should recreate itwith the appropriate cleanup/prepare commands between consecutivebenchmarks.
Below is a list ofpossible queries:
· Point queries:
| SELECT pad FROM sbtest WHERE id=N |
· UPDATEs on index column:
| UPDATE sbtest SET k=k+1 WHERE id=N |
· UPDATEs on non-index column:
| UPDATE sbtest SET c=N WHERE id=M |
· DELETE queries:
| DELETE FROM sbtest WHERE id=N |
· The generated row IDs are unique over eachtest run, so no row is deleted twice.
· INSERT queries:
| INSERT INTO sbtest (k, c, pad) VALUES(N, M, S) |
Below is a list ofoptions available for the database test mode:
| Option | Description | Default value | ||||||||
| --oltp-test-mode | Execution mode (see above). Possible values: simpe (simple), complex (advanced transactional) and nontrx (non-transactional) | complex | ||||||||
| --oltp-read-only | Read-only mode. No UPDATE, DELETE or INSERT queries will be performed. | off | ||||||||
| --oltp-skip-trx | Omit BEGIN/COMMIT statements, i.e. run the same queries as the test would normally run but without using transactions. | off | ||||||||
| --oltp-reconnect-mode | Reconnect mode. Possible values:
| session | ||||||||
| --oltp-range-size | Range size for range queries | 100 | ||||||||
| --oltp-point-selects | Number of point select queries in a single transaction | 10 | ||||||||
| --oltp-simple-ranges | Number of simple range queries in a single transaction | 1 | ||||||||
| --oltp-sum-ranges | Number of SUM range queries in a single transaction | 1 | ||||||||
| --oltp-order-ranges | Number of ORDER range queries in a single transaction | 1 | ||||||||
| --oltp-distinct-ranges | Number of DISTINCT range queries in a single transaction | 1 | ||||||||
| --oltp-index-updates | Number of index UPDATE queries in a single transaction | 1 | ||||||||
| --oltp-non-index-updates | Number of non-index UPDATE queries in a single transaction | 1 | ||||||||
| --oltp-nontrx-mode | Type of queries for non-transactional execution mode (see above). Possible values: select, update_key, update_nokey, insert, delete. | select | ||||||||
| --oltp-connect-delay | Time in microseconds to sleep after each connection to database | 10000 | ||||||||
| --oltp-user-delay-min | Minimum time in microseconds to sleep after each request | 0 | ||||||||
| --oltp-user-delay-max | Maximum time in microseconds to sleep after each request | 0 | ||||||||
| --oltp-table-name | Name of the test table | sbtest | ||||||||
| --oltp-table-size | Number of rows in the test table | 10000 | ||||||||
| --oltp-dist-type | Distribution of random numbers. Possible values: uniform (uniform distribution), gauss (gaussian distribution) and special. With special distribution a specified percent of numbers is generated in a specified percent of cases (see options below). | special | ||||||||
| --oltp-dist-pct | Percentage of values to be treated as 'special' (for special distribution) | 1 | ||||||||
| --oltp-dist-res | Percentage of cases when 'special' values are generated (for special distribution) | 75 | ||||||||
| --db-ps-mode | If the database driver supports Prepared Statements API, SysBench will use server-side prepared statements for all queries where possible. Otherwise, client-side (or emulated) prepared statements will be used. This option allows to force using emulation even when PS API is available. Possible values: disable, auto. | auto |
Also, eachdatabase driver may provide its own options. Currently only MySQL driver isavailable. Below is a list of MySQL-specific options:
| Option | Description | Default value |
| --mysql-host | MySQL server host. Starting from version 0.4.5 you may specify a list of hosts separated by commas. In this case SysBench will distribute connections between specified MySQL hosts on a round-robin basis. Note that all connection ports and passwords must be the same on all hosts. Also, databases and tables must be prepared explicitely on each host before executing the benchmark. | localhost |
| --mysql-port | MySQL server port (in case TCP/IP connection should be used) | 3306 |
| --mysql-socket | Unix socket file to communicate with the MySQL server |
|
| --mysql-user | MySQL user | user |
| --mysql-password | MySQL password |
|
| --mysql-db | MySQL database name. Note SysBench will not automatically create this database. You should create it manually and grant the appropriate privileges to a user which will be used to access the test table. | sbtest |
| --mysql-table-engine | Type of the test table. Possible values: myisam, innodb, heap, ndbcluster, bdb, maria, falcon, pbxt | innodb |
| --mysql-ssl | Use SSL connections. | no |
| --myisam-max-rows | MAX_ROWS option for MyISAM tables (required for big tables) | 1000000 |
| --mysql-create-options | Additional options passed to CREATE TABLE. |
|
Example usage:
| $ sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock prepare $ sysbench --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --oltp-read-only run |
The first commandwill create a MyISAM table 'sbtest' in a database 'sbtest' on a MySQL serverusing /tmp/mysql.sock socket, then fill this table with 1M records. The second command will runthe actual benchmark with 16 client threads, limiting the total number ofrequest by 100,000.