Why does a long delete query on a table slow down insert queries on other tables?

Question

MySQL

There is a large mysql8 innodb table with a size of 60GB, we apply the query "delete from bigtable where state=2;" to it. where state is indexed. The request starts running for several hours. For some reason, insert/update queries to other tables almost hang during this time, not related to the first one in any way (more than a minute delay on insert of one record). As that it is possible to correct a basis configuration to distribute loading?

Answer 1

Most likely, the brakes are due to the fact that UNDO for such a massive removal is very voluminous.
Organize removal in batches. Let's say 10k records.

CREATE PROCEDURE delete_rows()
BEGIN
    SELECT @@autocommit INTO @autocommit;
    SET SESSION autocommit = ON;
    REPEAT
        DELETE FROM bigtable WHERE state=2 LIMIT 10000;
        SELECT SLEEP(1) INTO @tmp;
    UNTIL NOT ROW_COUNT() END REPEAT;
    SET SESSION autocommit = @autocommit;
END

Answer 2

Place different tables in different tablespaces, and the tablespaces themselves on different physical media, the more physical devices you have, the more efficient independent operations will work.
Another point, but this needs to be tested on your data, no matter how paradoxical it may sound, but copying the necessary data into a new table and then deleting the old one entirely can be faster (and will not load the database so much), and it’s noticeable, but it requires that the backend be ready for this (in order to switch between tables in time).