Re: [Maria-developers] RFC: new replication feature "per-engine mysql.gtid_slave pos"

[Kristian Nielsen]

Ian Gilfillan  writes:

> In order to document this properly (and in response to

Great! Thanks for looking into this.

> https://jira.mariadb.org/browse/MDEV-13236) , I want to clarify a few
> things:

The main documentation is here, I'm sure it can be improved or made more
visible:

  https://mariadb.com/kb/en/mariadb/mysqlgtid_slave_pos-table/

> As I understand it:
> * the variable is dynamic, so changes becomes effective immediately

Yes. Note that changing it requires that all slaves are stopped. So in that
sense, it only takes effect once slaves are running again.

> * tables are created immediately whenever a transaction is seen from a
> specified engine (listed in the variable)

Yes, for some value of "immediately". As mentioned in
https://mariadb.com/kb/en/mariadb/mysqlgtid_slave_pos-table/, the creation
is asynchroneous.

What this means is that, the need to create a table is discovered inside
record_gtid(), which is deep in the replication of the transaction when it
is time to commit it. It is not a good place to try to create a table. So
the transaction continues without the new table, and the table creation is
started in a background thread. So most likely the table will only be
available a bit later, after that transaction (and possibly a few other)
have been replicated.

Usually, the table should be created in a fraction of a second. Also note
that thanks to two-phase commit, the system is behaving correctly (in terms
of crash safety and consistency) also for the transactions that complete
prior to the new table being available.

> * the manual creation of the tables was an initial design suggestion,
> and there should be no need to ever create a table manually.

Yes, generally there should be no need to ever create a table manually. The
initial table is created by mysql_install_db (or mysql_upgrade), and
--gtid-pos-auto-engines can create any other tables required.

The one situation I can think of where manual action makes sense is as
explained in the documentation, in a system using primarily MyISAM, but the
database was created as InnoDB. In that case it can make sense to ALTER the
table to MyISAM (but not to create a table manually).

> I don't have 10.3.1 running yet to test, but some questions:
> * what happens if slave threads are not stopped before changing the
> variable? Just delay the creation of the table, or can something
> unpleasant happen from a table being created in the middle of
> something?

As explained in the documentation, this results in an error that slaves must
be stopped before changing the variable, similar to many other replication
variables.

> * what happens if a storage engine is later removed from the variable?
> If the table still exists, I presume the existence of the table takes
> priority and the table is still used - the variable is just used to
> check the existence of a table and create it if needs be, not
> determine its use?

Yes. The variable only concerns the _creation_ of the table. The _use_ of
the table is automatic whenever the table is there.

> * what benefit does the variable being empty (the default) provide? I
> saw the comment about the principle of least surprise, but in what way
> could it being populated and the tables existing be negative? In other
> words, why would one not simply recommend setting this immediately?

As I wrote before, I do not have any strong opinions on this.

The feature is designed to work well in all cases. Generally a default of
--gtid-pos-auto-engines=innodb,tokudb,rocksdb should be beneficial. It was
always a sore spot in the original GTID implementation that there was no
really good answer to which engine the mysql.gtid_slave_pos table should be
created with, and it has caused real problem as well. If the
mysql.gtid_slave_pos table was created as MyISAM, and
--gtid-pos-auto-engines=innodb,tokudb,rocksdb set as default, then there
will be a decent solution to this issue.

There are also some arguments in favour of an empty default, at least
initially until more testing has been done. MariaDB replication is extremely
complex code, there can be bugs. As you say, there is the principle of least
surprise. New tables magically springing into life during replication does
have some element of unpleasant surprise. And do not forget that this
feature is mostly/only useful to users that want to use TokuDB and RocksDB
(both still relatively new), _and_ at the same time use InnoDB, in a
performance-critical setting. Not a common usecase.

Technically, setting a non-empty default is just putting the right default
in sql/mysqld.cc, the code is already written to handle it. The current
empty default is based on the feedback from the users who took an interest
in the feature so far.

> * If there is a benefit to it being empty (and therefore no multiple
> tables created), will there be a mechanism to remove a table beyond
> manually deleting?

There is currently no such mechanism, and there should not be 

Re: [Maria-developers] RFC: new replication feature "per-engine mysql.gtid_slave pos"

[Ian Gilfillan]

In order to document this properly (and in response to 
https://jira.mariadb.org/browse/MDEV-13236) , I want to clarify a few 
things:


As I understand it:
* the variable is dynamic, so changes becomes effective immediately
* tables are created immediately whenever a transaction is seen from a 
specified engine (listed in the variable)
* the manual creation of the tables was an initial design suggestion, 
and there should be no need to ever create a table manually.


I don't have 10.3.1 running yet to test, but some questions:
* what happens if slave threads are not stopped before changing the 
variable? Just delay the creation of the table, or can something 
unpleasant happen from a table being created in the middle of something?
* what happens if a storage engine is later removed from the variable? 
If the table still exists, I presume the existence of the table takes 
priority and the table is still used - the variable is just used to 
check the existence of a table and create it if needs be, not determine 
its use?
* what benefit does the variable being empty (the default) provide? I 
saw the comment about the principle of least surprise, but in what way 
could it being populated and the tables existing be negative? In other 
words, why would one not simply recommend setting this immediately?
* If there is a benefit to it being empty (and therefore no multiple 
tables created), will there be a mechanism to remove a table beyond 
manually deleting?



On 03/07/2017 15:15, Kristian Nielsen wrote:

I have now pushed the code to 10.3. It should appear in an upcoming MariaDB
10.3.1 release, IIUC.

Following the discussion so far, the default for --gtid-pos-auto-engines is
currently empty. It can be easily changed later (eg. to
innodb,tokudb,rocksdb) simply by changing the default value in
sql/mysqld.cc. The code will allow this default setting even if eg. tokudb
or rocksdb is not enabled.

(There might be a need to adjust a couple test cases that specially
manipulates mysql.gtid_slave_pos in devious ways).

Let me know in case of any problems or if there are comments/questions.

  - Kristian.




___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] RFC: new replication feature "per-engine mysql.gtid_slave pos"

[Kristian Nielsen]

I have now pushed the code to 10.3. It should appear in an upcoming MariaDB
10.3.1 release, IIUC.

Following the discussion so far, the default for --gtid-pos-auto-engines is
currently empty. It can be easily changed later (eg. to
innodb,tokudb,rocksdb) simply by changing the default value in
sql/mysqld.cc. The code will allow this default setting even if eg. tokudb
or rocksdb is not enabled.

(There might be a need to adjust a couple test cases that specially
manipulates mysql.gtid_slave_pos in devious ways).

Let me know in case of any problems or if there are comments/questions.

 - Kristian.

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] RFC: new replication feature "per-engine mysql.gtid_slave pos"

