Re: [Maria-developers] MariaDB 10 and MaxScale binlog router

older
[Maria-developers] Ubuntu Snappy...

Kristian Nielsen

2 Apr 2015 2 Apr '15

3:05 p.m.

Massimiliano Pinto writes: First, Massimiliano, please use the maria-developers@ list for all discussions about development on mariadb and related projects. It is very important to keep all communication in the open and work against mariadb becoming a proprietary SkySQL project rather than an open community project.

...

we started working with MariaDB 10 master & slaves setup with MaxScale binlog router (which was developed for MySQL 5.6 without GTID support)

...

Trying first MariaDB 10 setup we found this statement sent from Slave to Master (maxsacle here)

(1) SET @mariadb_slave_capability=4

Check the MARIA_SLAVE_CAPABILITY_* #defines in sql/log_event.h. When connecting, a slave announces its capabilities (which is basically its version). This tells the master about which events are understood by that version of the slave. The master will check each event if it can be handled by the slave. If not, the event will be converted on-the-fly to something the older slave can understand. For example, a MariaDB 5.5 (or MySQL) slave does not understand GTID events. So a MariaDB 10.0 master will convert the GTID event into a BEGIN query event for slaves that have capability < MARIA_SLAVE_CAPABILITY_GTID (4). If you are accepting connections from a slave, you can do the same conversion. If you do not, then older version slaves might break if they receive events generated by newer version master. You can decide which approach makes sense for your project. If you are connecting to a master, then you need to announce your capability. If you do not, you will be interpreted by the master as having an old version, and newer events (like GTID) will be filtered out and not seen.

...

We soon got another one statement not handled:

(2) SELECT binlog_gtid_pos('mst-bin.000001',310)

This is used by the slave to obtain the correct GTID position corresponding to the position at which it is starting, when it is connecting in non-GTID mode. When the slave has this information, it becomes easy for the DBA to switch to a new master using GTID: STOP SLAVE; CHANGE MASTER TO master_host='new_master', master_use_gtid=slave_pos; START MASTER; This works even if the slave was not using GTID mode prior to the CHANGE MASTER, thanks to that SELECT binlog_gtid_pos().

...

we are not handling it (just return the MaxScale default error message to the connected client)

Then the connected client/slave will assume this is an old master that does not support GTID. So the slave will not know the correct GTID position, and the easy CHANGE MASTER above may not be sufficient. Again, maybe that is fine for you, if you do not plan to support GTID.

...

We would like to hear your point of view, for (1) @mariadb_slave_capability, how to handle it? What does it mean? Any possible issues with different values? Will this prevent us the use of Binlog router with MariADB 10?

And for (2), is that ok just to ignore it? Would we get issues?

I hope the above answers it (else ask again). Basically, you can ignore (1) and return error in (2), and things should work similar to MariaDB 5.5. But there will be reduced functionality compared to MariaDB 10.0/10.1. - Kristian.

Show replies by date

Jean-François Gagné

3 Apr 3 Apr

3:04 p.m.

New subject: [Maria-developers] MariaDB 10 and MaxScale binlog router

On 2015-04-02 15:05, Kristian Nielsen wrote:

...

Massimiliano Pinto writes:

...
We [...] got another [...] statement not handled:

(2) SELECT binlog_gtid_pos('mst-bin.000001',310) This is used by the slave to obtain the correct GTID position corresponding to the position at which it is starting, when it is connecting in non-GTID mode.

When the slave has this information, it becomes easy for the DBA to switch to a new master using GTID:

STOP SLAVE; CHANGE MASTER TO master_host='new_master', master_use_gtid=slave_pos; START MASTER;

This works even if the slave was not using GTID mode prior to the CHANGE MASTER, thanks to that SELECT binlog_gtid_pos().

