Replace Into Table in MySQL InnoDB extremely slow

Question

Using MySQL (MariaDB to be exact though). I have the following script which needs to be run every other day to update my database, but it's unbearably slow. Each table to be updated takes hours to run. It's a `shell` script: ``` CMD_MYSQL="${MYSQL_DIR}mysql --local-infile=1 --default-character-set=utf8 --protocol=${MYSQL_PROTOCOL} --port=${MYSQL_PORT} --user=${MYSQL_USER} --pass=${MYSQL_PASS} --host=${MYSQL_HOST} --database=${MYSQL_DB}" ### Update MySQL Data ### ## table name are lowercase tablename=`echo $FILE | tr "[[:upper:]]" "[[:lower:]]"` echo "Uploading ($FILE) to ($MYSQL_DB.$tablename) with REPLACE option..." ## let's try with the REPLACE OPTION $CMD_MYSQL --execute="LOAD DATA LOCAL INFILE '$FILE.txt' REPLACE INTO TABLE $tablename CHARACTER SET utf8 FIELDS TERMINATED BY '|' IGNORE 1 LINES;" ## we need to erase the records, NOT updated today echo "erasing old records from ($tablename)..." $CMD_MYSQL --execute="DELETE FROM $tablename WHERE datediff(TimeStamp, now()) < 0;" ``` Yo…

Accepted Answer

This issue affects all versions of InnoDB (4.1+). Replacing duplicates in InnoDB is too slow. This is one place MyISAM is superior. MyISAM took 0.05 seconds.

The reason is that the duplicate key error handling in InnoDB relies on the undo log and statement rollback: 1. Lock the record. 2. Write update_undo log for delete-marking the record. 3. Write insert_undo log for inserting the record. 4. Attempt to insert the new record. 5. Notice the duplicate. 6. Roll back the undo log written in steps 3 and 2.

This is an issue in MySQL which has not been fixed in any version as of September 2019: https://bugs.mysql.com/bug.php?id=71507

They are planning to detect duplicate at step 2. This would avoid any rollback in this case.

Here are 2 suggestions:

- Use MyISAM if applicable

- Use queries such as `INSERT INTO ... ON DUPLICATE KEY UPDATE ...`

Replace Into Table in MySQL InnoDB extremely slow

Solution

Problem