Just last week we had a Galera Cluster for MySQL streaming replication feature deep dive webinar, by the person that implemented it – Seppo Jaakola. In fact, if you haven’t already read the entire story, we encourage you to read: Galera Streaming Replication: Too Big A Bite Can Choke Your Cluster. We also have the 60 minute video recording for reference.

A huge number of Galera Cluster users have always complained about having issues with large transactions (where writes hang but reads are OK), and we are certain that the correct usage of streaming replication is what will ease that burden. What constitutes a large transaction? Typically when you make big number of changes, e.g. batch jobs for maintenance, data loading, restoring backups, and sometimes even with schema upgrades. The symptom from a large transaction can cause the total cluster to hang, sometimes for a long time. The reason behind this is that Galera Cluster has to follow strict commit order, so even parallel replication applier threads must stop before commit to wait for the slow applying of a large transaction, and flow control will kick in and stop committing to cluster nodes.

Seppo then went on to do a little benchmark (on his laptop), with a 2-node cluster, 8 client connections, 1 large transaction updating all rows in a table with 2.5M rows. Naturally, you need to know how to identify large transactions, and then how you can also configure streaming replication. Seppo has simulations in the video as well, and you can see the difference between wsrep_trx_fragment_size (the transaction fragment size) for 10,000 rows, 100,000 rows and also 1,000,000 rows. This can help you decide what the optimal size is for the variable (which can be set in a dynamic fashion!).

Of course, nothing comes for free, and you must be aware that due to excessive logging and elevated replication overhead, streaming replication will cause degraded transaction throughput rate. Monitoring streaming replication is also covered, and we would love to hear your feedback if you would like to see this in Galera Manager.

Tell us if you’re using streaming replication and provide feedback at our Google Group