Details
Major
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;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
about 300% in top
i can try but's the server is frozen so it will do nothing probably
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.
i thinks it's that INSERT INTO `firm`.`firm_db` SET
maybe fulltext index freeze that?
later in the log, the insert takes a long time
works fine after removing the index
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.
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
Please comment to re-open if you have more information confirming it's a MariaDB server bug.
How big is it normally?
Please provide output of SHOW PROCESSLIST executed by root at the time when the freeze is happening.
Thanks.