MEMORY DELETE query takes more than 60 seconds of time with 100% CPU load

MySQL & MariaDB Other MySQL® Questions
1

SHOW TABLE STATUS IN db WHERE NAME LIKE ‘db_test_table’;
Name: db_test_table
Engine: MEMORY
Version: 10
Row_format: Fixed
Rows: 137969
Avg_row_length: 1266
Data_length: 170,11 MB (178 368 800)
Max_data_length: 1,69 GB (1 818 536 838)
Index_length: 17,77 MB (18 636 748)
Data_free: 1,26 MB (1 316 640)
Auto_increment: NULL

SHOW FULL PROCESSLIST;
Query | 52 | updating | DELETE FROM db_test_table WHERE 'regtime` != 1407657002 :
Time: 52
Rows_sent: 0
Rows_examined: 129071349

Usually DELETE query takes less than 1 milliseconds, but sometimes this time increases to 60,200,300 seconds or more with 100% CPU load. Is this normal and what can I do to speed up DELETE query execution?
I can upload table and query, on which it is possible to repeat this problem, on Monday.

2

On uploading file forum displays “Your file of 422779 bytes exceeds the limit of 100000. db_test_table.zip”. Filesize 412 Kb.
Where can I upload the table dump?

3

[URL]http://rghost.ru/57449917[/URL]

Try to execute query

DELETE FROM `db_test_table_copy` WHERE `bid`=2 AND `regtime`!=1407760802;

on this 9.8 Mb memory table. On my server it takes 1 minute and 35 seconds with 100% CPU loading all this time
Server version: 5.6.19

4

For the original query: DELETE FROM db_test_table WHERE 'regtime` != 1407657002, Rows_examined: 129071349 suggested that there was no index on regtime column, and 129Million rows were scanned sequentially which will take some time. Did you know how many rows got deleted?

The command “show create table db_test_table\G” will show the table and index definition.

Since the table is memory storage engine, no IO needed, it is not surprising to have the CPU running at full speed. Note that in ‘top’, 100% CPU means one core is fully occupied, not all cores.

5

For me it is a problem because it blocks all other queries on connection. And I think this query should take no more than 10 milliseconds, and usually what happens.

Ok, let’s look on this table:

Table dump: ​db_test_table.sql (9.8 МБ):

Query:
​

mysql-slow.log:

CPU:
​

Can you try to execute a query on your pc? Table dump u can download from this urls:

Maybe it’s btree index bug? Or configuration problems?

6

Chavy, thanks for the update. I tried with the data and agreed that there is a bug with DELETE query when the WHERE clause has conditions on >1 columns. I would suggest you to open a bug with MySQL at http://bugs.mysql.com.

Here are what I observed on community v5.6.19. The EXPLAIN plan to find the rows to delete is:

mysql> explain select * from db_test_table where regtime != 1407760802 and bid = 2;
+----+-------------+---------------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | db_test_table | range | idx | idx | 8 | NULL | 139179 | Using where |
+----+-------------+---------------+-------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

The SELECT query ran fast and examined relevant rows as expected:


# Query_time: 1.023239 Lock_time: 0.000159 Rows_sent: 1 Rows_examined: 135667
SET timestamp=1409205734;
select count(*) from db_test_table where regtime != 1407760802 and bid = 2;

DELETE query took a long time and examined hundreds of millions of rows, same as you observed. I guess there is a faulty repeat of loops. However DELETE with only one criteria rans fast:

# Query_time: 1.922281 Lock_time: 0.000127 Rows_sent: 0 Rows_examined: 135551
SET timestamp=1409205948;
delete from db_test_table where regtime != 1407760802;

I suspected the culprit had to do with composite index, but problem remained after I changed the composite index to a single column index.

mysql> show create table db_test_table\G *************************** 1. row ***************************
Table: db_test_table
Create Table: CREATE TABLE `db_test_table` (
`bid` smallint(5) unsigned NOT NULL,
`pid` smallint(5) unsigned NOT NULL,
`regtime` bigint(20) unsigned NOT NULL,
`vid` mediumint(9) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`vid`),
KEY `idx` (`regtime`) USING BTREE
) ENGINE=MEMORY AUTO_INCREMENT=142064 DEFAULT CHARSET=binary ROW_FORMAT=DYNAMIC;

------ Kill the query since it took too long
# Query_time: 266.423611 Lock_time: 0.000125 Rows_sent: 0 Rows_examined: 38114863
SET timestamp=1409207983;
delete from db_test_table where regtime != 1407760802 and bid = 2;

-------- Delete on single column remains fast
# Query_time: 2.225477 Lock_time: 0.000119 Rows_sent: 0 Rows_examined: 136506
SET timestamp=1409206867;
delete from db_test_table where regtime != 1407760802;