maria-developers team mailing list archive

[Kristian Nielsen <knielsen@xxxxxxxxxxxxxxx>]

I plan to implement MDEV-12179, per-engine mysql.gtid_slave_pos. Here is a
description of the high-level design, as a request for comments and/or
suggestion for changes.

The purpose of this is to fix a serious performance issue in replication
when multiple storage engines are used. Every replicated transaction updates
the replication GTID position in table mysql.gtid_slave_pos. There is a
large overhead if that table is using another storage engine than the rest
of the transaction, as then a cross-engine XA transaction needs to be done.
But if more than one storage engine is used on a server, some replicated
transactions will necessarily end up being cross-engine.

This problem becomes more interesting to fix with the new interest in
MyRocks. Though it already exists also for eg. people using MyISAM or TokuDB
simultaneously with InnoDB, for example.

The basic idea is to create multiple copies of the table, eg.
mysql.gtid_slave_pos_innodb and mysql.gtid_slave_pos_myrocks. This way, an
InnoDB transaction can update the InnoDB version of the table, and similar
for other engines.

To enable the feature, the DBA would create extra copies of the table for
the engines she wants:

  CREATE TABLE mysql.gtid_slave_pos_myrocks LIKE mysql.gtid_slave_pos;
  ALTER TABLE mysql.gtid_slave_pos_myrocks ENGINE=myrocks;

The server will look for all tables called mysql.gtid_slave_pos* at startup,
as well as on each START SLAVE.

This method makes migration from earlier versions simple. MariaDB already
has the ability to work with multiple redundant rows in the
mysql.gtid_slave_pos table. The column "sub_id" provides a version to
identify the most recent entry. So the server just needs to read multiple
tables (if present), and pick the newest version amongst all rows. Thus, the
new feature is compatible with older versions without any schema changes.

I think it is better than automatically creating copies of the table for all
engines; it is often the case that an engine (eg. TokuDB) is available in
the server binary, but the user does not want any tables created in that
engine.

This means that no speedup is obtained if the user does not create
additional mysql.gtid_slave_pos* tables. (However, things should still work
fine, thanks to XA synchronising cross-engine transactions).

 - Kristian.