MySQL Bugs: #71041: Please, document every instrument in P_S.setup

Bug #71041	Please, document every instrument in P_S.setup_instruments in details
Submitted:	29 Nov 2013 18:39	Modified:	5 Sep 2017 1:11
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	mutex, performance_schema, rwlock, setup_instruments

Description:
Both http://dev.mysql.com/doc/refman/5.6/en/setup-instruments-table.html and http://dev.mysql.com/doc/refman/5.6/en/performance-schema-instrument-naming.html do not explain what each individual instrument actually "instruments". 

What are these rwlocks about, for example:

mysql> select * from setup_instruments where name like 'wait/synch/rw%';
+------------------------------------------------------+---------+-------+
| NAME                                                 | ENABLED | TIMED |
+------------------------------------------------------+---------+-------+
| wait/synch/rwlock/sql/LOCK_grant                     | NO      | NO    |
| wait/synch/rwlock/sql/LOGGER::LOCK_logger            | NO      | NO    |
| wait/synch/rwlock/sql/LOCK_sys_init_connect          | NO      | NO    |
| wait/synch/rwlock/sql/LOCK_sys_init_slave            | NO      | NO    |
| wait/synch/rwlock/sql/LOCK_system_variables_hash     | NO      | NO    |
| wait/synch/rwlock/sql/Query_cache_query::lock        | NO      | NO    |
| wait/synch/rwlock/sql/gtid_commit_rollback           | NO      | NO    |
| wait/synch/rwlock/sql/LOCK_dboptions                 | NO      | NO    |
| wait/synch/rwlock/sql/MDL_lock::rwlock               | NO      | NO    |
| wait/synch/rwlock/sql/MDL_context::LOCK_waiting_for  | NO      | NO    |
| wait/synch/rwlock/myisam/MYISAM_SHARE::key_root_lock | NO      | NO    |
| wait/synch/rwlock/myisam/MYISAM_SHARE::mmap_lock     | NO      | NO    |
| wait/synch/rwlock/innodb/btr_search_latch            | NO      | NO    |
| wait/synch/rwlock/innodb/dict_operation_lock         | NO      | NO    |
| wait/synch/rwlock/innodb/fil_space_latch             | NO      | NO    |
| wait/synch/rwlock/innodb/checkpoint_lock             | NO      | NO    |
| wait/synch/rwlock/innodb/fts_cache_rw_lock           | NO      | NO    |
| wait/synch/rwlock/innodb/fts_cache_init_rw_lock      | NO      | NO    |
| wait/synch/rwlock/innodb/trx_i_s_cache_lock          | NO      | NO    |
| wait/synch/rwlock/innodb/trx_purge_latch             | NO      | NO    |
| wait/synch/rwlock/innodb/index_tree_rw_lock          | NO      | NO    |
| wait/synch/rwlock/innodb/index_online_log            | NO      | NO    |
| wait/synch/rwlock/innodb/dict_table_stats            | NO      | NO    |
| wait/synch/rwlock/innodb/hash_table_locks            | NO      | NO    |
| wait/synch/rwlock/sql/THR_LOCK_servers               | NO      | NO    |
| wait/synch/rwlock/sql/THR_LOCK_udf                   | NO      | NO    |
+------------------------------------------------------+---------+-------+
26 rows in set (0.00 sec)

(to say nothings about mutexes, 46 only for InnoDB...). For some of them purpose may be guessed by name, but what is this for:  wait/synch/rwlock/innodb/trx_i_s_cache_lock?

So, how users should decide what instruments to enable and measure time for? Enabling everything at startup probably adds some extra overhead and may influence performance negatively.

Moreover, I have some fuzzy reasons to think that there are more instruments in the code than those we get by default. Some of them probably should be enabled somehow during compile time. How one should find out, other than by reading the code? Maybe this is something to describe in the Internals manual?

How to repeat:
Try to find out from the manual what are these instruments actually instrumenting:

wait/synch/rwlock/sql/LOGGER::LOCK_logger
wait/synch/rwlock/innodb/trx_i_s_cache_lock

...

wait/synch/mutex/innodb/srv_misc_tmpfile_mutex
wait/synch/mutex/innodb/zip_pad_mutex

Pick up any other you like - none of them are explicitly explained or documented. Some just mentioned in examples and explained in great blogs posts...

Suggested fix:
Add at least few lines of description for each and every mutex and rwlock listed in setup_instruments in current MySQL 5.6. Explain which ones make sense to enable and measure time for in most "popular" use cases. Explain extra "cost" in memory use (and maybe potential performance impact) for each one of them.

Hello Valeriy,

Thank you for the report.

Thanks,
Umesh

Hi Valeriy,

The manual explains that specific instruments remain undocumented and why.

http://dev.mysql.com/doc/refman/5.6/en/performance-schema-instrumentation-checking.html

"
An exhaustive description of precisely what is instrumented is not given in this documentation, for several reasons:

* What is instrumented is the server code. Changes to this code occur often, which also affects the set of instruments.

* It is not practical to list all the instruments because there are hundreds of them.

* As described earlier, it is possible to find out by querying the setup_instruments table. This information is always up to date for your version of MySQL, also includes instrumentation for instrumented plugins you might have installed that are not part of the core server, and can be used by automated tools.
"

We recognize that this is not ideal, and have some ideas about addressing the situation. However, there is no timetable, so I am marking this bug report "To be fixed later."

Posted by developer:
 
Implemented framework for documentation to be added in 8.0.3.

The Performance Schema setup_instruments table now has columns for instrument metadata: Instrument properties, instrument volatility, and a documentation string describing the the instrument purpose. Also, the TIMED column now can be NULL, indicating that the instrument does not support timing. See http://dev.mysql.com/doc/refman/8.0/en/setup-instruments-table.html.