Blog

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?
shell> time mysql -e "select 1"
...
real    0m0.004s
user    0m0.003s
sys     0m0.000s

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:
mysql> show full processlist;
mysql> show open tables where in_use  0;
mysql> show engine innodb status;

OS Memory

  • Is the database configured to use enough memory?
  • Check for lack of server memory
shell> free -m
shell> top

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.

About the Author

Eric M. Stone
COO

Eric is a veteran of fast-paced, large-scale enterprise environments with 35 years of Information Technology experience. With a focus on HA/DR, from building data centers and trading floors to world-wide deployments, Eric has architected, coded, deployed and administered systems for a wide variety of disparate customers, from Fortune 500 financial institutions to SMB’s.

Add new comment