1

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

2
  • Your problem is not the hardware configuration, your problem is the software/service configuration. You should update performance variables in your Mysql configuration file adding for example: max_allowed_packet = 1024M Also try aditing some binary log variables from: dev.mysql.com/doc/refman/8.0/en/… Jun 22, 2018 at 15:31
  • @Ozan Temel Additional information request, please. Post on pastebin.com or here. D) complete MySQLTuner report to reveal by Engine, Data Size and table counts and additional helpful information. Optional very helpful information, if available includes - htop, top & mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, cat /proc/meminfo includes VMallocUused, for server tuning analysis. Check my profile for contact info, please. Jul 1, 2018 at 1:48

3 Answers 3

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.

0

(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;
0

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.

2
  • @ozantemel Have you had time to implement any of my suggestions or research the rollback and savepoint events? Thanks, Wilson Jul 8, 2018 at 21:13
  • @ozantemel Have you had time to implement any of my suggestions or research the rollback and savepoint events? Would like some feedback, upvote or accepted answers when you have time. Thanks Aug 11, 2018 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.