Lossless RBR for MySQL 8.0?

Lossless RBR

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?

  1. INSERTs are unchanged (as now): you get the full row
  2. 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.
  3. 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.  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:

  1. INSERTs with duplicate key: convert to UPDATE
  2. DELETEs with row not found: ignore as the data has gone anyway
  3. UPDATEs with non-matching PK: convert to INSERT
  4. 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?

Published by

Simon J Mudd

Born in England, I now live in Spain, but spent a few years living in the Netherlands. I previously worked in banking (financial markets) both in IT and as a broker, but IT has always had a stronger influence. Now working at booking.com as a Senior Database Administrator. Other interests include photography, and travel. Simon is married, with two children and lives in Madrid.

7 thoughts on “Lossless RBR for MySQL 8.0?”

  1. I fully agree with the “lossless” format:
    IMNSHO it is important that replication provides all values which may be needed to get the slave in sync with the master, so an UPDATE should include all fields (new values) rather than only the changed ones.

    I don’t yet see the important difference between Simon’s “safe recovery” and Oracle’s “idempotent” mode.
    AIUI, both would solve a real-world problem: When replication is running without GTIDs and some master crashes, they would allow to point the slave at another master at a position earlier in time, and the slave would repeat changes it had already processed via its original connection (rather than stop because of duplicate PK, missing PK and similar).

    1. Hi Jörg,

      To be honest using the current Full RBR and idempotent mode you may damage your data and you don’t notice as the change is made and given there are currently no counters there is no visibility into this. e.g. the before image does not match the row content on the server. This is very bad. If the before image matches there’s no problem.

      If this was not clear from my earlier feature request then I’m sorry as there are cases where things work fine, but there are also cases (say after a crash) where the on disk “row image” does not match what replication expects. This must be handled differently and currently your blanket IDEMPOTENT mode, while it may be a “get out of jail free” type setting it leaves the DBA with no confidence in his data and no knowledge of whether damage actually happened or not. That’s what I was looking for.

      If the full before/after image is provided then there’s information to catch such differences.

      Note: I’ve not made a FR for this but I’d love that minimal RBR provided some sort of extra checksum: assuming the storage format on master and slave is the same then provide or calculate an on-disk row checksum (including column definitions) and use that to match on the server. If the row definition does not match, as the DBA may have changed some settings on the slave, then you can’t check this, but if you can even with something like minimal RBR you are able to verify that the whole row is valid even when using minimal RBR. That’s not possible now.

      Does that explain what the difference is? I hope so. As the FR has been there for a long time. I use both full RBR and minimal RBR but the the minimal RBR is a performance optimisation which I’d not necessarily configure unless it’s necessary. (Hence the thought of the “lossless” setup which is more compact, yet provides enough info to be “safe”.)

  2. Hi Simon,

    thank you very much for your explanation, now I think I got the difference.
    I understand and share your desire to be informed when the before image differs on the slave. But even if there are such differences (from whatever reason – hardware defect, software bug, admin fault, …), the DBA’S best course may be to go forward, by letting replication continue in spite of them – and for that we need a clean way.

  3. I like the new proposed mode Lossless RBR as it make sense to retain just needed data on binary logs. But, as the write up went far away from just discussing the new binary log mode, I would like to say that, to have IDEMPOTENT as the default slave_exec_mode does not really make sense as sometimes you need to rely on some other tools to complete the work or rebuild completely a slave. If IDEMPOTENT is needed, this something the DBA is the person to decide on. Additionally, when slave_exec_mode is equal to IDEMPOTENT, it really make sense to have an additional status variable to tell how many offending rows were skipped in order to keep replication rolling.

Leave a Reply