Author: Wang Yue

Copyright (C) 2021 wingYue

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.


Recently, I have used dbslower in the bcc toolset. This tool can detect the slow query under the specified threshold of MySQL, which is very convenient to use.

Example of use:

Because this tool is a python script, open it and learn the internal mechanism.

The first paragraph is some use cases:

Here we focus on these two:

 dbslower mysql -p 480 -m 30  # trace MySQL queries slower than 30ms

dbslower mysql -x $(which mysqld)  # trace MySQL queries with uprobes

The same is to detect slow logs, one is to provide pid, and the other is to provide binary file address. At first glance, it is only a different way of use. In fact, it is completely different.

Let's look at the next paragraph

This section is used to confirm the final mechanism mode used. If pid is provided, then mode="MYSQL57", if binary address is provided, then mode="USDT".

Here USDT refers to (Userland Statically Defined Tracing) user-land static probes, that is, when using pid, the script actually uses the USDT static probe mechanism, and when using binary files, it uses another dynamic probe. Needle mechanism. To continue understanding the following paragraphs, let's first look at what static probes and dynamic probes actually mean.

Classification of probes

Probes can be roughly divided into two categories, static probes and dynamic probes.

The static probe is like the USDT we saw above, and in MySQL, it is the implementation of MySQL DTrace. Such probes need to be defined in the code in advance and enabled at compile time.

It should be noted that MySQL DTrace is deprecated in MySQL 5.7.18 and completely removed in MySQL 8.0, so we can only perform DTrace detection in earlier versions. MySQL's DTrace includes a lot of probes, such as query-start and query-done to detect the execution process of a statement, such as connection-start and connection-done to detect the process of client connection, these probes are required implemented in code. And to enable the probe, you need to increase the compile parameter -DENABLE_DTRACE=1.

Let's take a look at an official example to understand intuitively:

https://dev.MySQL.com/doc/refman/5.7/en/dba-DTrace-server.html

Two probes, query-start and query-done, are used here to get the statement execution time

Edit DTrace scripts

Execution effect

It is a pity that static probes are no longer available in the new version of MySQL, so we will not delve into it.

In addition to static probes, there is another type of dynamic probes, such as uprobes and kprobes that are often used in bfs technology. These probes are more flexible and can be dynamically added when the program is running. If you are familiar with the code, it can be described as an artifact.

I used MySQL8.0 as the experimental environment, and simply wrote an example of using uprobes to print query.

The key here is { printf("%s\n", str(*arg1)); } , which means to print out what the second parameter of the dispatch_command method points to.

The same principle, changing the function, can dynamically print any variable content we want, or use the probe to write some bpf scripts, which can achieve many functions and are very easy to use.

Alright, back to our dbslower script. In fact, this script implements both static probe and dynamic probe mechanisms.

When we use pid, such as dbslower MySQL -p 480 -m 30 , the variable mode value is "USDT", and we enter the else paragraph in the above figure, that is, we use query__start and query__done These two static probes, when the probe is triggered, are mounted to the corresponding processing functions query_start and query_end , the logic in these two processing functions is very simple, that is, to obtain The corresponding query content, recording time, etc., if you are interested, you can go to the script to view it, which will not be listed here.

In another case, when we use the binary file address, such as dbslower MySQL -x $(which MySQLd) , the variable mode value is "MYSQL57", entering the if paragraph in the above figure, and the value assigned to mysql_func_name in the script is the dispatch_command function , that is, using bfp, a dynamic probe is inserted before and after the function dispatch_command is executed. Similarly, when the probe is triggered, it is mounted to the corresponding processing function query_start and query_end calculation of time.

Summarize

  1. The dbslower script uses probes to detect MySQL query execution time.
  2. When dbslower uses pid, the static probe mechanism is actually used, and -DENABLE_DTRACE=1 needs to be enabled at compile time, and this mechanism has been removed in the new version of MySQL, so special attention should be paid to it.
  3. When dbslower uses the mysqld binary path, it actually uses the dynamic probe mechanism. Dynamic probes can be dynamically added when the program is running, regardless of the old and new versions of MySQL.
  4. The uprobe dynamic probe of bpf is very flexible. After skilled use, it is a great tool for some troubleshooting scenarios. The disadvantage is that in most cases, it needs to be configured according to the code.

爱可生开源社区
426 声望209 粉丝

成立于 2017 年,以开源高质量的运维工具、日常分享技术干货内容、持续的全国性的社区活动为社区己任;目前开源的产品有:SQL审核工具 SQLE,分布式中间件 DBLE、数据传输组件DTLE。