) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:6717

#8 0x00000000005aa206 in handler::multi_range_read_next (this=0x7fb9880e33a0, range_info=0x7fbb3c353400) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:5871

#9 0x0000000000804acb in QUICK_RANGE_SELECT::get_next (this=0x7fb988002050) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/opt_range.cc:10644

#10 0x000000000082ae2d in rr_quick (info=0x7fb98809c210) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/records.cc:369

#11 0x00000000006d44fd in sub_select (join=0x7fb98809a728, join_tab=0x7fb98809c180, end_of_records=) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_executor.cc:1259

#12 0x00000000006d2823 in do_select (join=0x7fb98809a728) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_executor.cc:936

#13 JOIN::exec (this=0x7fb98809a728) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_executor.cc:194

```

好了有了这些栈帧视乎发现一些共同点他们都处于innobase_srv_conc_enter_innodb函数下，本函数正是下面参数实现的方式：

- innodb_thread_concurrency

- innodb_concurrency_tickets

所以我随即告诉他检查这两个参数，如果设置了可以尝试取消。过后数据库故障得到解决。

###三、参数和相关说明

实际上涉及到的参数主要是innodb_thread_concurrency和innodb_concurrency_tickets。将高压力下线程之间抢占CPU而造成线程上下文切换的情况尽量阻塞在Innodb层之外，这就需要innodb_thread_concurrency参数了。同时又要保证对于那些(长时间处理线程)不会长时间的堵塞(短时间处理线程)，比如某些select操作需要查询很久，而某些select操作查询量很小，如果等待(长时间的select操作)结束后(短时间select操作)才执行，那么显然会出现(短时间select操作)饥饿问题，换句话说对(短时间select操作)是不公平的， 因此就引入了innodb_concurrency_tickets参数。

#####1、innodb_thread_concurrency

同一时刻能够进入Innodb层的会话(线程)数。如果在Innodb层干活的会话(线程)数量超过这个参数的设置，新会话(线程)将不能从MySQL层进入到Innodb层，它们将进入一个短暂的睡眠状态。休眠多久则通过参数innodb_thread_sleep_delay参数指定，如果还设置了参数innodb_adaptive_max_sleep_delay那么Innodb将会自动调整休眠时间，具体的算法实际上就在srv_conc_enter_innodb_with_atomics函数中，感兴趣的可以执行查看。

其次这种休眠实际上是一个定时醒来的时钟，通过::nanosleep或者select(多路IO转接函数)进行实现，定时唤醒后会话(线程)重新判断是否可以进入Innodb层。函数os_thread_sleep部分如下：

```

#elif defined(HAVE_NANOSLEEP)

struct timespect;

t.tv_sec = tm / 1000000;

t.tv_nsec = (tm % 1000000) * 1000;

::nanosleep(&t, NULL);

#else

struct timeval t;

t.tv_sec = tm / 1000000;

t.tv_usec = tm % 1000000;

select(0, NULL, NULL, NULL, &t);

```

关于到底如何设置这个值，官方文档有如下建议：

```

Use the following guidelines to help find and maintain an appropriate setting:

- If the number of concurrent user threads for a workload is less than 64, set

innodb_thread_concurrency=0.

- If your workload is consistently heavy or occasionally spikes, start by setting

innodb_thread_concurrency=128 and then lowering the value to 96, 80, 64, and so on, until

you find the number of threads that provides the best performance. For example, suppose your

system typically has 40 to 50 users, but periodically the number increases to 60, 70, or even 200.

You find that performance is stable at 80 concurrent users but starts to show a regression above

this number. In this case, you would set innodb_thread_concurrency=80 to avoid impacting

performance.

- If you do not want InnoDB to use more than a certain number of virtual CPUs for user threads

(20 virtual CPUs, for example), set innodb_thread_concurrency to this number (or possibly

lower, depending on performance results). If your goal is to isolate MySQL from other applications,

you may consider binding the mysqld process exclusively to the virtual CPUs. Be aware,

however, that exclusive binding could result in non-optimal hardware usage if the mysqld process

is not consistently busy. In this case, you might bind the mysqld process to the virtual CPUs but

also allow other applications to use some or all of the virtual CPUs.

- innodb_thread_concurrency values that are too high can cause performance regression due

to increased contention on system internals and resources.

- In some cases, the optimal innodb_thread_concurrency setting can be smaller than the

number of virtual CPUs.

- Monitor and analyze your system regularly. Changes to workload, number of users, or computing

environment may require that you adjust the innodb_thread_concurrency setting

