Hello to everyone!
Could someone help with the following issues?
We had plans to deploy Percona XtraDB Cluster with synchronous replication between cluster nodes.
We brought online the first cluster node using PXC. After that the management dropped the idea to configure a synchronous cluster because of the concerns that every transaction in Percona Cluster is considered committed after it is replicated to all cluster nodes and that may bring some latency in our application.
Because of the above reasons a decision was took to deploy a few servers with already existing PXC node as the master and add nodes with either PXC or Percona Server as asynchronous slaves.
Our issue with such an asynchronous setup is that the slave never cannot catch up with the master. We tried to replicate data to PXC (slave) and Percona Server using multithreaded replication, but same thing - replication lag is also increasing and the slave never cannot catch up.
Also sometimes some of our ibd tables suddenly grow very, very large (130-150GB when the normal size should be at most 10 GB). OPTIMIZE TABLE or converting problematic tables to MyISAM fixes the issue, but we would like to find the reason for such exponential grow.
Could someone please give us some hints what me be the problem with replication lag and big idb files?
Could Galera ROW binlog format be the reason? Will disabling Galera and switching to MIXED replication mode help?
Thank you in advance for your answers!