maria-developers team mailing list archive

[Kristian Nielsen <knielsen@xxxxxxxxxxxxxxx>]

Zardosht Kasheff <zardosht@xxxxxxxxx> writes:

> In this email, I will focus on (and hope to understand better) just
> the problems with having the binary log be an InnoDB table.

> I don't see issues with using an increasing uint64 as the primary key.
> You ask how we ensure that the order in the table is the same as the
> commit order in InnoDB. Why does this matter? As long as the user work
> done to InnoDB tables and changes to the binary log are done with the
> same transaction (the way MySQL 5.6 updates the relay-log info on
> slaves with the same transaction) I don't understand why matters. If
> XtraBackup works properly, then the backed up data should have
> something that is consistent across the board.

Yes, it is a subtle issue. Let me explain it in more depth.

Suppose we have 4 transactions: T1 T2 T3 T4. They run in parallel and commit
around the same time.

When we allocate rows for them in the binlog (in an InnoDB table), we happen
to assign them numbers like this:

    1: T1
    2: T2
    3: T3
    4: T4

And in the InnoDB redo log, they happen to be committed in order: T1 T3 T4 T2.

Now suppose a non-blocking XtraBackup is running in parallel with this with
the intention of provisioning a new slave. XtraBackup happens to take a
snapshot of InnoDB that has T1 T3 T4 committed (but not T2).

We restore the XtraBackup to a new slave. Now the problem is - which binlog
position should the slave start replicating from? If we start after (4: T4),
then we will have lost T2 on the slave. If we start at (2: T2), then we will
duplicate T3 and T4 on the slave. So the problem is that without a consistent
binlog order between innodb redo log and the binlog, we do not have a unique
position to start replicating from in the new slave.

So this is a small thing perhaps - you can just eg. give up on non-blocking
XtraBackup provisioning of slaves.

And anyway, it just occurs to me that MySQL 5.6 global transaction ID does not
have this issue, because it anyway gives up on having a consistent binlog
order. Instead it keeps track of all applied and not applied transactions, so
should be able to replicate T2 and skip T3 and T4. You might still need to
support non-GTID replication though.

BTW, a closely related idea would be to store the binlog inside the InnoDB
redo log (as extra info logged along with the transaction). This would solve
most problems, I believe. This might work well for many transactional storage
engines. The problem with InnoDB is that it has a cyclic log, so there is no
way to ensure that old binlogs are not overwritten while slaves might still
need them.

> As far as the performance issues go, you say we are writing the data 6
> times. With the current solution, we write the data four times, three
> for InnoDB and once to the binary log. So really, there seems to be a

Agree.

So as I said, it is tempting. Just use a table in the storage engine, and get
all the transactional consistency for free. We have a lot of nasty problems in
current MySQL/MariaDB because of things that write to all kinds of different
files, rather than use a common transactional framework.

So what I am saying - I thought a lot about this and similar ideas a couple of
years ago. I ended up deciding not to go this way, because of above-mentioned
problems and probably others that I have forgotten. But it is not clear that
it cannot work.

Of course, there will be quite a lot of practical work to move the binlog to a
storage engine. I suppose you have in mind a general extension to the storage
engine API so that other engines could own the binlog as well? A lot of
existing infrastructure and tools would need to deal with binlog tables rather
than binlog files.

Maybe the decisive factor was mostly that keeping the binlog, and slowly
adding improvements, can be done in small, evolutionary steps. So it seems the
more realistic approach, compared to a revolutionary approach of completely
rewriting the binlog.

I do not have the final answer. It would definitely be nice to see us move
towards being more transactional. It is a hard journey though.

 - Kristian.