DTrace Probes In MySQL 自定义探针
Inserting user-defined DTrace probes into MySQL source code is very useful to help user identify the performance problems in the application level and the database server, In addition, the cost of the USDT probe is basically neglectable. Each probes inserted into the src can be enabled by adding the code like:
If (PROVIDER_PROBE_ENABLED()
{
PROVIDER_PROBE(arg0,…);
}
The steps to add DTrace probes into MySQL is very straightforward.
Step 1: Figure out what probes are needed to insert into the source code
This is the difficult part that requires you understand the MySQL implementation details. Generally, it is good to insert probes to clarify the DB response time distribution including processing query, waiting on locks and latches, doing disk I/O, receiving/sending back data. You can certainly define more probes deep into each of the MySQL engines (such as: define probes to measure the cost of innodb sync spin wait)
Step 2: Define Provider and probes
Create a mysqlprovider.d file as:
provider mysql {
probe query__execute__start(int);
probe query__execute__finish(int);
…
};
It is required to define the probes with easy to understand name. The two underscore(__) is translated to hyphen(-) in the D script file, so the above two probes are called query-execute-startand query-execute-finish
Step 3: Define header file for probes
Create mysqlprovider.h file as:
#ifndef _MYSQLPROVIDER_H
#define _MYSQLPROVIDER_H
#ifdef ENABLE_DTRACE
#define MYSQL_QUERY_EXECUTE_START(arg0) \\
__dtrace_mysql__query_execute__start(arg0)
#define MYSQL_QUERY_EXECUTE_START_ENABLED() \\
__dtraceenabled_mysql__query_execute__start()
#define MYSQL_QUERY_EXECUTE_FINISH(arg0) \\
__dtrace_mysql__query_execute__finish(arg0)
#define MYSQL_QUERY_EXECUTE_FINISH_ENABLED() \\
__dtraceenabled_mysql__query_execute__finish()
extern void __ dtrace_mysql__query_execute__start(int)
extern int __ dtraceenabled_mysql__query_execute__start(void)
extern void __ dtrace_mysql__query_execute__finish(int)
extern int __ dtraceenabled_mysql__query_execute__finish(void)
#else
/\*
\*Unless DTrace is explicitly enabled with –enable-dtrace, the MYSQL macros will expand to no-ops.
\*/
#define MYSQL_QUERY_EXECUTE_START(arg0) \\
__dtrace_mysql__query_execute__start(arg0)
#define MYSQL_QUERY_EXECUTE_START_ENABLED() \\
__dtraceenabled_mysql__query_execute__start()
#define MYSQL_QUERY_EXECUTE_FINISH(arg0) \\
__dtrace_mysql__query_execute__finish(arg0)
#define MYSQL_QUERY_EXECUTE_FINISH_ENABLED()
#endif
#endif /\* _MYSQLPROVIDER_H \*/
Step 4: Insert the probes into source code
You need to include the header file created for DTrace probes before inserting the probe macro. And in order to monitor the server behavior as expected, it requires the knowledge of the MySQLsource code to add the probe macro into the right place.
#include <mysqlprovider.h>
mysql_parse {
…
bool
mysql_execute_command(THD \*thd)
{
MYSQL_QUERY_EXECUTE_START(thd->thread_id);
…
case SQLCOM_EXECUTE:
{
mysql_sql_stmt_execute(thd);
MYSQL_QUERY_EXECUTE_FINISH(thd->thread_id);
Break;
}
….
}
Step 5: Build MySQL with DTrace
You will need to specify the “—enable-dtrace” as the configure option to make the DTrace probes available in MySQL on Solaris 10 and above. On the other operating system without the DTracefacility, the DTrace probes are disabled as default.
In the Makefile, you can compile the 64-bit MySQL with DTrace probes as bellow:
mysqlproviders.o: mysqlproviders.d $(mysqld_OBJECTS)
dtrace -G -64 -s mysqlproviders.d $(mysqld_OBJECTS)
Now, at this point, you have completed inserting the DTrace probes into MySQL, and the probes are ready to use. For example, to use the query-execute-start and query-execute-stop probes, you can write a simple D script(query-execute.d) to measure the time spending on the query execution for each session.
#!/usr/sbin/dtrace –qs
mysql\*:::query-execute-start
{
self->init = timestamp;
}
mysql\*:::query-execute-finish
/self->init/
{
@inittime[args[0]] = sum(timestamp – self->init);
self->init = 0;
}
profile:::tick-5s
{
printf("--------------------------------------------------\\n");
printf("Date: %Y\\n", walltimestamp);
printf("Query execution time\\n");
printa(@inittime);
printf("--------------------------------------------------\\n");
}
Now, you can execute the script to get the data for query execution:
#./query_execute.d
--------------------------------------------------
Date: 2007 May 25 19:18:59
Query execution time
149 4542802785
146 4577178817
148 4586742308
147 4602289846
--------------------------------------------------
Please let me know if you find this is useful, any suggestions on which/where probes would be useful in the MySQL server and client application. You can contact me by email:Luojia.chen@sun.com, or comment on this blog.
Resources:
- Statically Defined Tracing for User Applications chapter of DTrace manual
- More USDT enhancements
- PostgreSQL DTrace User Guide
- Open Solaris Community:DTrace
最新文章
- opencv3.0.1 中的SurfFeaturesFinderGpu类的问题.
- sass基础用法
- [WPF]UserControl的MouseWheel事件触发
- C程序中对时间的处理——time库函数详解
- emacs yasnippet
- System.ArgumentOutOfRangeException: 年、月和日参数描述无法表示的 DateTime。
- AsyncTask类
- 【JavaScript】关于delete
- 如何自己编译生成Eclipse插件,如hadoop-eclipse-plugin-2.2.0.jar
- CDH5 安装过程
- css案例学习之层叠样式
- h5 localStorage存储大小(转)
- WPF的一些感悟
- 本地如何使用phpstudy环境搭建多站点
- scanf后面跟一个getchar
- Java集合框架(五)—— Map、HashMap、Hashtable、Properties、SortedMap、TreeMap、WeakHashMap、IdentityHashMap、EnumMap
- [BZOJ1112] [POI2008] 砖块Klo (treap)
- Ubuntu在终端执行命令时出现的错误
- PAT Basic 1046
- B. Heaters Div3
热门文章
- VUE element-ui下拉列表获取label值
- IOS上微信在输入框弹出键盘后,页面不恢复,下方有留白,有弹窗弹出时页面内容感应区域错位
- 洛谷2019 3月月赛 T3
- HDU 3007 最小圆覆盖 计算几何
- 【USACO2002 Feb】奶牛自行车队
- 自定义View(8)关于measure->;onMeasur->;setMeasuredDimension及getDefaultSize,resolveSizeAndState
- linux命令(006) -- w
- 【Leetcode 3】Longest Substring Without Repeating Characters0
- 【转】Linux下使用locale命令设置语言环境
- VC socket api使用引入