DELETE command not completing on 30,000,000 row table

Question

I have inherited a database and am looking to clean and speed it up. I have a table that contains 30,000,000 rows, many of which are junk data inserted due to an error on behalf of our programmer. Before I add any new, more optimized indexes, I converted the table from MyISAM to InnoDB and am looking to delete a lot of the rows that contain junk data.

The database is MySQL 5.0 and I have root access to the server. I was first running these commands through Adminer and then phpMyAdmin, both with the same results.

The command I am running is,

DELETE
FROM `tablename`
WHERE `columnname` LIKE '-%'

Essentially, delete anything in this column that begins with a dash -.

It runs for about 3-5 minutes and then when I view the process list, it's gone.

I then run,

SELECT *
FROM `tablename`
WHERE `columnname` LIKE '-%'

and it returns millions of rows.

Why is my delete statement not completing?

PS, I am aware of how out-of-date MySQL 5.0 is. I am working on moving the DB to MySQL 5.6 w InnoDB (maybe MariaDB 10 w XtraDB) but until that happens, I am looking to answer this with the DB as is.

--

Edit removed, see my answer.

Answer 1

Please look at the Architecture of InnoDB (picture from Percona CTO Vadim Tkachenko)

The rows you are deleting is being written into the undo logs. The file ibdata1 should be growing right now for the duration of the delete. According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace:

• Lots of Transactional Changes
• Very Long Transactions
• Lagging Purge Thread

In your case, reason #1 would occupy one rollback segment along with some of the undo space since you are deleting rows. Those rows must sit in ibdata1 until the delete is finished. That space is logically discarded but the diskspace does not shrink back.

You need to kill that delete right now. Once you kill the delete query, it will rollback the deleted rows.

You do this instead:

CREATE TABLE tablename_new LIKE tablename;
INSERT INTO tablename_new SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%';
RENAME TABLE
    tablename TO tablename_old,
    tablename_new TO tablename
;
DROP TABLE tablename_old;

You could have done this against the MyISAM version of the table first. Then, convert it to InnoDB.

Answer 2

I think we may have overcomplicated the answer that was in required in my case. I have no doubt that both Roland & Rick James are correct with their creation of a temporary table, injecting only rows that pass the filter NOT LIKE '-%' but the solution for me was "easier" because there was an important error I was unaware of until now and for that I apologize.

I ran the query in the mysql interactive prompt and noticed the error message,

mysql> DELETE FROM `slugs` WHERE `slug` LIKE '-%';
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

Through Googleing the error, I found the solution was to increase innodb_buffer_pool_size via the /etc/my.cnf file and rebooting the mysql daemon. For my server it was set to the default 8M and I increased it to 1G (the server has 32GB and this is the only table that is currently InnoDB).

mysql> DELETE FROM `slugs` WHERE `slug` LIKE '-%';
Query OK, 23517226 rows affected (27 min 33.23 sec)

Then I was able to run the command and delete 23 million records in ~27 minutes.

For those curious what innodb_buffer_pool_size should be set to, take note of how much RAM you have and then take a look at this thread that gives a suggested estimate in GBs.

Answer 3

Roland's suggestion can be sped up some by doing both things at once:

CREATE TABLE tablename_new LIKE tablename;
ALTER TABLE tablename_new ENGINE = InnoDB;
INSERT INTO tablename_new 
    SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%' ORDER BY primary_key;
RENAME TABLE
    tablename TO tablename_old,
    tablename_new TO tablename
;
DROP TABLE tablename_old;

But here is a blog that explains how to do big DELETEs in chunks, rather than seemingly taking forever: http://mysql.rjweb.org/doc.php/deletebig The gist is to walk through the table via the PK, doing 1K rows at once. (Of course there are more details to be aware of.)

And this blog addresses potential gotchas in the conversion to InnoDB: http://mysql.rjweb.org/doc.php/myisam2innodb

Answer 4

My first instinct would be to do multiple, smaller deletes by limiting the number of query results, and running the query multiple times:

DELETE
FROM `tablename`
WHERE `columnname` LIKE '-%' LIMIT 1000000

Answer 5

The easiest solution is to simply not do that -- do a smaller delete, which can be more easily processed.

In this case I would have recommended trying sequential deletes of the form:

DELETE
FROM `tablename`
WHERE `columnname` LIKE '-a%'

Answer 6

Maybe you could do something like this:

• Add a new field called deleted.
• Do an update like UPDATE tablename SET deleted=1 WHERE `columnname` LIKE '-a%'.
• Set cron to delete this at night time.