Improving Performance for Tungsten Replication to MySQL

The Question

Recently, a customer asked us:

Why would the Tungsten Replicator be slow to apply to a target MySQL server?

The Answer

When you run trepctl status and see:
appliedLatency : 7332.394
like this on a Replica, it is almost always due to the inability of the target database to keep up with the Applier’s stream of events.

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

Tune, Tune, Tune

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-Active-Active deployments, use MIXED
• For Active-Active or heterogeneous topologies, you MUST use ROW-based replication
  • ROW is much slower than MIXED (or STATEMENT) because it must move the actual row data, not just the simple statement
• Check the MySQL Documentation.
• This is how to dynamically set the binary log format value globally

  mysql> SET GLOBAL binlog_format = 'MIXED';
  mysql> SET GLOBAL binlog_format = 'ROW';
  mysql> SET GLOBAL binlog_format = 'STATEMENT';

This can of course also be set as part of mysql configuration file (to persist even if MySQL restarts):
my.cnf: binlog_format=row for example.

MySQL Tables

Verify That All Tables Are InnoDB

• Tungsten Cluster needs to use a transaction-safe storage engine to ensure the validity of the database. The InnoDB storage engine also provides automatic recovery in the event of a failure. Using MyISAM can lead to table corruption, and in the event of a switch or failover, an inconsistent state of the database, making it difficult to recover or restart replication effectively.
• InnoDB should therefore be the default storage engine for all tables, and any existing tables should be converted to InnoDB before deploying a Tungsten Cluster.
  my.cnf: default-storage-engine = InnoDB
• To check for NON-InnoDB tables, get a count and a list of all engines:

  mysql> SELECT COUNT(1) table_count,engine  FROM information_schema.tables  WHERE table_schema NOT IN ('information_schema','mysql') GROUP BY engine;
  +-------------+--------------------+
  | table_count | engine             |
  +-------------+--------------------+
  |         100 | NULL               |
  |          75 | InnoDB             |
  |           1 | MyISAM             |
  |          88 | PERFORMANCE_SCHEMA |
  +-------------+--------------------+

  mysql> SELECT COUNT(1) table_count,table_schema,engine FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') GROUP BY table_schema,engine;
  +-------------+--------------------+--------------------+
  | table_count | table_schema       | engine             |
  +-------------+--------------------+--------------------+
  |           1 | heartbeat          | InnoDB             |
  |           1 | matt               | InnoDB             |
  |          88 | performance_schema | PERFORMANCE_SCHEMA |
  |         100 | sys                | NULL               |
  |           1 | sys                | InnoDB             |
  |           1 | test               | InnoDB             |
  |           1 | test               | MyISAM             |
  |           2 | test_db1_demo      | InnoDB             |
  |           2 | test_db2_demo      | InnoDB             |
  |           2 | test_db3_demo      | InnoDB             |
  |           2 | test_db4_demo      | InnoDB             |
  |           2 | test_db5_demo      | InnoDB             |
  |           2 | test_db6_demo      | InnoDB             |
  |           2 | test_db7_demo      | InnoDB             |
  |           2 | test_db8_demo      | InnoDB             |
  |           2 | test_db9_demo      | InnoDB             |
  |           5 | tungsten_apac      | InnoDB             |
  |           5 | tungsten_ddg       | InnoDB             |
  |           5 | tungsten_east      | InnoDB             |
  |           5 | tungsten_emea      | InnoDB             |
  |           5 | tungsten_useast    | InnoDB             |
  |           5 | tungsten_west      | InnoDB             |
  |          23 | wordpress          | InnoDB             |
  +-------------+--------------------+--------------------+

Do the Tables Have Proper Indexes?

Make Sure All Tables Have a Primary Key

• All tables must have Primary Keys for Tungsten Clustering to perform best.
• Without a Primary Key for each table, Tungsten Replicator must attempt to locate the correct target row by constructing a massive SELECT of all columns. This is both slow, and has a small risk of matching more rows than intended.
• Check the list of tables with no Primary Key:

  mysql> select tables.table_schema, tables.table_name, tables.engine from information_schema.tables left join ( select table_schema, table_name from information_schema.statistics group by table_schema, table_name, index_name having sum( case  when non_unique = 0  and nullable != 'YES' then 1  else 0  end ) = count(*) ) t on tables.table_schema = t.table_schema and tables.table_name = t.table_name where t.table_name is null and tables.table_type = 'BASE TABLE' and tables.table_schema not in ('performance_schema', 'information_schema', 'mysql');
  +--------------+------------------+--------+
  | table_schema | table_name       | engine |
  +--------------+------------------+--------+
  | test         | ericsmyisamtable | MyISAM |
  | test         | ericwashere      | InnoDB |
  +--------------+------------------+--------+
  2 rows in set (0.01 sec)

Make Sure All Tables Have Good Indexes

• Do the tables have proper indexes to support the queries?
• The top performance hog of all is the lack of proper indexes. Without the correct indexing, SELECT queries must scan the entire table, which is extraordinarily slow.
• Use the slow query log to identify if any tungsten-owned queries are taking a long time:

  mysql> show variables like '%slow%';
  +---------------------------+---------------------------------------+
  | Variable_name             | Value                                 |
  +---------------------------+---------------------------------------+
  | log_slow_admin_statements | OFF                                   |
  | log_slow_slave_statements | OFF                                   |
  | slow_launch_time          | 2                                     |
  | slow_query_log            | OFF                                   |
  | slow_query_log_file       | /volumes/data/mysql/db1-demo-slow.log |
  +---------------------------+---------------------------------------+
  5 rows in set (0.00 sec)
  mysql> SET GLOBAL slow_query_log = 'ON';
  mysql> show variables like '%slow_query_log%';
  +---------------------+---------------------------------------+
  | Variable_name       | Value                                 |
  +---------------------+---------------------------------------+
  | slow_query_log      | ON                                    |
  | slow_query_log_file | /volumes/data/mysql/db1-demo-slow.log |
  +---------------------+---------------------------------------+
  2 rows in set (0.00 sec)
  shell> tail -f /volumes/data/mysql/db1-demo-slow.log
  mysql> SET GLOBAL slow_query_log = 'OFF';

• 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 into multiple volumes/mount points
• Implement multi-volume striping for improved i/o speed
• Make sure there are enough IOPS if using cloud instances

Summary

In this blog post we discussed Tungsten Replicator applier performance tuning - what some strategies are for improving performance when applying to a MySQL target database.