mysql 8.0, updates are sometimes stalling

Question

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

Answer 1

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.

Answer 2

I found your question after looking into why I often had poor performance with inserts and updates, and with connection frequently in the state "waiting for handler commit".

What I have subsequently found is that in MySQL 8.0 binary logging is enabled by default. That is a significant change from MySQL 5. I'm not using replication so didn't need binary logging. When I switched binary logging off on my system (added skip_log_bin=1 to MySql's configuration file) the "waiting for handler commit" became quite rare and performance took a significant step back towards its MySQL 5 levels.

Answer 3

Had the same problem. Adding:

   innodb_flush_log_at_trx_commit = 2

Solved the problem for me on MySQL 8.0.13 installation. Read here.

Answer 4

Well, @drookie was partially right, but it's not the full solution:

• Yes, it improves the speed and eliminates the lags IF you don't have a high-loaded and a big table at the same time
• No, it's not the solution, because after running for 24+ hours under the load it goes back to the pit, sadly...

In my case with 256Gb RAM server and a table with like a 1T records I've found a full solution that runs smoothly even after a week+ non-stop operation:

root@static /etc # cat /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log

bind-address    = 127.0.0.1
mysqlx-bind-address     = 127.0.0.1
max_allowed_packet=1G
innodb_buffer_pool_size = 180G
innodb_log_file_size = 1G
innodb_log_buffer_size = 1G
innodb_lock_wait_timeout = 30
innodb_flush_log_at_trx_commit = 0

innodb_lru_scan_depth=100
innodb_io_capacity=1900
innodb_flush_neighbors=2
innodb_max_dirty_pages_pct_lwm=1
innodb_max_dirty_pages_pct=1
innodb_change_buffer_max_size=50
range_optimizer_max_mem_size=0
innodb_flush_method=O_DIRECT

OS id Debian 10, the storage volume is 14x16Tb drives in RAID10 with a decent HW raid controller. Even more improvements: the memory footprint has reduced to 45-70 Gb and no CPU spikes! JUST give it a proper memory space for buffering and be about your business ;)