```

可以发现要合理的设置这个值并不那么容易并且要求较高。

#####2、innodb_concurrency_tickets

实际上这里的tickets可以理解为MySQL层和Innodb层交互的次数，比如一个select一条数据就是需要Innodb层返回一条数据然后MySQL层进行where条件的过滤然后返回给客户端，抛开where条件过滤的情况，如果我们一条语句需要查询100条数据，那么实际上需要进入Innodb层100次，那么实际上消耗的tickets就是100。当然对于insert select这种操作，需要的tickets是普通select的两倍，因为查询需要进入Innodb层一次，insert需要再次进入Innodb层一次，后面我们就使用insert select的方式来模拟堵塞的情况，最后还会给出说明。

这样我们也就理解为什么innodb_concurrency_tickets可以避免(长时间处理线程)长时间堵塞(短时间处理线程)的原因了。假设innodb_concurrency_tickets为5000(默认值)，有一个需要查询100W行数据的大select操作和一个需要查询100行数据的小select操作，大select操作先进行，但是当查询了5000行数据后将丢失CPU使用权，小select操作将会进行并且一次性完成。

最后关于这里涉及的参数可以继续参考官方文档中的说明，我们线上并没有设置这些参数，因为感觉很难设置合适，如果设置不当反而会遇到问题，就如本案例一样。

#####3、事务操作状态

实际上如果是处于这种堵塞情况，我们完全可以在information_schema.innodb_trx和show engine innodb status中看到如下：

```

---TRANSACTION 162307, ACTIVE 133 sec sleeping before entering InnoDB (这里)

mysql tables in use 2, locked 2

767 lock struct(s), heap size 106968, 212591 row lock(s), undo log entries 15451

MySQL thread id 14, OS thread handle 140736751912704, query id 1077 localhost root Sending data

insert into testui select * from testui

---TRANSACTION 162302, ACTIVE 320 sec, thread declared inside InnoDB 1

mysql tables in use 2, locked 2

2477 lock struct(s), heap size 336344, 609049 row lock(s), undo log entries 83582

MySQL thread id 13, OS thread handle 140737153779456, query id 1050 localhost root Sending data

insert into testti3 select * from testti3

mysql> select trx_id,trx_state,trx_query,trx_operation_state,trx_concurrency_tickets from information_schema.innodb_trx \G

*************************** 1. row ***************************

trx_id: 84325

trx_state: RUNNING

trx_query: insert into baguait4 select * from testgp

trx_operation_state: sleeping before entering InnoDB(这里)

trx_concurrency_tickets: 0

*************************** 2. row ***************************

trx_id: 84319

trx_state: RUNNING

trx_query: insert into baguait3 select * from testgp

trx_operation_state: sleeping before entering InnoDB

trx_concurrency_tickets: 0

```

我们可以看到事务操作状态被标记为‘sleeping before entering InnoDB’。但是需要注意一点的是对于只读事务比如select操作而言，show engine innodb status可能看不到。但是遗憾的是案例中朋友并没有采集trx_operation_state的值。

###四、模拟测试

这里我们简单模拟，我们一共启用3个事务，其中两个insert select操作，一个单纯的select操作，当然这里的都是耗时操作，涉及的表每个表都有大概100W的数据。

同时为了方便观察我们需要设置参数：

- innodb_thread_concurrency=1

- innodb_concurrency_tickets=10

操作步骤如下：

|S1|S2|S3|

|-|-|-|

|insert into baguait4 select * from testgp|||

||insert into baguait3 select * from testgp||

|||select * from baguait1|

如果多观察几次你可以看到如下的现象：

```

mysql> select trx_id,trx_state,trx_query,trx_operation_state,trx_concurrency_tickets from information_schema.innodb_trx \G show processlist;

*************************** 1. row ***************************

trx_id: 84529

trx_state: RUNNING

trx_query: insert into baguait4 select * from testgp

trx_operation_state: sleeping before entering InnoDB

trx_concurrency_tickets: 0

*************************** 2. row ***************************

trx_id: 84524

trx_state: RUNNING

trx_query: insert into baguait3 select * from testgp

trx_operation_state: inserting

trx_concurrency_tickets: 1

*************************** 3. row ***************************

trx_id: 422211785606640

trx_state: RUNNING

trx_query: select * from baguait1

trx_operation_state: sleeping before entering InnoDB

trx_concurrency_tickets: 0

3 rows in set (0.00 sec)

+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+

| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |

+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+

| 1 | event_scheduler | localhost | NULL | Daemon | 3173 | Waiting on empty queue | NULL | 0 | 0 |

| 6 | root | localhost | testmts | Query | 70 | Sending data | insert into baguait3 select * from testgp | 0 | 0 |

| 7 | root | localhost | testmts | Query | 68 | Sending data | insert into baguait4 select * from testgp | 0 | 0 |

| 8 | root | localhost | testmts | Query | 66 | Sending data | select * from baguait1 | 120835 | 0 |

| 9 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 |

+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+

5 rows in set (0.00 sec)

mysql>

mysql>

mysql>

mysql>

