MySQL 5.6 – High Disk I/O After Batch Deleting from InnoDB Table

deletemysql-5.6

I needed to delete many records from a 500GB innodb mysql table. I thought I made the right choice by running many delete statements to batch delete the data.

I deleted 10,000 rows at a time and it took around 28,000 iterations (280 million rows deleted) to complete. I had breaks between each delete to allow the read slave to not get too far behind.

Now that the deletions are complete, there are two mysql threads that are hammering the disks. One of them is reading, the other is writing. 85% of the I/O are writes.

I've read that there is a reordering operation that happens after a delete but I'm not sure if that's it. The processlist doesn't show anything out of the ordinary. Monitoring tools that have been watching the DB for some time don't show much of anything different either, with the exception that queries are taking longer, disk I/O is maxed out, and there is an increase in dirty buffer pool pages.

Is there anyway to find out what is going on and causing the high disk I/O? I'm also looking for a way to make it stop or decrease it's priority since the database is working fine otherwise.

MySql info: mysql Ver 14.14 Distrib 5.6.30-76.3, for Linux (x86_64) using 6.2

The table in question does not have a primary key or any unique identifier. It has ~925 million rows in it after the delete.

EDIT 1:

Server has 32GB of ram.

The rest of the mysql data on this server is 150gb.

SHOW CREATE TABLE tablename CREATE TABLE tablename ( column1 bigint(11) NOT NULL DEFAULT '0', column2 bigint(11) NOT NULL DEFAULT '0', column3 bigint(11) NOT NULL DEFAULT '0', column4 datetime NOT NULL DEFAULT '0000-00-00 00:00:00', column5 varchar(64) DEFAULT NULL, column5 int(11) NOT NULL DEFAULT '32', column7 varchar(64) DEFAULT NULL, column8 bigint(20) DEFAULT '0', column9 bigint(20) DEFAULT '0', column10 bigint(20) DEFAULT '5', column11 date DEFAULT NULL, column12 enum('src','dst','unknown') NOT NULL DEFAULT 'unknown', UNIQUE KEY uniquename (column1,column2,column5,column7,column4,column12), KEY column1 (column1,column2,column5,column7,column4), KEY column11_index (column11) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Column names changed to generic names.

The goal of doing small batch deletes was to try and not interfere with normal daily operations on the database.

There is a read slave, it performed all the delete operations and it is not suffering from the same high disk IO that the master is experiencing.

EDIT2/Update:

The server has been rebooted. Once the mysql service started (even with no clients connected), it continued to use all available disk resources to write to disk.

Is there anyway to tell why it's writing to disk constantly?

Best Answer

First and foremost if your data had some sort of identification (ie PRIMARY KEY, or TIMESTAMP) then you could have done:

DELETE FROM table_name WHERE PRIMARY_KEY_column < PRIMARY_KEY_value;
-- or 
DELETE FROM table_name WHERE TIMESTAMP_column < TIMESTAMP_value;

As for what to do now. I would run the following processes:

In a WHILE TRUE make sure that the mysql isn't hanging

mysql -h $HOST -P $port -uroot -e "show processlist;"

Check the row count to see if you are increasing or decreasing

SELECT COUNT(*) FROM table_name;

If the row count is increasing then you should check what other processes are being utilized. ps fux | grep mysql

However, if the row count is decreasing or consistent then it may mean that your deletes are not fully finished.

Wish I can be more help, but your question felt incomplete and thus not very clear.

LARGE DELETEs

Instead of deleting 70% of the table and create lots of rollback info, try copying the tables you want to keep.

CREATE TABLE adc_fetched_documents_new LIKE adc_fetched_documents;
INSERT INTO adc_fetched_documents_new
    SELECT * FROM adc_fetched_documents
    WHERE id > 73097292
;
ALTER TABLE adc_fetched_documents RENAME adc_fetched_documents_zap;
ALTER TABLE adc_fetched_documents_new RENAME adc_fetched_documents;
TRUNCATE TABLE adc_fetched_documents_zap;

Whenever, you delete 70% or more of the table, do it like this.

SMALL DELETEs

If you are deleting smaller chunks, perhaps you can do it with a DELETE JOIN:

CREATE TABLE deletion_ids (id INT NOT NULL PRIMARY KEY (id)) ENGINE=InnoDB;
INSERT INTO deletion_ids
    SELECT id FROM adc_fetched_documents
    WHERE id <= 73097292
;
DELETE B.* FROM deletion_ids A INNER JOIN adc_fetched_documents B USING (id);

Give it a Try !!!

Mysql – Deleting 200M+ rows from InnoDB table

Any data change action of this size if executed at once will cause a significant logging overhead and will require a lot of additional log space. That in turn will slow down the execution causing an even bigger impact on the rest of the system.

The best way to handle this is to break the delete down into smaller chunks. If you delete between 1000 and 10000 rows at a time and then wait for a few seconds the impact on other transactions should be small. Make sure each delete runs in a separate transaction so that all locks are released during the wait time. However, keep in mind that this will stretch out the delete over several hours or even days.

Also, even though it might sound counter intuitive, make sure there is an index supporting each delete so that MySQL does not have to execute a full table scan each time.

Best Answer

Related Solutions

Mysql – do anything about a very long running KILL command

LARGE DELETEs

SMALL DELETEs

Mysql – Deleting 200M+ rows from InnoDB table

Related Question