Bug #77944 performance_schema_instrument='%=OFF', not disable all instruments
Submitted: 5 Aug 2015 7:11 Modified: 21 Aug 2015 18:43
Reporter: Hiroyuki Itoh Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.7.8-rc, 5.7.9 OS:Linux
Assigned to: Marc Alff CPU Architecture:Any
Tags: performance_schema, setup_instruments

[5 Aug 2015 7:11] Hiroyuki Itoh
Description:
Please see documentation as below.
https://dev.mysql.com/doc/refman/5.7/en/performance-schema-startup-configuration.html 
===
To disable all instruments, use this option:
--performance-schema-instrument='%=OFF'
===

I set configuration as below.
===
[mysqld]
performance_schema = ON
performance-schema-instrument='%=OFF'
===

However, not disable all instruments.
(memory/performance_schema/% not disable.)

How to repeat:
set configuration as below and mysqld restart.

===
[mysqld]
performance_schema = ON
performance-schema-instrument='%=OFF'
===

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.8-rc, for Linux (x86_64) using  EditLine wrapper

Connection id:          3
Current database:       performance_schema
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.8-rc MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 8 min 48 sec

Threads: 1  Questions: 97  Slow queries: 0  Opens: 192  Flush tables: 1  Open tables: 185  Queries per second avg: 0.183
--------------