mysql> select trx_id,trx_state,trx_query,trx_operation_state,trx_concurrency_tickets from information_schema.innodb_trx \G show processlist;

*************************** 1. row ***************************

trx_id: 84529

trx_state: RUNNING

trx_query: insert into baguait4 select * from testgp

trx_operation_state: sleeping before entering InnoDB

trx_concurrency_tickets: 0

*************************** 2. row ***************************

trx_id: 84524

trx_state: RUNNING

trx_query: insert into baguait3 select * from testgp

trx_operation_state: sleeping before entering InnoDB

trx_concurrency_tickets: 0

*************************** 3. row ***************************

trx_id: 422211785606640

trx_state: RUNNING

trx_query: select * from baguait1

trx_operation_state: fetching rows

trx_concurrency_tickets: 3

3 rows in set (0.00 sec)

+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+

| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |

+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+

| 1 | event_scheduler | localhost | NULL | Daemon | 3177 | Waiting on empty queue | NULL | 0 | 0 |

| 6 | root | localhost | testmts | Query | 74 | Sending data | insert into baguait3 select * from testgp | 0 | 0 |

| 7 | root | localhost | testmts | Query | 72 | Sending data | insert into baguait4 select * from testgp | 0 | 0 |

| 8 | root | localhost | testmts | Query | 70 | Sending data | select * from baguait1 | 128718 | 0 |

| 9 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 |

+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+

5 rows in set (0.00 sec)

```

我们可以观察到trx_operation_state的状态3个操作都在交替的变化，但是总有2个处于‘sleeping before entering InnoDB’状态。并且我们可以观察到trx_concurrency_tickets总是不会大于10的。因此我们有理由相信在同一时刻只有一个操作进入了Innodb层。但是需要注意的是在show engine innodb status中观察不到select的操作如下：

```

------------

TRANSACTIONS

------------

Trx id counter 84538

Purge done for trx's n:o < 84526 undo n:o < 0 state: running but idle

History list length 356

Total number of lock structs in row lock hash table 0

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 422211785609424, not started

0 lock struct(s), heap size 1160, 0 row lock(s)

---TRANSACTION 422211785608032, not started

0 lock struct(s), heap size 1160, 0 row lock(s)

---TRANSACTION 84529, ACTIVE 103 sec inserting, thread declared inside InnoDB 6

mysql tables in use 2, locked 1

1 lock struct(s), heap size 1160, 0 row lock(s), undo log entries 111866

MySQL thread id 7, OS thread handle 140737158833920, query id 80 localhost root Sending data

insert into baguait4 select * from testgp

Trx read view will not see trx with id >= 84529, sees < 84524

---TRANSACTION 84524, ACTIVE 105 sec sleeping before entering InnoDB

mysql tables in use 2, locked 1

1 lock struct(s), heap size 1160, 0 row lock(s), undo log entries 105605

MySQL thread id 6, OS thread handle 140737159034624, query id 79 localhost root Sending data

insert into baguait3 select * from testgp

Trx read view will not see trx with id >= 84524, sees < 84524

```

但是我们还需要注意show engine innodb status有如下输出第一行说明了有2个会话(线程)堵塞在Innodb层以外。

```

--------------

ROW OPERATIONS

--------------

1 queries inside InnoDB, 2 queries in queue

3 read views open inside InnoDB

2 RW transactions active inside InnoDB

```

###五、实现方法

前面我们已经描述了每次MySQL层和Innodb层的交互都会进行一次这样的判断，它用来决定会话(线程)是否能够进入Innodb层，下面就是大概的逻辑，由函数innobase_srv_conc_enter_innodb调入。

```

->是否设置了参数innodb_thread_concurrency

->是

->是否tickets大于0

->是、直接进入Innodb层并且tickets减1

->否、调入函数srv_conc_enter_innodb

->调入函数srv_conc_enter_innodb_with_atomics

->开启死循环

->是否活跃线程数小于innodb_thread_concurrency设置

->是、增加活跃线程数，并且自动调整delay参数，退出死循环，满tickets进入Innodb层

->否、自动调整delay参数后设置事务操作状态为"sleeping before entering InnoDB"，然后进入休眠状态知道时间达到后重新醒来继续循环

->否、直接进入Innodb层

```

我们可以看到这个实现方式，在Inndob以外的会话(线程)会一直等待直到Inndob层内活跃的线程数小于innodb_thread_concurrency为止，并且每次进入Innodb层都会将tickets减1。

###其他：关于insert select操作消耗tickets的说明

这里额外说明一下，因为我在测试的时候看了一下，对于一行数据而言首先需要select查询出来然后再insert插入到表中，这里实际上一行数据涉及到进入Innodb层两次，那么就需要消耗2个tickets，下面留下两个栈帧供自己后面参考：

#####1、insert select查询数据进入Innodb层

```