Is this really needed ? My understanding is that the SQL_THREAD will remember the GTID of the last executed transaction, which make the GTID provided by "SELECT binlog_gtid_pos('mst-bin.000001',310)" quickly obsolete. This SELECT is useful when the IO_THREAD is started without starting the SQL_THREAD, in which case the SELECT allows us to repoint the slave with GTID to another master. But I do not see why someone would not want to start the SQL_THREAD in this situation. Moreover, I guess that the GTID returned by this function should not be the GTID of the transaction at this position, but the GTID of the previous transaction. This needs to read from the beginning of the binary logs (reading a binary log backward is not possible to my knowledge). If the binlog file size is 100 GB... (you can see my point I think). Also, if the previous position is not in the same write domain as the current transaction, the transaction from the right write domain (and all the other transaction from the other write domains) must be found. This looks terribly inefficient. There might be a subtility with multiple DomainIDs that I am missing: that SELECT might return the GTIDs of all write domains up to that position... Again, this needs to read all the binlog up to that position (with the hypothesis that the last TransactionID for each write domain is in the header of the binlog). I would prefer to forbid the slave to use automatic positioning (with GTID) until it had read the header of the next binlog. Basically, I prefer to push constraints to slaves than to have more expensive operations on the master. I am probably missing something, can someone explain to me why this SELECT is needed ? The need for that "SELECT binlog_gtid_pos(...)" makes it very hard to implement the MariaDB Slave Protocol in the Binlog Server is a "simple" way. If it is not needed in the protocol, I would prefer to simplify the slave protocol than to complexify the Binlog Server. Thanks in advance for precisions, Jean-François Gagné

Kristian Nielsen

9:50 p.m.

New subject: [Maria-developers] MariaDB 10 and MaxScale binlog router

Jean-François Gagné writes:

...

...
...
(2) SELECT binlog_gtid_pos('mst-bin.000001',310) This is used by the slave to obtain the correct GTID position corresponding to the position at which it is starting, when it is connecting in non-GTID mode.

When the slave has this information, it becomes easy for the DBA to switch to a new master using GTID:

STOP SLAVE; CHANGE MASTER TO master_host='new_master', master_use_gtid=slave_pos; START MASTER;

This works even if the slave was not using GTID mode prior to the CHANGE MASTER, thanks to that SELECT binlog_gtid_pos().

Is this really needed ?

Well, it's needed in the general case to be able to get the correct GTID position to automatically switch to a different master using GTID, as above. It is not _really_ needed in the sense that the DBA can just manually SET GLOBAL gtid_slave_pos='<position>' instead. Or the DBA might not have any need for using GTID in the first place. One of the primary goals of MariaDB GTID was to make it easy to start using it, that is why this was implemented.

...

My understanding is that the SQL_THREAD will remember the GTID of the last executed transaction, which make the GTID provided by "SELECT binlog_gtid_pos('mst-bin.000001',310)" quickly obsolete. This SELECT

...

There might be a subtility with multiple DomainIDs that I am missing: that SELECT might return the GTIDs of all write domains up to that position... Again, this needs to read all the binlog up to that

Correct. The GTID position in the general case has one GTID per replication domain id. And since some of those domains may have no replicated transactions for a long time, the binlog_gtid_pos() call is used to fetch the full position.

...

Moreover, I guess that the GTID returned by this function should not be the GTID of the transaction at this position, but the GTID of the previous transaction. This needs to read from the beginning of the binary logs (reading a binary log backward is not possible to my knowledge). If the binlog file size is 100 GB... (you can see my point I think). Also, if the previous position is not in the same

Yes, you are right, there will be a need to scan the most recent binlog. So there is the potential for a performance regression, more so with large binlog files and/or frequent slave connects. The intention was to have an index on the binlog files so that the GTID position can be found quickly (both for the gtid_slave_pos() call in the non-GTID case, and for GTID connect). But pressure to get the feature out meant it was released without, and I agree that this was unfortunate. Jonas Oreland said he has a patch already that implements this, and that will be contributed soon...

...

write domain as the current transaction, the transaction from the right write domain (and all the other transaction from the other write domains) must be found. This looks terribly inefficient.

...

write domain is in the header of the binlog). I would prefer to forbid the slave to use automatic positioning (with GTID) until it had read the header of the next binlog. Basically, I prefer to push

That could be reasonable. The counter-argument is that we need binlog indexing anyway for GTID mode. And with binlog indexes, the overhead for binlog_gtid_pos() will be negligible. So we could avoid the complications of introducing new kinds of states of a slave ("has a valid GTID position" vs. "does not have a valid GTID position"). In any case, if this causes a performance regression in practice, we will find some solution. Waiting to get the position from the header of the next binlog as you suggested, or some option to disable the binlog_gtid_pos() call, or something.

...

The need for that "SELECT binlog_gtid_pos(...)" makes it very hard to implement the MariaDB Slave Protocol in the Binlog Server is a "simple" way. If it is not needed in the protocol, I would prefer to simplify the slave protocol than to complexify the Binlog Server.

