Brief introduction of Thread and Construction of MySQL debugging Environment 01/19 Update SLTechnology News&Howtos

Brief introduction of Thread and Construction of MySQL debugging Environment

2025-01-19 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "Thread introduction and the Construction of MySQL debugging Environment". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

A brief introduction to threads

We know that MySQLD is a single-process, multithreaded user program, so we need to know what thread it is. In fact, the threads in MySQL are all POSIX threads, such as our session thread, DUMP thread, IO thread, and some other InnoDB threads are POSIX threads.

A process is actually a running program, and a process can contain multiple threads or only one thread. In Linux, threads are also called lightweight processes (light-weight process), or LWP for short, and the first thread of a process is usually called the master thread. The process is the smallest unit of memory allocation, and the thread is the smallest unit of CPU scheduling, that is, if the CPU has enough cores, then multiple threads can achieve the effect of parallel processing, and the kernel schedules threads directly. The following is a picture I saw when I was learning Linux threads, which I thought was easy to understand. I redrew it and put it below for your reference (figure 29-1, the original HD image is included in the original picture at the end of the article):

All threads within a process have the same code programs, heaps, global variables, shared libraries, etc., but each thread has its own independent stack space and registers, and they share the virtual memory address space of the process. Let's assume that it is a MySQLD process under a 32-bit operating system, and its virtual memory address is shown below. In fact, this diagram is also drawn with reference to the Linux UNIX system programming Manual (figure 29-2, the original HD image is included in the original image at the end of the article):

We find that the heap memory and global variables of threads are shared, so it is easy to share data between threads, but to control this shared memory requires the introduction of our thread synchronization technology, such as Mutex.

If you want to know what resources threads share, and what are the advantages and disadvantages of threads and processes, you can refer to the books I gave above.

2. PID, LWP ID, Thread TID

If you want to debug, you need to understand these three kinds of ID, of which PID and LWP ID are more important, because both debugging and operation and maintenance will encounter them, and Thread TID is rarely used without multithreaded development. Here is my summary of them:

PID: kernel allocation, used to identify the ID of each process. This should be the one that everyone is most familiar with.

LWP ID: kernel allocation, used to identify the ID of each thread, as if the thread were 'PID'. All threads under the same process have the same PID, but the LWP ID is different. The LWP ID of the master thread is the process PID.

Thread TID: the internal ID used within the process to identify each thread, this ID is rarely used.

Below I wrote a simple C test program just to observe these ID, it is through the master thread to create another thread, that is, the process contains two threads. They print their own PID, LWP ID and Thread TID respectively, and then do a cyclic self-adding operation to cause high CPU consumption. Then we use Linux's top-H and ps-eLlf commands to observe, respectively.

Program output

Here is the output of the program:

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

We can see that the PID of both threads is 13188, but the LWP ID of the master thread is 13188, and the LWP ID of the newly created thread is 13189. Then there is Thread TID, but explain more.

Top-H observation is as follows:

We can see that both threads are CPU-intensive threads, and the CPU is already saturated. Here we see that the 'PID'' in the output of the top-H command is LWP ID. Here we can also see that their memory information is exactly the same, and the memory information is actually the memory information of the whole process, because the process is the smallest unit of memory allocation.

Note that if the% cpu consumed by viewing this process at this time is more than 100%, close to 200%, as follows:

The ps-eLlf observation is as follows:

We can see that PID and LWP ID are included here, but with more explanation, you can try it yourself.

Third, how to correspond the thread of MySQL to LWP ID

In 5. 7, we can already correspond to LWP ID through MySQL statements, which makes performance diagnosis easier. For example, in my column above, if the two CPU-consuming threads are MySQL threads, then we get the LWP ID of the thread, and then we can use the statement to find out which thread of MySQL these two threads are. The statement is as follows:

The THREAD_OS_ID here is the LWP ID of the thread. Then let's take a look at it again using the ps-eLlf command, as follows:

