Hi, 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 suspect you may be out of luck on that point, then. You could try bumping innodb_purge_threads, but that will only help flush the history out faster when it is freed, if the rows are still held by open transactions, there's not much it can do.
During such 30+ minutes continuous history length increase, I see no very long query in REPEATABLE READ in slow query log. I also checked transaction information every second like : $ watch -n1 "mysql -e 'show engine innodb status\G'|sed -ne '/History/,$ p' -e '/^--------$/q'|egrep -v '(not started$|^0 lock.*, 0 row lock)'|tee ~/tmp/`date '+%Y%m%d%H%M%S'`.txt" and even the longest 'ACTIVE' transactions were less than 2 minutes. (example) History list length 7262 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION (0x7f4c81a7c580), ACTIVE 11 sec MariaDB thread id 5503, OS thread handle 139938822260480, query id 45313431 10.0.17.57 sanef_fr_prod Trx read view will not see trx with id >= 6648342753, sees < 6648341142 ---TRANSACTION (0x7f4c81a74c80), ACTIVE 3 sec starting index read mysql tables in use 4, locked 0 MariaDB thread id 5502, OS thread handle 139938826868480, query id 45313602 10.0.17.57 sanef_fr_prod Sending data SELECT ... ---TRANSACTION (0x7f4c81a74180), ACTIVE 3 sec MariaDB thread id 5499, OS thread handle 139942207690496, query id 45313557 10.0.17.57 sanef_fr_prod Trx read view will not see trx with id >= 6648343225, sees < 6648343178 ---TRANSACTION (0x7f4c81a73680), ACTIVE 107 sec MariaDB thread id 5498, OS thread handle 139945986635520, query id 45313307 10.0.17.57 sanef_fr_prod Trx read view will not see trx with id >= 6648328955, sees < 6648328944 ... ---TRANSACTION (0x7f4c81a6f480), ACTIVE 109 sec MariaDB thread id 5492, OS thread handle 139947430971136, query id 45308485 10.0.17.57 sanef_fr_prod Trx read view will not see trx with id >= 6648328951, sees < 6648328944 ... -------- Like this, I can see some long ACTIVE transactions without showing any query, but they don't explain such continuous increase for 30+ minutes. I tried several configuration changes, but no difference. * innodb_purge_threads =32 (maximum possible value) * innodb_undo_tablespaces = 0 (like MariaDB 10.4) are there any other variables I can try ? I would like to first focus on this 'continuous history length increase' issue, as this is a quite serious 'regression' that blocks to upgrade MariaDB. (on the other hand, 'stall issue' below can be solved with larger (but not crazy large) log file, so it has less priority).
The thing to look for is sawtooth pattern on your checkpoint age (you'll need monitoring tools that show that, like PMM or a fork of it I'm working on called SSM).
What is drawn as 'free redo log in %' in my graphs is ... 100 - (innodb_checkpoint_age / innodb_log_group_capacity * 100) so I believe that I'm already monitoring innodb_checkpoint_age and 'stalls' happen when innodb_checkpoint_age is large enough. Best regards, Kazuhiko