Good morning. I wanted to test how InnoDB will work on my servers and for this I made a slave and connected it to my master with myisam. The problem is that when the slave works on innodb, replication significantly slows down and at peak moments (about 6000 entries / min) begins to significantly delay. It is definitely not a problem with the connection with the master because the download does not have any delays. The database can not perform queries from relay logs quickly enough. However, when the table to which 99.9% of the replicated entries get is of the type myisam, everything returns to normal and replication is not delayed. Replicated entries are 95% inserts.

The database is very small because the application after processing data immediately deletes them so the number of records is a maximum of several thousand. The problem with replication occurs even when mysql is the only running process.

Is there any way to speed up replication in case I want to use innodb?


  1. MySQL version: 5.7.25
  2. Server: c5.xlarge https://aws.amazon.com/ec2/instance-types/c5/
  3. Database ssd: 900 IOPS
  4. Ubuntu 14.04

MySQL config:

innodb_buffer_pool_size = 4G

innodb_log_file_size = 500M

innodb_file_per_table = 1

innodb_flush_method = O_DIRECT

innodb_flush_log_at_trx_commit = 2

key_buffer_size = 32M

tmp_table_size = 32M

max_heap_table_size = 32M

query_cache_type = 0

query_cache_size = 0

max_connections = 700

thread_cache_size = 10

open_files_limit = 65535

server-id =  2

symbolic-links = 0

log-bin = mysql-bin


gtid-mode = on

enforce-gtid-consistency = 1

binlog_checksum = NONE

expire_logs_days = 1

slow_query_log = 1

log_slow_slave_statements = 1

Exemplary insert from relaylog:

abc.abc.INSERT INTO table (id,aciD,timestamp,statusCode,latitude,longitude,gpsAge,speedKPH,heading,altitude,transportID,inputMask,outputMask,ignitionState,address,dataSource,rawData,distanceKM,odometerKM,odometerOffset
VALUES ('sth','sth232',155379534,344,26.33,-91.1790616,0,32.828,34.0,1.05,'',1,0,1,'','UDP','0x434352STHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH
  • 1
    Additional information request from the SLAVE server. Post on pastebin.com and share the links. RAM size of your MySQL SLAVE server A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server tuning analysis. Mar 31, 2019 at 17:49
  • Thanks for response. i will setup it tomorrow because at the moment its working on myisam again.
    – dark0612
    Apr 2, 2019 at 12:49
  • Even MyISAM has tuning opportunities that can improve query completion speed. Apr 2, 2019 at 12:59
  • How many rows are INSERTed in a single transaction? Do they need to be in separate transactions? What is the setting of binlog_format?
    – Rick James
    Apr 18, 2019 at 15:01
  • How much RAM? Is that config for the Slave?
    – Rick James
    Apr 18, 2019 at 17:23

1 Answer 1


Make sure your disks are fast enough to catch up master.

The problem is that innodb is a transactional storage engine. It writes first into undo log and only then modified data to the tablespaces. The second issue is that mysql replication by default is single threaded. It means that transactions applied on the master in parallel will be in the binlog in sequential order, and all of them will be applied sequentially on the replica. You can try parallel replication toprocess binlog events in parallel.

Your Answer

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

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