精品免费在线观看-精品欧美-精品欧美成人bd高清在线观看-精品欧美高清不卡在线-精品欧美日韩一区二区

17站長網

17站長網 › 首頁 ›數據庫› Mysql › 查看內容

MySQL一個innodb_thread_concurrency設定不當引發的故障

2023-3-16 14:15| 查看: 1514 |來源: 互聯網

一、問題來源歡迎關注我的《深入理解MySQL主從原理 32講 》，如下：這是一個朋友問我的典型案例。整個故障現象表現為，MySQL數據庫頻繁的出現大量的請求不能響應 ...
              一、問題來源
              歡迎關注我的《深入理解MySQL主從原理 32講 》，如下：
              這是一個朋友問我的典型案例。整個故障現象表現為，MySQL數據庫頻繁的出現大量的請求不能響應。下面是一些他提供的證據：
              1、show processlist
              從狀態信息來看出現如下情況：
              insert操作：狀態為update
              update/delete操作：狀態為updating
              select操作：狀態為sending data
              因此可以推斷應該是語句執行期間出現了問題，由于篇幅原因只給出一部分，并且我將語句部分也做了相應截斷：
               
              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 INTO
              2、系統IO/CPU
              從vmstat來看，CPU使用不大，而IO也在可以接受的范圍內（vmstat wa%不高且b列為0）如下：
               
              vmstat--------------------------------------
              procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
               r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
               2  0 927300 3057100      0 53487316    0    0     5   192    0    0  3  1 96  0  0
              iostat--------------------------------------
              Linux 3.10.0-693.el7.x86_64 (fang-data1)     09/23/2019     _x86_64_    (32 CPU)
              avg-cpu:  %user   %nice %system %iowait  %steal   %idle
                         2.72    0.00    0.52    0.45    0.00   96.31
              Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
              sdb               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.61
              sda              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.19
              sdc              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.60
              sde               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.32
              sdd              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.78
              md126             0.00     0.00   11.30  468.80   164.79  5216.64    22.42     0.00    0.00    0.00    0.00   0.00   0.00
              dm-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.74
              dm-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.01
              dm-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
              這就比較奇怪了，一般來說數據庫不能及時響應請求很大可能是由于系統負載過高。如果說DML還可能是Innodb鎖造成的堵塞，但是大量sending data狀態下的select操作一般可能都和系統負載過高有聯系，但是這里系統負載還在可以接受的范圍內。
               
              二、pstack分析
              借助pstack查看線程的棧幀，查看pstack發現如下（由于篇幅限制只給出部分說明問題的部分）：
               
              1、insert 線程：
               
              Thread 85 (Thread 0x7fbb0d42b700 (LWP 20174)):
              #0  0x00007fbfae164c73 in select () from /lib64/libc.so.6
              #1  0x0000000000987c0f in os_thread_sleep (tm=<optimized out>) 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=<optimized out>, 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 81 (Thread 0x7fbb24b67700 (LWP 27490)):
              #0  0x00007fbfae164c73 in select () from /lib64/libc.so.6
              #1  0x0000000000987c0f in os_thread_sleep (tm=<optimized out>) 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/377", key_ptr=<optimized out>, key_len=<optimized out>, find_flag=<optimized out>) 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=<optimized out>, 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=<optimized out>, end_key=<optimized out>, eq_range_arg=<optimized out>, sorted=<optimized out>) 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=<optimized out>, fields=..., values=..., conds=0x7fb9400009c8, order_num=<optimized out>, order=<optimized out>, 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 66 (Thread 0x7fbb3c355700 (LWP 16028)):
              #0  0x00007fbfae164c73 in select () from /lib64/libc.so.6
              #1  0x0000000000987c0f in os_thread_sleep (tm=<optimized out>) 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/377/377", key_ptr=<optimized out>, key_len=<optimized out>, find_flag=<optimized out>) 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=<optimized out>, 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=<optimized out>, end_key=<optimized out>, eq_range_arg=<optimized out>, sorted=<optimized out>) 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=<optimized out>) 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 timespec    t;
                  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)
              

本文最后更新于 2023-3-16 14:15，某些文章具有時效性，若有錯誤或已失效，請在網站留言或聯系站長：[email protected]

              ·END·
              

              站長網微信號：w17tui，關注站長、創業、關注互聯網人 - 互聯網創業者營銷服務中心
              

              


              
    
              免責聲明：本站部分文章和圖片均來自用戶投稿和網絡收集，旨在傳播知識，文章和圖片版權歸原作者及原出處所有，僅供學習與參考，請勿用于商業用途，如果損害了您的權利，請聯系我們及時修正或刪除。謝謝！
              
            
              

              

              


              
                
              17站長網微信二維碼
              
                
              始終以前瞻性的眼光聚焦站長、創業、互聯網等領域，為您提供最新最全的互聯網資訊，幫助站長轉型升級，為互聯網創業者提供更加優質的創業信息和品牌營銷服務，與站長一起進步！讓互聯網創業者不再孤獨！
              
                
               掃一掃，關注站長網微信
              
                
            
              


              

              

              
    
              
        
              
            
              
                
              
                    
              
                        
              大家都在看
              
                    
              
                
              
                
              
                    
              
                        
                
                    
                
                
                
            
                
        
                
        


                
    

                

                

                

                

                
    
                

                


                

                
    
                

                

                

                

                熱門排行
                

                

                

                

                  

                  

                  

                  

                  

                  

                  

                  

                  最近更新
                  

                  

                  

                  

                    

                    

                    

                    

                    
    
                    

                    

                    

                    


    


                    

                    

                    








                    










主站蜘蛛池模板：
黄色aaaaa
|
久久9966精品国产免费
|
在线永久免费观看的毛片
|
a级网站|
免费一区二区三区视频狠狠
|
久久国模
|
中国黄色免费网站
|
久cao在线香蕉69影院
|
国产成人99久久亚洲综合精品
|
99爱在线精品视频免费观看9
|
婷婷色在线播放
|
亚洲国产精品一区二区三区久久
|
a免费视频
|
久久本道综合色狠狠五月
|
五月久久亚洲七七综合中文网
|
欧美亚洲一区二区三区
|
成人午夜看片
|
日韩精品一区二区三区中文3d
|
曰曰啪天天拍视频在线
|
亚洲欧美h
|
免费网站看v片在线成人国产系列
|
婷婷色爱区综合五月激情韩国
|
亚洲欧美综合国产不卡
|
色婷婷亚洲十月十月色天
|
韩国三级一线观看久
|
久久天天躁狠狠躁夜夜中文字幕
|
黄网站在线免费
|
黄色片在线播放
|
亚洲精品天堂一区二区三区
|
亚洲欧美日产综合一区二区三区
|
中国人免费观看高清在线观看二区
|
丁香婷婷激情综合
|
免费国产成人高清在线观看视频
|
精品香蕉99久久久久网站
|
9l国产精品久久久久麻豆
|
朴妮唛19禁福利视频在线
|
免费中文字幕不卡视频
|
最新精品视频在线观看
|
九九99九九视频在线观看
|
成人黄色免费网站
|
91亚洲国产在人线播放午夜
|