Performance Tuning Tungsten Replication to MySQL
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?
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:
OS Memory
√ Is the database configured to use enough memory?
√ Check for lack of server memory
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.