Shulou(Shulou.com)06/01 Report--

Source code version: 5.7.22

I. the source of the problem

Welcome to my "in-depth understanding of MySQL Master and Slave principle 32", as follows:

If the picture cannot be displayed, please see the following link:

Https://www.jianshu.com/p/d636215d767f

This is a typical case that a friend asked me. The whole fault phenomenon is that a large number of requests can not be responded to frequently in MySQL database. Here are some of the evidence he provided:

1 、 show processlist

Judging from the status information, the following occurs:

Insert operation: status is updateupdate/delete operation: status is updatingselect operation: status is sending data

Therefore, it can be inferred that there was a problem during the execution of the statement, only part of it was given because of the length, and I truncated the statement part accordingly:

Show processlist--.11827639 root dbmis Execute 9 updating UPDATE 17224594 root dbmis Execute 8 Sending data SELECT sum (exchange_coin) as exchange_coin FROM 17224595 root dbmis Execute 8 update INSERT INTO 17224596 root dg Execute 8 update INSERT INTO 17224597 root dbmis Execute 8 update INSERT INTO 17224598 root Dbmis Execute 7 update INSERT INTO 17224599 root dbmis Execute 7 Sending data SELECT COUNT (*) AS tp_count FROM 17224600 root dg Execute 7 update INSERT INTO 17224601 root dbmis Execute 6 update INSERT INTO 17224602 root dbmis Execute 6 Sending data SELECT sum (exchange_coin) as exchange_coin FROM 17224606 root dbmis Execute 5 update INSERT INTO 17224619 root dbmis Execute 2 update INSERT INTO 17224620 root dbmis Execute 2 update INSERT INTO 17224621 root dbmis Execute 2 Sending data SELECT sum (exchange_coin) as exchange_coin 17224622 root dg Execute 2 update INSERT INTO 17224623 root dbmis Execute 1 update INSERT INTO 17224624 root dbmis Execute 1 update INSERT INTO 17224625 root dg Execute 1 update INSERT INTO 17224626 root dbmis Execute 0 update INSERT INTO2, System IO/CPU

From the perspective of vmstat, CPU is not very useful, and IO is within an acceptable range (vmstat wa% is not high and b is listed as 0) as follows:

Vmstat--procs-memory--swap---io-----system---cpu- r b swpd free buff cache si so bi bo in cs us Sy id wa st 20 927300 3057100 0 53487316 00 5 192 00 96 0 0iostat--Linux 3.10.0-693.el7.x86_64 (fang-data1) 09 take 23 CPU 2019 _ x86 triple 6432 avg-cpu:% user% nice% system% Iowait% steal% idle 2.72 0.00 0.52 0.45 0.00 96.31Device: rrqm/s wrqm/s rUnip s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm% utilsdb 9.73 11.28 3.93 264.54 415.23 2624.20 22.64 0.25 0.93 3.25 0.90 0.80 21.61sda 10.13 11.59 6.34 264.22 450.68 2624.20 22.73 0.01 0.05 2.55 1.00 0.93 25.19sdc 11.60 11.36 5.03 263.12 453.02 2592.44 22.71 0.17 0.62 5.08 0 . 53 0.81 21.60sde 0.01 0.10 0.11 160.45 6.69 920.23 11.55 0.16 1.01 1.80 1.01 0.83 13.32sdd 11.26 11.30 2.23 263.18 412.90 2592.44 22.65 0.17 0.65 10.37 0.56 0.82 21.78md126 0.00 0.00 11.30 468.80 164.79 5216.64 22.42 0.00 0.00 0.00 0.00dm-0 0.00 0.00 0.11 58.80 6.69 920.23 31.47 0.15 2.56 1.96 2.56 2.16 12. 74dm-1 0.00 0.00 0.06 0.08 0.24 0.31 8.00 0.01 41.80 1.20 72.78 0.83 0.01dm-2 0.00 0.00 11.24 408.66 164.55 5216.33 25.63 0.14 0.32 1.02 0.30 0.46 19.29

