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

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.

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.

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