Unexplained slowness on small MySQL tables-CodePudding

I just migrated a database from a physical server to a virtual server. The new server uses master/master group replication.

I have serious performance issues with INSERT and UPDATE type queries. The SELECTs do not seem to be impacted.

For example, i got this table :

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

This table has exactly 24 rows when i get this in slow query logs :

# 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:\"[email protected]\";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';

Insert take 2 seconds and the update nearly 16 seconds

I also have the same problem with other tables but this one has a lot more records and more indexes.

But there, with so few recordings (24) I don't understand what's going on.

Any idea where the problem come from ?

Thank you in advance

CodePudding user response：

For those who have similar problems, il solved it with tuning Innodb vars

Here is my settings :

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

These settings save about 90% of the problem

Thanks to this post : Very slow writes on MySQL 8 - waiting for handler commit