We can find that they can correspond to each other, and you'd better try it yourself.

IV. Setting up the debugging environment

I think no matter what method is used to build the debugging environment, as long as it can play the role of debugging. Here is an introduction to my method. I debug using gdb directly under Linux. I think this method is very simple and effective. Basically, as long as you can install the source code, you can build the debugging environment. Let's look at the steps:

1. The first step is to download the MySQL source code package and decompress it.

I specially downloaded the official version of the 5.7.26 source code.

two。 Install MySQL using source code installation. Note that the debug option needs to be enabled.

Here are the options I use:

Cmake-DCMAKE_INSTALL_PREFIX=/root/sf/mysql3312/-DMYSQL_DATADIR=/root/sf/mysql3312/data/-DSYSCONFDIR=/root/sf/mysql3312/-DWITH_INNOBASE_STORAGE_ENGINE=1-DWITH_ARCHIVE_STORAGE_ENGINE=1-DWITH_BLACKHOLE_STORAGE_ENGINE=1-DWITH_FEDERATED_STORAGE_ENGINE=1-DWITH_PARTITION_STORAGE_ENGINE=1-DMYSQL_UNIX_ADDR=/root/sf/mysql3312/mysql3312.sock-DMYSQL_TCP_PORT=3306-DENABLED_LOCAL_INFILE=1-DEXTRA_CHARSETS=all-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_ General_ci-DMYSQL_USER=mysql-DWITH_BINLOG_PREALLOC=ON-DWITH_BOOST=/root/sf/mysql-5.7.26/boost/boost_1_59_0-DWITH_DEBUG=1

Note that the last-DWITH_DEBUG=1 must be opened.

3. Make&&make install

Compile and install.

4. Prepare the parameter file and initialize the MySQL database and ensure that it starts successfully

Handle this step by yourself and pay attention to the authority. My environment started successfully as follows:

[root@gp1 support-files] #. / mysql.server startStarting MySQL. SUCCESS! [root@gp1 support-files] #. / mysql.server stopShutting down MySQL.. SUCCESS!

5. Prepare the gdb command file

The following is the command file I prepared:

[root@gp1] # more debug.file break mainrun-defaults-file=/root/sf/mysql3312/my.cnf-user=mysql-gdb

The first line is to hit a breakpoint at the main function. The second line is what parameters MySQLD adds when gdb calls MySQLD, and note that run does not write it out.

6. Start MySQL using gdb

Start the debugging environment using the following command:

Gdb-x / root/debug.file / root/sf/mysql3312/bin/mysqld

Here is the record of my success in starting the debugging environment:

# gdb-x / root/debug.file / root/sf/mysql3312/bin/mysqldGNU gdb (GDB) Red Hat Enterprise Linux (7.2-92.el6) Copyright (C) 2010 Free Software Foundation, Inc.License GPLv3+: GNU GPL version 3 or later This is free software: you are free to change and redistribute it.There is NO WARRANTY, to the extent permitted by law. Type "show copying" and "show warranty" for details.This GDB was configured as "x86_64-redhat-linux-gnu" .for bug reporting instructions, please see:...Reading symbols from / root/sf/mysql3312/bin/mysqld...done.Breakpoint 1 at 0xec7c53: file / root/sf/mysql-5.7.26/sql/main.cc, line 25. [Thread debugging using libthread_db enabled] Breakpoint 1, main (argc=5 Argv=0x7fffffffe3b8) at / root/sf/mysql-5.7.26/sql/main.cc:2525 return mysqld_main (argc, argv) Missing separate debuginfos Use: debuginfo-install glibc-2.12-1.212.el6.x86_64 libaio-0.3.107-10.el6.x86_64 libgcc-4.4.7-18.el6.x86_64 libstdc++-4.4.7-18.el6.x86_64 nss-softokn-freebl-3.14.3-23.3.el6_8.x86_64 (gdb) cContinuing. [New Thread 0x7fffee883700 (LWP 29375)] [New Thread 0x7fff9a9f3700 (LWP 29376)] [New Thread 0x7fff99ff2700 (LWP 29377)] [New Thread 0x7fff995f1700 (LWP 29378)] [New Thread 0x7fff98bf0700 (LWP 29379)] [New Thread 0x7fff981ef700 (LWP 29380)] [New Thread 0x7fff977ee700 (LWP 29381)] [New Thread 0x7fff96ded700 (LWP 29382)] [New Thread 0x7fff963ec700 (LWP 29383)].