mysql>
mysql> select * from setup_instruments where ENABLED='YES';
+--------------------------------------------------------------------------------+---------+-------+
| NAME                                                                           | ENABLED | TIMED |
+--------------------------------------------------------------------------------+---------+-------+
| memory/performance_schema/mutex_instances                                      | YES     | NO    |
| memory/performance_schema/rwlock_instances                                     | YES     | NO    |
| memory/performance_schema/cond_instances                                       | YES     | NO    |
| memory/performance_schema/file_instances                                       | YES     | NO    |
| memory/performance_schema/socket_instances                                     | YES     | NO    |
| memory/performance_schema/metadata_locks                                       | YES     | NO    |
| memory/performance_schema/file_handle                                          | YES     | NO    |
| memory/performance_schema/accounts                                             | YES     | NO    |
| memory/performance_schema/events_waits_summary_by_account_by_event_name        | YES     | NO    |
| memory/performance_schema/events_stages_summary_by_account_by_event_name       | YES     | NO    |
| memory/performance_schema/events_statements_summary_by_account_by_event_name   | YES     | NO    |
| memory/performance_schema/events_transactions_summary_by_account_by_event_name | YES     | NO    |
| memory/performance_schema/memory_summary_by_account_by_event_name              | YES     | NO    |
| memory/performance_schema/events_stages_summary_global_by_event_name           | YES     | NO    |
| memory/performance_schema/events_statements_summary_global_by_event_name       | YES     | NO    |
| memory/performance_schema/memory_summary_global_by_event_name                  | YES     | NO    |
| memory/performance_schema/hosts                                                | YES     | NO    |
| memory/performance_schema/events_waits_summary_by_host_by_event_name           | YES     | NO    |
| memory/performance_schema/events_stages_summary_by_host_by_event_name          | YES     | NO    |
| memory/performance_schema/events_statements_summary_by_host_by_event_name      | YES     | NO    |
| memory/performance_schema/events_transactions_summary_by_host_by_event_name    | YES     | NO    |
| memory/performance_schema/memory_summary_by_host_by_event_name                 | YES     | NO    |
| memory/performance_schema/threads                                              | YES     | NO    |
| memory/performance_schema/events_waits_summary_by_thread_by_event_name         | YES     | NO    |
| memory/performance_schema/events_stages_summary_by_thread_by_event_name        | YES     | NO    |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name    | YES     | NO    |
| memory/performance_schema/events_transactions_summary_by_thread_by_event_name  | YES     | NO    |
| memory/performance_schema/memory_summary_by_thread_by_event_name               | YES     | NO    |
| memory/performance_schema/events_waits_history                                 | YES     | NO    |
| memory/performance_schema/events_stages_history                                | YES     | NO    |
| memory/performance_schema/events_statements_history                            | YES     | NO    |
| memory/performance_schema/events_statements_history.tokens                     | YES     | NO    |
| memory/performance_schema/events_statements_history.sqltext                    | YES     | NO    |
| memory/performance_schema/events_statements_current                            | YES     | NO    |
| memory/performance_schema/events_statements_current.tokens                     | YES     | NO    |
| memory/performance_schema/events_statements_current.sqltext                    | YES     | NO    |
| memory/performance_schema/events_transactions_history                          | YES     | NO    |
| memory/performance_schema/session_connect_attrs                                | YES     | NO    |
| memory/performance_schema/users                                                | YES     | NO    |
| memory/performance_schema/events_waits_summary_by_user_by_event_name           | YES     | NO    |
| memory/performance_schema/events_stages_summary_by_user_by_event_name          | YES     | NO    |
| memory/performance_schema/events_statements_summary_by_user_by_event_name      | YES     | NO    |
| memory/performance_schema/events_transactions_summary_by_user_by_event_name    | YES     | NO    |
| memory/performance_schema/memory_summary_by_user_by_event_name                 | YES     | NO    |
| memory/performance_schema/mutex_class                                          | YES     | NO    |
| memory/performance_schema/rwlock_class                                         | YES     | NO    |
| memory/performance_schema/cond_class                                           | YES     | NO    |
| memory/performance_schema/thread_class                                         | YES     | NO    |
| memory/performance_schema/file_class                                           | YES     | NO    |
| memory/performance_schema/socket_class                                         | YES     | NO    |
| memory/performance_schema/stage_class                                          | YES     | NO    |
| memory/performance_schema/statement_class                                      | YES     | NO    |
| memory/performance_schema/memory_class                                         | YES     | NO    |
| memory/performance_schema/setup_actors                                         | YES     | NO    |
| memory/performance_schema/setup_objects                                        | YES     | NO    |
| memory/performance_schema/events_statements_summary_by_digest                  | YES     | NO    |
| memory/performance_schema/events_statements_summary_by_digest.tokens           | YES     | NO    |
| memory/performance_schema/events_stages_history_long                           | YES     | NO    |
| memory/performance_schema/events_statements_history_long                       | YES     | NO    |
| memory/performance_schema/events_statements_history_long.tokens                | YES     | NO    |
| memory/performance_schema/events_statements_history_long.sqltext               | YES     | NO    |
| memory/performance_schema/events_transactions_history_long                     | YES     | NO    |
| memory/performance_schema/events_waits_history_long                            | YES     | NO    |
| memory/performance_schema/table_handles                                        | YES     | NO    |
| memory/performance_schema/table_shares                                         | YES     | NO    |
| memory/performance_schema/table_io_waits_summary_by_index_usage                | YES     | NO    |
| memory/performance_schema/table_lock_waits_summary_by_table                    | YES     | NO    |
| memory/performance_schema/events_statements_summary_by_program                 | YES     | NO    |
| memory/performance_schema/prepared_statements_instances                        | YES     | NO    |
| memory/performance_schema/scalable_buffer                                      | YES     | NO    |
+--------------------------------------------------------------------------------+---------+-------+
70 rows in set (0.00 sec)

mysql>

Suggested fix:
If set configuration performance_schema_instrument='%=OFF', disable all instruments.
[5 Aug 2015 18:12] Umesh Shastry
Hello!

Thank you for the report.
Observed this with 5.7.8, and 5.7.9 builds.

Thanks,
Umesh
[21 Aug 2015 18:43] Paul Dubois
Noted in 5.7.9, 5.8.0 changelogs.

In the Performance Schema setup_instruments table, it was possible to
set memory instrument to TIMED='YES', although memory operations are
never timed. Now such attempts are ignored. It was possible to set
built-in memory instruments (with names of the form
memory/performance_schema/%) to ENABLED='YES', although built-in
memory instruments cannot be disabled. Now such attempts are ignored.