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.

Improve this question
0

3 Answers 3

Reset to default
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.

Improve this answer
3
  • But is it possible to have a checkpoint in the middle of a single transaction?
    – FBB
    Sep 28, 2021 at 14:07
  • @FBB - <my-opinion>A checkpoint in the middle is is asking for trouble.</my-opinion>
    – Rick James
    Sep 29, 2021 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, 2021 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.

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

If your MySQL Server is causing out of memory problems, it's probably not related to the transaction size. I think that's a red herring.

InnoDB transactions can be larger than your RAM, because MySQL saves uncommitted work to disk as you go. If you run out of innodb transaction log space, it must flush modified pages from the buffer pool to disk, but these can still be related to an uncommitted transaction.

Another limit on transaction size is max_binlog_cache_size. If your MySQL Server uses a binary log, and a given transaction is larger than this configuration value, there's a risk that you'll corrupt your binary log, and replicas will stop with errors (I've seen this happen). Despite the reference to cache, this is not related to RAM either. Binlog cache uses both RAM and temporary files on disk.

MySQL Server uses the largest amount of RAM for the InnoDB Buffer Pool, not for transactions. But the Buffer Pool uses a fixed amount of RAM, which is allocated at MySQL Server startup.

In addition, there is a smaller amount of RAM allocated per connection and per query. I've seen this push total RAM usage over the system's physical RAM capacity if you have too many sessions running complex queries concurrently. Each query individually may be fine, but if 100 of them are running at the same time, it adds up to too much.

You can use the performance schema in MySQL 5.7 and later to measure memory usage, and you may be able to figure out the culprit. Here's a blog that describes how to do this: https://www.percona.com/blog/2020/11/02/understanding-mysql-memory-usage-with-performance-schema/

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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