Description:
According to the MySQL 5.7 dtrace documentation, dtrace was deprecated in 5.7 and removed in 8.0. As far as I can tell, at the time of 5.7 release (2015?), only Solaris was able to consume these tracepoints. While the exact history on dtrace became support on Linux is unclear to me (see more: [1]), MySQL 5.7 with dtrace can be easily compiled in 2021 and works just out of the box via USDT with the installation of the systemtap-sdt-dev package[2]. I have an example repo[3] that demonstrates this with MySQL 5.7, which shows that it is easily traced with its dtrace tracepoints working as USDT probes. Other similar works[4][5] also exists the leverage the same features.
With MySQL 8.0, and as promised, DTrace support was removed. Tracing MySQL on Linux thus has become significantly more challenging. One way is to use uprobe[6], which can hook into function calls and function returns. I've demonstrated this by tracing mysql_execute_command calls and returns in my example repo[3] for both 5.7 and 8.0, which more-or-less emulates the query_exec_start and query_exec_done trace points. However, getting the query out of the function call probes requires the knowledge of the exact offset for certain member variables on arguments passed to methods like mysql_execute_command (in my case, the THD class and the m_query_string variable). This is unstable from one MySQL version to another, which means that all tracing scripts may have to be reworked on every MySQL version upgrade. USDT/DTrace tracepoints, on the other hand, would be stable across versions. Further, USDT tracepoints in 5.7 provide significantly more granular information (such as lock timing, and row-by-row timing information). Reverse engineering MySQL to replicate the same with uprobe would require significantly more effort, especially if the user is not well aware of MySQL internals.
As one can see, the ability to trace MySQL on Linux has been significantly reduced since the removal of dtrace from MySQL on 8.0. It can be argued that this is a regression, as advanced tracing tools build on 5.7 are not longer usable with 8.0 at all. I'm creating this issue to start a discussion about maybe adding dtrace/USDT back to the MySQL codebase, which will enable advanced performance analysis tools to continue to work (or be built) for MySQL on Linux.
[1]: https://www.brendangregg.com/blog/2016-10-27/dtrace-for-linux-2016.html
[2]: https://sourceware.org/systemtap/wiki/AddingUserSpaceProbingToApps
[3]: https://github.com/shuhaowu/mysqld-bpf/
[4]: https://github.com/iovisor/bcc/blob/master/tools/dbslower.py
[5]: https://github.com/iovisor/bcc/blob/master/tools/mysqld_qslower.py
[6]: https://www.brendangregg.com/blog/2015-06-28/linux-ftrace-uprobe.html
How to repeat:
On MySQL 5.7:
- Download MySQL 5.7 source on a recent enough Linux (I'm using Ubuntu 20.04).
- Install systemtap-sdt-dev (on Debian derivatives, not sure what the package name is on other distros).
- Compile MySQL with -DENABLE_DTRACE=1.
- Install bcc v0.23.0 (https://github.com/iovisor/bcc/)
- Run my tracing script in USDT mode (https://github.com/shuhaowu/mysqld-bpf/blob/master/mysql/trace/mysql_query_tracer.py) and see MySQL queries when they're executed.
On MySQL 8.0:
- Download MySQL 5.7 source on a recent enough Linux (I'm using Ubuntu 20.04).
- Install systemtap-sdt-dev (on Debian derivatives, not sure what the package name is on other distros).
- Compile MySQL with -DENABLE_DTRACE=1.
- The previous step won't work.
Suggested fix:
Reintroduce the DTRACE_PROBEs back to the MySQL codebase as previously. Possibly revert these work:
- https://dev.mysql.com/worklog/task/?id=9581
- https://github.com/mysql/mysql-server/commit/574157a61e92e4d374dd674a27b820c1d762415b
I tried on the 8.0 branch and it doesn't revert cleanly:
Unmerged paths:
(use "git restore --staged <file>..." to unstage)
(use "git add/rm <file>..." as appropriate to mark resolution)
both modified: .gitignore
both modified: CMakeLists.txt
both modified: cmake/component.cmake
both modified: cmake/plugin.cmake
both modified: libmysql/CMakeLists.txt
deleted by us: libmysqld/CMakeLists.txt
both modified: mysys/CMakeLists.txt
both modified: mysys/mf_keycache.cc
deleted by us: mysys_ssl/CMakeLists.txt
both modified: sql/CMakeLists.txt
both modified: sql/filesort.cc
both modified: sql/handler.cc
both modified: sql/mysqld.cc
deleted by us: sql/net_serv.cc
both modified: sql/sp_instr.cc
both modified: sql/sql_audit.cc
deleted by us: sql/sql_cache.cc
both modified: sql/sql_call.h
both modified: sql/sql_cmd_dml.h
both modified: sql/sql_connect.cc
both modified: sql/sql_cursor.cc
both modified: sql/sql_delete.h
both modified: sql/sql_insert.h
both modified: sql/sql_parse.cc
both modified: sql/sql_prepare.cc
both modified: sql/sql_select.cc
both modified: sql/sql_select.h
both modified: sql/sql_update.h
both modified: storage/archive/ha_archive.cc
both modified: storage/blackhole/ha_blackhole.cc
both modified: storage/csv/ha_tina.cc
both modified: storage/example/ha_example.cc
both modified: storage/federated/ha_federated.cc
both modified: storage/heap/ha_heap.cc
both modified: storage/myisam/ha_myisam.cc
both modified: storage/myisammrg/ha_myisammrg.cc
both modified: unittest/gunit/CMakeLists.txt
