Bug #71274 Manual does not provide enough details about background threads in P_S.threads
Submitted: 2 Jan 2014 12:50 Modified: 19 Feb 2016 2:04
Reporter: Valeriy Kravchuk Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: Paul Dubois CPU Architecture:Any
Tags: background, missing manual, performance_schema, threads

[2 Jan 2014 12:50] Valeriy Kravchuk
Description:
http://dev.mysql.com/doc/refman/5.6/en/threads-table.html says:

"threads provides information about background threads, so it can be used to monitor activity the other thread information sources cannot."

That's true, but unfortunately this page (or any other, if I had not missed anything) does not give details about exact names and purpose of each background thread created. All we see is this:

"- TYPE

The thread type, either FOREGROUND or BACKGROUND. User connection threads are foreground threads. Threads associated with internal server activity are background threads. Examples are internal InnoDB threads, “binlog dump” threads sending information to slaves, and slave I/O and SQL threads."

I wonder why not to list all possible names and explain the meanings of other columns for them, so that users are not forced to make "educated guess" while deciding what to instrument and how to interpret the output.

How to repeat:
Run any query on PERFORMANCE_SCHEMA.TREADS checking details for background threads, like this one:

mysql> select thread_id, name, instrumented from performance_schema.threads where type='BACKGROUND';
+-----------+----------------------------------------+--------------+
| thread_id | name                                   | instrumented |
+-----------+----------------------------------------+--------------+
|         1 | thread/sql/main                        | YES          |
|         2 | thread/innodb/io_handler_thread        | YES          |
|         3 | thread/innodb/io_handler_thread        | YES          |
|         4 | thread/innodb/io_handler_thread        | YES          |
|         5 | thread/innodb/io_handler_thread        | YES          |
|         6 | thread/innodb/io_handler_thread        | YES          |
|         7 | thread/innodb/io_handler_thread        | YES          |
|         8 | thread/innodb/io_handler_thread        | YES          |
|         9 | thread/innodb/io_handler_thread        | YES          |
|        10 | thread/innodb/io_handler_thread        | YES          |
|        11 | thread/innodb/io_handler_thread        | YES          |
|        14 | thread/innodb/srv_lock_timeout_thread  | YES          |
|        15 | thread/innodb/srv_monitor_thread       | YES          |
|        16 | thread/innodb/srv_purge_thread         | YES          |
|        17 | thread/innodb/srv_error_monitor_thread | YES          |
|        18 | thread/innodb/srv_master_thread        | YES          |
|        19 | thread/innodb/page_cleaner_thread      | YES          |
|        20 | thread/sql/shutdown                    | YES          |
|        21 | thread/sql/con_sockets                 | YES          |
+-----------+----------------------------------------+--------------+
19 rows in set (0.01 sec)

Now, try to find out what is the purpose of each thread, should we expect to see non-NULL content in PROCESSLIST_* columns or PARENT_THREAD_ID column for it, and so on.

Suggested fix:
Please, document all background threads in PERFORMANCE_SCHEMA.THREADS table in details, including their purpose, relation to entries in SHOW PROCESSLIST etc.
[2 Jan 2014 18:41] Umesh Shastry
Hello Valeriy,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[19 Feb 2016 2:04] Paul Dubois
"Please, document all background threads in PERFORMANCE_SCHEMA.THREADS table in details, including their purpose, relation to entries in SHOW PROCESSLIST etc."

The purpose of the page is to describe the table structure, not enumerate the (subject to change) set of background threads.