Unexplained slowness on small MySQL tables

Question

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

Or

# 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';

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?

No problem with structure or query. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4f8fab73aee4a48cec79fad99f36a2af Try to perform OPTIMIZE TABLE. — Akina, Mar 02 '22 at 09:39
Try to get the profile for the queries. Read https://dev.mysql.com/doc/refman/8.0/en/show-profile.html, see https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=20463ad2181fe95d839998fbb795f7ca — Akina, Mar 02 '22 at 10:10
Let's see.. you migrated to a virtual server.. could it be.. that hardware somehow affects operations? You are aware that different hardware produces different speeds and that it's responsible for the speed of code execution? — N.B., Mar 02 '22 at 11:07
@akina After many profiling tests, it seems that the problem is with "waiting for handler commit" — Alaindeseine, Mar 03 '22 at 07:57
Show execution plan and profile for 1-2 problematic queries. — Akina, Mar 03 '22 at 09:00
Are the two Primaries in the same physical server? If not, how far apart are they? What other queries were running at the same time as the queries you mentioned? — Rick James, Mar 03 '22 at 16:32
@Akina Problem are with INSERT and UPDATE, SELECT are ok I solved problem with thuning innodb vars I will post my settings in a reply — Alaindeseine, Mar 03 '22 at 16:34

score 1 · Accepted Answer · answered Mar 03 '22 at 16:36

1

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

answered Mar 03 '22 at 16:36

Alaindeseine

3,260
1
11
21

innodb_io_capacity=1900 may be much more than necessary - and cause premature failure of HDD, SSD or NVME. My current recommendation is 500 for HDD or SSD and 900 for NVME devices. View profile for contact info, if you have questions to get in touch. – Wilson Hauck Mar 04 '22 at 00:19
1

@WilsonHauck server is a VPS, not a physical one – Alaindeseine Mar 04 '22 at 08:54
Values are still important even on VPS instances. For current analysis of your server, additional information request, please. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; for server workload tuning analysis to provide suggestions for more speed. – Wilson Hauck Mar 05 '22 at 15:59

Unexplained slowness on small MySQL tables

1 Answers1