update setup_instruments se..."/>
溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

隨筆：MySQL setup_instruments中關(guān)于部分信息不能修改

發(fā)布時(shí)間：2020-08-09 13:51:17 來(lái)源：ITPUB博客 閱讀：248 作者：gaopengtttt 欄目：MySQL數(shù)據(jù)庫(kù)

朋友告訴我如下操作不能修改


mysql> update setup_instruments set enabled='no' where name='memory/performance_schema/table_handles';
Query OK, 1 row affected (2.61 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from setup_instruments  where name='memory/performance_schema/table_handles';
+-----------------------------------------+---------+-------+
| NAME                                    | ENABLED | TIMED |
+-----------------------------------------+---------+-------+
| memory/performance_schema/table_handles | YES     | NO    |
+-----------------------------------------+---------+-------+
1 row in set (0.00 sec)

我測(cè)試發(fā)現(xiàn)所有memory/performance_schema/* 的值都不能更改，但是其他值可以更改。。8.0.17依然如此。

既然不能修改則跟一下update接口，我一共跟蹤了：

  • table_setup_instruments::update_row_values：修改接口
  • table_setup_instruments::make_row：update_enabled 變量傳入值
  • table_setup_instruments::rnd_next()：update_enabled 定義值

幾個(gè)接口。

一、為什么不能修改

查看table_setup_instruments::update_row_values函數(shù)你會(huì)發(fā)現(xiàn)memory/performance_schema/* 這幾行值這里都會(huì)進(jìn)入如下邏輯：

case 1: /* ENABLED */
        /* Do not raise error if m_update_enabled is false, silently ignore. */
        if (m_row.m_update_enabled) //這里是 false
        {
          value= (enum_yes_no) get_field_enum(f);
          m_row.m_instr_class->m_enabled= (value == ENUM_YES) ? true : false;
        }
        break;

因?yàn)閙_row.m_update_enabled==false 因此不能修改。其他的值這里是true。
這里我們也會(huì)看到實(shí)際上值只有兩個(gè)YES或者是NO，不能是其他值。如果update修改為其他值會(huì)直接報(bào)錯(cuò)。

二、m_update_enabled來(lái)源

也就是table_setup_instruments::rnd_next()函數(shù)進(jìn)行判斷如果是VIEW_BUILTIN_MEMORY則會(huì)設(shè)置update_enabled為false，具體如下：

case pos_setup_instruments::VIEW_BUILTIN_MEMORY: 
      update_enabled= false;//這里設(shè)置了false
      update_timed= false;
...

當(dāng)然何為VIEW_BUILTIN_MEMORY，不太清楚，沒仔細(xì)看了。。

最后本表訪問是全表掃描方式。因?yàn)樯蠈咏涌跒閔andler::ha_rnd_next，其含義為如下：

The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
源碼函數(shù)解釋：Reads the next row in a table scan (also used to read the FIRST row in a table scan).
全表掃描訪問下一條數(shù)據(jù)

debug會(huì)發(fā)現(xiàn)不斷的會(huì)訪問下一條數(shù)據(jù)。最后performance_schema是一個(gè)獨(dú)立的引擎，雖然很簡(jiǎn)單。

三、備用棧幀

1、修改數(shù)據(jù)


#0  PFS_engine_table::update_row (this=0x7ffe7c1026c0, table=0x7ffe7c1b0370, old_buf=0x7ffe7c1b13f8 "'", new_buf=0x7ffe7c1b1270 "'", fields=0x7ffe7c1b1580)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/perfschema/pfs_engine_table.cc:573
#1  0x0000000001942680 in ha_perfschema::update_row (this=0x7ffe7c1b0d70, old_data=0x7ffe7c1b13f8 "'", new_data=0x7ffe7c1b1270 "'")
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/perfschema/ha_perfschema.cc:293
#2  0x0000000000f90b70 in handler::ha_update_row (this=0x7ffe7c1b0d70, old_data=0x7ffe7c1b13f8 "'", new_data=0x7ffe7c1b1270 "'")
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/handler.cc:8509
#3  0x000000000168ca00 in mysql_update (thd=0x7ffe7c012940, fields=..., values=..., limit=18446744073709551615, handle_duplicates=DUP_ERROR, 
    found_return=0x7fffec0f4bd8, updated_return=0x7fffec0f4bd0) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_update.cc:887
#4  0x0000000001692f28 in Sql_cmd_update::try_single_table_update (this=0x7ffe7c008f78, thd=0x7ffe7c012940, switch_to_multitable=0x7fffec0f4c7f)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_update.cc:2896
#5  0x0000000001693475 in Sql_cmd_update::execute (this=0x7ffe7c008f78, thd=0x7ffe7c012940) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_update.cc:3023
#6  0x00000000015cc8e9 in mysql_execute_command (thd=0x7ffe7c012940, first_level=true) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:3756
#7  0x00000000015d30c6 in mysql_parse (thd=0x7ffe7c012940, parser_state=0x7fffec0f6600) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901
#8  0x00000000015c6c5a in dispatch_command (thd=0x7ffe7c012940, com_data=0x7fffec0f6d70, command=COM_QUERY)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490

2、讀取數(shù)據(jù)

#0  table_setup_instruments::make_row (this=0x7ffe7c1026c0, klass=0x2f2e3c0, update_enabled=true, update_timed=true)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/perfschema/table_setup_instruments.cc:260
#1  0x00000000019a4b1f in table_setup_instruments::rnd_next (this=0x7ffe7c1026c0)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/perfschema/table_setup_instruments.cc:172
#2  0x0000000001942ab2 in ha_perfschema::rnd_next (this=0x7ffe7c1b0d70, buf=0x7ffe7c1b1270 "")
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/perfschema/ha_perfschema.cc:351
#3  0x0000000000f83812 in handler::ha_rnd_next (this=0x7ffe7c1b0d70, buf=0x7ffe7c1b1270 "") at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/handler.cc:3146
#4  0x00000000014e2b3d in rr_sequential (info=0x7fffec0f4870) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/records.cc:521
#5  0x000000000168c7b3 in mysql_update (thd=0x7ffe7c012940, fields=..., values=..., limit=18446744073709551615, handle_duplicates=DUP_ERROR, 
    found_return=0x7fffec0f4bd8, updated_return=0x7fffec0f4bd0) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_update.cc:811
#6  0x0000000001692f28 in Sql_cmd_update::try_single_table_update (this=0x7ffe7c008f78, thd=0x7ffe7c012940, switch_to_multitable=0x7fffec0f4c7f)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_update.cc:2896
#7  0x0000000001693475 in Sql_cmd_update::execute (this=0x7ffe7c008f78, thd=0x7ffe7c012940) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_update.cc:3023
#8  0x00000000015cc8e9 in mysql_execute_command (thd=0x7ffe7c012940, first_level=true) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:3756
#9  0x00000000015d30c6 in mysql_parse (thd=0x7ffe7c012940, parser_state=0x7fffec0f6600) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901
#10 0x00000000015c6c5a in dispatch_command (thd=0x7ffe7c012940, com_data=0x7fffec0f6d70, command=COM_QUERY)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490
向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手