Shulou(Shulou.com)06/01 Report--

My friend told me that the following operations could not be modified.

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: 0mysql > 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)

My test found that none of the values of memory/performance_schema/* can be changed, but other values can be changed. 8.0.17 this is still the case.

Since you can't modify it, follow the update interface. I've tracked it altogether:

Table_setup_instruments::update_row_values: modify API table_setup_instruments::make_row:update_enabled variable input value table_setup_instruments::rnd_next (): update_enabled definition value

Several interfaces.

First, why can't it be modified

If you look at the table_setup_instruments::update_row_values function, you will find that the values of these lines of memory/performance_schema/* enter the following logic:

Case 1: / * ENABLED * / / * Do not raise error if m_update_enabled is false, silently ignore. * / if (m_row.m_update_enabled) / / this is false {value= (enum_yes_no) get_field_enum (f); mendrow. massively instracted class- > massively enabled (value= = ENUM_YES)? True: false;} break

Because m_row.m_update_enabled==false cannot be modified. Other values. This is true.

Here we will also see that the actual value is only two YES or NO, not other values. If update is changed to another value, the error will be reported directly.

II. Source of m_update_enabled

That is, the table_setup_instruments::rnd_next () function determines that if it is VIEW_BUILTIN_MEMORY, it will set update_enabled to false, as follows:

Case pos_setup_instruments::VIEW_BUILTIN_MEMORY: update_enabled= false;//. False update_timed= false;... is set here.

Of course, what is VIEW_BUILTIN_MEMORY? I don't know. I didn't look at it carefully.

Finally, the access to this table is full table scan. Because the upper layer interface is handler::ha_rnd_next, its meaning is as follows:

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. Source function interpretation: Reads the next row in a table scan (also used to read the FIRST row in a table scan). Full table scan to access the next piece of data

Debug will find that it will constantly access the next piece of data. In the end, performance_schema is a stand-alone engine, though very simple.

3. Alternate stack frame

1. Modify data

# 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. Read data

# 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.