On 2024-12-12 16:52, Gordan Bobic wrote:
On 2024-12-04 18:31, Gordan Bobic wrote:
I just tried READ COMMITTED everywhere on 11.4 and I had no such consistent increase of the history length (see the attachment). But it is not an acceptable solution and we anyway had no such issue on 10.4 with READ COMMITTED and REPEATABLE READ combination.
I tried MariaDB 10.5. Both 'consistent increase of the history length' issue and 'MariaDB stall' issue happen on MariaDB 10.5.27 like I already tried on 11.4.4 while running many READ COMMITTED DML and many REPEATABLE READ DQL queries in parallel.
Didn't you previously mention that the tx history length problem goes away when running globally in read-committed mode (similar to behaviour of innodb_locks_unsafe_for_binlog on 10.4)?
Here is the summary for history length issue: (RC = READ COMMITTED, RR = REPEATABLE READ) Good * 10.4, RR only with innodb_locks_unsafe_for_binlog * 10.4, RC for DML, RR for DQL * 10.5, RC only Bad * 10.5, RC for DML, RR for DQL * 11.4, RC for DML, RR for DQL 'RC only' is not a 'solution' for our application. It is only to see if there is any behaviour difference. And 'RC for DML, RR for DQL' has no issue with 10.4 (and 10.3), but has issue with 10.5 and 11.4.
On the subject of the stall you mention, if we are talking about the stall that happens when the locks finally resolve and the tx history gets purged, that is a serious problem that I have yet to manage to fully tune out in cases where I have experienced it, but it can be partially mitigated by setting something like the following:
innodb_max_purge_lag_wait = 1M # I find that around 1M is where things start to go badly sideways, you may want to try lower for your workload, e.g. 100K innodb_max_purge_lag = 100K innodb_purge_threads = 16 # assuming you have at least 16 CPU cores on the server)
I tried these variables but saw no difference, still had 'consistent history length increase' and 'MariaDB stall' issues.
It is not yet clear which changes between 10.4 and 10.5 cause these issues. https://mariadb.com/kb/en/changes-and-improvements-in-mariadb-10-5/
Are there any parameter to make 10.5 (or later) behave like 10.4 for these issues ? Or can they be simply regression bugs ?
If you tracked down the difference in behaviour specifically to innodb_locks_unsafe_for_binlog, then that was removed in this commit: https://github.com/MariaDB/server/commit/1a6f470464171bd1144e4dd6f169bb4018f...
Thanks. I know this commit. I even reverted it (i.e. revived innodb_locks_unsafe_for_binlog) on MariaDB 11.4, but the behaviour was nearly same as '11.4, RC for DML, RR for DQL'. Best regards, Kazuhiko