Explore MySQL with Systemtap

Http://www.actionsky.com/docs/archives/168#Systemtap

Directory

• 1 Systemtap
• 2 level of support for SYSTEMTAP observation points
  • 2.1 Official compiled MySQL 5.7.11
  • 2.2 Compiling MySQL 5.7.11
• 3 Systemtap Use Example
  • 3.1 Systemtap Script
  • 3.2 Run
• 4 Miscellaneous

Systemtap

MySQL supports DTrace probe, which provides some useful observation points for dtrace (probe). Systemtap can also use these observations as a low-cost means of observing MySQL.

Several commonly used observation points:
1. function, can observe functions of access/return
2. Statement, you can directly observe a line in the source code
3. Marker, observation points provided by the source

function and marker are commonly used in everyday life. Especially marker, the MySQL source provides a point of observation that is useful for learning MySQL behavior.

degree of support for Systemtap observation pointsthe official compiled MySQL 5.7.11

Officially compiled MySQL supports function observation points

> stap -L ‘process("/opt/mysql-5.7.11-linux-glibc2.5-x86_64/bin/mysqld").function("dispatch_command")‘process("/opt/mysql-5.7.11-linux-glibc2.5-x86_64/bin/mysqld").function("[email protected]/export/home/pb2/build/sb_0-17781605-1454370718.35/mysql-5.7.11/sql/sql_parse.cc:1183") $thd:struct THD* $com_data:union COM_DATA const* $command:enum enum_server_command

Official compiled MySQL does not support the mark observation point

> stap -v -L ‘process("/opt/mysql-5.7.11-linux-glibc2.5-x86_64/bin/mysqld").mark("*")‘Pass 1: parsed user script and 109 library script(s) using 96972virt/38616res/5780shr/32800data kb, in 150usr/10sys/164real ms.Pass 2: analyzed script: 0 probe(s), 0 function(s), 0 embed(s), 0 global(s) using 97764virt/40340res/6684shr/33592data kb, in 10usr/0sys/4real ms.Tip: /usr/share/doc/systemtap/README.Debian should help you get started

compiling MySQL 5.7.11

The definition can be found in the MySQL source code dtrace.cmake :

 Check if OS supports DTraceMACRO(CHECK_DTRACE) FIND_PROGRAM(DTRACE dtrace) MARK_AS_ADVANCED(DTRACE) # On FreeBSD, dtrace does not handle userland tracing yet IF(DTRACE AND NOT CMAKE_SYSTEM_NAME MATCHES "FreeBSD")   SET(ENABLE_DTRACE ON CACHE BOOL "Enable dtrace") ENDIF()

In the Linux environment, as long as the environment exists, dtrace it will be opened ENABLE_DTRACE . So pack it systemtap-sdt-dev up and carry on the CMake. No additional configuration options are required and need not be turned on WITH_DEBUG .

(Note: According to systemtap-sdt-dev the manual, dtrace used to convert the file.d file into a header file to participate in the compilation, not the real dtrace .)

> apt-get install -sdt-dev> cmake -DBUILD_CONFIG=mysql_release -DDOWNLOAD_BOOST=1 -DDOWNLOAD_BOOST_TIMEOUT=3600 -DWITH_BOOST=/opt/boost -L .

Check cmake the output variable (the output is truncated):

...ENABLE_DTRACE:BOOL=ON...WITH_DEBUG:BOOL=OFF...

ENABLE_DTRACECompiled MySQL support for Mark observation point (output truncated)

> stap -L ‘process("/usr/local/mysql/bin/mysqld").mark("*")‘process("/usr/local/mysql/bin/mysqld").mark("command__done") $arg1:longprocess("/usr/local/mysql/bin/mysqld").mark("command__start") $arg1:long $arg2:long $arg3:long $arg4:longprocess("/usr/local/mysql/bin/mysqld").mark("connection__done") $arg1:long $arg2:long...process("/usr/local/mysql/bin/mysqld").mark("update__start") $arg1:long

Systemtap Use Example

For example, use Systemtap's mark observation point to observe SQL parsing.

Systemtap Script

global latencyprobe process("/usr/local/mysql/bin/mysqld").mark("query__parse__start") {        printf ("parsing %s\n", user_string($arg1))        latency[tid()] = gettimeofday_ns()}probe process("/usr/local/mysql/bin/mysqld").mark("query__parse__done") {        printf ("parse latency: %dns\n", gettimeofday_ns() - latency[tid()])}

Run

In this example, the Systemtap is compiled in a docker container and then executed in the production environment, so it is divided into two steps: Compile and execute. You can also use stap execution directly.

> stap -v /opt/test_mysql_sql_parse.stp -m test_mysql_sql_parse.ko...$ sudo staprun -v test_mysql_sql_parse.kostaprun:insert_module:183 Module test_mysql_sql_parse inserted from file /opt/test_mysql_sql_parse.koparsing select @@version_comment limit 1parse latency: 54094nsparsing create database test.a(a int)parse latency: 48596ns

You can see the statements and parsing times in the output that contain SQL parsing.

Miscellaneous

1. Systemtap the observation cost is low, and the cost is higher when the observation will be automatically disconnected, without affecting the operation of the observed program, it can be considered in the production environment as a must kill technology use.
2. In a docker container, kernel probe can be observed, but the userspace probe of the same container is not observed. The reason is unknown.
3. If there is a soft connection on the path to MySQL, you may not be able to userspace probe for an unknown reason

Explore MySQL with Systemtap