Почему долгий запрос delete к таблице сильно тормозит запросы insert к другим таблицам?

Question

[Юрий Б]

Есть большая таблица mysql8 innodb размером 60гб, к ней применяем запрос "delete from bigtable where state=2;" где state - проиндексировано. Запрос начинает работать несколько часов. Почему то это время почти виснут запросы insert/update к другим таблицам, никак с первой не связанным (более минуты задержка на insert одной записи). Как то можно конфигурацию базы подправить, чтобы распределить нагрузку?

Comments

[Дмитрий]

Обычно такие дела вешают на крон в 3 часа ночи, когда нагрузка на сайт минимальна. Операции с дисковой подсистемой же

Answer 1

[Akina]

Скорее всего, тормоза связаны с тем, что UNDO для такого массированного удаления весьма объёмен.

Организуйте удаление пакетами. Скажем, по 10к записей.

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

[rPman]

Разместить разные таблицы в разных таблеспейсах, а сами таблеспейсы на разных физических носителях, чем больше физических устройств у тебя будет, тем эффективнее будут работать независимые операции.

Еще момент, но это надо тестировать на твоих данных, как бы это парадоксально не звучало, но скопировать нужные данные в новую таблицу и затем удалить старую целиком может оказаться быстрее (и не будет так нагружать базу), причем заметно, но требует чтобы бакэенд был к такому готов (чтобы вовремя переключиться между таблицами).