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...") |
(No difference)
|
Revision as of 13:52, 8 August 2012
- 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