Rajiv's picture

In our previous blogs, we covered new MariaDB 10.4 features, performance changes, and InnoDB changes. Also, we included a small brief on how a MariaDB 10.4 release with a new Galera Cluster can impact our lives!

 

Now, in this blog, we are going to put light on the features of Galera Cluster 26.4.0, aka Galera 4. Take a look at each of them, and explore how every highlight of Galera 4 can affect your setup while working with New MariaDB Cluster 10.4.

Problems MariaDB Facea

Galera Cluster is not a drop-in replacement for standalone MySQL. The writeset certification work introduced edge cases and several limitations that limit the ability to migrate into the Galera Cluster.

 

Of those limits, three are the most common limitations. They are as follows;

  • Problems with long transactions
  • Problems with large transactions
  • Problems with hotspots in tables

 

Though these problems are major; however, the Galera 4 Streaming Replication stand a chance to reduce these limitations. To understand this, let’s review all three limitations which I have listed above;

Long Running Transactions

Galera 4 replicates transactions as writesets which are certified on the members of the cluster. The long-running transactions are a bit problematic in Galera. Thus, every node in Galera can apply given writeset.

 

The problem is that whenever the local nodes are locked, replication across the cluster isn’t possible. Moreover, when you are busy in writing more than one Galera node, there are chances that transactions get modify in some nodes.

 

Thus, it makes transactions a time taking process. In such cases, the certification fails, and the long-running transaction rolls back where it was started. Sending writes to more number of nodes in the cluster increases the transaction time and fails the certification.

Hotspots

During transactions, rows are frequently updated. Locking down the nodes means rows in a transaction are locked locally. Again, on sending writes to more than one node, the same counter is modified in almost every node at the same time; thus, causing conflicts and failing certifications.

 

“If writes aren't sent to one node, you will see certification conflicts and time to time rollbacks.”

 

The only solution to this problem is to send your writes to one node at a time instead of distributing each of them across the whole cluster. To do so, ClusterControl deploys proxies like HAProxy and ProxySQL.

 

Both proxies can be configured in order to send the writes to only one node.

 

In general, an application when working with Galera Cluster must handle rollbacks from the database. However, when the problem arises from the time taking and failing transaction to large transactions. In this case, sending the traffic to a single node isn’t a solution.

Large Transactions

When the transaction completes, the writesets are sent for certifications and then to all nodes. This may put up a question in your mind;

 

“How big transaction can be in Galera while preparing writesets?”

 

Always remember, a large transaction reduces the cluster performance. Therefore, it is necessary to limit transactions. For that, we have two variables;

  • wsrep_max_ws_rows: This variable limits the number of rows per transaction and can be set from 0 to unlimited.
  • wsrep_max_ws_size: This variable sets the size (only up to 2 GB) of row.

 

Therefore, the largest transaction in Galera Cluster is up to 2GB.

How Galera 4 Streaming Replication Reduces The Problems?

Certification and applying of the large transactions take time, thus, creating a “lag”. If the transactions are still being applied at nodes, then read after write may result in incorrect data.

 

But, we cannot solve all these problems without focussing on the solutions.

 

And Galera 4’s Streaming Replication is a top-notch mitigator of all these problems. When the writeset splits into parts - we don’t need to wait for the whole transaction to finish before data replicates.

 

It looks wonderful, but how the certification looks like in such cases?

 

When writeset splits into parts, all involved rows gets locked on every Cluster node, causing each fragment to certify. Until now locks being created locally will now be created on all nodes. This is a severe change in how the Galera 4 works!

 

This resolves the case of locking rows! Now transaction fragments come in and reduce the probability of the rolled back transactions. Moreover, locally executed conflicting transactions will not get the locks, instead will have to wait for the completion of replicating transaction before row locks releases.

 

In hotspots, streaming replication put locks on all nodes while updating the row. Furthermore, if there is a need to update some other queries in the same row, wait for the lock to release before executing the changes.

 

