朋友告诉我如下操作不能修改
-
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修改为其他值会直接报错。
二、mupdateenabled来源
也就是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