2

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.

Warner
  • 23,440
  • 2
  • 57
  • 69
scetoaux
  • 1,269
  • 2
  • 12
  • 25

2 Answers2

7

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]

  • 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: http://dba.stackexchange.com/questions/12675/mysql-reads-writes-per-table – anarcat Mar 18 '15 at 18:20
1

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.

pQd
  • 29,561
  • 5
  • 64
  • 106
  • 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 :-) –  Sep 01 '11 at 01:23
  • The link to mysqlsla is broken. Do you have a replacement? – Jules Aug 14 '15 at 08:48