#0 innobase_srv_conc_enter_innodb (prebuilt=0x7ffedcb98d10) at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:1740

#1 0x0000000001a53f7c in ha_innobase::general_fetch (this=0x7ffedcb9d760, buf=0x7ffedc9469b0 "\375\n", direction=1, match_mode=0)

at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9846

#2 0x0000000001a545ee in ha_innobase::rnd_next (this=0x7ffedcb9d760, buf=0x7ffedc9469b0 "\375\n")

at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:10083

#3 0x0000000000f836d6 in handler::ha_rnd_next (this=0x7ffedcb9d760, buf=0x7ffedc9469b0 "\375\n") at /mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:3146

#4 0x00000000014e2a55 in rr_sequential (info=0x7ffedcb4f120) at /mysqldata/percona-server-locks-detail-5.7.22/sql/records.cc:521

#5 0x0000000001581277 in sub_select (join=0x7ffedcb4ea20, qep_tab=0x7ffedcb4f0d0, end_of_records=false)

at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1280

#6 0x0000000001580be6 in do_select (join=0x7ffedcb4ea20) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:950

#7 0x000000000157eaa2 in JOIN::exec (this=0x7ffedcb4ea20) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:199

#8 0x0000000001620327 in handle_query (thd=0x7ffedc012960, lex=0x7ffedc014f90, result=0x7ffedcc46680, added_options=1342177280, removed_options=0)

at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_select.cc:185

#9 0x000000000180466d in Sql_cmd_insert_select::execute (this=0x7ffedcc46608, thd=0x7ffedc012960)

```

#####2、insert select插入数据进入Innodb层

```

#0 innobase_srv_conc_enter_innodb (prebuilt=0x7ffedcb9c6f0) at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:1740

#1 0x0000000001a50587 in ha_innobase::write_row (this=0x7ffedc946470, record=0x7ffedcb78d00 "\375\n")

at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:8341

#2 0x0000000000f9041d in handler::ha_write_row (this=0x7ffedc946470, buf=0x7ffedcb78d00 "\375\n") at /mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:8466

#3 0x00000000018004b9 in write_record (thd=0x7ffedc012960, table=0x7ffedcb8f940, info=0x7ffedcc466c8, update=0x7ffedcc46740)

at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_insert.cc:1881

#4 0x00000000018019b9 in Query_result_insert::send_data (this=0x7ffedcc46680, values=...) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_insert.cc:2279

#5 0x00000000015853a8 in end_send (join=0x7ffedcb4ea20, qep_tab=0x7ffedcb4f248, end_of_records=false)

at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:2925

#6 0x0000000001581f71 in evaluate_join_record (join=0x7ffedcb4ea20, qep_tab=0x7ffedcb4f0d0) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1645

#7 0x0000000001581372 in sub_select (join=0x7ffedcb4ea20, qep_tab=0x7ffedcb4f0d0, end_of_records=false)

at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1297

#8 0x0000000001580be6 in do_select (join=0x7ffedcb4ea20) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:950

#9 0x000000000157eaa2 in JOIN::exec (this=0x7ffedcb4ea20) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:199

#10 0x0000000001620327 in handle_query (thd=0x7ffedc012960, lex=0x7ffedc014f90, result=0x7ffedcc46680, added_options=1342177280, removed_options=0)

at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_select.cc:185

#11 0x000000000180466d in Sql_cmd_insert_select::execute (this=0x7ffedcc46608, thd=0x7ffedc012960)

```

实际上插入数据正是在查询完数据后调用函数evaluate_join_record的时候，通过回调了函数Query_result_insert::send_data来实现，这点和单纯的select不一样单纯的select这里调入是函数Query_result_send::send_data如下：

```

#0 Query_result_send::send_data (this=0x7ffedcc465f8, items=...) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_class.cc:2915

#1 0x00000000015853a8 in end_send (join=0x7ffedcb4e930, qep_tab=0x7ffedcb4f4b0, end_of_records=false)

at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:2925

#2 0x0000000001581f71 in evaluate_join_record (join=0x7ffedcb4e930, qep_tab=0x7ffedcb4f338) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1645

#3 0x0000000001581372 in sub_select (join=0x7ffedcb4e930, qep_tab=0x7ffedcb4f338, end_of_records=false)

at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1297

#4 0x0000000001580be6 in do_select (join=0x7ffedcb4e930) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:950

#5 0x000000000157eaa2 in JOIN::exec (this=0x7ffedcb4e930) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:199

#6 0x0000000001620327 in handle_query (thd=0x7ffedc012960, lex=0x7ffedc014f90, result=0x7ffedcc465f8, added_options=0, removed_options=0)

at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_select.cc:185

#7 0x00000000015d1f77 in execute_sqlcom_select (thd=0x7ffedc012960, all_tables=0x7ffedcc45cf0) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5445

```

**作者微信：gp_22389860**