[MDEV-7862] server freeze for a moment when inserting Created: 2015-03-28  Updated: 2021-02-15  Resolved: 2021-02-15

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.0.17
Fix Version/s: 10.0.25, 10.1.14

Type: Bug Priority: Major
Reporter: Pavel Dvorak Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 0
Labels: None
Environment:

debian 7.8 x64

Attachments: Zip Archive proc.zip    

 Description   

i have a script that insert about 200 records/s to a table
about every 10min the server freeze for 30 seconds, almost all queries freeze, the cpu usage in top drops to 100%
there's no crash, no error in log
there's a lot of queries in slow log but i guess that's because of the freeze

the table:

CREATE TABLE IF NOT EXISTS `firm_db` (
 
  `id` int(11) NOT NULL,
 
  `name_ti` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
 
  `id_number` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
 
  `firm_db_id` int(11) NOT NULL,
 
  `country_id` int(11) NOT NULL,
 
  `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
 
  `uid` varchar(10) CHARACTER SET utf8 NOT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
 
 
 
ALTER TABLE `firm_db`
 
  ADD PRIMARY KEY (`id`),
 
  ADD UNIQUE KEY `country_firm_id` (`country_id`,`firm_db_id`),
 
  ADD KEY `name_ti` (`name_ti`),
 
  ADD KEY `id_number` (`id_number`),
 
  ADD KEY `firm_db_id` (`firm_db_id`),
 
  ADD KEY `country_id` (`country_id`),
 
  ADD KEY `uid` (`uid`),
 
  ADD FULLTEXT KEY `name_ft` (`name`);
 
 
 
ALTER TABLE `firm_db`
 
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



 Comments   
Comment by Elena Stepanova [ 2015-03-30 ]

the cpu usage in top drops to 100%

How big is it normally?

Please provide output of SHOW PROCESSLIST executed by root at the time when the freeze is happening.
Thanks.

Comment by Pavel Dvorak [ 2015-03-30 ]

about 300% in top

i can try but's the server is frozen so it will do nothing probably

Comment by Elena Stepanova [ 2015-03-30 ]

The fact that you can not do it is also interesting to know, but it should be verified.
Please just make sure that you connected to the server beforehand, because it's possible that you won't be able to connect, even if you can actually run SHOW PROCESSLIST from an existing connection.

Comment by Pavel Dvorak [ 2015-03-30 ]

i thinks it's that INSERT INTO `firm`.`firm_db` SET
maybe fulltext index freeze that?

Comment by Pavel Dvorak [ 2015-03-30 ]

later in the log, the insert takes a long time

Comment by Pavel Dvorak [ 2015-04-08 ]

works fine after removing the index

Comment by Elena Stepanova [ 2015-04-15 ]

Hi,

So, according to the process list, all queries freeze in 'query end' state.
This effect is most commonly attributed to disk issues – disk space problems or anything else that prevents the server from writing to the disk, e.g. binary logs.

Please check/monitor the disk to see if there is some unusual activity or problem correlated in time with the delays you observe.

Here is one of probably numerous discussions on the subject: http://stackoverflow.com/questions/13234290/lots-of-query-end-states-in-mysql-all-connections-used-in-a-matter-of-minutes

Somebody says there that they resolved the problem by unsetting sync_binlog. You can also experiment with innodb_flush_log_at_trx_commit.

Comment by Pavel Dvorak [ 2015-04-15 ]

yes, sync is on
i can try to turn it off
strange is it occures only with fulltext index
threre are no disk error messages in log

Comment by Elena Stepanova [ 2015-05-18 ]

Please comment to re-open if you have more information confirming it's a MariaDB server bug.

Comment by David Schnall [ 2016-02-28 ]

Might be linked to this mysql bug: http://bugs.mysql.com/bug.php?id=73816

Currently in mariadb the only workaround I found was to disable binary logging
This bug will be fixed in the upcoming mysql 5.6.30, 5.7.12 releases so I guess it will be fixed in future mariadb 10.0.25+ and 10.1.13+ releases.

Comment by Marko Mäkelä [ 2020-10-19 ]

thiru, please check this. I think that if the MySQL fix was merged, it should have been in the following merge to 10.0 and merge to 10.2.

Please also note that there were 2 huge commits to 10.2 to apply InnoDB changes from MySQL 5.7.9 and MySQL 5.7.14. You should also check that these commits did not discard any part of the fix. It could be a good idea to compare the InnoDB code as of those commits to the MySQL 5.7.9 and 5.7.14 releases.

Comment by Thirunarayanan Balathandayuthapani [ 2020-10-19 ]

It is already merged to 10.1 already.

commit f1aae861eee711cc718ca8fe52cc08d326b948cf
 
Author: Sergei Golubchik <serg@mariadb.org>
 
Date:   Tue Apr 26 19:05:10 2016 +0200
 
 
 
    5.6.30

Comment by Thirunarayanan Balathandayuthapani [ 2020-10-19 ]

I compared the patch manually. It exists in 10.1, 10.2 as well

Generated at Thu Feb 08 07:22:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.