The binlog server can probably just ignore that call. It should not cause problems - only the automatic switch to GTID mode will not work, but I think the binlog server does not support GTID anyway. - Kristian.

Jonas Oreland

16 Apr 16 Apr

12:28 a.m.

New subject: [Maria-developers] MariaDB 10 and MaxScale binlog router

thx for reminding me kristian about the gtid index patch! /Jonas On Fri, Apr 3, 2015 at 12:50 PM, Kristian Nielsen wrote:

...

Jean-François Gagné writes:

...
...
...
(2) SELECT binlog_gtid_pos('mst-bin.000001',310) This is used by the slave to obtain the correct GTID position corresponding to the position at which it is starting, when it is connecting in non-GTID mode.

When the slave has this information, it becomes easy for the DBA to switch to a new master using GTID:

STOP SLAVE; CHANGE MASTER TO master_host='new_master', master_use_gtid=slave_pos; START MASTER;

This works even if the slave was not using GTID mode prior to the CHANGE MASTER, thanks to that SELECT binlog_gtid_pos().

Is this really needed ?

Well, it's needed in the general case to be able to get the correct GTID position to automatically switch to a different master using GTID, as above.

It is not _really_ needed in the sense that the DBA can just manually SET GLOBAL gtid_slave_pos='<position>' instead. Or the DBA might not have any need for using GTID in the first place.

One of the primary goals of MariaDB GTID was to make it easy to start using it, that is why this was implemented.

...
My understanding is that the SQL_THREAD will remember the GTID of the last executed transaction, which make the GTID provided by "SELECT binlog_gtid_pos('mst-bin.000001',310)" quickly obsolete. This SELECT

...
There might be a subtility with multiple DomainIDs that I am missing: that SELECT might return the GTIDs of all write domains up to that position... Again, this needs to read all the binlog up to that

Correct. The GTID position in the general case has one GTID per replication domain id. And since some of those domains may have no replicated transactions for a long time, the binlog_gtid_pos() call is used to fetch the full position.

...
Moreover, I guess that the GTID returned by this function should not be the GTID of the transaction at this position, but the GTID of the previous transaction. This needs to read from the beginning of the binary logs (reading a binary log backward is not possible to my knowledge). If the binlog file size is 100 GB... (you can see my point I think). Also, if the previous position is not in the same

Yes, you are right, there will be a need to scan the most recent binlog. So there is the potential for a performance regression, more so with large binlog files and/or frequent slave connects.

The intention was to have an index on the binlog files so that the GTID position can be found quickly (both for the gtid_slave_pos() call in the non-GTID case, and for GTID connect). But pressure to get the feature out meant it was released without, and I agree that this was unfortunate.

Jonas Oreland said he has a patch already that implements this, and that will be contributed soon...

...
write domain as the current transaction, the transaction from the right write domain (and all the other transaction from the other write domains) must be found. This looks terribly inefficient.

...
write domain is in the header of the binlog). I would prefer to forbid the slave to use automatic positioning (with GTID) until it had read the header of the next binlog. Basically, I prefer to push

That could be reasonable.

The counter-argument is that we need binlog indexing anyway for GTID mode. And with binlog indexes, the overhead for binlog_gtid_pos() will be negligible. So we could avoid the complications of introducing new kinds of states of a slave ("has a valid GTID position" vs. "does not have a valid GTID position").

In any case, if this causes a performance regression in practice, we will find some solution. Waiting to get the position from the header of the next binlog as you suggested, or some option to disable the binlog_gtid_pos() call, or something.

...
The need for that "SELECT binlog_gtid_pos(...)" makes it very hard to implement the MariaDB Slave Protocol in the Binlog Server is a "simple" way. If it is not needed in the protocol, I would prefer to simplify the slave protocol than to complexify the Binlog Server.

The binlog server can probably just ignore that call. It should not cause problems - only the automatic switch to GTID mode will not work, but I think the binlog server does not support GTID anyway.

- 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

Jean-François Gagné

8:10 p.m.

New subject: [Maria-developers] MariaDB 10 and MaxScale binlog router

Hi Jonas, will it be possible to disable the creation/resource consumption of this index ? As I am planning to stick to file/position negotiation, I would like to avoid consuming resource consumption for features that I do not need. Many thanks, Jean-François Gagné On 2015-04-15 15:28, Jonas Oreland wrote:

