Benchmarking: Sysbench (OLTP)
Installation and Setup
apt-get | yum install sysbench
apt-get | yum install mysql-serverCreate a database for sysbench to use
mysqladmin -u root create sbtestCreate entries in the database
# on centos6, i needed to specify the db driver, on ubuntu 12.04 i didnt
sysbench --test=oltp --db-driver=mysql --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-user=root prepareRun 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
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.00Note: from the run, all processes running about 80-85%
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.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400042 (8865.43 per sec.)
other operations: 200006 (1266.49 per sec.)And once more with 16 threads, performance slowed down
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.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400014 (8179.52 per sec.)
other operations: 200002 (1168.50 per sec.)Clean and Restart
mysqladmin drop sbtest (enter y)
mysqladmin create sbtestResults
- 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