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/1a6f470464171bd1144e4dd6f169bb4018f2e81a
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
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