Large transactions will benefit most from the streaming replication as there will be no limits to the transaction size. Also, there is no need to wait until the whole transaction finishes.

 

“Due to streaming replication, the large transactions split into fragments and utilize network better. Instead of sending the 2GB of data at once, the data is split into fragments and can be sent over a longer period of time.”

Options In Streaming Replication

There are two types of streaming replications;

  • First is wsrep_trx_fragment_size, which tells us about the size of a fragment (if the size is set to 0, it means streaming replication is disabled)
  • Second is wsrep_trx_fragment_unit, which tells what the fragment is! By default a fragment is bytes, but it can be ‘statements’ or ‘rows’ too.

 

For users, these variables help in replicating a particular query using streaming replication.

 

For example, set the size to 1 and unit to statements, it updates streaming replication for a single query. The best practice to avoid rollbacks is to reduce the size of a transaction as much as it is possible.

Streaming Replication Drawbacks

Yes, there are few streaming replication drawbacks because the locks are now on all the nodes of Cluster.

  • Now, after using streaming replication, the large transaction rolls back on all of the nodes.
  • Another drawback is that the load on cluster increases. When writesets from each fragment of large transactions are stored in wsrep_schema.SR table, on all the nodes, then double-write buffer implements and increase the load on the cluster.

 

Therefore, a few lines above, we told you the size of transactions must be short as long as it is feasible.

Backup Locks

In the new MariaDB Cluster 10.4 users will benefit from the backup locks for State Snapshot Transfers. The idea behind SST’s execution using mariabackup is based upon dataset, which is to be transferred with redo logs collected in the background.

 

After this, a global lock is acquired, which ensures that no write happens and the final position of redo gets collected and stored. The locking for MariaDB is performed using FLUSH TABLES WITH READ LOCK.

 

Not only transactions wait for the lock, but the data also flushed to disk.

 

However, with MariaDB 10.4, it is possible to use less intrusive BACKUP LOCK, which doesn’t require data to be flushed and commits are blocked for the duration of the lock.

 

This means less intrusive SST operations! If you want to run your Galera Cluster in emergency mode on one node, then SST doesn’t impact cluster operations.

 

Galera 4 has introduced three new functions which support causal reads in the applications;

  • WSREP_LAST_WRITTEN_GTID(): It returns GTID of the client’s last write
  • WSREP_LAST_SEEN_GTID(): It returns GTID of the client’s observed last transaction
  • WSREP_SYNC_WAIT_UPTO_GTID(): It blocks the client till the GTID passes to the function committed on the node.

 

Yes, you can easily enforce causal reads in Galera, but utilizing those functions implement safe read after write in the application without making changes in Galera configuration.

Upgrade to New MariaDB Galera 10.4

If you want to try Galera 4, then it is available in its latest release MariaDB 10.4. Stop the whole 10.3 Cluster, upgrade it to 10.4 and again start it back.

 

This is a limitation which users will face in upgrades which can be removed in the next version. However, a live upgrade option is required, but to make it sure, both MariaDB 10.3 and MariaDB 10.4 should coexist in the same Galera Cluster.

 

Setting up asynchronous replication between the old and new Galera Cluster is also a good option.

 

We really hope that you enjoyed this overview about the advantages of MariaDB 10.4 Galera Cluster, its streaming replication and update related information.

 

Being a Tier 3 level data center, we, Everdata Technologies, has helped in upgrading our clients and customers MySQL Clusters from time to time. MySQL has always been considered as the best way to deal with data.

Considering Maria DB as a clone of MySQL is not the wrong thing. But, the InnoDB changes and streaming replication changes in MariaDB Cluster 10.4 though made upgrade a little bit difficult.

 

However, Everdata Technologies guides on Upgrade MariaDB on Windows and Upgrade MariaDB older versions of 2019 and 2018 to MariaDB Cluster 10.4 helped users and clients most and successfully allowed them to use the new changes.

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.