Значение по умолчанию MySQL AUTOCOMMIT=1 может ограничить производительность загруженного сервера базы данных. По возможности объедините несколько связанных операций изменения данных в одну транзакцию, используя SET AUTOCOMMIT=0 или оператор START TRANSACTION, а затем оператор COMMIT после внесения всех изменений.

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

В качестве альтернативы, для транзакций, состоящих только из одного оператора SELECT, включение AUTOCOMMIT помогает InnoDB распознавать и оптимизировать транзакции только для чтения. Требования см. в разделе 10.5.3, «Оптимизация транзакций InnoDB только для чтения».

Избегайте выполнения отката после вставки, обновления или удаления огромного количества строк. Если большая транзакция замедляет производительность сервера, откат может ухудшить проблему, потребовав в несколько раз больше времени, чем исходные операции изменения данных. Завершение процесса базы данных не помогает, так как откат возобновляется при запуске сервера.

Чтобы свести к минимуму вероятность этой проблемы:

Чтобы избавиться от запущенного отката, произошедшего при его возникновении, увеличьте размер буфера пула, чтобы откат стал ограниченным процессором и выполнялся быстро, или остановите и перезапустите сервер с помощью innodb_force_recovery=3, как описано в разделе 17.18.2, «Восстановление InnoDB».

Если вы можете позволить потерю некоторых последних завершенных транзакций при неожиданном выходе, вы можете установить параметр innodb_flush_log_at_trx_commit в 0. InnoDB пытается выполнить запись журнала один раз в секунду, хотя запись не гарантируется.

При изменении или удалении строк, сами строки и связанные InnoDB не удаляются физически немедленно, или даже немедленно после коммита транзакции. Старые данные сохраняются до тех пор, пока не завершатся транзакции, начатые ранее или одновременно, чтобы эти транзакции могли получить доступ к предыдущему состоянию измененных или удаленных строк. Таким образом, долговременная транзакция может предотвратить InnoDB от очистки данных, измененных другой транзакцией.

Когда строки изменяются или удаляются в рамках длительной транзакции, другие транзакции, использующие уровни изоляции READ COMMITTED и REPEATABLE READ, должны выполнить больше работы для восстановления более ранних данных, если они считывают те же строки.

Когда длительная транзакция изменяет таблицу, запросы к этой таблице от других транзакций не используют технику PAGE_MAX_TRX_ID. Запросы, которые обычно могли извлечь все столбцы результатов из вторичного индекса, вместо этого обращаются к соответствующим значениям в данных таблицы.

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