MySQL 8.0 – How to Resolve Update Stalling Issues

Update: The scope of this question does not cover basic performance tweaks and considers these are done already. So if you are happy to share some evident variables like innodb_io_capacity, innodb_flush_log_at_trx_commit or even setting innodb_flush_method to a wonderful knob O_DIRECT you discovered recently, this is not the case.

Recently I moved one of my production servers from MySQL 5.7 to 8.0. Immediately after that I noticed that update statements are sometimes (not always) taking more time (enormously) and tend to stack during these periods.

Mysql config is the same, hardware is similar (same CPU, same amount of RAM and its type, same disk model (only the size is increased)), so the only two thing that changed is the FreeBSD release (11.x -> 12.x) and MySQL version (5.7.x -> 8.0). I really doubt this has something to do with FreeBSD 12, so I assume it's the MySQL 8.0 specifics.

Looking at the server performance I can say there's plenty of CPU available (at least 30% or more) and disk i/o is saturated at 50% at it's most. So the question is – what's in the 8.0 that makes updates to stall at some moments ?

Here's the update statement and it's profile:

UPDATE mp_user SET ts_update = NOW(), latitude = '42.962993621826',
longitude = '47.550437927246', accuracy = '515.91363525391', 
source = 'lbs', batteryLevel = '81', steps = '0', turnovers = '0',
setCoordsTime = NOW() WHERE id = '3185997';


'starting','0.000294'
'Executing hook on transaction ','0.000072'
'starting','0.000066'
'checking permissions','0.000068'
'Opening tables','0.000144'
'init','0.000097'
'System lock','0.000095'
'updating','7.698311'
'end','0.000270'
'query end','0.000115'
'waiting for handler commit','0.012015'
'closing tables','0.000301'
'freeing items','0.000185'
'cleaning up','0.000108'

Here's the table structure:

Create Table: CREATE TABLE `mp_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ts` datetime NOT NULL,
  `ts_update` datetime NOT NULL,
  `lastWakeUpPush` datetime DEFAULT NULL,
  `type` enum('parent','child','admin') NOT NULL,
  `latitude` double(10,7) NOT NULL DEFAULT '0.0000000',
  `longitude` double(10,7) NOT NULL DEFAULT '0.0000000',
  `accuracy` float(10,2) NOT NULL DEFAULT '0.00',
  `source` enum('gps','wifi','lbs') DEFAULT NULL,
  `batteryLevel` int(4) DEFAULT NULL,
  `steps` int(4) DEFAULT NULL,
  `turnovers` int(4) DEFAULT NULL,
  `setCoordsTime` datetime DEFAULT NULL,
  `phoneLogin` varchar(45) DEFAULT NULL,
  `password` varchar(40) DEFAULT NULL,
  `salt` varchar(15) DEFAULT '',
  `licenseTo` datetime NOT NULL,
  `monthForRepostUsed` tinyint(1) NOT NULL DEFAULT '0',
  `monthForQuizUsed` tinyint(1) NOT NULL DEFAULT '0',
  `isTrial` tinyint(1) NOT NULL DEFAULT '1',
  `deviceUid` varchar(10) DEFAULT NULL,
  `warnings`   set('backgroundDataOff','geoDeviceOff','geoAppOff','noSim','noMobileDataTransfer','pushesOff','batteryOptimization','mic','appStats','noGoogleServices','backgroundServicesOff','wifiOff','accessibilityOff') DEFAULT NULL,
  `locale` enum('en','en-US','ru','ar','zh','cs','nl','et','fi','fr','de','el','he','hu','id','it','ja','kk','ko','lv','lt','pl','pt','ro','esp','sv','tr','uk','vi','bg','sr','pt-BR') NOT NULL DEFAULT 'ru',
  `lastNcPush` datetime DEFAULT NULL,
  `priceGroup` int(11) DEFAULT NULL COMMENT 'Foreign key to mp_geoZonePriceGroup',
  `healthCheckTime` datetime DEFAULT NULL,
  `lastLogin` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phoneLogin` (`phoneLogin`),
  KEY `ts_update` (`ts_update`),
  KEY `setCoordsTime` (`setCoordsTime`,`type`,`lastWakeUpPush`),
  KEY `deviceUid` (`deviceUid`),
  KEY `batteryLevel` (`batteryLevel`),
  KEY `mp_user_mp_geoZonePriceGroup_id_fk` (`priceGroup`),
  KEY `lastLoginidx` (`lastLogin`)
) ENGINE=InnoDB AUTO_INCREMENT=9465064 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci