InnoDB Lock wait timeout

Today I was troubleshooting an application that runs DELETES on a table frequently. The table is used to schedule work. There are entries scheduled for the future and when the time comes, it performs the work that was scheduled. The table was previously MyISAM but I had it converted to InnoDB in order to improve throughput because the locking had become too excessive. After it was converted to InnoDB we started to get these type of errors:

MYSQL ERROR MESSAGE:
------------------
Query execution was interrupted

BAD QUERY:
------------------
db: database_server_1 -- DELETE FROM my_table
WHERE id = '200'
  AND col1       = 'xxx'
  AND col2    = '222'
  AND col3     = 0
  AND col4  = 0


MYSQL ERROR MESSAGE:
------------------
SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

BAD QUERY:
------------------
db: database_server_1 -- DELETE FROM my_table
WHERE id = '100'
  AND col1       = 'xxx'
  AND col2    = '123'
  AND col3     = 0
  AND col4  = 0

I did some profiling and watched the queries come in and the DELETES were taking over 50 seconds. These are pretty simple DELETES and should be finishing much faster. I did some searching and came upon this link in stack overflow:

http://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded

I checked innodb_lock_wait_timeout on my server and I hadn't changed it, so it was still at the default of 50 seconds. Instead of increasing the innodb_lock_wait_timeout to something larger, I took a look at the table. It is heavily indexed and has over 6 millions rows. We had kept several years worth of historical data in the table without cleaning it up. We only needed 7 days worth of historical data! I got permission to clean it up. The table was too large to reasonably DELETE the old records and because of the DML queries constantly coming in, I couldn't do anything without introducing deadlocks. I got a short downtime scheduled and ran these commands and had it cleaned up in less than 10 seconds.


CREATE TABLE IF NOT EXISTS my_table_new LIKE my_table;

LOCK TABLE my_table WRITE, my_table_new WRITE;

INSERT INTO my_table_new
SELECT * FROM my_table
WHERE `date_created` BETWEEN '2016-03-27 23:59:59' AND '2030-03-27 23:59:59';

DROP TABLE IF EXISTS my_table;

UNLOCK TABLES;

RENAME TABLE `my_table_new` TO `my_table`;

The new row count was less than 200,000. After decreasing the table row count, the DELETE queries were sub second and finishing too quickly to see in a SHOW PROCESSLIST command.

Moral of the story is that one way to improve query performance is simply to work with smaller data sets. Archive data, shard data, delete data, or partition data so that your queries are working with smaller data sets and DML commands are updating smaller indexes.