Is there an InnoDB max transaction size and what happens if it is reached?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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/