Of course there are other improvements on top of this as well, such as the new replication tables, but I won’t go in to that here. Today I want to focus on the kind of data you can now get in MySQL 5.7 for debugging what a foreground user connection has been doing.

The problem is that a lot of that extra data that I wanted exposed is really of a structured form, such as the last N statements (or in 5.7, last N transactions even) that a connection has executed, or a summary of wait information, for example. Everybody loves JSON now’a’days, so that was the obvious choice to me – I wanted a JSON object, per thread, of the available data from Performance Schema.

And so started the writing of a query from hell. But the output is, if I may say so myself, a thing of beauty.

Lets whet that appetite a little (and you may need to scroll to the right):

This is a snapshot of one of the connections from a MySQL Enterprise Monitor server, with this particular connection having inserted some data related to the Query Analyzer, with one past transaction that inserted a normalized and example statement in a single transaction with a final commit, and currently inserting another normalized statement.

Because it is currently executing an INSERT on the `memtrunkio__quan`.`normalized_statements_by_server_by_schema` table, you can also see that it holds a SHARED_WRITEmetadata lock (for the duration of the TRANSACTION) on that table, and holds a table handle on that table as well.

Finally, you can see a summary of all of that connections memory usage, and timing information for both stages and lower level waits for those transactions and statements that it has executed.

Whilst developing this output, I’ve been using it almost exclusively against our own MEM schema. In the process it’s managed to help me find some interesting bugs. For instance, here’s a previous incarnation of that output, that helped find a bug where we were not committing between DELETE statements when purging data from all of our instrument tables. Ultimately this meant we were holding open huge transactions, with many many metadata locks being held across all of the instrument tables:

Yea I think this is just my copy/paste from my terminal. I’ve tried to make sure it does validate all along the way, so if you find anything that doesn’t validate when trying it directly (rather than my copy/pasted stuff), let me know.