0

We have a MySQL master database which replicates to a MySQL slave. We were experiencing issues where MySQL was showing a high number of writes (but not an increased number of queries being ran) for a short period of time (a few hours). We are trying to investigate the cause.

Normally our binary logs are 1 GB in file size but during the period that we were experiencing these issues, the log files jumped to 8.5 GB.

When I run mysqlbinlog --short-form BINARYLOG.0000 on one of the 8.5 GB binary log it only returns 196 KB of queries and data. When I run mysqlbinlog --short-form on a normal binary log (1 GB) it returns around 8,500 KB worth of queries and database activity. That doesn't make sense because it has 7 GB more of data yet returns less than a 1 GB binary log file.

I see lots of these statements with very sequential timestamps, but I'm not sure if that's related to the problem because they're in both the normal period as well as when we experienced these issues.

SET TIMESTAMP=1391452372/*!*/;COMMIT/*!*/;
SET TIMESTAMP=1391452372/*!*/;BEGIN/*!*/;COMMIT/*!*/;
SET TIMESTAMP=1391452372/*!*/;BEGIN/*!*/;COMMIT/*!*/;
SET TIMESTAMP=1391452372/*!*/;BEGIN/*!*/;COMMIT/*!*/;

How can I determine what caused those binary logs to balloon in size which also caused high writes, so much so it took the server offline at points, almost like a DDoS attack would?

How could mysqlbinlog return so much less data, even though the binary log file itself had 7 GB more? What can I do to identify the difference between a normal period where the binary logs are 1 GB to the period we had issues with the 8 GB binary log? Thank you for any help you can provide.

Bill

Improve this question

2 Answers 2

Reset to default
1

Don't use --short-form. You are suppressing much of what you want to see.

--short-form

Display only the statements contained in the log, without any extra information or row-based events. This is for testing only, and should not be used in production systems.

— http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_short-form

That is a largely pointless option, since it returns only the statements contained in the log. If you have a binlog that large, it's unlikely that it's full of statements -- it's full of row events.

--base64-output=decode-rows --verbose are the options you are looking for.

http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog-row-events.html

You will, at the very least, not be disappointed by the quantity of data returned... it will be much larger than the actual binlogs themselves, which are stored in a relatively tightly-packed format.

Improve this answer
2
  • I was using the --short-form in order to make the file more manageable in terms of opening and searching. I've tried gVim and it crashes on the 8GB files. I'll see what I get using your options and if it's something I can open and look at without it crashing.
    – billvsd
    Feb 6, 2014 at 21:10
  • You aren't likely to be able to open it with anything, but you can pipe the output of mysqlbinlog through less and page through it, using the space bar, and use / followed by a simple text string or regular expression to seek forward in the stream to the next occurrence of the search string. Control-u to go up a screen or ctrl-d to go down. Or pipe through grep to find or count occurrences of specific things. Or use split or sed or tail or head to divide it into more manageable chunks.
    – Michael - sqlbot
    Feb 6, 2014 at 23:23
0

Check the value of binlog-row-image. If it is full, try to change it to minimal.

Details: Binary Log Options and Variables

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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