请选择 进入手机版 | 继续访问电脑版

技术控

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

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

[复制链接]
暧昧害死人 发表于 2016-10-5 05:51:13
164 1
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.
点赞 发表于 2016-10-5 07:20:04
在网上碰上一群流氓并不可怕,可怕的是碰上了一堆流氓软件。
回复 支持 反对

使用道具 举报

我要投稿

回页顶回复上一篇下一篇回列表
手机版/c.CoLaBug.com ( 粤ICP备05003221号 | 文网文[2010]257号 | 粤公网安备 44010402000842号 )

© 2001-2017 Comsenz Inc.

返回顶部 返回列表