Performance Tuning Tungsten Replication to MySQL

Published Date Author: , Posted May 21st, 2019 at 11:57:54am

The Question

Recently, a customer asked us:

Why would Tungsten Replicator be slow to apply to MySQL?


The Answer

Performance Tuning 101

When you run trepctl status and see:
appliedLatency : 7332.394
like this on a slave, it is almost always due to the inability for the target database to keep up with the applier.

This means that we often need to look first to the database layer for the solution.

Here are some of the things to think about when dealing with this issue:

Architecture and Environment
 Are you on bare metal?
 Using the cloud?
 Dev or Prod?
 Network speed and latency?
 Distance the data needs to travel?
 Network round trip times? Is the replicator applying to a database installed on the same server or is it applying over the network to a remote server?

[crayon-6605e2b76e802742806128/]

Observe the value for real – if it is 15ms or more chances are you will see slow apply rates.

MySQL Binary Logging
 What binary logging format are you using?
mysql> select @@global.binlog_format;

  • For non-Multi-Master deployments, use MIXED
  • For Multi-Master topologies, use ROW

MySQL Tables
 Verify that all tables are InnoDB.
 Also make sure all tables have a Primary Key.
 Do the tables have proper indexes?
 Use the slow query log to identify if any tungsten-owned queries are taking a long time
 The MySQL EXPLAIN command is very useful in understanding slow queries:
https://dev.mysql.com/doc/refman/5.7/en/using-explain.html
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
https://dev.mysql.com/doc/refman/5.7/en/explain-extended.html

MySQL Locks
 MySQL locks can prevent queries from completing in a timely manner. Check for queries that are holding locks open:

[crayon-6605e2b76e80a566163606/]

OS Memory
 Is the database configured to use enough memory?
 Check for lack of server memory

[crayon-6605e2b76e80e920488150/]

Physical Disk
 Check for disk i/o contention – this is often the real issue, especially with remote disk
shell> iostat -xpne 2
 Add SSD storage into your production systems
 Split filesystems up
 Implement multi-volume striping for improved i/o speed
 Make sure there are enough IOPS if using cloud instances


Summary

The Wrap-Up

In this blog post we discussed Tungsten Replicator applier performance tuning.

To learn about Continuent solutions in general, check out https://www.continuent.com/solutions


The Library

Please read the docs!

For more information about monitoring Tungsten clusters, please visit https://docs.continuent.com.

Tungsten Clustering is the most flexible, performant global database layer available today – use it underlying your SaaS offering as a strong base upon which to grow your worldwide business!

For more information, please visit https://www.continuent.com/solutions

Want to learn more or run a POC? Contact us.

Comments're closed  Comments are closed.