Benchmarking: Sysbench (OLTP) - Revision history

David: /* Run Tests */

2012-08-08T14:06:55Z

Run Tests

David: /* Installation and Setup */

2012-08-08T13:58:40Z

Installation and Setup

David at 13:57, 8 August 2012

2012-08-08T13:57:13Z

David: Created page with "# apt-get install sysbench # apt-get install mysql-server # create a database for sysbench to use mysqladmin -u root create sbtest # create entries in the database root@cal2..."

2012-08-08T13:52:17Z

Created page with "# apt-get install sysbench # apt-get install mysql-server # create a database for sysbench to use mysqladmin -u root create sbtest # create entries in the database root@cal2..."

New page

# apt-get install sysbench
# apt-get install mysql-server

# create a database for sysbench to use
mysqladmin -u root create sbtest

# create entries in the database
root@cal2:~# sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-user=root 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'...

# on centos6, i needed to specify the db driver
sysbench --test=oltp --db-driver=mysql --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-user=root prepare

# run test
root@cal2:~# sysbench --mysql-user=root --num-threads=4 --max-requests=100000 --test=oltp --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: 4

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 "LOCK TABLES READ" 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: 1400042
write: 0
other: 200006
total: 1600048
transactions: 100003 (634.95 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400042 (8889.25 per sec.)
other operations: 200006 (1269.89 per sec.)

Test execution summary:
total time: 157.4982s
total number of events: 100003
total time taken by event execution: 628.0861
per-request statistics:
min: 5.87ms
avg: 6.28ms
max: 40.13ms
approx. 95 percentile: 6.72ms

Threads fairness:
events (avg/stddev): 25000.7500/48.17
execution time (avg/stddev): 157.0215/0.00

# note from the run, all processes running about 80-85%
# increased threads to 8, performance virtually identical
root@cal2:~# sysbench --mysql-user=root --num-threads=8 --max-requests=100000 --test=oltp --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: 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 "LOCK TABLES READ" 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: 1400042
write: 0
other: 200006
total: 1600048
transactions: 100003 (633.24 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400042 (8865.43 per sec.)
other operations: 200006 (1266.49 per sec.)

Test execution summary:
total time: 157.9216s
total number of events: 100003
total time taken by event execution: 1261.1887
per-request statistics:
min: 5.96ms
avg: 12.61ms
max: 104.49ms
approx. 95 percentile: 27.20ms

Threads fairness:
events (avg/stddev): 12500.3750/525.47
execution time (avg/stddev): 157.6486/0.03

# and once more with 16 threads
root@cal2:~# sysbench --mysql-user=root --num-threads=16 --max-requests=100000 --test=oltp --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 "LOCK TABLES READ" 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: 1400014
write: 0
other: 200002
total: 1600016
transactions: 100001 (584.25 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400014 (8179.52 per sec.)
other operations: 200002 (1168.50 per sec.)

Test execution summary:
total time: 171.1610s
total number of events: 100001
total time taken by event execution: 2736.1917
per-request statistics:
min: 6.04ms
avg: 27.36ms
max: 189.89ms
approx. 95 percentile: 57.89ms

Threads fairness:
events (avg/stddev): 6250.0625/594.77
execution time (avg/stddev): 171.0120/0.02

# clean out database and start again:
mysqladmin drop sbtest (enter y)
mysqladmin create sbtest

# ht on - sb node
# updated sb node with an ssd (Samsung ones, same as the calxeda nodes) 3k tps
[root@head mysql]# sysbench --db-driver=mysql --mysql-user=root --num-threads=4 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --oltp-read-only run
sysbench 0.4.12: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 4

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 "LOCK TABLES READ" 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: 1400028
write: 0
other: 200004
total: 1600032
transactions: 100002 (3163.79 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400028 (44293.09 per sec.)
other operations: 200004 (6327.58 per sec.)

Test execution summary:
total time: 31.6083s
total number of events: 100002
total time taken by event execution: 126.0993
per-request statistics:
min: 0.98ms
avg: 1.26ms
max: 5.37ms
approx. 95 percentile: 1.43ms

Threads fairness:
events (avg/stddev): 25000.5000/205.23
execution time (avg/stddev): 31.5248/0.00

# using more threads 8
[root@head mysql]# sysbench --db-driver=mysql --mysql-user=root --num-threads=8 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --oltp-read-only run
sysbench 0.4.12: multi-threaded system evaluation benchmark

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 "LOCK TABLES READ" 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: 1400042
write: 0
other: 200006
total: 1600048

transactions: 100003 (4073.25 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400042 (57025.48 per sec.)
other operations: 200006 (8146.50 per sec.)

Test execution summary:
total time: 24.5512s
total number of events: 100003
total time taken by event execution: 196.0180
per-request statistics:
min: 1.12ms
avg: 1.96ms
max: 12.73ms
approx. 95 percentile: 2.50ms

Threads fairness:
events (avg/stddev): 12500.3750/129.68
execution time (avg/stddev): 24.5022/0.00

# 16 threads, seems worse than 8 (32 virtual cores total)
[root@head mysql]# sysbench --db-driver=mysql --mysql-user=root --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --oltp-read-only run
sysbench 0.4.12: multi-threaded system evaluation benchmark

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 "LOCK TABLES READ" 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: 1400070
write: 0
other: 200010
total: 1600080
transactions: 100005 (3666.75 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400070 (51334.50 per sec.)
other operations: 200010 (7333.50 per sec.)

Test execution summary:
total time: 27.2735s
total number of events: 100005
total time taken by event execution: 435.9056
per-request statistics:

min: 1.92ms
avg: 4.36ms
max: 19.55ms
approx. 95 percentile: 5.07ms

Threads fairness:
events (avg/stddev): 6250.3125/48.18
execution time (avg/stddev): 27.2441/0.00

# all virtual core 32 threads
[root@head mysql]# sysbench --db-driver=mysql --mysql-user=root --num-threads=32 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --oltp-read-only run
sysbench 0.4.12: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32

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 "LOCK TABLES READ" 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: 1400126
write: 0
other: 200018
total: 1600144
transactions: 100009 (3542.14 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400126 (49589.91 per sec.)
other operations: 200018 (7084.27 per sec.)

Test execution summary:
total time: 28.2341s
total number of events: 100009
total time taken by event execution: 902.8747
per-request statistics:
min: 4.69ms
avg: 9.03ms
max: 46.63ms
approx. 95 percentile: 10.09ms

Threads fairness:
events (avg/stddev): 3125.2812/112.22
execution time (avg/stddev): 28.2148/0.00

min: 1.92ms
avg: 4.36ms
max: 19.55ms
approx. 95 percentile: 5.07ms

Threads fairness:
events (avg/stddev): 6250.3125/48.18
execution time (avg/stddev): 27.2441/0.00

# all virtual core 32 threads
[root@head mysql]# sysbench --db-driver=mysql --mysql-user=root --num-threads=32 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --oltp-read-only run
sysbench 0.4.12: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32

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 "LOCK TABLES READ" 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: 1400126
write: 0
other: 200018
total: 1600144
transactions: 100009 (3542.14 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400126 (49589.91 per sec.)
other operations: 200018 (7084.27 per sec.)

Test execution summary:
total time: 28.2341s
total number of events: 100009
total time taken by event execution: 902.8747
per-request statistics:
min: 4.69ms
avg: 9.03ms
max: 46.63ms
approx. 95 percentile: 10.09ms

Threads fairness:
events (avg/stddev): 3125.2812/112.22
execution time (avg/stddev): 28.2148/0.00

min: 1.92ms
avg: 4.36ms
max: 19.55ms
approx. 95 percentile: 5.07ms

Threads fairness:
events (avg/stddev): 6250.3125/48.18
execution time (avg/stddev): 27.2441/0.00

# all virtual core 32 threads
[root@head mysql]# sysbench --db-driver=mysql --mysql-user=root --num-threads=32 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --oltp-read-only run
sysbench 0.4.12: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32

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 "LOCK TABLES READ" 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: 1400126
write: 0
other: 200018
total: 1600144
transactions: 100009 (3542.14 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400126 (49589.91 per sec.)
other operations: 200018 (7084.27 per sec.)

Test execution summary:
total time: 28.2341s
total number of events: 100009
total time taken by event execution: 902.8747
per-request statistics:
min: 4.69ms
avg: 9.03ms
max: 46.63ms
approx. 95 percentile: 10.09ms

Threads fairness:
events (avg/stddev): 3125.2812/112.22
execution time (avg/stddev): 28.2148/0.00

@@ Line 2: / Line 2: @@
 <syntaxhighlight>
-   apt-get install sysbench
+   apt-get | yum install sysbench
-   apt-get install mysql-server
+   apt-get | yum install mysql-server
 </syntaxhighlight>

@@ Line 60: / Line 60: @@
      events (avg/stddev):           25000.7500/48.17
      execution time (avg/stddev):   157.0215/0.00
-# note from the run, all processes running about 80-85%
+<syntaxhighlight>
-# increased threads to 8, performance virtually identical
+  sysbench --mysql-user=root --num-threads=8 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --oltp-read-only run
 ...
      transactions:                        100003 (633.24 per sec.)
@@ Line 69: / Line 73: @@
      read/write requests:                 1400042 (8865.43 per sec.)
      other operations:                    200006 (1266.49 per sec.)
-Test execution summary:
+And once more with 16 threads, performance slowed down
-Threads fairness:
+<syntaxhighlight>
-    events (avg/stddev):           12500.3750/525.47
+   sysbench --mysql-user=root --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --oltp-read-only run
 ...
      transactions:                        100001 (584.25 per sec.)
@@ Line 91: / Line 84: @@
      read/write requests:                 1400014 (8179.52 per sec.)
      other operations:                    200002 (1168.50 per sec.)
+</syntaxhighlight>
 == Clean and Restart ==
@@ Line 112: / Line 91: @@
    mysqladmin create sbtest
 </syntaxhighlight>
 == Results ==

@@ Line 1: / Line 1: @@
-# apt-get install sysbench
+== Installation and Setup ==
-# create a database for sysbench to use
+<syntaxhighlight>
-mysqladmin -u root create sbtest
+  apt-get install sysbench
-# create entries in the database
+Create a database for sysbench to use
-root@cal2:~# sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-user=root prepare
+<syntaxhighlight>
-sysbench 0.4.12:  multi-threaded system evaluation benchmark
+  mysqladmin -u root create sbtest
+</syntaxhighlight>
-# on centos6, i needed to specify the db driver
+Create entries in the database
 sysbench --test=oltp --db-driver=mysql --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-user=root prepare
-# run test
+== Run Tests ==
 root@cal2:~# sysbench --mysql-user=root --num-threads=4 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --oltp-read-only run
 sysbench 0.4.12:  multi-threaded system evaluation benchmark
@@ Line 62: / Line 64: @@
 # increased threads to 8, performance virtually identical
 root@cal2:~# sysbench --mysql-user=root --num-threads=8 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --oltp-read-only run
-sysbench 0.4.12:  multi-threaded system evaluation benchmark
+...
      transactions:                        100003 (633.24 per sec.)
      deadlocks:                           0      (0.00 per sec.)
@@ Line 105: / Line 86: @@
 # and once more with 16 threads
 root@cal2:~# sysbench --mysql-user=root --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --oltp-read-only run
-sysbench 0.4.12:  multi-threaded system evaluation benchmark
+...
      transactions:                        100001 (584.25 per sec.)
      deadlocks:                           0      (0.00 per sec.)
@@ Line 147: / Line 107: @@
-# clean out database and start again:
+== Clean and Restart ==
-mysqladmin drop sbtest (enter y)
+<syntaxhighlight>
-mysqladmin create sbtest
+  mysqladmin drop sbtest (enter y)
 # ht on - sb node
 # updated sb node with an ssd (Samsung ones, same as the calxeda nodes) 3k tps