Mysql – Much slower replication on Innodb compared to theisam

innodbmyisamMySQLreplication

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?

Specification:

  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

log-slave-updates=ON

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.BEGIN 
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
KM,geozoneIndex,geozoneID,creationTime,streetAddress,city,stateProvince,postalCode,country,subdivision,speedLimitKPH,isTollRoad,gpsFixType,gpsFixStatus,horzAccuracy,vertAccuracy,HDOP,satelliteCount,batteryLevel,batteryVolts,batteryTemp,s
ignalStrength,sequence,priority,entityID,entityType,driverID,driverStatus,driverMessage,sensorLow,sensorHigh,costCenter,jobNumber,rfidTag,attachType,attachData,etaTimestamp,etaUniqueID,etaDistanceKM,etaLatitude,etaLongitude,stopID,stopSt
atus,stopIndex,messageTimestamp,messageID,messageStatus,fuelPressure,fuelUsage,fuelTemp,fuelLevel,fuelLevel2,fuelEconomy,fuelTotal,fuelTrip,fuelIdle,fuelPTO,fuelEngineOn,engineRpm,engineHours,engineOnHours,engineLoad,engineTorque,idleHou
rs,workHours,workDistanceKM,transOilTemp,oilCoolerInTemp,oilCoolerOutTemp,coolantPressure,coolantLevel,coolantTemp,engineTemp,intakeTemp,brakeGForce,acceleration,accelerometerXYZ,brakePressure,massAirFlowRate,oilPressure,oilLevel,oilTemp
,airPressure,airFilterPressure,turboPressure,ptoEngaged,ptoHours,ptoDistanceKM,throttlePos,brakePos,vBatteryVolts,j1708Fault,faultCode,malfunctionLamp,tirePressure,tireTemp,tankLevel,doorStateMask,lightsStateMask,thermoAverage0,thermoAve
rage1,frequencyHz,tripStartTime,tripStopTime,tripDistanceKM,tripIdleHours,tripPtoHours,tripMaxSpeedKPH,tripMaxRpm,tripStartLatitude,tripStartLongitude,tripElapsedSeconds,tripBrakeCount,tripClutchCount) 
VALUES ('sth','sth232',155379534,344,26.33,-91.1790616,0,32.828,34.0,1.05,'',1,0,1,'','UDP','0x434352STHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH
HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH',0.0,5453.285,0.0,0,'',4554353,'','','','','','',0.0,0,0,2,0.0,0.0,0.8,12,0.0,4.172,0.0,-81.0,34,0,'',0,'',0,'',0,0,0,'','','','',0,0,0.0,0
.0,0.0,0,0,0,0,0,0,0.0,36.9,0.0,0.7080000000000001,0.0,0.0,0.0,0.0,0.0,0.0,0.0,44,2.54,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,85.0,0.0,0.0,0.0,0.0,'',0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0,0.0,0.0,0.876,0.0,12.01400000000
0001,0,'',0,'','',0.0,0,0,0.0,0.0,0.0,0,0,5143.809,0.0,0.0,0.0,0,0.0,0.0,0,0,0)abc.abc.COMMIT

Best Answer

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.

Related Question