Google Cloud SQL Replication Delay

Question

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?

Thank you.

Current master: 4cpu 15gb ram and 900gb ssd

Current replica: 8cpu 30gb ram and 900gb ssd

EDIT

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.

SHOW GLOBAL STATUS

SHOW GLOBAL VARIABLES

Answer 1

I have seen this happens a lot in Amazon RDS. This occurs with row-based replication when a Master get hammered with lots of writes and they get serialized to one I/O thread.

What you should do is dynamically change the following:

SET GLOBAL innodb_flush_log_at_trx_commit = 2;

Problem is you need the SUPER privilege, which neither Amazon RDS nor Google CloudSQL allow. For Amazon RDS, you change the value in the DB Parameter Group (the list of server options for the MySQL Instance). If you can change the dynamic option through some option group in Google CloudSQL platform, this is the option to change. When replication catches back up, change back to 1.

The answer posted by Rick James would be great if you had multiple databases in the CloudSQL instance. He said

All operations going into a single database? Any chance of upgrading past 5.6; multi-threaded replication may help.

Try change the dynamic option for now to get rid of the current replication lag, and try his suggestion of going with MySQL 5.7 and setup multithreaded replication as the long-term solution.

Answer 2

(Too many questions for a Comment)

Let's see SHOW CREATE TABLE and a sample operation. There may be some clues.

How are the large batches performed? One transaction per one write? Or one million inserts in a single transaction? Or something in between?

Row based replication? InnoDB?

Can the inserts be "batched"? Or is it LOAD DATA?

All operations going into a single database? Any chance of upgrading past 5.6; multi-threaded replication may help.

While there are not likely to be any tunables worth adjusting, if you could provide these for each server, it might give more clues (including answering some of the above questions). (Use post.it or some other site; they won't fit here.)

SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;

Answer 3

Suggestions to consider for your my.cnf-ini [mysqld] section

innodb_log_file_size=2G  # from 512M to reduce log rotations
innodb_log_buffer_size=256M  # from 8M for ~15 minutes in RAM
max_connections=200  # from 4000 - max used in 56 days was 72 concurrent
thread_cache_size=100  # from 48 to support volume
read_rnd_buffer_size=192K  # from 256K to lower handler_read_rnd_next RPS
key_cache_age_threshold=64800  # from 300 seconds to lower key_reads RPS
key_cache_division_limit=50  # from 100 for Hot/Warm cache
key_cache_block_size=16384  # from 1024 to reduce CPU overhead
innodb_change_buffer_max_size=15  # from 25 percent to reduce CHG set aside
innodb_flushing_avg_loops=10  # from 30 to reduce delay in loop
innodb_lru_scan_depth=128  # from 1024 to reduce CPU every SEC see V8 refman
innodb_purge_threads=4  # from 1 to support higher activity rate
innodb_write_io_threads=64  # from 4 to expedite WD
max_write_lock_count=16  # to allow RD after nn write lock requests vs up to 4 Billion
sort_buffer_size=2M  # from 256K to reduce sort_merge_passes of ~ 500,000

observations from your STATUS counts, . A) ~100 million com_rollback events counted . B) 368 com_savepoint activites with no matching release to free resources . C) ~11 million handler_rollback events counted . D) 476 handler_savepoint activites with no matching release to free resources . E) ~8 million aborted_clients from ~31 million connections

for additional observations, check my profile for contact info and get in touch with Skype, please.