[Kristian Nielsen]

I have now most of the implementation of MDEV-12179 done. I wanted to
present the way the feature now looks, and point to the code, in case there
are any further comments on the design or implementation before it is
finalised.

To recap, the idea is to improve performance when using multiple
transactional storage engines (eg. InnoDB/TokuDB/MyRocks) on the same
replication slave, but not in the same transactions. By allowing more than
one mysql.gtid_slave_pos* tables (one for each used engine), costly
cross-engine transactions can be avoided.

The feature is enabled by a system variable:

  --gtid-pos-auto-engines=innodb,myrocks

This way, if the replication slave sees an innodb or myrocks transaction, it
will create a new table mysql.gtid_slave_pos_innodb or
mysql.gtid_slave_pos_myrocks in which to record the GTID position.

For monitoring the feature, I added new status variables:

Transactions_multi_engine

  Number of transactions that changed data in multiple (transactional)
  storage engines.

Rpl_transactions_multi_engine

  Number of replicated transactions that involved changes in multiple
  (transactional) storage engines, before considering the update of the
  mysql.gtid_slave_posXXX table.

Transactions_gtid_foreign_engine

  Number of replicated transactions where the update of the
  mysql.gtid_slave_posXXX table had to choose a storage engine that did not
  otherwise participate in the transaction.

The current code is here:

  https://github.com/knielsen/server/tree/mdev12179

Following previous discussions, the default of --gtid-pos-auto-engines is
currently empty (no automatic creation of tables by default). If desired, we
could later enable auto-creation of a select set of storage engines (eg
"innodb,myisam").

The auto-creation happens asynchroneously, in the background. Auto-creation
should be a very rare event, and I spent some effort to minimise the
overhead of this auto-creation on the normal processing of replicated
transactions. On server start, as well as on START SLAVE, the available
mysql.gtid_slave_pos* tables are auto-discovered and read as needed.

Originally, I had the idea to supply a store procedure
mysql.gtid_pos_add_engine() which would allow the DBA to explicitly create a
table for a specific engine. But it seems such stored procedure is not much
needed when --gtid-pos-auto-engines is available, and we do not have a prior
history of providing such standard stored procedures. It could be easily
added later, if desired.

 - Kristian.

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] RFC: new replication feature "per-engine mysql.gtid_slave pos"

[Kristian Nielsen]

Jonas Oreland  writes:

> how about
> --gtid_auto_create_engine_list=
>
> default value = innodb,tokudb
>
> (and the stored proc)

Yes, I like that a lot better, thanks for the suggestion.

And following Jean-François' suggestion, I guess the default would be empty
in the first version, and could later be extended to some reasonable
non-empty list.

 - Kristian.

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] RFC: new replication feature "per-engine mysql.gtid_slave pos"

[Will Fong]

Hi Kristian!

On Wed, Mar 8, 2017 at 4:58 PM, Kristian Nielsen
 wrote:
> One thing that could be done is to supply a standard stored procedure to add
> an engine table, to avoid direct changes to the mysql database:
>
>   mysql.gtid_pos_add_engine("MyRocks");