Have you noticed the LWP ID here? we've talked about it earlier. At this point, the MySQL client program can connect to MySQLD as follows:

# / root/sf/mysql3312/bin/mysql-S'/root/sf/mysql3312/mysql3312.sock'Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 2Server version: 5.7.26-debug-log Source distributionCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > select version (); +-+ | version () | +-+ | 5.7.26-debug-log | +-+ 1 row in set (0.00 sec)

Well, at this point, the basic debugging environment has been set up, and we can find it very simple. Then we can debug the breakpoint. My common gdb commands include:

Info threads: view all threads

Thread n: specify a thread

Bt: view a thread stack frame

B: set breakpoint

C: continue execution

S: execute a line of code, and if the code function is called, enter the function

N: execute a line of code, and the function call does not enter

P: print the value of a variable

List: text information for printed code

Of course, gdb also has many orders, so you can refer to other materials on your own.

Use the debugging environment to prove a problem.

Here we will use an example to look at the use of the debugging environment. We mentioned in section 15 that binlog cache is written to binary log during the flush phase of order commit, and the function binlog_cache_data::flush is called. All right, we can type the breakpoint to this function as follows:

(gdb) b binlog_cache_data::flushBreakpoint 2 at 0x1846333: file / root/sf/mysql-5.7.26/sql/binlog.cc, line 1674.

Then we execute a thing on the MySQL client as follows, and submit:

Mysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > insert into gpdebug values (1); Query OK, 1 row affected (0.03 sec) mysql > commit

The commit has already been blocked, and the breakpoint is triggered as follows:

Breakpoint 2, binlog_cache_data::flush (this=0x7fff3c00df20...) At / root/sf/mysql-5.7.26/sql/binlog.cc:16741674 DBUG_ENTER ("binlog_cache_data::flush")

We use the bt command to look at the stack frame and find as follows:

# 0 binlog_cache_data::flushat / root/sf/mysql-5.7.26/sql/binlog.cc:1674#1 0x0000000001861b41 in binlog_cache_mngr::flush at / root/sf/mysql-5.7.26/sql/binlog.cc:967#2 0x00000000018574ce in MYSQL_BIN_LOG::flush_thread_caches at / root/sf/mysql-5.7.26/sql/binlog.cc:8894#3 0x0000000001857712 in MYSQL_BIN_LOG::process_flush_stage_queue at / Root/sf/mysql-5.7.26/sql/binlog.cc:8957#4 0x0000000001858d19 in MYSQL_BIN_LOG::ordered_commit at / root/sf/mysql-5.7.26/sql/binlog.cc:9595#5 0x00000000018573b4 in MYSQL_BIN_LOG::commitat / root/sf/mysql-5.7.26/sql/binlog.cc:8851#6 0x0000000000f58de9 in ha_commit_trans at / root/sf/mysql-5.7.26/sql/handler.cc:1799#7 0x000000000169e02b in Trans_commit at / root/sf/mysql-5.7.26/sql/transaction.cc:239.

All right, if you see this stack frame, you can prove our point. If you want to learn more about the code, you can start from this stack frame. But it is worth noting that this is built on the premise of knowing the function interface function, if we do not know that writing binary log will call the binlog_cache_data::flush function, then debugging will not be easy, I often encounter this dilemma. Therefore, I have given a lot of such interfaces throughout the series for interested friends to debug and test.

This is the end of the introduction to Thread and the Construction of MySQL debugging Environment. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.