We have a project which does write data on a second generation MySQL 5.6
The master's write behaviour: Average 70 write operations per second. The thing is 2 times a day, application writes data along 3 hours each time. And the each of them writes 2500 operations per second.
I believe this write operations cause the never ending replication delay. Once the replication delay starts, not able to recover. Increasing the replica's source(cpu and memory) is not working.
What should i do to make replicas sync? I presume master/slave architecture is not the solution for this write operations.
Should i use bigger master for both read and write operations instead of master-slave?
Current master: 4cpu 15gb ram and 900gb ssd
Current replica: 8cpu 30gb ram and 900gb ssd
It's happening like 50k inserts in a single transaction. Replication is row based. Inserts can be batched. All operations going into a single database though there are other databases exist in the master. Upgrading to 5.7 is likely possible.
I'm trying to create an external read replica because setting global variables is not possible due to super privilege. As you describe, i'm going to increase parallel workers for multi threaded replication and innodb_flush_log_at_trx_commit.
Also i'll try max allowed packets. The delayed replica is destroyed. So the status and variables below are represent the master.