...

thx for reminding me kristian about the gtid index patch!

/Jonas

On Fri, Apr 3, 2015 at 12:50 PM, Kristian Nielsen mailto:knielsen@knielsen-hq.org> wrote:

Jean-François Gagné mailto:jeanfrancois.gagne@booking.com> writes:

>>> (2) SELECT binlog_gtid_pos('mst-bin.000001',310) >> This is used by the slave to obtain the correct GTID position corresponding to >> the position at which it is starting, when it is connecting in non-GTID mode. >> >> When the slave has this information, it becomes easy for the DBA to switch to >> a new master using GTID: >> >> STOP SLAVE; >> CHANGE MASTER TO master_host='new_master', master_use_gtid=slave_pos; >> START MASTER; >> >> This works even if the slave was not using GTID mode prior to the CHANGE >> MASTER, thanks to that SELECT binlog_gtid_pos(). > > Is this really needed ?

Well, it's needed in the general case to be able to get the correct GTID position to automatically switch to a different master using GTID, as above.

It is not _really_ needed in the sense that the DBA can just manually SET GLOBAL gtid_slave_pos='<position>' instead. Or the DBA might not have any need for using GTID in the first place.

One of the primary goals of MariaDB GTID was to make it easy to start using it, that is why this was implemented.

> My understanding is that the SQL_THREAD will remember the GTID of the > last executed transaction, which make the GTID provided by "SELECT > binlog_gtid_pos('mst-bin.000001',310)" quickly obsolete. This SELECT

> There might be a subtility with multiple DomainIDs that I am missing: > that SELECT might return the GTIDs of all write domains up to that > position... Again, this needs to read all the binlog up to that

Correct. The GTID position in the general case has one GTID per replication domain id. And since some of those domains may have no replicated transactions for a long time, the binlog_gtid_pos() call is used to fetch the full position.

> Moreover, I guess that the GTID returned by this function should not > be the GTID of the transaction at this position, but the GTID of the > previous transaction. This needs to read from the beginning of the > binary logs (reading a binary log backward is not possible to my > knowledge). If the binlog file size is 100 GB... (you can see my > point I think). Also, if the previous position is not in the same

Yes, you are right, there will be a need to scan the most recent binlog. So there is the potential for a performance regression, more so with large binlog files and/or frequent slave connects.

The intention was to have an index on the binlog files so that the GTID position can be found quickly (both for the gtid_slave_pos() call in the non-GTID case, and for GTID connect). But pressure to get the feature out meant it was released without, and I agree that this was unfortunate.

Jonas Oreland said he has a patch already that implements this, and that will be contributed soon...

> write domain as the current transaction, the transaction from the > right write domain (and all the other transaction from the other write > domains) must be found. This looks terribly inefficient.

> write domain is in the header of the binlog). I would prefer to > forbid the slave to use automatic positioning (with GTID) until it had > read the header of the next binlog. Basically, I prefer to push

That could be reasonable.

The counter-argument is that we need binlog indexing anyway for GTID mode. And with binlog indexes, the overhead for binlog_gtid_pos() will be negligible. So we could avoid the complications of introducing new kinds of states of a slave ("has a valid GTID position" vs. "does not have a valid GTID position").

In any case, if this causes a performance regression in practice, we will find some solution. Waiting to get the position from the header of the next binlog as you suggested, or some option to disable the binlog_gtid_pos() call, or something.

> The need for that "SELECT binlog_gtid_pos(...)" makes it very hard to > implement the MariaDB Slave Protocol in the Binlog Server is a > "simple" way. If it is not needed in the protocol, I would prefer to > simplify the slave protocol than to complexify the Binlog Server.

The binlog server can probably just ignore that call. It should not cause problems - only the automatic switch to GTID mode will not work, but I think the binlog server does not support GTID anyway.

- Kristian.

_______________________________________________ Mailing list: https://launchpad.net/~maria-developers https://launchpad.net/%7Emaria-developers Post to : maria-developers@lists.launchpad.net mailto:maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers https://launchpad.net/%7Emaria-developers More help : https://help.launchpad.net/ListHelp

Jonas Oreland

20 Apr 20 Apr

10:59 a.m.

New subject: [Maria-developers] MariaDB 10 and MaxScale binlog router

