3

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
2
  • 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, 2019 at 11:17
  • Do you have a large number of rows with NULL for phoneLogin?
    – Rick James
    Aug 16, 2020 at 5:55

5 Answers 5

6

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.

2
  • thank you! but... since you've talked to Percona folks, maybe they mentioned... what's the downside to setting it to 0? Meaning, if it can safely be set to 0 why does this limit even exist then?
    – twelve40
    Mar 31, 2021 at 20:36
  • 1
    Well, this seems to be obvious: with 0 an optimizer theoretically can consume up to infinite amount of RAM, invoking OOM killer at the end. Definitely not that safe as it seems to be.
    – drookie
    Apr 5, 2021 at 5:53
6

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.

1
  • 1
    I’m using slaves in both 5.x and 8.x installations, so this is not the case.
    – drookie
    Jul 18, 2020 at 15:41
1

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.

2
  • Really can't understand why people down vote with no explanation. Seems this website was taken over by trolls. Being more professional, the innodb_flush_log_at_trx_commit can reduce the disc writes and help with the problem.
    – aviv
    Aug 16, 2020 at 19:48
  • I've had similar problems restoring an import from another local environment exported from Workbench. I had the setting at 0. A change to "2" seemed to eliminate the "waiting for handler commit" issue. (I upvoted, do not understand the downvote either).
    – wistlo
    Mar 11, 2021 at 2:30
1

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

-1

I am having the same issue. I have a 2 slaves with similar setup one has a lag occasionally and the other one never has the lag.

innodb_flush_log_at_trx_commit = 1 on both the servers

I was not very convinced 'range_optimizer_max_mem_size' will resolve my issue but to my surprise this parameter was actually '0' on the slave that never has lag. Will be changing the parameter to 0 and see if there's any actual difference.

0

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.