DTrace and MySQL

DTrace is a framework present in Solaris and Apple’s Leopard, and allows for real-time and integrated analysis of userland programs and kernels. DTrace manages to accomplish this on production systems without impacting the performance of the software. Despite the lack of embedded probes for MySQL, we can still get useful information from MySQL, information that often is not readily obtainable by other means, and in fact allows us to have an even more complete set of data then what is present in MySQL logs.

DTrace has a pid provider that allows us to look into any function of a program. The only drawback is that you have to look through the source code to identify the functions; however, this also demonstrates how one builds probe sets into userland programs, and actually has the advantage of consolidating all your analysis using only the single pid provider.

Rockwood will demonstrate how:

• It’s possible to get real time statistical data about what queries are being executed and how often. Typically this kind of information comes from the parsing of a MySQL query log

• It’s possible to sort queries based on execution times and you can even determine “slow” queries that are not even logged by MYSQL slow query log

• It’s possible to evaluate query cache hit rates in real time

• It’s possible to be free of any logging in MySQL yet have even more performance and behavioral data

• To integrate DTrace’s view of MySQL with its view into web applications that are accessing

DTrace can be a very powerful tool in analyzing MySQL performance and behavior, and in gaining a complete understanding of the flow of data from application to database back to application.