Description:
The OS is ubuntu-server-14.04-x64.
The configuration is:
mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
| 10000 |
+-------------------+
1 row in set (0.00 sec)
mysql> select @@innodb_commit_concurrency;
+-----------------------------+
| @@innodb_commit_concurrency |
+-----------------------------+
| 1000 |
+-----------------------------+
1 row in set (0.00 sec)
mysql> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 5242880 |
+---------------------------+
1 row in set (0.00 sec)
And I run the OLTP test of sysbench-0.4.12 with the following command:
sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-user=root --max-time=10 --oltp-test-mode=complex --max-requests=0 --num-threads=100 --mysql-password=123 prepare
sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-user=root --max-time=10 --oltp-test-mode=complex --max-requests=0 --num-threads=100 --mysql-password=123 run
sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-user=root --max-time=10 --oltp-test-mode=complex --max-requests=0 --num-threads=100 --mysql-password=123 cleanup
everything is OK:
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 100
Doing OLTP test.
Running mixed OLTP 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
Threads started!
Time limit exceeded, exiting...
(last message repeated 99 times)
Done.
OLTP test statistics:
queries performed:
read: 66976
write: 23414
other: 9374
total: 99764
transactions: 4590 (456.20 per sec.)
deadlocks: 194 (19.28 per sec.)
read/write requests: 90390 (8983.83 per sec.)
other operations: 9374 (931.68 per sec.)
Test execution summary:
total time: 10.0614s
total number of events: 4590
total time taken by event execution: 1003.0695
per-request statistics:
min: 1.30ms
avg: 218.53ms
max: 1602.04ms
approx. 95 percentile: 433.93ms
Threads fairness:
events (avg/stddev): 45.9000/3.18
execution time (avg/stddev): 10.0307/0.01
However, when I increase the thread number and table size with the following command, the test hangs:
sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=root --max-time=10 --oltp-test-mode=complex --max-requests=0 --num-threads=1000 --mysql-password=123 prepare
sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=root --max-time=10 --oltp-test-mode=complex --max-requests=0 --num-threads=1000 --mysql-password=123 run
sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=root --max-time=10 --oltp-test-mode=complex --max-requests=0 --num-threads=1000 --mysql-password=123 cleanup
The shell just stop there:
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 1000
Doing OLTP test.
Running mixed OLTP 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
Threads started!
when I configure the innodb_commit_concurrency=0, all commands run OK.
How to repeat:
run the sysbench command above.
Suggested fix:
Maybe this is because the innodb_commit_concurrency=1000 is insufficient for 1000 threads, but i can accept that the performance is not good, but it should not hang.
Am I right please help.
Thanks!