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!
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!