Things to remember while using Galera Cluster Streaming Replication

What are things you must consider before using Galera Cluster Streaming Replication ? 


In Streaming Replication, the node breaks the transaction into fragments, then certifies and replicates them on the slaves while the transaction is still in progress. Once certified, the fragment can no longer be aborted by conflicting transactions. Additionally, Streaming Replication allows the node to process transaction write-sets greater than 2Gb. So, How do you decide whether go with Galera Cluster Streaming Replication or not ? We caution our customers before choosing Galera Cluster Streaming Replication considering several limitations and costs attached to it (explained below the limitations of Galera Cluster Streaming Replication in this post). Even you have decided to proceed with Galera Cluster Streaming Replication, We recommend you to enable it only for a session-level and then only on specific transactions.

What is so compelling about Galera Cluster Streaming Replication ?

  • Troubleshooting long-running transactions performance in Galera Cluster ? It is worth trying Galera Cluster Streaming Replication 

Many time long-running write transactions on Galera Cluster aborts the transaction. Because, longer it takes for a node to commit a transaction, the greater the likelihood that the cluster will apply a smaller, conflicting transaction before the longer one can replicate to the cluster. When this happens, the cluster aborts the long-running transaction. In Galera Cluster Streaming Replication, Once the node replicates and certifies a fragment, it is no longer possible for other transactions to abort it. The certification keys are generated from record locks, therefore they don’t cover gap locks or next key locks. If the transaction takes a gap lock, it is possible that a transaction, which is executed on another node, will apply a write set which encounters the gap log and will abort the streaming transaction.

  • Replicating Large Data WRITE transactions on Galera Cluster

Galera Cluster performance hugely depends on the network infrastructure quality, the node locally processes the transaction and doesn’t replicate the data until you commit. while slave nodes apply a large transaction, they cannot commit other transactions they receive, which may result in Flow Control throttling of the entire cluster. In Streaming Replication, the node begins to replicate the data with each transaction fragment, rather than waiting for the commit. This allows you to spread the replication over the lifetime of the transaction. This also allows the slave node to process incrementally the entire large transaction with a minimal impact on the cluster.

  • Hot Records – Troubleshooting “UPDATEs” Performance in Galera Cluster 

When your application needs to update frequently the same records from the same table (e.g., implementing a locking scheme, a counter, or a job queue), Streaming Replication allows you to force critical changes to replicate to the entire cluster. For instance, consider the use case of a Mobile Ad. Network  that creates clicks for a campaign. When the transaction starts, it updates the table ad_clicks, setting the queue position for the clicks. Under normal replication, two transactions can come into conflict if they attempt to update the queue position at the same time.

You can avoid this with Streaming Replication. As an example of how to do this, you would first execute the following SQL statement to begin the transaction:

START TRANSACTION;

After reading the data that you need for the application, you would enable Streaming Replication by executing the following two SET statements:

SET SESSION wsrep_trx_fragment_unit='statements';
SET SESSION wsrep_trx_fragment_size=1;

Next, set the user’s position in the queue like so:

UPDATE ad_clicks
SET queue_position = queue_position + 1;

With that done, you can disable Streaming Replication by executing one of the previous SET statements, but with a different value like so:

SET SESSION wsrep_trx_fragment_size=0;

You can now perform whatever additional tasks you need to prepare the Ad. Clicks, and then commit the transaction:

COMMIT;

During the ad. clicks  transaction, the client initiates Streaming Replication for a single statement, which it uses to set the queue position. The queue position update then replicates throughout the cluster, which prevents other nodes from coming into conflict with the new clicks.

Let’s talk about Galera Cluster Streaming Replications Limitations – Not everything is always awesome with Galera Cluster Streaming Replication 😉

There are limitations to Galera Cluster Streaming Replication, This is definitely not the solution for all your Galera Replication challenges. The two major challenges with Galera Cluster Streaming Replication are ” Performance bottlenecks during a transaction and rollback ” , We have explained these below in detail:

Performance bottleneck during a transaction 

When Streaming Replication is enabled on Galera Cluster Version 4, the each node in the cluster begins recording its write-sets to the wsrep_streaming_log table in the mysql database. This done to guarantee the persistence of Streaming Replication updates in the event that they crash. But, This operations will increase the load on specific node, which definitely will be a performance bottleneck during peak load hours.

Performance bottleneck during rollbacks 

If you ever rollback a transaction when Streaming Replication is in use, The rollback operation consumes the entire available system resources. So if your application frequently need to be rolled back, This will become a major performance bottleneck for Galera Cluster Streaming Replication Ops. So we strongly recommend to use shorter transactions whenever possible. In the event that your application performs batch processing or scheduled housekeeping tasks, consider splitting these into smaller transactions in addition to using Streaming Replication.

About MinervaDB Corporation 88 Articles
Independent and vendor neutral consulting, support, remote DBA services and training for MySQL, MariaDB, Percona Server, PostgreSQL and ClickHouse with core expertize in performance, scalability and high availability . We are an virtual corporation, all of us work from home on multiple timezones and stay connected via Email, Skype, Google Hangouts, Phone and IRC supporting over 250 customers worldwide

3 Comments on Things to remember while using Galera Cluster Streaming Replication

  1. Simply want to say your article is as astonishing. The clarity in your post is simply great and i could assume you’re an expert on this subject. Fine with your permission allow me to grab your feed to keep up to date with forthcoming post. Thanks a million and please continue the gratifying work.|

Comments are closed.

UA-155183614-1