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';

'Executing hook on transaction ','0.000072'
'checking permissions','0.000068'
'Opening tables','0.000144'
'System lock','0.000095'
'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` (
  `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
  • Having exactly the same issue. Write-intense DB (INSERT INTO ... ON DUPLICATE KEY UPDATE), after upgrade sometimes write queries begin stacking. below process that is in Status "waiting for handler commit". – Sych May 21 '19 at 11:17

That's an 8.0 optimizer issue. Can easily be fixed either doubling the range_optimizer_max_mem_size (and, if this is not helping, doubling it further and further) or just by setting it to zero, this way it can use up to all the amount of available memory.

Did the trick for me. Unfortunately, I wasn't the guy to figure it out, but rather guys from paid percona support desk. I was told this is likey to happen on huge tables.

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.