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函数下,本函数正是下面参数实现的方式:
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 可以发现要合理的设置这个值并不那么容易并且要求较高。
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,某些文章具有时效性,若有错误或已失效,请在网站留言或联系站长:17tui@17tui.com