i don't honestly remember, but we tend to make a disable switch for all changes we do... will check once i get around to it...(have to clear backlog after conference visit now) /Jonas On Thu, Apr 16, 2015 at 8:10 PM, Jean-François Gagné < jeanfrancois.gagne@booking.com> wrote:

...

Hi Jonas, will it be possible to disable the creation/resource consumption of this index ? As I am planning to stick to file/position negotiation, I would like to avoid consuming resource consumption for features that I do not need. Many thanks,

Jean-François Gagné

On 2015-04-15 15:28, Jonas Oreland wrote:

thx for reminding me kristian about the gtid index patch!

/Jonas

On Fri, Apr 3, 2015 at 12:50 PM, Kristian Nielsen < knielsen@knielsen-hq.org> wrote:

...
Jean-François Gagné writes:

...
...
...
(2) SELECT binlog_gtid_pos('mst-bin.000001',310) This is used by the slave to obtain the correct GTID position corresponding to the position at which it is starting, when it is connecting in non-GTID mode.

When the slave has this information, it becomes easy for the DBA to switch to a new master using GTID:

STOP SLAVE; CHANGE MASTER TO master_host='new_master', master_use_gtid=slave_pos; START MASTER;

This works even if the slave was not using GTID mode prior to the CHANGE MASTER, thanks to that SELECT binlog_gtid_pos().

Is this really needed ?

Well, it's needed in the general case to be able to get the correct GTID position to automatically switch to a different master using GTID, as above.

It is not _really_ needed in the sense that the DBA can just manually SET GLOBAL gtid_slave_pos='<position>' instead. Or the DBA might not have any need for using GTID in the first place.

One of the primary goals of MariaDB GTID was to make it easy to start using it, that is why this was implemented.

...
My understanding is that the SQL_THREAD will remember the GTID of the last executed transaction, which make the GTID provided by "SELECT binlog_gtid_pos('mst-bin.000001',310)" quickly obsolete. This SELECT

...
There might be a subtility with multiple DomainIDs that I am missing: that SELECT might return the GTIDs of all write domains up to that position... Again, this needs to read all the binlog up to that

Correct. The GTID position in the general case has one GTID per replication domain id. And since some of those domains may have no replicated transactions for a long time, the binlog_gtid_pos() call is used to fetch the full position.

...
Moreover, I guess that the GTID returned by this function should not be the GTID of the transaction at this position, but the GTID of the previous transaction. This needs to read from the beginning of the binary logs (reading a binary log backward is not possible to my knowledge). If the binlog file size is 100 GB... (you can see my point I think). Also, if the previous position is not in the same

Yes, you are right, there will be a need to scan the most recent binlog. So there is the potential for a performance regression, more so with large binlog files and/or frequent slave connects.

The intention was to have an index on the binlog files so that the GTID position can be found quickly (both for the gtid_slave_pos() call in the non-GTID case, and for GTID connect). But pressure to get the feature out meant it was released without, and I agree that this was unfortunate.

Jonas Oreland said he has a patch already that implements this, and that will be contributed soon...

...
write domain as the current transaction, the transaction from the right write domain (and all the other transaction from the other write domains) must be found. This looks terribly inefficient.

...
write domain is in the header of the binlog). I would prefer to forbid the slave to use automatic positioning (with GTID) until it had read the header of the next binlog. Basically, I prefer to push

That could be reasonable.

The counter-argument is that we need binlog indexing anyway for GTID mode. And with binlog indexes, the overhead for binlog_gtid_pos() will be negligible. So we could avoid the complications of introducing new kinds of states of a slave ("has a valid GTID position" vs. "does not have a valid GTID position").

In any case, if this causes a performance regression in practice, we will find some solution. Waiting to get the position from the header of the next binlog as you suggested, or some option to disable the binlog_gtid_pos() call, or something.

...
The need for that "SELECT binlog_gtid_pos(...)" makes it very hard to implement the MariaDB Slave Protocol in the Binlog Server is a "simple" way. If it is not needed in the protocol, I would prefer to simplify the slave protocol than to complexify the Binlog Server.

The binlog server can probably just ignore that call. It should not cause problems - only the automatic switch to GTID mode will not work, but I think the binlog server does not support GTID anyway.

- 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

3437

Age (days ago)

3455

Last active (days ago)

List overview

Download

5 comments

3 participants

participants (3)

Jean-François Gagné
Jonas Oreland
Kristian Nielsen