我刚刚将数据库从物理服务器迁移到虚拟服务器。新服务器使用主/主组复制。
INSERT和UPDATE类型的查询存在严重的性能问题。SELECTs似乎没有受到影响。
例如，我得到了以下表格：
CREATE TABLE `sys_sessions` (
`session_id` varchar(50) NOT NULL,
`session_name` varchar(50) NOT NULL,
`session_path` varchar(50) NOT NULL,
`session_vars` text NOT NULL,
`last_accessed` decimal(15,3) NOT NULL,
`remote_ip_addr` char(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='Table perttant de stocker les session utilisateurs';
ALTER TABLE `sys_sessions`
ADD PRIMARY KEY (`session_id`,`session_name`,`session_path`),
ADD KEY `last_access` (`last_accessed`);
当我在慢速查询日志中得到这个时，这个表正好有24行：
# Time: 2022-03-02T08:08:43.871669Z
# User@Host: xxxxx[xxxxx] @ [xxx.yyy.zzz.aaa] Id: 24236224
# Query_time: 2.031483 Lock_time: 0.000143 Rows_sent: 0 Rows_examined: 0
use qms_server_sessions;
SET timestamp=1646208521;
insert into qms_server_sessions.sys_sessions (session_path, session_name, session_id, session_vars, last_accessed, remote_ip_addr) values ('hippocad_SAP', 'SAP', '7e76d20441f4d8c42fea47108f78136b', 'session_security_key|s:64:"2d50e943885292013fe1f7960c6cf63b6ca75add79060ea237a27a4b7e489b09";is_connected|s:1:"N";connexion_last_access_datetime|s:19:"2022-03-02 09:08:41";', 1646208521.837, 'xxx.yyy.zzz.www');
或
# Time: 2022-03-02T08:21:23.171509Z
# User@Host: xxxx[xxxx] @ [xxx.yyy.zzz.aaa] Id: 24238829
# Query_time: 15.843765 Lock_time: 0.000186 Rows_sent: 0 Rows_examined: 1
use database_sessions;
SET timestamp=1646209267;
update database_sessions.sys_sessions set session_vars = 'referrer|s:143:"https://xx.xxx.xx/index.php?mode=html&module=surveys&view=surveys&session_name=sap_51_20220302092049&&session_from=sap_51_20220302092049";query_string|s:136:"mode=ajax&module=surveys&view=surveys&action=datas&session_name=sap_51_20220302092049&xaction=print&da=tokens_invoice&oid=748&l=fr&w=PDF";user_session_security_key|s:64:"4f9ffc4f48657a1473dfd3ef2338a2199cd40f9a4a9d8271bfbdab2ab19a6857";current_perimetre|s:31:"Cabinet d'études Informatiques";current_perimetre_ID|i:1;adm_user_id|i:51;_SAP_MANDANT_URL|i:5;customer_id|s:0:"";external_uid|s:0:"";external_cid|s:0:"";admin_level|s:0:"";connexion_id|i:51;user_detail_id|i:51;alpha_id|s:32:"1824e656539b11e8b256002215a9bfb4";wording_id|i:1;connexion_name|s:6:"xxxx";pydio_username|s:0:"";pydio_adminname|s:0:"";login|s:6:"xxxx";connexion_ipaddr|s:14:"xxx.yyy.zzz.aaa";insee|i:95770;is_blog_network_admin|s:0:"";lastlogin|s:19:"2022-03-01 18:29:10";nblogin|i:14;failed_login|i:0;nomcomplet|s:12:"john Query";email|s:23:"xxx.yyy@tutu.org";avatar_image_src|s:36:"1824e656539b11e8b256002215a9bfb4.png";database_root|s:0:"";company_id|i:1;company_name|s:31:"Cabinet d'études Informatiques";profil_id|s:1:"4";role_id|s:1:"3";admin_level_name|s:11:"Consultants";debugSQL|s:0:"";my_menu_list|a:6:{i:0;a:3:{s:4:"name";s:15:"Tableau de bord";s:3:"url";s:45:"/index.php?mode=html&module=home&view=welcome";s:4:"icon";s:14:"icon-dashboard";}i:1;a:3:{s:4:"name";s:14:"Questionnaires";s:3:"url";s:48:"/index.php?mode=html&module=surveys&view=surveys";s:4:"icon";s:12:"icon-surveys";}i:2;a:3:{s:4:"name";s:16:"Mes informations";s:3:"url";s:49:"/index.php?mode=html&module=config&view=myaccount";s:4:"icon";s:21:"icon-mes-informations";}i:3;a:3:{s:4:"name";s:12:"Mot de passe";s:3:"url";s:48:"/index.php?mode=html&module=config&view=password";s:4:"icon";s:13:"icon-password";}i:4;a:3:{s:4:"name";s:11:"Mes favoris";s:3:"url";s:49:"/index.php?mode=html&module=config&view=favorites";s:4:"icon";s:21:"icon-favorites-folder";}i:5;a:3:{s:4:"name";s:22:"Conditions générales";s:3:"url";s:46:"/index.php?mode=html&module=documents&view=cgv";s:4:"icon";s:0:"";}}', last_accessed = 1646209267.3243 where session_path = 'hippocad_SAP' and session_name = 'sap_51_20220302092049' and session_id = 'ojfnuhlussi5vhah2ehvtg04gj';
插入需要2秒，更新将近16秒。
我对其他表也有同样的问题，但这个表有更多的记录和索引。
但是，由于录音太少，我不明白发生了什么。
知道问题是从哪里来的吗？
对于那些有类似问题的人，我通过调优Innodb-vars-解决了这个问题
这是我的设置：
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
innodb_buffer_pool_size=3221225472
这些设置可以节省大约90%的问题
感谢这篇文章：MySQL 8上写得很慢-等待处理程序提交