This is strange. Generally speaking, the failure of the database to respond to requests in a timely manner is probably due to the high load on the system. If DML may also be caused by Innodb locks, a large number of select operations in the sending data state may generally be associated with high system load, but the system load is still within an acceptable range.

2. Pstack analysis

Look at the stack frame of the thread with the help of pstack, and look at the pstack findings as follows (due to space limitations, only part of the problem is given):

1. Insert thread:

Thread 85 (Thread 0x7fbb0d42b700 (LWP 20174)): # 0 0x00007fbfae164c73 in select () from / lib64/libc.so.6#1 0x0000000000987c0f in os_thread_sleep (tm=) at / home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/os/os0thread.cc:287#2 0x00000000009e4dea in srv_conc_enter_innodb_with_atomics (trx=trx@entry=0x7fba4802f9c8) at / home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc: 276'3 srv_conc_enter_innodb (trx=trx@entry=0x7fba4802f9c8) at / home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc:511#4 0x000000000093b948 in innobase_srv_conc_enter_innodb (trx=0x7fba4802f9c8) at / home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:1280#5 ha_innobase::write_row (this=0x7fb8440ab260 Record=0x7fb8440ab650 "") at / home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:6793#6 0x00000000005b440f in handler::ha_write_row (this=0x7fb8440ab260, buf=0x7fb8440ab650 "") at / home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:7351#7 0x00000000006dd3a8 in write_record (thd=thd@entry=0x1d396c90, table=table@entry=0x7fb8440aa970, info=info@entry=0x7fbb0d429400) Update=update@entry=0x7fbb0d429480) at / home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_insert.cc:1667#8 0x00000000006e2541 in mysql_insert (thd=thd@entry=0x1d396c90, table_list=, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_REPLACE Ignore=false) at / home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_insert.cc:1072#9 0x00000000006fa90a in mysql_execute_command (thd=thd@entry=0x1d396c90) at / home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_parse.cc:3500

2. Update thread

Thread 81 (Thread 0x7fbb24b67700 (LWP 27490)): # 0 0x00007fbfae164c73 in select () from / lib64/libc.so.6#1 0x0000000000987c0f in os_thread_sleep (tm=) at / home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/os/os0thread.cc:287#2 0x00000000009e4dea in srv_conc_enter_innodb_with_atomics (trx=trx@entry=0x7fb94003c608) at / home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc: 276'3 srv_conc_enter_innodb (trx=trx@entry=0x7fb94003c608) at / home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc:511#4 0x000000000093ae4e in innobase_srv_conc_enter_innodb (trx=0x7fb94003c608) at / home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:1280#5 ha_innobase::index_read (this=0x7fb95c05b540 Buf=0x7fb95c2ae4f0 "\ 377\ 377", key_ptr=, key_len=, find_flag=) at / home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:7675#6 0x00000000005ab6e0 in ha_index_read_map (find_flag=HA_READ_KEY_EXACT, keypart_map=3, key=0x7fb940017048 "7\ 307\ 017e\ 257h", buf= This=0x7fb95c05b540) at / home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:2753#7 handler::read_range_first (this=0x7fb95c05b540, start_key=, end_key=, eq_range_arg=, sorted=) at / home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:6717#8 0x00000000005aa206 in handler::multi_range_read_next (this=0x7fb95c05b540 Range_info=0x7fbb24b65240) at / home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:5871#9 0x0000000000804acb in QUICK_RANGE_SELECT::get_next (this=0x7fb94000f720) at / home/install/lnmp1.5/src/mysql-5.6.40/sql/opt_range.cc:10644#10 0x000000000082ae2d in rr_quick (info=0x7fbb24b65410) at / home/install/lnmp1.5/src/mysql-5.6.40/sql/records.cc:369#11 0x0000000000766e1b in mysql_update (thd=thd@entry=0x1d1f2250, table_list=) Fields=..., values=..., conds=0x7fb9400009c8, order_num=, order=, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=found_return@entry=0x7fbb24b65800, updated_return=updated_return@entry=0x7fbb24b65d60) at / home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_update.cc:744

