ITPub博客

首页 > 数据库 > MySQL > 随笔:MySQL setup_instruments中关于部分信息不能修改

随笔:MySQL setup_instruments中关于部分信息不能修改

原创 MySQL 作者:gaopengtttt 时间:2019-12-13 18:13:39 0 删除 编辑

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


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)

我测试发现所有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 定义值

几个接口。

一、为什么不能修改

查看table_setup_instruments::update_row_values函数你会发现memory/performance_schema/* 这几行值这里都会进入如下逻辑:

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;

因为m_row.m_update_enabled==false 因此不能修改。其他的值这里是true。
这里我们也会看到实际上值只有两个YES或者是NO,不能是其他值。如果update修改为其他值会直接报错。

二、m_update_enabled来源

也就是table_setup_instruments::rnd_next()函数进行判断如果是VIEW_BUILTIN_MEMORY则会设置update_enabled为false,具体如下:

case pos_setup_instruments::VIEW_BUILTIN_MEMORY: 
      update_enabled= false;//这里设置了false
      update_timed= false;
...

当然何为VIEW_BUILTIN_MEMORY,不太清楚,没仔细看了。。

最后本表访问是全表扫描方式。因为上层接口为handler::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.
源码函数解释:Reads the next row in a table scan (also used to read the FIRST row in a table scan).
全表扫描访问下一条数据

debug会发现不断的会访问下一条数据。最后performance_schema是一个独立的引擎,虽然很简单。

三、备用栈帧

1、修改数据


#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、读取数据

#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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-2668559/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
wxh gp_22389860 <<深入理解MySQL主从原理专栏>> 发布 可加WX了解

注册时间:2008-10-13

  • 博文量
    715
  • 访问量
    3013635