技术控

    今日:68| 主题:49103
收藏本版 (1)
最新软件应用技术尽在掌握

[其他] Linux MySQL Slow Query Tracing with bcc/BPF

[复制链接]
暧昧害死人 发表于 2016-10-5 05:51:13
64 1

立即注册CoLaBug.com会员,免费获得投稿人的专业资料,享用更多功能,玩转个人品牌!

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
My mysqld_qslower tool prints MySQL queries slower than a given threshold, and is run on the MySQL server. By default, it prints queries slower than 1 millisecond:
  1. # mysqld_qslower `pgrep -n mysqld`
  2. Tracing MySQL server queries for PID 14371 slower than 1 ms...
  3. TIME(s)        PID          MS QUERY
  4. 0.000000       18608   130.751 SELECT * FROM words WHERE word REGEXP '^bre.*n$'
  5. 2.921535       18608   130.590 SELECT * FROM words WHERE word REGEXP '^alex.*$'
  6. 4.603549       18608    24.164 SELECT COUNT(*) FROM words
  7. 9.733847       18608   130.936 SELECT count(*) AS count FROM words WHERE word REGEXP '^bre.*n$'
  8. 17.864776      18608   130.298 SELECT * FROM words WHERE word REGEXP '^bre.*n$' ORDER BY word
复制代码
This is a bit like having a custom slow queries log, where the threshold can be picked on the fly.
  It is a    bcctool that uses the MySQL USDT probes (user statically defined tracing) that were introduced for DTrace. bcc is a front-end and a collection of tools that use new Linux enhanced BPF tracing capabilities.  
  USDT support in bcc/BPF is new, and involves allowing BPF code to be attached to USDT probes, eg, from mysqld_qslower:
  1. # enable USDT probe from given PID
  2. u = USDT(pid=pid)
  3. u.enable_probe(probe="query__start", fn_name="do_start")
  4. u.enable_probe(probe="query__done", fn_name="do_done")
复制代码
... and then fetching arguments to those USDT probes. This BPF code hashes the timestamp and the query string pointer (from arg1) to the current thread (pid) for later lookup:
  1. struct start_t {
  2.     u64 ts;
  3.     char *query;
  4. };
  5. BPF_HASH(start_tmp, u32, struct start_t);
  6. int do_start(struct pt_regs *ctx) {
  7.     u32 pid = bpf_get_current_pid_tgid();
  8.     struct start_t start = {};
  9.     start.ts = bpf_ktime_get_ns();
  10.     bpf_usdt_readarg(1, ctx, &start.query);
  11.     start_tmp.update(&pid, &start);
  12.     return 0;
  13. };
