Sample Header Ad - 728x90

What contributes to the transaction size on a MySQL UPDATE?

0 votes
2 answers
3183 views
**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?
Asked by scarba05 (103 rep)
Jan 15, 2021, 09:29 AM
Last activity: Nov 15, 2021, 12:58 AM