Debugging MySQL Threads and LWP IDs with GDB: Building a Debug Environment and Analyzing Thread IDs

The author introduces the need to understand MySQL threads (POSIX threads) for effective debugging and performance analysis, recommending two books on POSIX multithreading and Linux system programming.

Thread Basics

MySQL runs as a single‑process multi‑threaded program; each thread is a POSIX thread with its own LWP ID. A process can contain many threads, each sharing code, heap, globals, and libraries but having separate stacks and registers.

The article includes diagrams (images) illustrating thread layout and a 32‑bit process memory map.

It explains that threads share heap and globals, making synchronization (e.g., mutexes) essential.

PID, LWP ID, Thread TID

PID – kernel‑assigned process identifier.

LWP ID – kernel‑assigned lightweight‑process (thread) identifier; all threads in a process share the same PID but have distinct LWP IDs.

Thread TID – internal thread identifier used rarely in multithreaded development.

A simple C test program is provided to print these IDs:

# ./gaopengtest
main thread: pid 13188 tid 2010470144 lwp 13188
new thread:  pid 13188 tid 2010461952 lwp 13189

Running top -H shows both threads consuming high CPU, and the PID column actually displays the LWP ID. ps -eLlf also lists PID and LWP ID.

When CPU usage exceeds 100% it indicates multiple threads are fully utilizing cores.

And a ps -eLlf snapshot:

Mapping MySQL Threads to LWP IDs (MySQL 5.7+)

The article shows a SQL query that joins sys.processlist with performance_schema.threads to retrieve THREAD_OS_ID , which corresponds to the LWP ID.

mysql> select a.thd_id,b.THREAD_OS_ID,a.user,b.TYPE
       from sys.processlist a, performance_schema.threads b
       where b.thread_id=a.thd_id;

The result lists MySQL internal threads (e.g., background, io threads) together with their OS thread IDs, confirming the mapping.

Setting Up a MySQL Debugging Environment

Download MySQL source (5.7.26) and extract.

Configure with cmake enabling -DWITH_DEBUG=1 and other required options (list shown in the article).

Run make && make install .

Initialize the data directory and start MySQL (example output shows successful start).

Create a GDB command file ( debug.file ) containing a breakpoint at main and the run command with the appropriate options.

Launch GDB: gdb -x /root/debug.file /root/sf/mysql3312/bin/mysqld . The session output demonstrates thread creation and the breakpoint at main .

Common GDB commands for MySQL debugging are listed (info threads, thread n, bt, b, c, s, n, p, list).

Example: Debugging a Binlog Flush

By setting a breakpoint at binlog_cache_data::flush and executing a simple transaction ( BEGIN; INSERT ...; COMMIT; ), the author shows the breakpoint being hit and the resulting stack trace, proving that the binlog is written during the commit phase.

(gdb) b binlog_cache_data::flush
Breakpoint 2 at 0x1846333: file /root/sf/mysql-5.7.26/sql/binlog.cc, line 1674.
...
Breakpoint 2, binlog_cache_data::flush (this=0x7fff3c00df20...)
    at /root/sf/mysql-5.7.26/sql/binlog.cc:1674
1674      DBUG_ENTER("binlog_cache_data::flush");
(gdb) bt
#0  binlog_cache_data::flush ...
#1  binlog_cache_mngr::flush ...
#2  MYSQL_BIN_LOG::flush_thread_caches ...
#3  MYSQL_BIN_LOG::process_flush_stage_queue ...
#4  MYSQL_BIN_LOG::ordered_commit ...
#5  MYSQL_BIN_LOG::commit ...
#6  ha_commit_trans ...
#7  trans_commit ...
...

The article concludes that such stack traces allow developers to verify internal MySQL behavior, provided they know the relevant function interfaces.

Finally, the author promotes his book “深入理解MySQL主从原理 32讲” for deeper study of MySQL replication.