3

I'm running a single slave off a MySQL master. I'm using this version:

Ver 14.14 Distrib 5.7.13, for linux-glibc2.5 (x86_64) using  EditLine wrapper

The problem I have is that the system will replicate quite happily for a day or so and then something hits it and the log files on the master grow hugely.

The data directory for the database is about 9.5GB in size.

When the replication problem happens, the log files can take up 175GB of disk space in a very short amount of time (filling the disk).

How do I debug what is happening? Can anyone suggest what to look for in the database operations that may cause this huge blow-out?

1 Answer 1

2

I think your replication uses ROW as binlog_format. To verify, please run this on the master:

SHOW VARIABLES LIKE 'binlog_format' 

The result would be one of the following values: statement, row, or mixed. More info.

In the case of ROW format, every changed row will add info to the bin log. If there are statements that affect a table of 1000000 rows, all these changes will be written to the binlog.

For example, suppose you have a huge table, with 1M rows, and you run a statement like this:

UPDATE big_table SET hashed_email=md5(email)

This statement will add 1M entries to the binlog when it is row based, while it adds only one entry to the binlog when it is statement based.

Suggested solution:

Enable the general query log around the time when this this issue usually happens, and analyze all the statements to see if there are many that does update operation on huge number of rows.

4
  • Thank-you! That definitely could be something to do with it. Previously, the system worked on MySQL 5.5, which seems to have used statement-based logging by default. I've swapped over from ROW to STATEMENT for this 5.7.13 installation and am going to monitor it closely now to see if the problem recurs (hopefully not!).
    – Peter K.
    Dec 21, 2016 at 21:07
  • OK, the system has been holding up past where I was seeing problems before. The logging definitely showed me some issues. Thank-you for such a useful answer. :-)
    – Peter K.
    Dec 22, 2016 at 12:20
  • 1
    @PeterK. - This should lead to re-thinking your need for huge UPDATEs/DELETEs. Consider opening another Question to discuss how to redo such. For example, PARTITIONing can eliminate deletes in a time-series.
    – Rick James
    Dec 22, 2016 at 16:43
  • @RickJames Thanks for that! I'm going to go through and see what we can do differently and will definitely ask once I have a sensibly formed question.
    – Peter K.
    Dec 22, 2016 at 19:01

Your Answer

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

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