Recently, a customer asked us:
Why would Tungsten Replicator be slow to apply to MySQL?
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
- 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:
- 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;
- Is the database configured to use enough memory?
- Check for lack of server memory
shell> free -m shell> top
- 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
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
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.