Make InnoDB thread IDs available

I also want to see the thread ID of *all* threads inside InnoDB. Right now the IO threads don't show their thread ID, and of course the purge thread is invisible as Peter says. This is important when I can see a mutex is being held by some thread. I want to be able to see what that thread is.

We should create INFORMATION_SCHEMA.INNODB_THREADS table
with columns
InnoDB_thread_id | InnoDB_trx_id| OS_Thread_id | MySQL_process_id | status | info |

status - idle, working, etc
where info can contain any info, i.e. amount of records to purge

InnoDB_trx_id: Thread doesn't own trx. Session has trx.

MySQL_process_id: same for all?

I mean the above items seem wrong for the i_s.

Vadim, please don't make confuse by wrong specification.

What is "innodb_thread"?
Do you mean background threads created by os_thread_create()? and their OS id stocked to thread_ids[] ?

If so, they are not related to mysqld at all. They are just pthread created by innodb.
They work independently from other thread. And they have each different purposes and processes.
I think they should be profiled differently each,
Otherwise, it should be useless function.

If you simply worry about purge_thread (officially supported from 5.5),
you should add information for "BACKGROUND THREAD" section of show engine innodb status after the srv_master_thread entry.

I don't agree such i_s, seems not worth to add.

show engine innodb status is total mess already.
We should clean up output, and not add additional information which can't be parsed.

We should add I_S where information is clear and structured.

each background threads have different role and process.
So common columns are not useful to analyze problems, if occur. (or mess, "info" contains particle of "show innodb status"?)
Almost empty for me.

I don't agree useless i_s only to satisfy your curiosity, and to support it forever.
I don't believe person who don't read "show innodb status" can read when it is in i_s.

However you say "show innodb status" mess, for me, it is the best information to solve performance problem.
In this case, i_s is more mess than show innodb status.
I think why you feel mess is because you don't understand InnoDB, e.g. you suggested to include InnoDB_trx_id to the "thread".

The performance problem should be solved by totally viewing.
I don't believe such limited i_s solves something.

Please explain for what such information is useful.
Please explain what information should be included to the i_s concretely?

Do you think how the different items into common format of the i_s? without filtered? without twisted?
I always need raw information from InnoDB.
e.g. current information about master thread (5.5)
---------
srv_master_thread loops: 1 1_second, 1 sleeps, 0 10_second, 1 background, 1 flush
srv_master_thread log flush and writes: 1
---------

Structured info is needed to able to handle it from script or from external monitoring systems like Cacti and Nagios.
That's why INFORMATION_SCHEMA was created to provide SQL-like access to internal data.

If you do not like it - you are not forced to use it. We can add the same info to SHOW INNODB STATUS if this is more
preferable way for you.
But we also will have INFORMATION_SCHEMA.

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.

Hi,

Looks like great start which allows to extend amount of information reported
if we need it to. For example if we need to add number of IOs read/write
threads have processed
or something like this we can do it.

I'm wondering why do we have InnodbTheadID and OSThreadID are not they same
in all cases ?

On Mon, Mar 28, 2011 at 4:32 AM, Laurynas Biveinis <
<email address hidden>> wrote:

> 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...

Is this actually being targeted to this milestone? my guess not, so we should unassign it from it.

It is not, also it is blocked on bug 737895 as it touches thread Windows-specific code.

I have an almost-finished patch for this. If anyone wants to work on this, please contact me.

This has been partially but not fully addressed by 5.7 PERFORMANCE_SCHEMA.threads table.

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2317