mysql 8.0，更新有時會停止

更新：這個問題的範圍不包括基本的性能調整，並認為這些已經完成。因此，如果您樂於分享一些明顯的變數，例如innodb_io_capacity，innodb_flush_log_at_trx_commit甚至設置innodb_flush_method為您最近發現的一個很棒的旋鈕O_DIRECT，情況並非如此。

最近，我將我的一台生產伺服器從 MySQL 5.7 移到了 8.0。緊接著我注意到更新語句有時（並不總是）花費更多時間（非常）並且在這些期間往往會堆積。

Mysql 配置相同，硬體相似（相同的 CPU，相同數量的 RAM 及其類型，相同的磁碟型號（只是增加了大小）），所以唯一改變的兩件事是 FreeBSD 版本（11.x -> 12.x) 和 MySQL 版本 (5.7.x -> 8.0)。我真的懷疑這與 FreeBSD 12 有關，所以我認為這是 MySQL 8.0 的細節。

從伺服器性能來看，我可以說有大量可用的 CPU（至少 30% 或更多），並且磁碟 i/o 最多在 50% 時飽和。所以問題是 - 8.0 中有什麼會導致更新在某些時候停止？

這是更新聲明及其簡介：

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'

這是表結構：

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

這是一個 8.0 優化器問題。可以很容易地通過將其加倍range_optimizer_max_mem_size（如果這沒有幫助，將其進一步加倍）或僅將其設置為零來修復，這樣它就可以使用所有可用記憶體量。

為我做了訣竅。不幸的是，我不是解決這個問題的人，而是來自付費 percona 支持台的人。有人告訴我這很可能發生在大桌子上。

在調查了為什麼我經常在插入和更新方面表現不佳以及連接經常處於“等待處理程序送出”狀態後，我發現了你的問題。

我隨後發現，在 MySQL 8.0 中，二進制日誌記錄是預設啟用的。這是 MySQL 5 的一個重大變化。我沒有使用複制，所以不需要二進制日誌記錄。當我在我的系統上關閉二進制日誌（將 skip_log_bin=1 添加到 MySql 的配置文件）時，“等待處理程序送出”變得非常罕見，並且性能向 MySQL 5 級別邁出了一大步。

引用自：https://dba.stackexchange.com/questions/232365