Sysbench – Simulated Database Testing

man sysbench

oltp
This test mode was written to benchmark a real database performance. At the prepare
stage the following table is created in the specified database (sbtest by default):

Example usage: (THESE DON'T WORK AS IS - A DB NEEDS CREATING FIRST - SEE BELOW)

$ sysbench --test=oltp --mysql-table-type=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 command will create a MyISAM table 'sbtest' in a database 'sbtest' on a MySQL
server using /tmp/mysql.sock socket, then fill this table with 1M records. The second
command will run the actual benchmark with 16 client threads, limiting the total number
of request by 100,000.

--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. (default: sbtest)

AUTHOR
Alexey Kopytov <kaamos@users.sourceforge.net>
Author.

First you have to install mysql:

sudo apt-get install mysql-server-5.5

Now create a db and grant all privileges to a user. Log in to mysql as root:

su root

mysql -u root -p

mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.06 sec)

create database sbtest ;

mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sbtest |
+--------------------+
4 rows in set (0.00 sec)

grant all privileges on *.* to 'root'@'localhost' ;

mysql> quit
Bye

As the first and second example lines above do not work in Mint, this is no longer a DB "simulation" but tests a real DB you had to create above. I used my 32bit slowest PC so just 3 for comparison. This DB test may be useful if looking to transfer a DB to another server and get an idea how well it will perform compared the the old one before you move it etc...

minimint stevee # sysbench --test=oltp --mysql-user=root --mysql-password=xxxxxx --mysql-db=sbtest --oltp-table-size=1000000 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'...

sysbench --num-threads=16 --max-requests=100000 --test=oltp --mysql-user=root --mysql-password=xxxxx --mysql-db=sbtest --oltp-table-size=1000000 --oltp-read-only 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: 16

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
Maximum number of requests for OLTP test is limited to 100000
Threads started!

OLTP test statistics:
queries performed:
read: 1400000
write: 0
other: 200000
total: 1600000
transactions: 100000 (183.14 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400000 (2563.96 per sec.)
other operations: 200000 (366.28 per sec.)

Test execution summary:
total time: 546.0298s
total number of events: 100000
total time taken by event execution: 8735.0764
per-request statistics:
min: 3.09ms
avg: 87.35ms
max: 456.36ms
approx. 95 percentile: 123.10ms

Threads fairness:
events (avg/stddev): 6250.0000/24.90
execution time (avg/stddev): 545.9423/0.02

Comparison 1, the HP64:

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

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
Maximum number of requests for OLTP test is limited to 100000
Threads started!
Done.

OLTP test statistics:
queries performed:
read: 1400000
write: 0
other: 200000
total: 1600000
transactions: 100000 (431.29 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400000 (6038.00 per sec.)
other operations: 200000 (862.57 per sec.)

Test execution summary:
total time: 231.8647s
total number of events: 100000
total time taken by event execution: 3708.6049
per-request statistics:
min: 3.19ms
avg: 37.09ms
max: 131.28ms
approx. 95 percentile: 49.82ms

Threads fairness:
events (avg/stddev): 6250.0000/66.88
execution time (avg/stddev): 231.7878/0.01

Comparison 2, the Dell64:

Maximum number of requests for OLTP test is limited to 100000
Threads started!
Done.

OLTP test statistics:
queries performed:
read: 1400000
write: 0
other: 200000
total: 1600000
transactions: 100000 (589.12 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400000 (8247.70 per sec.)
other operations: 200000 (1178.24 per sec.)

Test execution summary:
total time: 169.7443s
total number of events: 100000
total time taken by event execution: 2714.8378
per-request statistics:
min: 1.76ms
avg: 27.15ms
max: 107.29ms
approx. 95 percentile: 38.89ms

Threads fairness:
events (avg/stddev): 6250.0000/59.86
execution time (avg/stddev): 169.6774/0.00

Comparison 3, the Intel HP64:

OLTP test statistics:
queries performed:
read: 1400000
write: 0
other: 200000
total: 1600000
transactions: 100000 (272.75 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400000 (3818.54 per sec.)
other operations: 200000 (545.51 per sec.)

Test execution summary:
total time: 366.6323s
total number of events: 100000
total time taken by event execution: 5865.1829
per-request statistics:
min: 1.49ms
avg: 58.65ms
max: 209.58ms
approx. 95 percentile: 94.70ms

Threads fairness:
events (avg/stddev): 6250.0000/77.06
execution time (avg/stddev): 366.5739/0.01

HP Intel Dual 64 2GBDell 64 6GBHP Pav 64 4GBHP Pav 32 1GB
 read/write requests: 3818.54 per sec 8247.70 per sec6038.00 per sec 2563.96 per sec
total time: 366.6323s 169.7443s 231.8647s 546.0298s

Comments are closed.

Post Navigation