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