复制代码
The full source to mysqld_qslower is    here, and more    example output.  
  The tplist tool from bcc can be used to list USDT probes from a pid or binary. Eg:
  1. # tplist -l /usr/local/mysql/bin/mysqld
  2. /usr/local/mysql/bin/mysqld mysql:filesort__start
  3. /usr/local/mysql/bin/mysqld mysql:filesort__done
  4. /usr/local/mysql/bin/mysqld mysql:handler__rdlock__start
  5. /usr/local/mysql/bin/mysqld mysql:handler__rdlock__done
  6. /usr/local/mysql/bin/mysqld mysql:handler__unlock__done
  7. /usr/local/mysql/bin/mysqld mysql:handler__unlock__start
  8. /usr/local/mysql/bin/mysqld mysql:handler__wrlock__start
  9. /usr/local/mysql/bin/mysqld mysql:handler__wrlock__done
  10. /usr/local/mysql/bin/mysqld mysql:insert__row__start
  11. /usr/local/mysql/bin/mysqld mysql:insert__row__done
  12. /usr/local/mysql/bin/mysqld mysql:update__row__start
  13. /usr/local/mysql/bin/mysqld mysql:update__row__done
  14. /usr/local/mysql/bin/mysqld mysql:delete__row__start
  15. /usr/local/mysql/bin/mysqld mysql:delete__row__done
  16. /usr/local/mysql/bin/mysqld mysql:net__write__start
  17. /usr/local/mysql/bin/mysqld mysql:net__write__done
  18. /usr/local/mysql/bin/mysqld mysql:net__read__start
  19. /usr/local/mysql/bin/mysqld mysql:net__read__done
  20. /usr/local/mysql/bin/mysqld mysql:query__exec__start
  21. /usr/local/mysql/bin/mysqld mysql:query__exec__done
  22. /usr/local/mysql/bin/mysqld mysql:query__cache__miss
  23. /usr/local/mysql/bin/mysqld mysql:query__cache__hit
  24. /usr/local/mysql/bin/mysqld mysql:connection__start
  25. /usr/local/mysql/bin/mysqld mysql:connection__done
  26. /usr/local/mysql/bin/mysqld mysql:select__start
  27. /usr/local/mysql/bin/mysqld mysql:select__done
  28. /usr/local/mysql/bin/mysqld mysql:query__parse__start
  29. /usr/local/mysql/bin/mysqld mysql:query__parse__done
  30. /usr/local/mysql/bin/mysqld mysql:command__start
  31. /usr/local/mysql/bin/mysqld mysql:command__done
  32. /usr/local/mysql/bin/mysqld mysql:query__start
  33. /usr/local/mysql/bin/mysqld mysql:query__done
  34. /usr/local/mysql/bin/mysqld mysql:update__start
  35. /usr/local/mysql/bin/mysqld mysql:update__done
  36. /usr/local/mysql/bin/mysqld mysql:multi__update__start
  37. /usr/local/mysql/bin/mysqld mysql:multi__update__done
  38. /usr/local/mysql/bin/mysqld mysql:delete__start
  39. /usr/local/mysql/bin/mysqld mysql:delete__done
  40. /usr/local/mysql/bin/mysqld mysql:multi__delete__start
  41. /usr/local/mysql/bin/mysqld mysql:multi__delete__done
  42. /usr/local/mysql/bin/mysqld mysql:insert__start
  43. /usr/local/mysql/bin/mysqld mysql:insert__done
  44. /usr/local/mysql/bin/mysqld mysql:insert__select__start
  45. /usr/local/mysql/bin/mysqld mysql:insert__select__done
  46. /usr/local/mysql/bin/mysqld mysql:keycache__read__block
  47. /usr/local/mysql/bin/mysqld mysql:keycache__read__miss
  48. /usr/local/mysql/bin/mysqld mysql:keycache__read__done
  49. /usr/local/mysql/bin/mysqld mysql:keycache__read__hit
  50. /usr/local/mysql/bin/mysqld mysql:keycache__read__start
  51. /usr/local/mysql/bin/mysqld mysql:keycache__write__block
  52. /usr/local/mysql/bin/mysqld mysql:keycache__write__done
  53. /usr/local/mysql/bin/mysqld mysql:keycache__write__start
  54. /usr/local/mysql/bin/mysqld mysql:index__read__row__start
  55. /usr/local/mysql/bin/mysqld mysql:index__read__row__done
  56. /usr/local/mysql/bin/mysqld mysql:read__row__start
  57. /usr/local/mysql/bin/mysqld mysql:read__row__done
复制代码
You can also use "readelf -n .../mysqld" to double check. Applications that have these probes typically need to be compiled with --with-dtrace or --enable-dtrace on Linux for them to be included in the binary.
友荐云推荐




上一篇:48 characters enough to crash most Linux distros, says sysadmin
下一篇:非全屏 Weex 页面开发中的 Android 适配
酷辣虫提示酷辣虫禁止发表任何与中华人民共和国法律有抵触的内容!所有内容由用户发布,并不代表酷辣虫的观点,酷辣虫无法对用户发布内容真实性提供任何的保证,请自行验证并承担风险与后果。如您有版权、违规等问题,请通过"联系我们"或"违规举报"告知我们处理。

点赞 发表于 2016-10-5 07:20:04
在网上碰上一群流氓并不可怕,可怕的是碰上了一堆流氓软件。
回复 支持 反对

使用道具 举报

*滑动验证:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

我要投稿

推荐阅读

扫码访问 @iTTTTT瑞翔 的微博
回页顶回复上一篇下一篇回列表手机版
手机版/CoLaBug.com ( 粤ICP备05003221号 | 文网文[2010]257号 )|网站地图 酷辣虫

© 2001-2016 Comsenz Inc. Design: Dean. DiscuzFans.

返回顶部 返回列表