Bug #91220 oltp test hang when innodb_commit_concurrency is set to 1000
Submitted: 12 Jun 2018 7:21 Modified: 12 Jul 2018 20:40
Reporter: haochen he Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:5.6.33 OS:Any
Assigned to: CPU Architecture:Any

[12 Jun 2018 7:21] haochen he
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!
[12 Jun 2018 7:40] haochen he
sorry, that happen just once, when i restart the mysqld it disappears
[12 Jun 2018 20:40] MySQL Verification Team
Then the issue it's not repeatable according your prior comment?. Thanks.
[13 Jul 2018 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".