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?
- MySQL version: 5.7.25
- Server: c5.xlarge https://aws.amazon.com/ec2/instance-types/c5/
- Database ssd: 900 IOPS
- Ubuntu 14.04
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