Make Linux thread id available in PROCESSLIST

Registered by Laurynas Biveinis on 2015-09-09

Add a column "TID" to table "information_schema.processlist"

Semantic:
---------

Expose the "TID", or thread/task identifier, as defined by the underlying
operating system.

For Linux, this corresponds to light-weight process ID (LWP ID).

TID is a field exposed in particular:
- in perf: http://man7.org/linux/man-pages/man1/perf-stat.1.html
- is ps -L: http://man7.org/linux/man-pages/man1/ps.1.html
- in /proc/[pid]/task/[tid]: http://man7.org/linux/man-pages/man5/proc.5.html

Benefits:
---------

By exposing the thread TID in the "information schema.processlist", a DBA can correlate:
- a user session (see existing column "ID")
- a currently executing statement (see existing column "INFO")
- to an operating system Light Weigh Process (LWP), aka a thread, with new
column "TID".

This is critical to inspect / monitor MySQL server threads with tools external
to the server itself, such as linux perf.

https://github.com/percona/percona-server/pull/209

Blueprint information

Status:
Complete
Approver:
Laurynas Biveinis
Priority:
Medium
Drafter:
Yura Sorokin
Direction:
Approved
Assignee:
Yura Sorokin
Definition:
Approved
Series goal:
Accepted for 5.6
Implementation:
Implemented
Milestone target:
milestone icon 5.6.27-75.0
Started by
Laurynas Biveinis on 2015-10-26
Completed by
Laurynas Biveinis on 2015-10-27

Related branches

Sprints

Whiteboard

Original whiteboard:
https://mariadb.atlassian.net/browse/MDEV-6756

Proposed spec:
A new column named "TID" of type "BIGINT UNSIGNED" must be added at the end of "information_schema.processlist" table so that
"SHOW COLUMNS FROM information_schema.processlist LIKE 'TID'" would produce the following results.
  Field: TID
   Type: bigint(21) unsigned
   Null: YES
    Key:
Default: NULL
  Extra:

Please notice that "TID" can be NULL.

This field must show system thread ID associated with corresponding MySQL thread.
On Linux platforms it must be set to light-weight process ID (LWP ID), the same value produced by
"ps -L ..." system command. Please notice that to get this value from the server code "syscall(SYS_gettid)" / "gettid()" can be used.
On other platforms this field must always be set to NULL.

For backward compatibility with various monitoring tools the output of the "SHOW PROCESSLIST" command must not be changed. It must include the same 10 fields as before. For instance
                           Id: 1
                     User: root
                     Host: localhost:32943
                         db: NULL
           Command: Query
                     Time: 0
                     State: init
                        Info: SHOW PROCESSLIST
          Rows_sent: 0
Rows_examined: 0

In case when Percona thread pool is enabled ("thread_handling=pool-of-threads") "TID" field must be set only for active threads. For connections with queued statements or with no currently executing statements it must always be NULL.

(?)

Work Items

This blueprint contains Public information 
Everyone can see this information.