TL/DR: There’s been talk of moving the next release of MySQL to minimal RBR: I’d like to suggest an alternative: lossless RBR
For MySQL 5.8 there was talk / suggestions about moving to minimal RBR as the default configuration (http://mysqlserverteam.com/planning-the-defaults-for-mysql-5-8/). I’m not comfortable with this because it means that by default you do not have the full data in the binlog stream of the changes to a table.
The use of minimal RBR is an optimisation, it is done deliberately in busy environments where the size of written data is large and it is not convenient/possible to keep all data. Additionally the performance of full RBR can in some cases be an issue especially for “fat/wide” tables etc. It is true that minimal RBR helps considerably here. There are several issues it resolves:
- reduces network bandwidth between master and slaves
- reduces disk i/o reading / writing the binlog files
- reduces disk storage occupied by said binlogs
There was also a comment about enabling IDEMPOTENT mode by default on a slave.
This is a mode which basically ignores most errors. That does not seem wise. As a DBA by default you want the server to not lose or munge data. There are times when you may decide to forgo that requirement, but the DBA should decide and the default behaviour should be safe.
Thus the idea of lossless RBR came to mind. What would this involve compared to the current modes of FULL or MINIMAL RBR?
- INSERTs are unchanged (as now): you get the full row
- DELETEs are as per minimal RBR: The primary key is sent and the matching row is removed. IFF on a slave the pks differed and more than one row would be deleted this should be treated as an error.
- UPDATEs: Send the pk + full new image, thus ensuring that all new data is sent. This reduces the event size by ~ 1/2 so would be especially good for fat tables and tables where large updates go through. If the PK columns do not change then it should be sufficient to send the new row image and pk column names etc
Related to this behaviour it would be most convenient to implement an existing FR (bug#69223) to require that table definitions via CREATE/ALTER TABLE MUST HAVE a PK. I’ve seen several issues where a developer has not thought a primary key was important (they often forget replication) and this would trigger problems. Inserts would work fine but any updates that happened afterwards would trigger a problem, not on the master but on all slaves. I think that by default this behaviour should be enabled. There may be situations where it needs to be disabled but they are likely to be rather limited.
This new mode LOSSLESS RBR is clearly a mix between full and minimal and it ensures that data pushed into a slave will always be complete. I think that is a better target to aim for with MySQL 8.0 than the suggested MINIMAL RBR.
You may know that I do not like IDEMPOTENT mode much. I have created several FRs to add counters to “lost/ignored events” so we can see the impact of using this mode (usually it is used after an outage to keep replication going even if this may mean some data is not being updated correctly. Usually this is better than having a slave with 100% stale data.)
I would really also like to see you adding a “safe recovery mode” where statements which won’t damage the slave more are accepted.
The examples are in bug#54250 but basically include:
- INSERTs with duplicate key: convert to UPDATE
- DELETEs with row not found: ignore as the data has gone anyway
- UPDATEs with non-matching PK: convert to INSERT
- UPDATEs with non-matching columns: update what you can. (This is likely to happen with full RBR as minimal RBR should never generate this type of error.)
[ For each of these 4 states: add counters to indicate how many times this has happened, so we can see if we’re “correcting” or “fixing” errors or not. ]
You’ll notice that lossless RBR would work perfectly with this even after a crash as you’ll have all the data you need, so you’ll never make the state of the database any worse than it was before.
I would like to see the FRs I’ve made regarding improving RBR being implemented as whether lossless RBR becomes a new replication mode or not they would help DBAs both diagnose and fix problems more easily than now.
It is probably also worth noting that FULL RBR is actually useful for a variety of scenarios, for example for exporting changes to other non-MySQL systems. We miss for this the definition of tables, and current systems need to extract that out of band which is a major nuisance. Exporting to external systems may not have happened that frequently in the past, but as larger companies use MySQL this becomes more and more important. For this type of system FULL RBR is probably needed even though it may not be used on the upstream master. I would expect that in most cases LOSSLESS RBR would also serve this purpose pretty well and reduce the replication footprint. The only environment that may need traditional FULL RBR is where auditing of ALL changes in a table is needed and thus both the before and after images are required.
Is it worth adding yet another replication mode to MySQL? That is a good question and it may not be worth the effort. However the differences between FULL and LOSSLESS RBR should be minimal: the only difference is the amount of data that’s pushed into the binlog so the scope of changes etc should be more limited. Improving replication performance seems to be a good goal: we all need that, but over-optimising should be considered more carefully. I think we are still missing the monitoring metrics which help us diagnose and be better aware of issues in RBR and the “tools” or improvements which would make recovery easier. Unless you live in the real world of systems which break it is hard to understand why these “obscure” edge cases matter that much. The responses like: “just restart mysqld” may make sense in some environments, but really are not realistic in systems that run 24x7x365. With replication it is similar: stopped replication is worse than replication that is working, but where data may not be complete. Depending on the situation you may tolerate that “incomplete data” (temporarily) while gaining the changes which your apps need to see. However, it is vitally important to be able to measure the “damage” and that is why counters like the ones indicated above are so vital. It allows you to distinguish 1 broken row, or 1,000,000 and decide on how to prioritise and deal with that as appropriate.
While I guess the MySQL replication developers are busy I would certainly be interested in hearing their thoughts on this possible new replication mode and would definitely prefer it over the suggested minimal RBR as a default for 8.0. Both FULL and MINIMAL RBR have their place, but perhaps LOSSLESS would be a better default? What do you think?