3. Select thread

Thread 66 (Thread 0x7fbb3c355700 (LWP 16028)): # 0 0x00007fbfae164c73 in select () from / lib64/libc.so.6#1 0x0000000000987c0f in os_thread_sleep (tm=) at / home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/os/os0thread.cc:287#2 0x00000000009e4dea in srv_conc_enter_innodb_with_atomics (trx=trx@entry=0x7fb988354858) at / home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc: 276'3 srv_conc_enter_innodb (trx=trx@entry=0x7fb988354858) at / home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc:511#4 0x000000000093ae4e in innobase_srv_conc_enter_innodb (trx=0x7fb988354858) at / home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:1280#5 ha_innobase::index_read (this=0x7fb9880e33a0 Buf=0x7fb988351b50 "377 / 377", key_ptr=, key_len=, find_flag=) at / home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:7675#6 0x00000000005ab6e0 in ha_index_read_map (find_flag=HA_READ_AFTER_KEY, keypart_map=7, key=0x7fb988134a48 "", buf=, this=0x7fb9880e33a0) at / home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:2753#7 handler::read_range_first (this=0x7fb9880e33a0) Start_key=, end_key=, eq_range_arg=, sorted=) 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

All right, with these stack frames, it depends on finding something in common. They are all under the innobase_srv_conc_enter_innodb function, which is how the following parameters are implemented:

Innodb_thread_concurrencyinnodb_concurrency_tickets

So I immediately told him to check these two parameters and try to cancel if they were set. After that, the database failure was solved.

Parameters and related instructions

Actually, the main parameters involved are innodb_thread_concurrency and innodb_concurrency_tickets. The context switching caused by preempting CPU between threads under high pressure is blocked outside the Innodb layer as far as possible, which requires the innodb_thread_concurrency parameter. At the same time, it is necessary to ensure that for those (long-time processing threads) that will not be blocked for a long time (short-time processing threads), for example, some select operations need to be queried for a long time, while the query amount of some select operations is very small. If you wait for the end of (long-time select operations) (short-time select operations) before execution, then obviously (short-time select operations) hunger will occur, in other words, it is unfair to (short-time select operations). So the innodb_concurrency_tickets parameter is introduced.

1 、 innodb_thread_concurrency

The number of sessions (threads) that can enter the InnoDB layer at the same time. If the number of sessions (threads) working in the Innodb layer exceeds the setting of this parameter, new sessions (threads) will not be able to enter the Innodb layer from the MySQL layer, and they will enter a short sleep state. The sleep time is specified by the parameter innodb_thread_sleep_delay. If the parameter innodb_adaptive_max_sleep_delay is also set, Innodb will automatically adjust the sleep time. The specific algorithm is actually in the srv_conc_enter_innodb_with_atomics function, which can be viewed if you are interested.

Secondly, this kind of dormancy is actually a clock that wakes up regularly, which is realized by: nanosleep or select (multiple IO transfer function). After regular awakening, the session (thread) re-judges whether it can enter the Innodb layer. The os_thread_sleep section of the function is as follows:

# elif defined (HAVE_NANOSLEEP) struct timespec t; t.tv_sec = tm / 1000000; t.tv_nsec = (tm% 1000000) * 1000000;:: nanosleep (& t, NULL); # else struct timeval t; t.tv_sec = tm / 1000000; t.tv_usec = tm% 1000000; select (0, NULL, & t)

As to how to set this value, the official documentation has the following suggestions:

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, setinnodb_thread_concurrency=0.- If your workload is consistently heavy or occasionally spikes, start by settinginnodb_thread_concurrency=128 and then lowering the value to 96, 80, 64, and so on, untilyou find the number of threads that provides the best performance. For example, suppose yoursystem 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 abovethis number. In this case, you would set innodb_thread_concurrency=80 to avoid impactingperformance.- 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 possiblylower, 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 processis not consistently busy. In this case, you might bind the mysqld process to the virtual CPUs butalso allow other applications to use some or all of the virtual CPUs.- innodb_thread_concurrency values that are too high can cause performance regression dueto increased contention on system internals and resources.- In some cases, the optimal innodb_thread_concurrency setting can be smaller than thenumber of virtual CPUs.- Monitor and analyze your system regularly. Changes to workload, number of users, or computingenvironment may require that you adjust the innodb_thread_concurrency setting

