Currently each of our MySQL transaction is calculating and updating 100K+~million rows into a 100K to billion row table. Sometimes such a transaction failed due to unclear reasons and we are trying to triage it. Some people suggest that limiting the number of rows in each transaction (less than 100 Ks) is a good practice. However, we would like to quantify better what is the transaction limit in our case. Also we would like to make the transaction error message more informative by including more system status during a transaction failure, so that we can
confidently know what is the transaction limit in the current hardware spec. Any two cents? Right now we are printing the MySQL "show variables" when a transaction failed, and also semi-manually compare the system resources using grafana. https://grafana.com/ This is quite laborious and might not be accurate since grafana might have some delayed, etc.
Thanks.
Instead of trying to analyze a too-big transaction, let's discuss options for eliminating it.
Would it be OK to break it into, say, 10K rows at a time? That is, after each 10K rows, there is a COMMIT. This breaks one transaction into several. From a performance and crash point of view, this is a definite win. From an ACID point of view, well, you need to answer that.
Assuming you can break it apart, I recommend the technique given here as a possible way to efficiently perform the query:
http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks (Yes, with minor changes, it applies to UPDATE.)