Setting innodb_thread_concurrency to 0 dynamically can result in limited performance

  I'm messing around with a sysbench test (32 threads) and innodb_thread_concurrency and seeing something weird. The server is PS 5.6 on a EC2 m3.xlarge. The sysbench is just oltp.lua with 32 threads with 20 tables at 5m rows each.

If I set innodb_thread_concurrency to 32, I get good throughput, close to 1k tps:

[ 67s] threads: 32, tps: 979.00, reads: 13762.01, writes: 3919.00, response time: 86.93ms (95%), errors: 0.00, reconnects: 0.00
[ 68s] threads: 32, tps: 986.83, reads: 13792.67, writes: 3939.33, response time: 68.54ms (95%), errors: 0.00, reconnects: 0.00
[ 69s] threads: 32, tps: 994.17, reads: 13878.32, writes: 3978.66, response time: 82.45ms (95%), errors: 0.00, reconnects: 0.00
[ 70s] threads: 32, tps: 978.96, reads: 13733.47, writes: 3914.85, response time: 70.99ms (95%), errors: 0.00, reconnects: 0.00
[ 71s] threads: 32, tps: 928.03, reads: 12942.44, writes: 3712.13, response time: 80.16ms (95%), errors: 0.00, reconnects: 0.00

If I change it to 1, I get an expected performance drop:

[ 72s] threads: 32, tps: 381.00, reads: 5266.02, writes: 1521.00, response time: 147.23ms (95%), errors: 0.00, reconnects: 0.00
[ 73s] threads: 32, tps: 364.00, reads: 5133.00, writes: 1454.00, response time: 172.54ms (95%), errors: 0.00, reconnects: 0.00
[ 74s] threads: 32, tps: 353.00, reads: 4992.00, writes: 1418.00, response time: 179.76ms (95%), errors: 0.00, reconnects: 0.00
[ 75s] threads: 32, tps: 351.00, reads: 4887.01, writes: 1402.00, response time: 192.28ms (95%), errors: 0.00, reconnects: 0.00

However, (and this is the weird part), if I set it to 0, I get even worse performance:

[ 80s] threads: 32, tps: 289.00, reads: 4037.98, writes: 1156.00, response time: 4.51ms (95%), errors: 0.00, reconnects: 0.00
[ 81s] threads: 32, tps: 213.00, reads: 2989.99, writes: 852.00, response time: 13.53ms (95%), errors: 0.00, reconnects: 0.00
[ 82s] threads: 32, tps: 282.00, reads: 3941.02, writes: 1128.00, response time: 4.77ms (95%), errors: 0.00, reconnects: 0.00
[ 83s] threads: 32, tps: 289.00, reads: 4050.00, writes: 1156.00, response time: 4.39ms (95%), errors: 0.00, reconnects: 0.00
[ 84s] threads: 32, tps: 296.00, reads: 4144.01, writes: 1184.00, response time: 4.40ms (95%), errors: 0.00, reconnects: 0.00

(this sustains indefinitely if I let it)

Ok, so if I set it back to 32, it looks good again:

[ 92s] threads: 32, tps: 977.00, reads: 13789.07, writes: 3921.02, response time: 93.24ms (95%), errors: 0.00, reconnects: 0.00
[ 93s] threads: 32, tps: 980.93, reads: 13713.03, writes: 3920.72, response time: 80.16ms (95%), errors: 0.00, reconnects: 0.00
[ 94s] threads: 32, tps: 998.05, reads: 13937.74, writes: 3990.21, response time: 91.42ms (95%), errors: 0.00, reconnects: 0.00
[ 95s] threads: 32, tps: 989.66, reads: 13882.27, writes: 3965.65, response time: 67.00ms (95%), errors: 0.00, reconnects: 0.00

Now if I set it to 0 again, I get slightly *better* performance than 32:

[ 104s] threads: 32, tps: 1015.02, reads: 14277.31, writes: 4063.09, response time: 35.49ms (95%), errors: 0.00, reconnects: 0.00
[ 105s] threads: 32, tps: 1015.98, reads: 14192.75, writes: 4050.93, response time: 37.69ms (95%), errors: 0.00, reconnects: 0.00
[ 106s] threads: 32, tps: 1005.83, reads: 14114.62, writes: 4036.32, response time: 42.87ms (95%), errors: 0.00, reconnects: 0.00
[ 107s] threads: 32, tps: 1005.23, reads: 14126.23, writes: 4089.93, response time: 45.05ms (95%), errors: 0.00, reconnects: 0.00

The above are all dynamic changes to innodb_thread_concurrency (SET GLOBAL ...) while sysbench was running.

If I cleanly restart the server with innodb_thread_concurrency=0, I get the high performance I expect.

I was curious, so I checked the SHOW ENGINE INNODB STATUS data about the thread queue in each case. I did a fresh restart and then (with sysbench oltp 32 threads running again):

ip-10-0-0-247 mysql> show global variables like 'innodb_thread_concurrency';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 0 |
+---------------------------+-------+
1 row in set (0.00 sec)

ip-10-0-0-247 mysql> pager grep -A6 "ROW OPERATIONS"
PAGER set to 'grep -A6 "ROW OPERATIONS"'

ip-10-0-0-247 mysql> show engine innodb status\G
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
32 read views open inside InnoDB
32 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
32 out of 1000 descriptors used
1 row in set (0.00 sec)

ip-10-0-0-247 mysql> set global innodb_thread_concurrency=1;
Query OK, 0 rows affected (0.00 sec)

ip-10-0-0-247 mysql> show engine innodb status\G
ROW OPERATIONS
--------------
0 queries inside InnoDB, 30 queries in queue
31 read views open inside InnoDB
31 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
31 out of 1000 descriptors used
1 row in set (0.00 sec)

ip-10-0-0-247 mysql> set global innodb_thread_concurrency=0;
Query OK, 0 rows affected (0.00 sec)

ip-10-0-0-247 mysql> show engine innodb status\G
ROW OPERATIONS
--------------
0 queries inside InnoDB, 31 queries in queue
31 read views open inside InnoDB
31 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
31 out of 1000 descriptors used
1 row in set (0.00 sec)

So on startup with concurrency = 0, I see no queued queries. However, if I set the concurrency to a positive integer and then set it _back_ to 0, I see queries still getting queued. This is Percona-Server-server-56-5.6.24-rel72.2.el7.x86_64