Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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?

share|improve this answer
    
If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. –  ypercube Apr 10 at 8:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.