MySQL Bugs: #54455: innodb needs a way to limit consistent read snapshot age

Bug #54455	innodb needs a way to limit consistent read snapshot age
Submitted:	12 Jun 2010 11:11	Modified:	12 Jun 2010 13:40
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	8.0, 8.4, 9.4	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
too many times we see cases where long-running idle transactions are causing these symptoms: endlessly increasing ibdata* file size, climbing history list length,
and general slowing down of all transactions. e.g:

Trx id counter DB5DD
Purge done for trx's n:o < A66FD undo n:o < 3
History list length 87585
---TRANSACTION A66FC, ACTIVE 9256 sec, process no 18606, OS thread id 1192036672

How to repeat:
on any innodb server that is doing alot of DML, just run "start transaction with consistent snapshot" and leave the connection open.  to avoid wait_timeout, you can "select 1" every few minutes.  monitor innodb status..

Suggested fix:
i dunno, create some option to let dba configure maximum length of a transaction, so that innodb kills it and it returns 'Snapshot Too Old' or something.
it can be disabled by default to maintain existing compatibility.

Thank you for the feature request.

I really, really want this feature. It is an intermittent source of problems and it is easy to forget to look at SHOW INNODB STATUS or SHOW INNODB TRANSACTION STATUS to find the long-open transaction.

also reported as bug #34414 which demonstrates the problem is more widespread.

Mark, would handling of a thread that's blocking flush in a similar way to a lock wait timeout be sufficient or are there more cases that you think we should handle? Flush blocked is a clear "no choice but to act" situation because the server can't do any more DML work until the situation is resolved.

There are other possible cases, which ones are of interest to you and also have a fairly low chance of side effects, with decently reliable heuristics to use to decide "stop that thread now"?

My last comment should have been purge thread, not flush thread.

http://yoshinorimatsunobu.blogspot.com/2011/04/tracking-long-running-transactions-in.html

http://www.mysqlperformanceblog.com/2011/03/08/how-to-debug-long-running-transactions-in-m...

http://www.mysqlperformanceblog.com/2011/06/02/active-with-locks-now-thats-a-problem/

http://mysqlquicksand.wordpress.com/2012/11/15/runaway-history-list/

Mark, you can look at my patch in bug#67906(http://bugs.mysql.com/bug.php?id=67906)

Also:
http://bugs.mysql.com/bug.php?id=72362
http://blog.jcole.us/2014/04/16/a-little-fun-with-innodb-multi-versioning/

More related text:
http://smalldatum.blogspot.com/2015/07/the-impact-of-long-running-transactions.html
https://bugs.mysql.com/bug.php?id=74919 
(purge should remove intermediate rows between far snapshots)

https://www.percona.com/blog/2017/05/08/chasing-a-hung-transaction-in-mysql-innodb-history...

With lagging purge you may very well expose this:

https://bugs.mysql.com/bug.php?id=84958
(InnoDB's MVCC has O(N^2) behaviors)

See "Long running transactions" in:
https://blog.koehntopp.info/2020/07/27/mysql-transactions.html

https://bugs.mysql.com/bug.php?id=100547
is another report of this scenario.

related:
 https://bugs.mysql.com/bug.php?id=104619

https://lefred.be/?p=5603&preview=1&_ppp=2a12518709
(A graph a day, keeps the doctor away ! – MySQL History List Length)

https://www.youtube.com/watch?v=PBvGicWAkKI
(Episode-099 - Is having a large InnoDB History List Length a performance issue?)

related:
https://bugs.mysql.com/bug.php?id=119396
(Increase in undo log size , slower purging and no truncation even after restart)