Mysql – How to a MySQL delete statement affect a table with over 30 millions records

deleteMySQL

I have to delete about 28 million records from a InnoDB table in MySQL.
I am wondering what happens if I am doing a select statement on the same table during the delete operation.
I guess the delete will take some time to complete.

I am using MySQL version 5.6.
I don't want to delete all records I will have a where condition or maybe having.
Thank you for your time.

Best Answer

Instead of deleting a large percentage of the rows you might better negate your WHERE-condition and INSERT/SELECT the remaining rows into a new table. Then DROP the original table and RENAME new to old (or TRUNCATE and re-INSERT). This way there's only a very small period (DROP/RENAME) where the table is not available. It's a very common process in a Data Warehouse where you deal with really large numbers of rows.

You need to run both the INSERT/SELECT and the DROP TABLE within a single transaction, so no other session is able to do any DML between the INSER and the DROP. I don't know the exact mySQL/InnoDb syntax and transaction behavior, but this is a skeleton (of course you need to test before):

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 
START TRANSACTION;
LOCK TABLES newTab WRITE; 
INSERT INTO newTab SELECT * FROM oldTab; 
DROP TABLE oldTab;  -- might need a COMMIT?;
RENAME newTab TO oldTab;
COMMIT; -- ?

Related Solutions

Mysql – Optimizing DELETE Query on MySQL MEMORY Table

I would probably convert your Memory table to InnoDB

ALTER TABLE session ENGINE=InnoDB

for your situation. Memory tables shine for low writes, many-reads that you don't care if you lose the data (loading configuration values, for instance). But when you start writing a lot, the table locks kill you if you have many connections accessing it:

Despite the in-memory processing for MEMORY tables, they are not necessarily faster than InnoDB tables on a busy server, for general-purpose queries, or under a read/write workload. In particular, the table locking involved with performing updates can slow down concurrent usage of MEMORY tables from multiple sessions. [src]

Tracking of session data may seem like a good candidate because of not caring of the data-loss, but I think you might gain significant benefit of removing table locks by switching to InnoDB in this case.

Mysql – How to make a select statement get blocked

Deadlocking by SELECTs can be done in a variety of ways. I have written posts about them

You can have SELECTs get deadlocked by UPDATEs and DELETEs
- Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
You can have UPDATEs and DELETEs blocked by SELECTs
- How are DB locks tied to connections and sessions?
- Is Oracle DB immune to the InnoDB deadlocks found in MySQL?
You can just lock the table before the SELECT and unlock it afterwards
- LOCK TABLES table READ;
- SELECT ... ;
- UNLOCK TABLES;
You could perform a large UPDATE in a transaction with the SERIALIZEABLE isoaltion level. The MySQL Documentation on SERIALIZEABLE isoaltion level says:

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

Cutting right to the chase, you could just use

SELECT ... FOR UPDATE, then try SELECT against those rows
SELECT ... LOCK IN SHARE MODE, then try to UPDATE or DELETE those rows
See the MySQL Documentation

Give it a Try !!!

Best Answer

Related Solutions

Mysql – Optimizing DELETE Query on MySQL MEMORY Table

Mysql – How to make a select statement get blocked

Related Question