If you have a business no matter how small, you are collecting data, and you need to have your data accessible to make informed decisions about how to make your business better. The more successful you become the more data you are producing and the more you become dependent by it. This is when you start to realize your must have your data in a safe place like a database instead some spreadsheet.

But to put your data in a database is not enough, you must be sure the database will be robust, resilient, and always available when you or your customers need it.

When design architectures for robust database architectures, we always discuss about High Availability (HA) and Disaster Recovery (DR). These two concepts are elements of the larger umbrella that is the Business continuity plan.

To cover the different needs, we (Database Architects) use/apply two main models: Tightly Coupled cluster, and Loosely Coupled cluster (latest presentation https://www.slideshare.net/marcotusa/best-practicehigh-availabilitysolutiongeodistributedfinal).

The combination of the two different model allow us to build solid, resilient, scalable HA solution geographically distributed.

Like this:

Until now the part that was NOT natively supported in the architecture above was how to failover the replication channel from a node to another node in case of crash.

This element is currently cover by custom script(https://github.com/y-trudeau/Mysql-tools/tree/master/PXC) develop by my colleague Yves Trudeau.

But Oracle in MySQL 8.0.22 introduced the Asynchronous failover feature. In this blog I am going to check if this feature is enough to cover what we need to avoid using external movable/custom script or if instead there is still work to do.

My scenario is the following:

I have a PXC 8.0.20 cluster on DC1 and I am going to replicate towards a Group Replication cluster 8.0.22.

First, I need to establish the replication, to do so I follow the standard steps:

• Take a backup from one PXC node
• Copy over to DC2 and have a SINGLE MySQL 8.0.22 node start.
• Once all the internal upgrades are done, I am ready to create the Group Replication cluster as for https://dev.mysql.com/doc/refman/8.0/en/group-replication-deploying-in-single-primary-mode.html

Once I am up and running and have a situation like this:

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 38809616-e149-11ea-b0cc-080027d0603e | gr1         |        3306 |       ONLINE |     PRIMARY |         8.0.22 |
| group_replication_applier | 959cc074-e14b-11ea-b90c-080027d0603e | gr2         |        3306 |       ONLINE |   SECONDARY |         8.0.22 |
| group_replication_applier | a56b38ec-e14b-11ea-b9ce-080027d0603e | gr3         |        3306 |       ONLINE |   SECONDARY |         8.0.22 |
| group_replication_applier | b7039e3b-f744-11ea-854c-08002734ed50 | gr4         |        3306 |       ONLINE |   SECONDARY |         8.0.22 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

Then I am ready to create the replication channel.

To do so I am first doing keeping the OLD style without Auto-failover. To do so:

On one of the PCX nodes:

Create user replica_dc@’192.168.4.%’ identified by ‘secret’;
Grant REPLICATION SLAVE on *.* to replica_dc@’192.168.4.%’;

Create user replica_dc@’192.168.4.%’ identified by ‘secret’;
Grant REPLICATION SLAVE on *.* to replica_dc@’192.168.4.%’;

On the PRIMARY of my Grou Replication cluster:

CHANGE MASTER to
master_user='<user>',
master_password='<secret>',
master_host='192.168.4.22',
master_auto_position=1,
source_connection_auto_failover=0,
master_retry_count=6,
master_connect_retry=10
for channel "dc1_to_dc2";

CHANGE MASTER to
master_user='<user>',
master_password='<secret>',
master_host='192.168.4.22',
master_auto_position=1,
source_connection_auto_failover=0,
master_retry_count=6,
master_connect_retry=10
for channel "dc1_to_dc2";

A brief explanation about the above:

• source_connection_auto_failover=0,  <--This enable/disable auto-failover
• master_retry_count=6,                       <-- The number of attempts
• master_connect_retry=10                  <-- Specifies the interval between reconnection attempts

Once Replication is up, we will have this scenario:

Now in case of a crash of the PXC node from which I replicate for, my Group replication cluster will stop replicating:

To prevent this to happen I will use the new functionality to allow Asynchronous replication failover.

To do so the first thing I have to do is to add the list of possible SOURCE in the new table that is locate in MySQL (mysql.replication_asynchronous_connection_failover), to do it I don’t need to use an insert command but instead a function:

asynchronous_connection_failover_add_source(channel, host, port, network_namespace, weight)

where:

• channel: The replication channel for which this replication source server is part of the source list.
• host: The host name for this replication source server.
• port: The port number for this replication source server.
• network_namespace: The network namespace for this replication source server (when specified).
• weight: The priority of this replication source server

In my case I will have:

SELECT asynchronous_connection_failover_add_source('dc1_to_dc2', '192.168.4.22', 3306, null, 100);
SELECT asynchronous_connection_failover_add_source('dc1_to_dc2', '192.168.4.23', 3306, null, 80);
SELECT asynchronous_connection_failover_add_source('dc1_to_dc2', '192.168.4.233', 3306, null, 50);

Now, and this is important, given the information is located in a table in the mysql.schema , the information present in the Primary is automatically replicated all over the cluster. Given that all nodes SEE the list of potential Source.

But given we are running our Group Replication cluster in Single Primary mode we can have only the Primary acting as REPLICA, because only the Primary can write. Hope that is clear.

To activate the failover we just need to pass the command:

First let us check the status:

SELECT CHANNEL_NAME, SOURCE_CONNECTION_AUTO_FAILOVER FROM performance_schema.replication_connection_configuration where CHANNEL_NAME = 'dc1_to_dc2';
+--------------+---------------------------------+
| CHANNEL_NAME | SOURCE_CONNECTION_AUTO_FAILOVER |
+--------------+---------------------------------+
| dc1_to_dc2   | 0                               |
+--------------+---------------------------------+

SELECT CHANNEL_NAME, SOURCE_CONNECTION_AUTO_FAILOVER FROM performance_schema.replication_connection_configuration where CHANNEL_NAME = 'dc1_to_dc2';
+--------------+---------------------------------+
| CHANNEL_NAME | SOURCE_CONNECTION_AUTO_FAILOVER |
+--------------+---------------------------------+
| dc1_to_dc2   | 0                               |
+--------------+---------------------------------+

Ok all good I can activate the auto_failover:

stop slave for channel 'dc1_to_dc2';
CHANGE MASTER TO source_connection_auto_failover=1 for channel "dc1_to_dc2";
start slave for channel 'dc1_to_dc2';
SELECT CHANNEL_NAME, SOURCE_CONNECTION_AUTO_FAILOVER FROM performance_schema.replication_connection_configuration where CHANNEL_NAME = 'dc1_to_dc2';

+--------------+---------------------------------+
| CHANNEL_NAME | SOURCE_CONNECTION_AUTO_FAILOVER |
+--------------+---------------------------------+
| dc1_to_dc2   | 1                               |
+--------------+---------------------------------+

stop slave for channel 'dc1_to_dc2';
CHANGE MASTER TO source_connection_auto_failover=1 for channel "dc1_to_dc2";
start slave for channel 'dc1_to_dc2';
SELECT CHANNEL_NAME, SOURCE_CONNECTION_AUTO_FAILOVER FROM performance_schema.replication_connection_configuration where CHANNEL_NAME = 'dc1_to_dc2';

+--------------+---------------------------------+
| CHANNEL_NAME | SOURCE_CONNECTION_AUTO_FAILOVER |
+--------------+---------------------------------+
| dc1_to_dc2   | 1                               |
+--------------+---------------------------------+

Perfect all is running, and this is my layout now:

At this point if something happens to my current SOURCE the auto-failover will be triggered, and I should see my GR Primary move to the other node.

After I kill the pxc_node2 the error log of the primary will present:

[ERROR] [MY-010584] [Repl] Slave I/O for channel 'dc1_to_dc2': error reconnecting to master This email address is being protected from spambots. You need JavaScript enabled to view it.:3306' - retry-time: 10 retries: 1 message: Can't connect to MySQL server on '192.168.4.23' (111), Error_code: MY-002003
...
[ERROR] [MY-010584] [Repl] Slave I/O for channel 'dc1_to_dc2': error reconnecting to master This email address is being protected from spambots. You need JavaScript enabled to view it.:3306' - retry-time: 10 retries: 6 message: Can't connect to MySQL server on '192.168.4.23' (111), Error_code: MY-002003

[System] [MY-010562] [Repl] Slave I/O thread for channel 'dc1_to_dc2': connected to master This email address is being protected from spambots. You need JavaScript enabled to view it.:3306',replication started in log 'FIRST' at position 53656167
[Warning] [MY-010549] [Repl] The master's UUID has changed, although this should not happen unless you have changed it manually. The old UUID was 28ae74e9-12c7-11eb-8d57-08002734ed50.

The Primary identify the SOURCE failed, try N time as asked waiting for each try T seconds. After that it try to change the master choosing it form the given list. If we use a different weight value, the node that will be elected will be the active one with the highest weight value.

Once a node is elected as SOURCE, the replication channel WILL NOT fail back also if a node with higher weight value will become available. The replication SOURCE change only if there is a failure in the communication between SOURCE-REPLICA pair.

This is what I have now:

Is this enough?

Well let us say that we are very close, but no this is not enough.

Why?

Because this cover ONLY a side of the picture. Which is the REPLICA node able to change the SOURCE. But if we are talking of a cluster like Group Replication, we need to have a mechanism that will allow the CLUSTER (and not only the single node) to fail-over.

What does it mean?

Here I mean that IF the Primary fails (remember only a Primary can become a REPLICA because only a Primary can write), another Primary will be elected by the cluster, and I would expect that if my previous Primary was replicating from a SOURCE, then the new one will do the same starting from the last valid applied position.

I am sure Oracle had already identified this as a possible issue, given to me this new feature sounds something done to make the architecture based on MySQL more resilient. As such the above looks like the most logic step forward when thinking to Tightly Coupled cluster like Group Replication.

Summary of commands

Set the source:

change master to master_user='replica_dc', master_password=<secret>, master_host='192.168.4.22', master_auto_position=1, source_connection_auto_failover=0,   master_retry_count=6, master_connect_retry=10 for channel "dc1_to_dc2";

change master to master_user='replica_dc', master_password=<secret>, master_host='192.168.4.22', master_auto_position=1, source_connection_auto_failover=0,   master_retry_count=6, master_connect_retry=10 for channel "dc1_to_dc2";

Set the list:

SELECT asynchronous_connection_failover_add_source('dc1_to_dc2', '192.168.4.22', 3306, null, 100);
SELECT asynchronous_connection_failover_add_source('dc1_to_dc2', '192.168.4.23', 3306, null, 80);
SELECT asynchronous_connection_failover_add_source('dc1_to_dc2', '192.168.4.233', 3306, null, 50);

SELECT asynchronous_connection_failover_add_source('dc1_to_dc2', '192.168.4.22', 3306, null, 100);
SELECT asynchronous_connection_failover_add_source('dc1_to_dc2', '192.168.4.23', 3306, null, 80);
SELECT asynchronous_connection_failover_add_source('dc1_to_dc2', '192.168.4.233', 3306, null, 50);

Delete an entry from the list

SELECT asynchronous_connection_failover_delete_source('dc1_to_dc2', '192.168.4.22', 3306, '');

SELECT asynchronous_connection_failover_delete_source('dc1_to_dc2', '192.168.4.22', 3306, '');

Check if a replication channel is using automatic failover:

SELECT CHANNEL_NAME, SOURCE_CONNECTION_AUTO_FAILOVER FROM performance_schema.replication_connection_configuration where CHANNEL_NAME = 'dc1_to_dc2';

SELECT CHANNEL_NAME, SOURCE_CONNECTION_AUTO_FAILOVER FROM performance_schema.replication_connection_configuration where CHANNEL_NAME = 'dc1_to_dc2';

To change the SOURCE setting:

stop slave for channel 'dc1_to_dc2';
CHANGE MASTER TO source_connection_auto_failover=1 for channel "dc1_to_dc2";
start slave for channel 'dc1_to_dc2';

stop slave for channel 'dc1_to_dc2';
CHANGE MASTER TO source_connection_auto_failover=1 for channel "dc1_to_dc2";
start slave for channel 'dc1_to_dc2';

Conclusions

This is a very nice feature that will significantly increase the architecture resilience when using asynchronous replication in the architecture.

Still I think we need to wait to have it fully cover what is needed. This is a great first step but not enough, we still must manage the replication if a node in the REPLICA cluster fails.

I really hope we will see the evolution of that soon.

In the meantime, GREAT WORK MySQL/Oracle team, really!!

References

https://dev.mysql.com/doc/refman/8.0/en/replication-functions-source-list.html

https://www.slideshare.net/marcotusa/best-practicehigh-availabilitysolutiongeodistributedfinal

https://github.com/y-trudeau/Mysql-tools/tree/master/PXC

https://dev.mysql.com/doc/refman/8.0/en/change-master-to.html

A small thing that brings huge help.

The other day I was writing some code to process a very large amount of items coming from a social media API. My items were ending in a queue in MySQL and then needed to be processed and eventually moved.

The task was not so strange,  but what I have to do is to develop a queue processor.  Now when you need to process a queue you have two types of queue: static and dynamic.

The static comes in a batch of N number of items in a given time interval and is normally easier to process given you have a defined number of items that you can split in chunks and process in parallel.

The dynamic is… well... more challenging. One option is to wait to have a predefined number of items, and then process them as if they were a static queue.

But this approach is not very good, given it is possible that it will delay a lot the processing of an item for all the time it has to wait to reach the desired queue’s size.

The other possibility is to have the processing jobs work on a single item and not in chunk/batch. But, this is not optimal when given the chance to process a queue in batch to speed up the processing time.

My incoming queue is a bit unpredictable, a mix of fixed sizes and a few thousand coming sparse, without a clear interval.  So I was there thinking on how to process this and already starting to design a quite complex mechanism to dynamically calculate the size of the possible chunks and the number of jobs, when…

An aside: some colleagues know my habit to read the whole MySQL manual, from A to Z, at least once a year. It's a way for me to review what is going on and sometimes to dig in more in some aspects. This normally also gives me a good level of confidence about new features and other changes on top of reading the release notes.

...When … looking at the documentation for something else, my attention was captured by:

“To avoid waiting for other transactions to release row locks, NOWAIT and SKIP LOCKED options may be used with SELECT ... FOR UPDATE or SELECT ... FOR SHARE locking read statements.”

Wait -  what???

Let me dig in a bit:

“SKIP LOCKED. A locking read that uses SKIP LOCKED never waits to acquire a row lock. The query executes immediately, removing locked rows from the result set.”

Wow, how could I have missed that?

It was also not new but in MySQL 8.0.1, the milestone release. Having experience with Oracle, I knew what SKIP LOCKED does and how to use it. But I was really not aware that it was also available in MySQL.

In short, SKIP LOCKED allows you to lock a row (or set of them), bypassing the rows already locked by other transactions.

The classic example is:

# Session 1:
mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
mysql> INSERT INTO t (i) VALUES(1),(2),(3);
mysql> START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+

# Session 2:
mysql> START TRANSACTION;
mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+

But what is important for me is that given an N number of jobs running, I can bypass all the effort of calculating the dynamic chunks, given that using SKIP LOCKED that will happen as well, if in a different way.

All good, but what performance will I have using SKIP LOCK in comparison to the other two solutions?

I have run the following tests on my laptop, so not a real server, and used a fake queue processor I wrote on the fly to test the things you can find on GitHub here.  

What I do is to create a table like this:

CREATE TABLE `jobs` (
  `jobid` int unsigned NOT NULL AUTO_INCREMENT,
  `time_in` bigint NOT NULL,
  `time_out` bigint DEFAULT '0',
  `worked_time` bigint DEFAULT '0',
  `processer` int unsigned DEFAULT '0',
  `info` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`jobid`),
  KEY `idx_time_in` (`time_in`,`time_out`),
  KEY `idx_time_out` (`time_out`,`time_in`),
  KEY `processer` (`processer`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Then I will do three different methods of processing:

1. Simple process, reading and writing a row a time
2. Use chunks to split my queue
3. Use SKIP LOCKED

To clarify the difference existing between the 3 different way of processing the queue let us use 3 simple images:

In simple processing each row represent a possible lock that the other processes must wait for.

In chunk processing given each process knows what records to lock they can go in parallel.

In SKIP LOCKED also if each process have no idea of what rows they need to lock, is enough to say the size of the chunk, and MySQL will return the records available. 

I will repeat the tests for a static queue, and after for a dynamic queue for 20 seconds. Let's see what happens.

Test one - simple processing and static queue:

queue Processor report:
=========================
Start Time                = Tue Jul 28 13:28:25 CEST 2020
End Time                  = Tue Jul 28 13:28:31 CEST 2020
Total Time taken(ms)      =     6325.0
Number of jobs            =          5
Number of Items processed =      15000
Avg records proc time     =     220308

Test 2 use the chunks and static queue

Chunk no loop
--------------
queue Processor report:
=========================
Start Time                = Tue Jul 28 13:30:18 CEST 2020
End Time                  = Tue Jul 28 13:30:22 CEST 2020
Total Time taken(ms)      =     4311.0
Number of jobs            =          5
Number of Items processed =      15000
Avg records proc time     =     391927

Test three - use SKIP LOCKED and static queue:

SKIP LOCK - no loop
------------------------
queue Processor report:
=========================
Start Time                = Tue Jul 28 13:32:07 CEST 2020
End Time                  = Tue Jul 28 13:32:11 CEST 2020
Total Time taken(ms)      =     4311.0
Number of jobs            =          5
Number of Items processed =      15000
Avg records proc time     =     366812

So far, so good.

Time is almost the same (actually in this test, it's exactly the same), normally fluctuating a bit up and down by a few ms.

 Average execution by commit fluctuates a bit:

Here, the base execution is faster for the simple reason that the application is processing one record against a batch of records of the other two.

Now it is time to see what happens if instead of a static batch, I have a process that fills the queue constantly. If you want picture what will happen on each test, just imagine this:

• Some pipes will put water in a huge tank
• The base solution will try to empty the tank using a small glass of water but acting very fast at each run
• With Chunk it will wait for the water to reach a specific level, then will use a fixed-size bucket
• Using SKIP LOCK, it will constantly look at the tank and will choose the size of the bucket based on the quantity of the water present.

To simulate that, I will use five threads to write new items, five to process the queue, and will run the test for 20 seconds.

Here we will have some leftovers; that is how much water remains in the tank because the application was not emptied with the given bucket. We can say it is a way to measure the efficiency of the processing, where the optimal sees the tank empty.

Test one -  simple processing and static queue:

Basic no loop
--------------
queue Processor report:
=========================
Start Time                = Tue Jul 28 13:42:37 CEST 2020
End Time                  = Tue Jul 28 13:43:25 CEST 2020
Total Time taken(ms)      =    48586.0
Number of jobs            =          5
Number of Items processed =      15000
Total Loops executed (sum)=         85
Avg records proc time     =     243400

Leftover
+----------+------------+
| count(*) | max(jobId) |
+----------+------------+
|   143863 |     225000 |
+----------+------------+

Test 2 use the chunks and static queue:

Chunk no loop
--------------
queue Processor report:
=========================
Start Time                = Tue Jul 28 13:44:56 CEST 2020
End Time                  = Tue Jul 28 13:45:44 CEST 2020
Total Time taken(ms)      =    47946.0
Number of jobs            =          5
Number of Items processed =      15000
Total Loops executed (sum)=         70
Avg records proc time     =     363559

Leftover
+----------+------------+
| count(*) | max(jobId) |
+----------+------------+
|       53 |     195000 |
+----------+------------+

Test 3 use SKIP LOCKED and static queue:

queue Processor report:
=========================
Start Time                = Tue Jul 28 14:46:45 CEST 2020
End Time                  = Tue Jul 28 14:47:32 CEST 2020
Total Time taken(ms)      =    46324.0
Number of jobs            =          5
Number of Items processed =      15000
Total Loops executed (sum)=       1528
Avg records proc time     =     282658

Leftover
+----------+------------+
| count(*) | max(jobId) |
+----------+------------+
|        0 |       NULL |
+----------+------------+

 Here, the scenario is a bit different than the one we had with the static queue.

Here, the scenario is a bit different than the one we had with the static queue.

Record processing when comparing by chunk and SKIP LOCK is again more efficient in the second one. This is because it optimizes the size of the “bucket” and given that it can sometimes process fewer records per loop.

As we can see when using SKIP LOCK, the application was able to execute 1528 loops to process the queue against the 70 of the chunk and 85 of the basic approach.

In the end, the only one that was able to empty the tank was the solution with SKIP LOCK.

Conclusion

Processing queues can be simple when we need to process a fixed number of items, but if you need an adaptive approach, then the situation changes. You can find yourself writing quite complex algorithms to optimize the processing.

Using SKIP LOCK helps you in keeping the code/solution simple and move the burden of identifying the record to process onto the RDBMS.

SKIP LOCK is something that other technologies like Oracle-DB and Postgres already implemented, and their developer communities use.

MySQL implementation comes a bit later, and the option is not widely known or used in the developer’s community using MySQL, but it should.

Give it a try and let us know!

NOTE !!

SKIP LOCK is declared unsafe for statement replication, you MUST use ROW based replication if you use it.

References

MySQL 8.0 Hot Rows with NOWAIT and SKIP LOCKED

MySQL 8.0 Reference Manual: Locking Reads