Following the discussion here: MySQL 8: very slow INSERT…SELECT within the same server for huge partitioned table

We started to update our tables to the new indexes. Since downtime longer than a minute is not acceptable due to the nature of the system, we’re doing it by copying data from the old table into the new one (meanwhile new data is inserted into the old table).

There’re several servers with slightly different MySQL versions and hardware. Two servers have been successfully moved to using the new table by now. But the third one got problematic: as soon as we run

INSERT INTO `new_messages` SELECT * FROM `messages` WHERE `ID` BETWEEN @N1 AND @N2;

the software that inserts live data into the source table (through a stored procedure that does a few checks and INSERTs a row into the messages table) just freezes. When the copying query finishes, it unfreezes and proceeds running normally.

The failing server has MySQL 8.0.35 while the previous ones run 8.0.20. I’ve manually checked system variables and they basically seem to have the same or similar values. The hardware might be somewhat different but has nearly the same characteristics in general.

One thing that worried me at the very beginning is the number of INSERTs/s in SHOW ENGINE INNODB STATUS: the failing server has it nearly about 6000/s average, which is 2 and 5 times less than the two servers that moved successfully, but that’s still quite a lot as live data arrives at only around 50 messages/s.

Dumping the whole table (hundreds of millions of records) is too long a downtime. Using smaller chunks wouldn’t really change much: if run without delays in between, that’s the same long freeze for the whole system, with delays it might take forever.

TL;DR: INSERT INTO … SELECT causes freeze for normal INSERT queries on the source table on one server but not others. A bit different hardware, a bit different versions of MySQL, nearly the same configuration.

I appreciate any suggestions on how to detect the cause of the problem as well as some theories on what could be wrong.