You can find that it is not so easy and demanding to set this value reasonably.

2 、 innodb_concurrency_tickets

In fact, the tickets here can be understood as the number of interactions between the MySQL layer and the Innodb layer. For example, for a select, a piece of data needs to be returned by the Innodb layer, and then the MySQL layer filters the where condition and then returns it to the client. Put aside the case of where conditional filtering, if we need to query 100 pieces of data in a statement, we actually need to enter the Innodb layer 100 times, then the actual consumption of tickets is 100. Of course, for the operation of insert select, you need twice as much tickets as ordinary select, because the query needs to enter the Innodb layer once, and the insert needs to enter the Innodb layer again. Later, we will use insert select to simulate the congestion, and we will give an explanation at last.

In this way, we can understand why innodb_concurrency_tickets can avoid long-time jams (long-time processing threads) and short-time processing threads. Assuming that innodb_concurrency_tickets is 5000 (default), there is a large select operation that needs to query 100W rows of data and a small select operation that needs to query 100W rows of data. The big select operation is carried out first, but when 5000 rows of data are queried, the right to use CPU will be lost, and the small select operation will be carried out and completed at one time.

Finally, you can continue to refer to the official documentation for the parameters involved here. We do not set these parameters online because we find it difficult to set them properly. If we do not set them properly, we will encounter problems, as in this case.

3. Transaction operation status

In fact, if you are in this kind of congestion, we can see the following in information_schema.innodb_trx and show engine innodb status:

-TRANSACTION 162307, ACTIVE 2767 sec sleeping before entering InnoDB (here) mysql tables in use 2, locked 2767 lock struct (s), heap size 106968, 212591 row lock (s), undo log entries 15451MySQL thread id 14, OS thread handle 140736751912704, query id 1077 localhost root Sending datainsert into testui select * from testui---TRANSACTION 162302, ACTIVE 320 sec, thread declared inside InnoDB 1mysql tables in use 2, locked 22477 lock struct (s), heap size 336344,609049 row lock (s), undo log entries 83582MySQL thread id 13, OS thread handle 140737153779456 Query id 1050 localhost root Sending datainsert into testti3 select * from testti3mysql > select trx_id,trx_state,trx_query,trx_operation_state Trx_concurrency_tickets from information_schema.innodb_trx\ RUNNING trx_query * 1. Row * * trx_id: 84325 trx_state: RUNNING trx_query: insert into baguait4 select * from testgp trx_operation_state: Sleeping before entering InnoDB (here) trx_concurrency_tickets: zero * 2. Row * * trx_id: 84319 trx_state: RUNNING trx_query: insert into baguait3 select * from testgp trx_operation_state: sleeping before entering InnoDBtrx_concurrency_tickets: 0

We can see that the transaction operation status is marked as' sleeping before entering InnoDB'. However, it is important to note that for read-only transactions such as select operations, show engine innodb status may not be able to see it. But unfortunately, the friend in the case did not collect the value of trx_operation_state.

Fourth, simulation test

Here we simply simulate, we enable a total of three transactions, including two insert select operations, a simple select operation, of course, these are time-consuming operations, each table involved has about 100W of data.

At the same time, in order to facilitate observation, we need to set parameters:

Innodb_thread_concurrency=1innodb_concurrency_tickets=10

The steps are as follows:

S1S2S3insert into baguait4 select * from testgpinsert into baguait3 select * from testgpselect * from baguait1

