Comment 9 for bug 592365

This comment has many questions, so please bear with me :)

Here is what the current implementation shows for an idle system:

SELECT InnoDB_thread_id, OS_Thread_id, MySQL_process_id, thread_type, status, info FROM information_schema.innodb_threads;
InnoDB_thread_id OS_Thread_id MySQL_process_id thread_type status info
139959585359616 139959585359616 21119 I/O waiting for i/o request insert buffer thread
139959506581248 139959506581248 21119 I/O waiting for i/o request log thread
139959498188544 139959498188544 21119 I/O waiting for i/o request read thread
139959489795840 139959489795840 21119 I/O waiting for i/o request read thread
139959481403136 139959481403136 21119 I/O waiting for i/o request write thread
139959473010432 139959473010432 21119 I/O waiting for i/o request write thread
139959451444992 139959451444992 21119 lock timeout monitor waiting
139959443052288 139959443052288 21119 long semaphore wait monitor active
139959434659584 139959434659584 21119 InnoDB monitor active
139959459837696 139959459837696 21119 LRU dump/restore thread started
139959426266880 139959426266880 21119 master waiting for server activity loops: 1 1_second, 1 sleeps, 0 10_second 1 background, 1 flush; log flush and writes: 1
139959417874176 139959417874176 21119 purge stopped Purge trx n:o 0, undo n:o 0; purge next stored 0, page_no 0 offset 0, hdr_page_no 0, hdr_offset 0

The InnoDB_thread_id and OS_Thread_id are synonymous. The MySQL process id is the same for all the threads.

The schema is different from the one in comment #2:
- Removed InnoDB_trx_id, meaningless for utility threads.
- Added thread type column ("master", "I/O", "purge" etc.).
Please let me know if these changes are OK and if any other schema changes should be made.

The purge thread information is displayed similarly to how it is displayed when data corruption is found in undo records.
It might be useful to add further information: current purge view, current amount of records waiting to be purged, threshold of this amount to start the purge.

The thread that rolls back transactions during crash recovery is not supported ATM. Please let me know if it should be supported and what info should be displayed about it.

The current patch basically works with whatever information threads already provide, thus there is some inconsistency between various thread statuses: the lock timeout thread is "active" when it is actually checking & canceling locks and "waiting" otherwise. The long semaphore wait thread and InnoDB monitor thread are "active" when they are started, regardless if it they are sleeping or doing work. The LRU dump/restore thread is always assumed to be "started". Please let me know if I should unify the statuses.

Also no thread-specific info is provided for lock timeout, InnoDB monitor, LRU dump/restore threads. Please let me know if any info is needed for them.

The current implementation does not support query threads.