Comments on: pt-online-schema-change and binlog_format

By: Mike Benshoof

Mike Benshoof — Thu, 27 Jun 2013 19:28:21 +0000

@Ronald – Thanks for clarifying! I wasn’t suggesting to switch to RBR exclusively, but rather to just use the session level (via –set-vars in pt-osc) if the slave was already IO bound on due to read queries and a small buffer pool.

And yes, the comment on testing and verification is extremely important as this approach is very workload dependent.

By: Ronald Bradford

Ronald Bradford — Thu, 27 Jun 2013 15:15:09 +0000

@nettedfish The use of RBR can be an increase in Binary Log size depending on the types of queries you execute. It some well structured applications it’s not a problem (i.e. not updating thousands of rows in one query).

MySQL 5.6 also introduces binlog_row_image (http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_row_image) which can reduce the footprint.

binlog_format is also dynamic, so technically you could switch to RBR for the duration of OSC if the impact offset is of benefit. As always testing and verification is necessar.y

By: Mike Benshoof

Mike Benshoof — Tue, 25 Jun 2013 13:17:41 +0000

Indeed, that is one of the downsides to using RBR, especially in this context (altering a large table). In the case where disk space for the binlogs is a major concern, then this approach may not be the best or even feasible.

Like most options, there is usually a tradeoff when changing a setting like this.

Aside from the disk space, if write IO is already approaching it’s limits on the master, this may not be a good approach. Unfortunately, as is true with most things, this solution is workload dependent.

By: nettedfish

nettedfish — Tue, 25 Jun 2013 10:35:24 +0000

binlog produced by RBR would be very large.