If you look at it a few more times, you can see the following phenomena:

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 InnoDBtrx_concurrency_tickets: 0 * * * 2. Row * trx_id: 84524 trx_state: RUNNING trx_query: insert into baguait3 select * from testgp trx_operation_state: insertingtrx_concurrency_tickets: 1 * * 3. Row * * trx_id: 422211785606640 trx_state: RUNNING trx_query: select * from baguait1 trx_operation_state: sleeping before entering InnoDBtrx_concurrency_tickets: 03 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 > 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 InnoDBtrx_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 InnoDBtrx_concurrency_tickets: 0 * * 3. Row * * trx_id: 422211785606640 trx_state: RUNNING trx_query: select * from baguait1 trx_operation_state: fetching rowstrx_concurrency_tickets: 33 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)

We can observe that the state of the trx_operation_state changes alternately among the three operations, but there are always two in the 'sleeping before entering InnoDB' state. And we can observe that the trx_concurrency_tickets is never greater than 10. So we have reason to believe that only one operation enters the Innodb layer at a time. However, it should be noted that the operations of select that are not observed in show engine innodb status are as follows:

-TRANSACTIONS-Trx id counter 84538Purge done for trx's NRO

< 84526 undo n:o < 0 state: running but idleHistory list length 356Total number of lock structs in row lock hash table 0LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 422211785609424, not started0 lock struct(s), heap size 1160, 0 row lock(s)---TRANSACTION 422211785608032, not started0 lock struct(s), heap size 1160, 0 row lock(s)---TRANSACTION 84529, ACTIVE 103 sec inserting, thread declared inside InnoDB 6mysql tables in use 2, locked 11 lock struct(s), heap size 1160, 0 row lock(s), undo log entries 111866MySQL thread id 7, OS thread handle 140737158833920, query id 80 localhost root Sending datainsert into baguait4 select * from testgpTrx read view will not see trx with id >

= 84529, sees

< 84524---TRANSACTION 84524, ACTIVE 105 sec sleeping before entering InnoDBmysql tables in use 2, locked 11 lock struct(s), heap size 1160, 0 row lock(s), undo log entries 105605MySQL thread id 6, OS thread handle 140737159034624, query id 79 localhost root Sending datainsert into baguait3 select * from testgpTrx 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 queue3 read views open inside InnoDB2 RW transactions active inside InnoDB五、实现方法 前面我们已经描述了每次MySQL层和Innodb层的交互都会进行一次这样的判断，它用来决定会话（线程）是否能够进入Innodb层，下面就是大概的逻辑，由函数innobase_srv_conc_enter_innodb调入。 ->

Whether the parameters innodb_thread_concurrency-> Yes-> whether tickets is greater than 0-> Yes, enter the Innodb layer directly and tickets minus 1-> No, Call function srv_conc_enter_innodb-> call function srv_conc_enter_innodb_with_atomics-> Open dead loop-> whether the number of active threads is less than the innodb_thread_concurrency setting-> Yes, increase the number of active threads And automatically adjust the delay parameters, exit the endless loop, enter the Innodb layer with full tickets-> No, set the transaction operation status to "sleeping before entering InnoDB" after automatically adjusting the delay parameters, and then enter the dormancy state until the time is up and wake up to continue the cycle-> No, directly enter the Innodb layer

We can see that in this implementation, sessions (threads) outside the Inndob wait until the number of active threads in the Inndob layer is less than innodb_thread_concurrency, and the tickets is subtracted by 1 each time it enters the Innodb layer.

Other: instructions on insert select operation consuming tickets

Here's an additional explanation, because when I was testing, I saw that for a row of data, we first need to query it by select and then insert it into the table by insert. In fact, a row of data is involved in entering the Innodb layer twice, so you need to consume 2 tickets. Leave two stack frames for your reference:

1. Insert select query data into the InnoDB layer # 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 "\ 375n") 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 insert data into InnoDB layer # 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)

In fact, when the function evaluate_join_record is called after querying the data, the function Query_result_insert::send_data is called back to achieve this, which is different from the simple select. The simple select is called into the function Query_result_send::send_data as follows:

# 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

Author Wechat: gp_22389860

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.