What contributes to the transaction size on a MySQL UPDATE?

Question

Background

We're running a MySQL 8 INNODB cluster behind some Java services. We received an error from the application because we'd exceeded the group replication transaction size limit (“Error on observer while running replication hook 'before_commit'”). Our limit was set to 150Mb at the time.

Problem

Looking at the transaction involved I don't understand how it might have involved anything like 150Mb.

It involved an update to two tables

update my_table mt 
inner join my_table_aud mta on mt.id = mta.id 
  set mt.boolean_column_1 = TRUE, 
      mt.boolean_column_2 = TRUE, 
      mt.varchar_column = coalesce(mt.varchar_column, ?2), 
      mta.varchar_column = coalesce(mta.varchar_column, ?2) 
where mt.boolean_column_1 = FALSE 
AND mta.rev <= ?1

which involved approximately 100 rows in my_table and maybe 200 rows in my_table_aud. Plus one other simple insert to a different table. The varchar columns were updated with around 10 bytes of data.

However the two tables involved in the UPDATE do both have a different longtext column, which wasn't updated. There would have been on average maybe 1MB in text per row updated in those columns.

The only explanation I can think of for us exceeding the transaction limit would be that the text in longtext columns contributed to the transaction size, even though they were not referenced in the update.

I searched for documentation on what contributes to the transaction size of a transaction in MySQL and haven't been able to find anything useful.

Please can someone help my understanding of how the transaction size limit might have been exceeded in this scenario?

Answer 1

The transaction size is not just based on the amount of data you're actually change, rather the whole state of the row before you made the change is preserved in case of a rollback and therefore the entire row is essentially copied. From further research it sounds like as much as even the entire page of rows is copied to the rollback segment, so depending on how many pages contain the rows you're updating, it could easily be a lot more data generated in the transaction then what you're actually updating.

Answer 2

The group_replication_transaction_size_limit parameter was added to MySQL 8.0.23 and 5.7.33 to fix a bug that would allow an attacker to crash MySQL (this needed a lot of a determination though). I blogged about this bug a year ago (An Unprivileged User can crash your MySQL Server).

(I am preparing a follow-up post, and I found this question while Googling group_replication_transaction_size_limit.)

In Group Replication and for optimized parallel replication, a data structure called the Write-Set is maintained in MySQL. Every row modified is saved as a primary-key hash in the Write-Set. In Group Replication, this allows to detect conflicting transactions run on different nodes and reject commit. For Optimized parallel replication, it allows to detect transaction dependencies for parallel execution on replica.

In the 8.0.23 release notes, we can read:

Replication: When the system variable transaction_write_set_extraction=XXHASH64 is set, which is the default in MySQL 8.0 and a requirement for Group Replication, the collection of writes for a transaction previously had no upper size limit. Now, for standard source to replica replication, the numeric limit on write sets specified by binlog_transaction_dependency_history_size is applied, after which the write set information is discarded but the transaction continues to execute. Because the write set information is then unavailable for the dependency calculation, the transaction is marked as non-concurrent, and is processed sequentially on the replica. For Group Replication, the process of extracting the writes from a transaction is required for conflict detection and certification on all group members, so the write set information cannot be discarded if the transaction is to complete. The byte limit set by group_replication_transaction_size_limit is applied instead of the numeric limit, and if the limit is exceeded, the transaction fails to execute. (Bug #32019842)

I am not an expert in Group Replication or in the Write-Set, but my best guess is that the way you run this update is dumping more than only the updated rows in the Write-Set, and that the whole Write-Set ends-up taking more RAM than allowed.