0

I would like to know if there is a max transaction size for InnoDB. Probably it is innodb_log_file_size * innodb_log_files_in_group + innodb_log_buffer_size? And this max size would be shared between all ongoing transactions?

What happens when this max size is reached? The transaction is aborted?

I ask this question because we are having out of memory issues with our MySQL server, we're running out of ideas to understand the problem, and we wonder if it could be caused by transactions beeing too large.

0
0

When the transaction log becomes full, it causes a checkpoint, as explained in the manual:

When InnoDB has written the redo log files full, it must write the modified contents of the buffer pool to disk in a checkpoint.

3
  • But is it possible to have a checkpoint in the middle of a single transaction?
    – FBB
    Sep 28 at 14:07
  • @FBB - <my-opinion>A checkpoint in the middle is is asking for trouble.</my-opinion>
    – Rick James
    Sep 29 at 16:53
  • 1
    "is it possible to have a checkpoint in the middle of a single transaction" -- it is entirely possible if you run out of log space or free buffers.
    – mustaccio
    Sep 29 at 17:49
0

innodb_log_file_size needs to be at least 10 times the size of the largest BLOB in the transaction.

innodb_log_file_size * innodb_log_files_in_group is limited by disk space, hence won't cause OOM.

innodb_log_buffer_size is in RAM. Its default of a mere 8M is probably fine for all situations. It is just a buffer.

Other than that, I don't think there is any limit on the size of transactions.

UPDATEing all the rows of a billion-row table is an awful thing to need to do, but it is a simple way to build a huge transaction. It will be slow, but won't be "too big".

OOM is a different matter. That is usually solvable by decreasing some settings in my.cnf. We need a lot more info to analyze that.

1
  • And is it OK if one transaction is larger than the total log file size?
    – FBB
    Sep 29 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.