My concern is about configuration management and automated
deployments. Having an extra "query" step is not so ideal...


> So to summarise
>
>  - Auto create mysql.gtid_slave_pos_{innodb,tokudb,myrocks} if a transaction
>is seen in one of those engines
>
>  - A --skip-gtid-auto-create-pos-table to disable this auto behaviour
>
>  - A stored procedure mysql.gtid_pos_add_engine() to facilitate manually
>adding an engine table.
>
> What do you think, does this sound ok? Suggestions for better ways?

I like this idea very much!!!


Thanks,
-will


-- 
Will Fong, Senior Support Engineer
MariaDB Corporation

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] RFC: new replication feature "per-engine mysql.gtid_slave pos"

[Kristian Nielsen]

jocelyn fournier  writes:

> Why not using the XA support flag of the engine to check if we should
> create the gtid_slave_pos_{engine} file ?

The only additional engine currently that supports XA is Spider, if my grep
skills are not failing me.

But Spider does not seem appropriate for automatically creating
mysql.gtid_slave_pos_spider. I am not even sure it is possible to create a
Spider table without specifying some remote server to connect to.

In general, I am fine with choosing auto-creating engines based on some
generic criteria, if that is what people wants.

It just seems to me better to write in the documentation an explicit list of
engines that users can expect to have automatically created tables for, to
try to avoid unpleasant surprises. Like this one with the Spider engine. So
adding an engine that automatically creates a table requires a conscious
decision.

Of course, normally we try to avoid hardcoded lists of specific engines. But
we do have it in some places. For example, there is a list of engines that
are built-in to the server by default. Maybe auto-creating
mysql.gtid_slave_pos could be considered similar.

But let me know what you think.

 - Kristian.



___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] RFC: new replication feature "per-engine mysql.gtid_slave pos"

[jocelyn fournier]

Hi Kristian!

Le 08/03/2017 à 09:58, Kristian Nielsen a écrit :

Will Fong  writes:

But even better woulf be if most users did not have to do anything to get
the improved performance. Maybe we could just have a hardcoded list of
engines that are suitable for mysql.gtid_slave_pos (eg. innodb, tokudb,
myrocks). And replication will automatically create a suitable
mysql.gtid_slave_pos_XXX if it sees a transaction in one of those engines,
unless --skip-gtid-auto-create-pos-table is set. For more
exotic/experimental engine, the user can manually call
mysql.gtid_pos_add_engine() if desired.
Why not using the XA support flag of the engine to check if we should 
create the gtid_slave_pos_{engine} file ?


  Jocelyn


___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] RFC: new replication feature "per-engine mysql.gtid_slave pos"

[Kristian Nielsen]

Will Fong  writes:

> What about a configuration setting in my.cnf that will do the above magically?
>
> I would prefer not making direct changes to the mysql database.

I agree that this is not ideal.

But do you have a suggestion for how the semantics of such an option should
be? Too much magic is not good either.

For example, if the user does a blackhole engine transaction, do we really
want a blackhole mysql.gtid_slave_pos? Or archive engine, which does not
support deletes?

One thing that could be done is to supply a standard stored procedure to add
an engine table, to avoid direct changes to the mysql database:

  mysql.gtid_pos_add_engine("MyRocks");

This would create the table, and could also check eg. that a table does not
already exist etc. This seems better.

But even better woulf be if most users did not have to do anything to get
the improved performance. Maybe we could just have a hardcoded list of
engines that are suitable for mysql.gtid_slave_pos (eg. innodb, tokudb,
myrocks). And replication will automatically create a suitable
mysql.gtid_slave_pos_XXX if it sees a transaction in one of those engines,
unless --skip-gtid-auto-create-pos-table is set. For more
exotic/experimental engine, the user can manually call
mysql.gtid_pos_add_engine() if desired.

So to summarise

 - Auto create mysql.gtid_slave_pos_{innodb,tokudb,myrocks} if a transaction
   is seen in one of those engines

 - A --skip-gtid-auto-create-pos-table to disable this auto behaviour

 - A stored procedure mysql.gtid_pos_add_engine() to facilitate manually
   adding an engine table.

What do you think, does this sound ok? Suggestions for better ways?

And thanks for commenting, this helped me rethink this part of the design
which I was not very happy with.

 - Kristian.

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] RFC: new replication feature "per-engine mysql.gtid_slave pos"

[Will Fong]

Hi,

On Mon, Mar 6, 2017 at 5:11 PM, Kristian Nielsen
 wrote:
> 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;

What about a configuration setting in my.cnf that will do the above magically?

I would prefer not making direct changes to the mysql database.

Thanks,
-will


-- 
Will Fong, Senior Support Engineer
MariaDB Corporation

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

[Maria-developers] RFC: new replication feature "per-engine mysql.gtid_slave pos"

[Kristian Nielsen]

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.

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp