1

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
  • 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. – Wilson Hauck Mar 31 '19 at 17:49
  • Thanks for response. i will setup it tomorrow because at the moment its working on myisam again. – dark0612 Apr 2 '19 at 12:49
  • Even MyISAM has tuning opportunities that can improve query completion speed. – Wilson Hauck Apr 2 '19 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 '19 at 15:01
  • How much RAM? Is that config for the Slave? – Rick James Apr 18 '19 at 17:23
0

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, privacy policy and cookie policy

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