Matching MariaDB internal thread ID with OS thread

In order to find a thread id, that would normally be returned by gettid() inside the thread, we might use a simple query:

MariaDB [oap]> show processlist;
| Id  | User | Host      | db   | Command | Time | State | Info             | Progress |
| 358 | root | localhost | oap  | Query   |    0 | NULL  | show processlist |    0.000 |

The Id column has little to do with OS thread ID. You can not match it with any thread you find via ps command.

This might be necessary if we have a long query running and need to examine what a certain thread is doing to make sure everything is fine.

For instance, here is an output of a few MariaDB threads

mysql    11646 10834 11646  0   20  ?     00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/ --socket=/var/lib/mysql/mysql.sock
mysql    11646 10834 11648  0   20  ?     00:00:28 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/ --socket=/var/lib/mysql/mysql.sock
mysql    11646 10834 11649  0   20  ?     00:00:38 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/ --socket=/var/lib/mysql/mysql.sock

The third column is an OS thread ID. And it does not match Maria’s internal ID

It is possible to turn on performance schema and examine the threads table in the corresponding database. A field named THREAD_OS_ID of the table will give you an exact OS thread ID.

mysql> SHOW VARIABLES LIKE 'performance_schema';
| Variable_name      | Value |
| performance_schema | ON    |
mysql> SELECT * FROM threads;

But, it requires the server to be restarted and seriously hurts overall performance.

So, there must be another way to find OS thread ID. And it exists.

First off, it works only for InnoDB databases, which usually is not a problem, since it is the most common DB engine nowdays. We need to examine InnoDB status:

mysql>show engine innodb status\G;
Trx id counter 866A5
Purge done for trx's n:o < 866A3 undo n:o < 0
History list length 1058
---TRANSACTION 0, not started
MySQL thread id 278, OS thread handle 0x7f7798249700, query id 5180433 localhost root
show engine innodb status
---TRANSACTION 7C82C, not started
MySQL thread id 255, OS thread handle 0x7f7798200700, query id 5014696 localhost root
---TRANSACTION 866A3, ACTIVE 459 sec updating or deleting
mysql tables in use 1, locked 1
27737 lock struct(s), heap size 3029432, 2849739 row lock(s), undo log entries 1444562
MySQL thread id 195, OS thread handle 0x7f77982db700, query id 5137989 localhost root
delete from deliverlines

Here, in the TRANSACTIONS section, we can see a MySQL thread ID and a corresponding OS thread handle, which is nothing else than an address of pthread_t struct of a MariadDB OS thread. The same identifier will be returned by pthread_self()

Now we need to match OS thread handle with OS thread ID. It can be done by attaching MariaDB server process by a debugger. Be careful, the server will be suspended when you attach to it, so do the things fast

# gdb attach 11646
(gdb) info threads
  4    Thread 0x7f779836d700 (LWP 11666) "mysqld" 0x00007f779ee9e101 in sigwait () from /lib64/
  3    Thread 0x7f7798249700 (LWP 9613) "mysqld" 0x00007f779ee9d49d in read () from /lib64/
  2    Thread 0x7f77982db700 (LWP 10442) "mysqld" 0x00007f779ee9d49d in read () from /lib64/
* 1    Thread 0x7f779f2b7840 (LWP 11646) "mysqld" 0x00007f779d704dfd in poll () from /lib64/

Now, it is clear that OS thread handle 0x7f77982db700 has tid with number 10442.

Attach to it and enjoy.

# strace -p 10442