Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

One of the databases I maintain has recently increased its write load by 2x.

In order to find out what insert/update statements are causing that load increase, I'm looking for a tool to report and analyze mysql binlog files.

I've looked at Maatkit, which is a superb toolkit for many tasks, but it does only work with slow and general logs.

Before start reinventing the wheel, is there any tool/s (pereferibly opensource) that can help me with this?

Thanks in advance.

share|improve this question

2 Answers 2

up vote 6 down vote accepted

Actually Maatkit's mk-query-digest understands binary log format input too. Daniel Nichter wrote mysqlsla, and subsequently wrote the binary log parser for mk-query-digest. I think I can speak for him and say "use mk-query-digest, it is much more powerful." Try this:

mk-query-digest --type binlog /path/to/file [other options if desired]

share|improve this answer
    
thanks for maatkit and multitude of great blog posts/articles :-] –  pQd Jul 20 '10 at 15:01
    
mk-query-digest does not actually understand the binlog format natively, it needs to be converted to .sql first using binlog. also, "other options" is where all the fun is, at least provide examples for this... there are good ideas here: dba.stackexchange.com/questions/12675/… –  anarcat Mar 18 at 18:20

check out mysqlsla. one of it's nice features: 'canonizing' of requests from:

select a,b,c from talbeName where x=123 and y=23;

to form:

select a,b,c from talbeName where x=? and y=?;

so you can easily get breakdown on query types.

share|improve this answer
1  
mk-query-digest also does that (we call it a query fingerprint) and it is much more complete and powerful. So again, use mk-query-digest instead of mysqlsla; Daniel stopped working on mysqlsla years ago when he started working on mk-query-digest :-) –  Baron Schwartz Sep 1 '11 at 1:23

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.