How to include Innodb_trx_id in MySQL slow log

Question

Evaluating MySQL's slow query log with pt-query-digest from the Percona Toolkit, I need to group queries by transaction. As it seems, the easiest way to do this is to group by the Innodb_trx_id attribute.

However, my logs don't include this attribute. I tried with vanilla MySQL, MariaDB and Percona Server -- so I figure, you probably need to enable logging of this attribute. Any pointers on how to do this?

Thanks, Victor

Answer 1

The extended fields that Percona added are not available in stock MySQL.

The only other option is to add a comment to your SQL query in your app. The comments are added to the log, and pt-query-digest can parse fields out of comments with the --embedded-attributes option.

It's a bit tricky to discover the transaction id. You can query the transaction for your current thread:

SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX 
WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();

But you find no row in that table until your transaction has created any locks. That is, a transaction that does nothing but SELECT will not show up in that table.

You could simply embed CONNECTION_ID() into your query comments, on the assumption that grouping by connection is as good as grouping by transaction.

Answer 2

MySQL Slow log is completely a separate entity from innodb_trx information which you get from Information_Schema innodb_trx table.

MySQl slow log logs all slow running queries specified while enabling it, it does not matter whether queries are within TRANSACTION or not slow log will record all slow running queries according to options specified in my.cnf file.

I suggest to understand these tables from I_S Innodb_trx, innodb_lock_waits and innodb_locks tables from I_S and analyze result of it.

Answer 3

As I found out, this seems to be a feature (exclusively?) available on Percona server. There, it can be enabled with "log_slow_verbosity=innodb" in my.cnf.

http://www.percona.com/doc/percona-server/5.5/diagnostics/slow_extended_55.html

Does anyone know if there is a way to enable this information on a regular MySQL server?