increasing InnoDB history length and stalls on MariaDB 11.4
Hello, Our application has a sustained rate of DML queries in parallel with DQL. We can achieve good performance using innodb-locks-unsafe-for-binlog on MariaDB 10.4, with careful consideration of its 'unsafeness'. But innodb-locks-unsafe-for-binlog is removed in MariaDB 10.5 or later and our application runs much slower without it because of gap locking. https://mariadb.com/docs/server/ref/mdb/cli/mariadbd/innodb-locks-unsafe-for... To achieve a good performance as before, we now use READ COMMITTED transaction isolation for DML and use REPEATABLE READ isolation for DQL on MariaDB 11.4. This way guarantees the consistency for our application. Our database contains totally 13.7G rows and uses 4.5TB storage. Here is the configuration : innodb_buffer_pool_size = 17179869184 (16GB) innodb_buffer_pool_instances = 16 (no meaning in 11.4) innodb_log_file_size = 134217728 (128MB) innodb_flush_method = O_DIRECT Please see the attached graphs drawing 'InnoDB history length (right axis)' and 'free redo log in % (left axis)' (i.e. 100 - Redo Log Occupancy) while running our reference workload. https://mariadb.com/kb/en/innodb-redo-log/#determining-the-redo-log-occupanc... These tests are done with the same workload, but only MariaDB version and/or configuration is different. (Please disregard the 'warehouse history length' and 'activities history length' series in those plots, they are not relevant to the current issue.) On MariaDB 10.4, the performance and the behaviour between 'using innodb-locks-unsafe-for-binlog' and 'using READ COMMITTED and REPEATABLE READ' are similar. But on MariaDB 11.4, I found two serious issues. Issue 1: InnoDB history length keeps growing while running lots of DML and DQL queries in parallel. InnoDB history length is stable around 5k on 10.4, but it keeps growing on 11.4 up to 100k until the end. In our experience, increases in history list length are roughly correlated with poor query performance (causing queries to "step over tombstones" ?), and we do our best to avoid having long-running transactions in order to allow older snapshots to be freed ASAP. We are satisfied with the status on 10.4, where history list is constant. What is the reason behind such increases on 11.4 and is there any configuration to avoid such ? Issue 2: MariaDB stalls when free redo log is too small. When MariaDB stalls, even a quite simple query takes too long. (table definition) CREATE TABLE `catalog` ( `uid` bigint(20) unsigned NOT NULL, `path` varchar(255) NOT NULL DEFAULT '', ... PRIMARY KEY (`uid`), KEY `Path` (`path`), ... ) (from pt-query-digest output) # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 2 91 # Exec time 2 716s 1s 17s 8s 15s 5s 7s # Lock time 0 22ms 156us 491us 238us 366us 70us 204us # Rows sent 0 663 3 10 7.29 8.91 1.09 6.98 # Rows examine 0 663 3 10 7.29 8.91 1.09 6.98 # Rows affecte 0 0 0 0 0 0 0 0 # Bytes sent 0 52.10k 330 749 586.24 685.39 68.46 563.87 # Query size 0 470.83k 3.56k 5.26k 5.17k 5.20k 225.56 5.20k SELECT uid, path FROM catalog WHERE path in ('...', /*... omitted 80 items ...*/)\G (here, these SELECT are issued in READ COMMITTED isolation level) Our understanding is that the stalls are caused by the undo log cleanup job, and/or the innodb buffer pool writeback job. Increasing the undo log size a lot seems to reduce the frequency of those stalls. For our reference workload here, we still have stalls with 1GB log file but we don't have stalls with 2GB or more log file. If we need 'big enough' log file size to avoid stalls, will the ideal size be proportional to the amount of data or proportional to the buffer pool size ? We have never had such stall on MariaDB 10.4 even with small 128MB log file. How this difference happen and is there any configuration to improve the behaviour ? Thanks in advance ! Best regards, Kazuhiko SHIOZAKI
On Tue, Dec 3, 2024 at 12:40 PM Kazuhiko via discuss <discuss@lists.mariadb.org> wrote:
Hello,
Our application has a sustained rate of DML queries in parallel with DQL. We can achieve good performance using innodb-locks-unsafe-for-binlog on MariaDB 10.4, with careful consideration of its 'unsafeness'. But innodb-locks-unsafe-for-binlog is removed in MariaDB 10.5 or later and our application runs much slower without it because of gap locking. https://mariadb.com/docs/server/ref/mdb/cli/mariadbd/innodb-locks-unsafe-for...
To achieve a good performance as before, we now use READ COMMITTED transaction isolation for DML and use REPEATABLE READ isolation for DQL on MariaDB 11.4. This way guarantees the consistency for our application.
Have you actually verified that your application needs repeatable-read? It's possible but in my experience it is extremely rare for applications to re-read the rows they already read. Not to say it never happens, but I have seen application implementations that actually needs this maybe twice out of thousands of applications I've worked on over the last 25 years.
Our database contains totally 13.7G rows and uses 4.5TB storage. Here is the configuration :
innodb_buffer_pool_size = 17179869184 (16GB) innodb_buffer_pool_instances = 16 (no meaning in 11.4) innodb_log_file_size = 134217728 (128MB) innodb_flush_method = O_DIRECT
Please see the attached graphs drawing 'InnoDB history length (right axis)' and 'free redo log in % (left axis)' (i.e. 100 - Redo Log Occupancy) while running our reference workload. https://mariadb.com/kb/en/innodb-redo-log/#determining-the-redo-log-occupanc... These tests are done with the same workload, but only MariaDB version and/or configuration is different. (Please disregard the 'warehouse history length' and 'activities history length' series in those plots, they are not relevant to the current issue.)
On MariaDB 10.4, the performance and the behaviour between 'using innodb-locks-unsafe-for-binlog' and 'using READ COMMITTED and REPEATABLE READ' are similar. But on MariaDB 11.4, I found two serious issues.
Issue 1: InnoDB history length keeps growing while running lots of DML and DQL queries in parallel.
InnoDB history length is stable around 5k on 10.4, but it keeps growing on 11.4 up to 100k until the end. In our experience, increases in history list length are roughly correlated with poor query performance (causing queries to "step over tombstones" ?), and we do our best to avoid having long-running transactions in order to allow older snapshots to be freed ASAP. We are satisfied with the status on 10.4, where history list is constant. What is the reason behind such increases on 11.4 and is there any configuration to avoid such ?
Have you been able to measure the performance impact of this? 100K isn't usually high enough to start causing a measurable performance degradation, that usually starts to happen north of 1M history length.
Issue 2: MariaDB stalls when free redo log is too small.
When MariaDB stalls, even a quite simple query takes too long.
(table definition) CREATE TABLE `catalog` ( `uid` bigint(20) unsigned NOT NULL, `path` varchar(255) NOT NULL DEFAULT '', ... PRIMARY KEY (`uid`), KEY `Path` (`path`), ... )
(from pt-query-digest output) # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 2 91 # Exec time 2 716s 1s 17s 8s 15s 5s 7s # Lock time 0 22ms 156us 491us 238us 366us 70us 204us # Rows sent 0 663 3 10 7.29 8.91 1.09 6.98 # Rows examine 0 663 3 10 7.29 8.91 1.09 6.98 # Rows affecte 0 0 0 0 0 0 0 0 # Bytes sent 0 52.10k 330 749 586.24 685.39 68.46 563.87 # Query size 0 470.83k 3.56k 5.26k 5.17k 5.20k 225.56 5.20k SELECT uid, path FROM catalog WHERE path in ('...', /*... omitted 80 items ...*/)\G (here, these SELECT are issued in READ COMMITTED isolation level)
Our understanding is that the stalls are caused by the undo log cleanup job, and/or the innodb buffer pool writeback job. Increasing the undo log size a lot seems to reduce the frequency of those stalls. For our reference workload here, we still have stalls with 1GB log file but we don't have stalls with 2GB or more log file. If we need 'big enough' log file size to avoid stalls, will the ideal size be proportional to the amount of data or proportional to the buffer pool size ? We have never had such stall on MariaDB 10.4 even with small 128MB log file. How this difference happen and is there any configuration to improve the behaviour ?
This looks like the furious flushing regression that was introduced in 10.5 and doesn't appear to have been fixed since. It is possible to tune it out to a large extent, though: https://shatteredsilicon.net/mariadb-furious-flushing-fix-checkpoint-age/
Hi, On 2024-12-03 12:21, Gordan Bobic wrote:
To achieve a good performance as before, we now use READ COMMITTED transaction isolation for DML and use REPEATABLE READ isolation for DQL on MariaDB 11.4. This way guarantees the consistency for our application.
Have you actually verified that your application needs repeatable-read? It's possible but in my experience it is extremely rare for applications to re-read the rows they already read. Not to say it never happens, but I have seen application implementations that actually needs this maybe twice out of thousands of applications I've worked on over the last 25 years.
Our queries are dynamic and we also have queries which depends on the result of earlier queries in the same transaction. On MariaDB 10.4 with innodb-locks-unsafe-for-binlog, we use REPEATABLE READ everywhere. Having a few carefully-checked exceptions to use READ COMMITTED would be fine, but would require more work than we can afford.
Issue 1: InnoDB history length keeps growing while running lots of DML and DQL queries in parallel.
InnoDB history length is stable around 5k on 10.4, but it keeps growing on 11.4 up to 100k until the end. In our experience, increases in history list length are roughly correlated with poor query performance (causing queries to "step over tombstones" ?), and we do our best to avoid having long-running transactions in order to allow older snapshots to be freed ASAP. We are satisfied with the status on 10.4, where history list is constant. What is the reason behind such increases on 11.4 and is there any configuration to avoid such ?
Have you been able to measure the performance impact of this? 100K isn't usually high enough to start causing a measurable performance degradation, that usually starts to happen north of 1M history length.
For the performance impact, I compared pt-query-digest of slow queries between : * 10.4 with READ COMMITTED and REPEATABLE READ * 11.4 with READ COMMITTED and REPEATABLE READ, with 2GB log file size where we had no stalls On 10.4, all slow queries are DML. On 11.4, we have fewer DML slow queries but we also have some DQL slow queries. 'DQL slow queries on 11.4 only' is a sign of performance impact of much more history length, I guess. (and 'fewer DML slow queries on 11.4' should come from various improvements on recent MariaDB). And this reference workload simulates a small part of what we do in production. So if the history length keeps increasing like this, it will reach 1M or more. 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.
Issue 2: MariaDB stalls when free redo log is too small. ... This looks like the furious flushing regression that was introduced in 10.5 and doesn't appear to have been fixed since. It is possible to tune it out to a large extent, though: https://shatteredsilicon.net/mariadb-furious-flushing-fix-checkpoint-age/
Your article looks quite interesting and the symptom we had seems identical. And I tried the same configuration as you wrote there, innodb_adaptive_flushing = 0 innodb_adaptive_flushing_lwm = 50 innodb_max_dirty_pages_pct_lwm = 50 but the behaviour was still same, i.e. MariaDB stalled when free redo log is low. I can admit 128MB log file size is too small for 16GB buffer pool even though we had no issue on MariaDB 10.4, but MariaDB 11.4 still stalls with 1GB log file as well. Best regards, Kazuhiko
On Wed, Dec 4, 2024 at 6:23 PM <kazuhiko@nexedi.com> wrote:
Issue 1: InnoDB history length keeps growing while running lots of DML and DQL queries in parallel.
InnoDB history length is stable around 5k on 10.4, but it keeps growing on 11.4 up to 100k until the end. In our experience, increases in history list length are roughly correlated with poor query performance (causing queries to "step over tombstones" ?), and we do our best to avoid having long-running transactions in order to allow older snapshots to be freed ASAP. We are satisfied with the status on 10.4, where history list is constant. What is the reason behind such increases on 11.4 and is there any configuration to avoid such ?
Have you been able to measure the performance impact of this? 100K isn't usually high enough to start causing a measurable performance degradation, that usually starts to happen north of 1M history length.
For the performance impact, I compared pt-query-digest of slow queries between : * 10.4 with READ COMMITTED and REPEATABLE READ * 11.4 with READ COMMITTED and REPEATABLE READ, with 2GB log file size where we had no stalls
On 10.4, all slow queries are DML. On 11.4, we have fewer DML slow queries but we also have some DQL slow queries. 'DQL slow queries on 11.4 only' is a sign of performance impact of much more history length, I guess. (and 'fewer DML slow queries on 11.4' should come from various improvements on recent MariaDB).
And this reference workload simulates a small part of what we do in production. So if the history length keeps increasing like this, it will reach 1M or more.
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. FWIW, I am actively considering taking on post-EOL maintenance of 10.4.x and backporting applicable 10.5.x patches to it. Reason being performance anomalies similar to the ones you have bumped into.
Issue 2: MariaDB stalls when free redo log is too small. ... This looks like the furious flushing regression that was introduced in 10.5 and doesn't appear to have been fixed since. It is possible to tune it out to a large extent, though: https://shatteredsilicon.net/mariadb-furious-flushing-fix-checkpoint-age/
Your article looks quite interesting and the symptom we had seems identical. And I tried the same configuration as you wrote there, innodb_adaptive_flushing = 0 innodb_adaptive_flushing_lwm = 50 innodb_max_dirty_pages_pct_lwm = 50 but the behaviour was still same, i.e. MariaDB stalled when free redo log is low.
I can admit 128MB log file size is too small for 16GB buffer pool even though we had no issue on MariaDB 10.4, but MariaDB 11.4 still stalls with 1GB log file as well.
Contrary to widely popular belief that redo log size is supposed to be in any meaningful way relative in size to the buffer pool size, the two have no common basis. Your redo log needs to be large enough to: 1) absorb short bursts of writes 2) be large enough to make sure it doesn't roll over during your mariabackup process 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). The settings I mentioned cure that for me, and with that cured, you won't get stalls because the log won't fill up. If your log is filling up anyway because the burst of writes is too large (checkpoint age graph goes flat at the top, rather than sawtooth pattern), you will probably need to increase some combination of: innodb_io_capacity_max: I normally set this to between 2/3 and 3/4 of what your disks are capable of delivering with innodb_page_size random writes. innodb_io_capacity: Half of innodb_io_capacity_max is usually a good first pass estimate. innodb_write_io_threds: Bump this if your iops are struggling to touch innodb_io_capacity_max under load and this is making checkpoint age high.. But if that doesn't get your checkpoint age back under control, you may just have to increase the redo log size and see if that makes the problem go away.
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
Hi, On 2024-12-05 21:44, Kazuhiko via discuss 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. 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 ? Best regards, Kazuhiko
On Thu, Dec 12, 2024 at 5:31 PM <kazuhiko@nexedi.com> wrote:
Hi,
On 2024-12-05 21:44, Kazuhiko via discuss 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)? 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)
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...
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
On Thu, Dec 12, 2024 at 10:43 PM Kazuhiko via discuss <discuss@lists.mariadb.org> wrote:
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
Could you please also test the recent 10.6.20 release? It includes some performance improvements to the InnoDB undo log subsystem that were not applied to the 10.5 series, most recently MDEV-34515. The 11.4 series could suffer from other performance anomalies. At least one contention point around log_sys.mutex was removed thanks to the new ib_logfile0 format in 10.8. Sometimes, removing a contention point may make some other contention point prominent for a particular workload. It's a bit hard to imagine that log_sys.mutex might have acted as a useful "throttle" for something, like the parameters that were deprecated and disabled in https://jira.mariadb.org/browse/MDEV-23379, but I have seen that everything is possible. In case you are running on NUMA hardware, it might be worth trying to bind the mariadbd process to a single NUMA node in order to avoid cross-NUMA traffic. In some workloads, going from 2 NUMA nodes to 1 can double the throughput. I have tried to improve our NUMA performance (any reduction of mutex contention should help with that), but the high-level design is really targeting SMP, not being NUMA friendly. With best regards, Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB plc
Hi, On 2024-12-13 11:46, Marko Mäkelä wrote:
On Thu, Dec 12, 2024 at 10:43 PM Kazuhiko via discuss <discuss@lists.mariadb.org> wrote:
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
Could you please also test the recent 10.6.20 release? It includes some performance improvements to the InnoDB undo log subsystem that were not applied to the 10.5 series, most recently MDEV-34515.
In 'RC for DML, RR for DQL' combination, here are the more detailed list for history length issue : Good * 10.4.34 * 10.5.5, 10.5.16, 10.5.19, 10.5.20 * 10.6.13 Bad * 10.5.21, 10.5.22, 10.5.27 * 10.6.15, 10.6.20 * 11.4.3, 11.4.4 So it seems that the changes between 10.5.20 and 10.5.21 caused this issue. # I skipped 10.6.14 because of the warning in the release note. # https://mariadb.com/kb/en/mariadb-10-6-14-release-notes/
In case you are running on NUMA hardware, it might be worth trying to bind the mariadbd process to a single NUMA node in order to avoid cross-NUMA traffic. In some workloads, going from 2 NUMA nodes to 1 can double the throughput. I have tried to improve our NUMA performance (any reduction of mutex contention should help with that), but the high-level design is really targeting SMP, not being NUMA friendly.
The current performance test environment is running on a virtual machine, thus I'm not sure if it is a NUMA 'hardware'. But anyway its impact should be much smaller than history length behaviour difference shown above, I think. Best regards, Kazuhiko
On 2024-12-13 14:53, Kazuhiko via discuss wrote:
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
Could you please also test the recent 10.6.20 release? It includes some performance improvements to the InnoDB undo log subsystem that were not applied to the 10.5 series, most recently MDEV-34515.
In 'RC for DML, RR for DQL' combination, here are the more detailed list for history length issue :
Good * 10.4.34 * 10.5.5, 10.5.16, 10.5.19, 10.5.20 * 10.6.13
Bad * 10.5.21, 10.5.22, 10.5.27 * 10.6.15, 10.6.20 * 11.4.3, 11.4.4
So it seems that the changes between 10.5.20 and 10.5.21 caused this issue.
# I skipped 10.6.14 because of the warning in the release note. # https://mariadb.com/kb/en/mariadb-10-6-14-release-notes/
Sorry, 10.6.13 seems 'not always' good. I tried 10.6.13 with 4GB log file and 256MB log file cases (buffer pool size is 16GB on both tests). No history length increase issue on 10.6.13 with 4GB log file. BUT quite bad behaviour on 10.6.13 with 256MB log file, i.e. some history length caused by cleanup process still remained at the beginning, and increased history length remained even after everything finished. Also 1-2 seconds stall happened several times. (see the attached graphs) On 10.5.20, I had no such issue on both 4GB log file and 256MB log file, and its behaviour was quite 10.4.34. Best regards, Kazuhiko
Hi Kazuhiko, all, On Sat, Dec 14, 2024 at 12:58 AM <kazuhiko@nexedi.com> wrote:
Sorry, 10.6.13 seems 'not always' good.
I tried 10.6.13 with 4GB log file and 256MB log file cases (buffer pool size is 16GB on both tests).
No history length increase issue on 10.6.13 with 4GB log file. BUT quite bad behaviour on 10.6.13 with 256MB log file, i.e. some history length caused by cleanup process still remained at the beginning, and increased history length remained even after everything finished. Also 1-2 seconds stall happened several times. (see the attached graphs)
On 10.5.20, I had no such issue on both 4GB log file and 256MB log file, and its behaviour was quite 10.4.34.
Can you (or anyone) post a test script as well as some details of the test environment that is reproducing this regression? I would need to reproduce this regression in order to analyze and fix it. Best regards, Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB plc
Hi Marko, On 2025-01-07 12:22, Marko Mäkelä wrote:
No history length increase issue on 10.6.13 with 4GB log file. BUT quite bad behaviour on 10.6.13 with 256MB log file, i.e. some history length caused by cleanup process still remained at the beginning, and increased history length remained even after everything finished. Also 1-2 seconds stall happened several times. (see the attached graphs)
On 10.5.20, I had no such issue on both 4GB log file and 256MB log file, and its behaviour was quite 10.4.34.
Can you (or anyone) post a test script as well as some details of the test environment that is reproducing this regression? I would need to reproduce this regression in order to analyze and fix it.
I could reproduce 'history list increase' issue with the attached schema and python script, that executes 50% R-C DML and 50% R-R DQL using 64 threads. On 10.5.20, history length at the end of each iteration is quite stable, like < 50. On 10.6.13, history length keeps increasing. My configuration is not that special. innodb_flush_method = O_DIRECT innodb_change_buffering = none innodb_buffer_pool_size = 17179869184 innodb_buffer_pool_instances = 64 Kazuhiko
Hi Kazuhiko, On Wed, Jan 8, 2025 at 12:54 PM <kazuhiko@nexedi.com> wrote:
I could reproduce 'history list increase' issue with the attached schema and python script, that executes 50% R-C DML and 50% R-R DQL using 64 threads.
Thank you. I have asked our performance tester to reproduce this. For what it is worth, for quite some time we have observed a huge history list length during certain Sysbench workloads. The problem was significantly improved for some workloads by some changes such as MDEV-32050. One surprise was that under some circumstances, the purge tasks could steal too many resources from SQL processing. The actual "fix" of https://jira.mariadb.org/browse/MDEV-34515 was to limit the default size of a purge batch to 127, to match the number of usable rollback segments when using innodb_undo_tablespaces>1. Roughly speaking, InnoDB can concurrently start or commit at most that many transactions. If the purge batch is larger, it appears to block the starting or finishing of user transactions. I made several other improvements based on my observations while diagnosing MDEV-34515, but in the end it was the configuration parameter change that "fixed" that artificial test case (a table with 1 record and 1 secondary index). The parameter innodb_buffer_pool_instances has no effect starting with MariaDB Server 10.5. I believe that the motivation of splitting the buffer pool was to work around contention on buf_pool.mutex. We do have one known performance bug MDEV-33966 in that area. Quite some time ago, I started https://github.com/MariaDB/server/pull/3522 but did not get to refine it. Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB plc
Hi Marko, On 2025-01-09 07:19, Marko Mäkelä wrote:
On Wed, Jan 8, 2025 at 12:54 PM <kazuhiko@nexedi.com> wrote:
I could reproduce 'history list increase' issue with the attached schema and python script, that executes 50% R-C DML and 50% R-R DQL using 64 threads.
Thank you. I have asked our performance tester to reproduce this.
I ran this test on several latest MariaDB versions. As our real use case is much larger than possible physical RAM, I intentionally ran this test with the default innodb_buffer_pool_size, i.e. 128MB, so that data / index size will exceed the buffer size during the test. Thus [mysqld] section configuration is like : slow_query_log long_query_time = 1 innodb_flush_method = O_DIRECT histogram_size = 0 optimizer_use_condition_selectivity = 1 With MariaDB 10.4, history length is quite stable, always less than 100. For any version >= 10.5, it keeps growing even though we have no long running transactions. But with these '20 iteration' tests, the overall duration are not so different even with such higher history length. Can we assume that 'high history length without running long transaction' is harmless ? Another interesting result is 'slow query analysis'. We had slow (i.e.
= 1sec) queries not only in DML but also very simple DQL.
[DML] # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Exec time 99 250125s 1s 14s 4s 6s 1s 3s (10.4.34) # Exec time 99 228618s 1s 89s 4s 7s 2s 4s (10.5.28) # Exec time 99 204810s 1s 11s 3s 6s 1s 3s (10.6.21) # Exec time 99 207643s 1s 10s 3s 6s 1s 3s (10.11.11) # Exec time 99 224214s 1s 10s 4s 6s 1s 4s (11.4.5) [DQL] # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Exec time 0 1352s 1s 3s 1s 2s 212ms 1s (10.4.34) # Exec time 0 1022s 1s 46s 3s 9s 4s 1s (10.5.28) # Exec time 0 30s 1s 1s 1s 1s 60ms 1s (10.6.21) # Exec time 0 140s 1s 1s 1s 1s 51ms 1s (10.11.11) # Exec time 0 58s 1s 1s 1s 1s 85ms 1s (11.4.5) Here, 10.5.28 is really horrible. It took 46s at maximum with a simple DQL using the primary key. But in my test workload with prod-like data, I had 'several seconds freeze' on 10.11 and 11.4 as well, that cannot be reproduced with this simple test unfortunately. If you have any idea to suppress or investigate 'freeze' issue, it would be very appreciated! For now I don't know the reason behind, like freeing undo log ? writing buffer to storage ? or anything else ?
For what it is worth, for quite some time we have observed a huge history list length during certain Sysbench workloads. The problem was significantly improved for some workloads by some changes such as MDEV-32050. One surprise was that under some circumstances, the purge tasks could steal too many resources from SQL processing. The actual "fix" of https://jira.mariadb.org/browse/MDEV-34515 was to limit the default size of a purge batch to 127, to match the number of usable rollback segments when using innodb_undo_tablespaces>1. Roughly speaking, InnoDB can concurrently start or commit at most that many transactions. If the purge batch is larger, it appears to block the starting or finishing of user transactions. I made several other improvements based on my observations while diagnosing MDEV-34515, but in the end it was the configuration parameter change that "fixed" that artificial test case (a table with 1 record and 1 secondary index).
Setting innodb_purge_batch_size to 300 (the old default value) did not change this test result, at least on 11.4.5, i.e. history length still keeps growing. Best regards, Kazuhiko
Hi Kazuhiko, https://jira.mariadb.org/browse/MDEV-36472 was finally filed on our side for this anomaly. I am afraid that the performance of purging committed transaction history was reduced due to the correctness fixes MDEV-26465, MDEV-30671 and MDEV-31355. Before those fixes, some history could have been removed prematurely, while it was possible for some older transaction to access the records. I suspect that some crashes on MVCC reads (MDEV-14407) had occurred exactly because some data had been purged before these bugs had been fixed. I can see two possible developments how we could improve this. The easier one would be to introduce a global Boolean variable that you could set to make the purge more aggressive. Old transactions would fail with an error. There is an InnoDB internal error code DB_MISSING_HISTORY, which currently should only be possible when the undo logs are corrupted in some way. We could start returning a higher level error, such as ER_CHECKREAD, which was recently enabled by the innodb_snapshot_isolation setting (MDEV-35124). This error will always result in the transaction being rolled back, just like a deadlock would. A more advanced solution would be to finally implement per-record transaction identifiers in secondary indexes (MDEV-17598). This should significantly speed up the purge operation in secondary indexes. Best regards, Marko
Gordan Bobic via discuss <discuss@lists.mariadb.org> writes:
FWIW, I am actively considering taking on post-EOL maintenance of 10.4.x and backporting applicable 10.5.x patches to it. Reason being performance anomalies similar to the ones you have bumped into.
I think this is a good idea. I also think the MariaDB Foundation could take a role in this. I'll be happy to assist with the backporting of patches. I think the important thing will be to be quite restrictive about which patches are back-ported. It should only be critical fixes, mostly security fixes and regressions. There's usually a good argument for some person's favorite patch to include, but the whole purpose of this will be to provide a very stable branch, which is best facilitated by minimizing changes done. - Kristian.
On Wed, Jan 1, 2025 at 11:37 PM Kristian Nielsen <knielsen@knielsen-hq.org> wrote:
Gordan Bobic via discuss <discuss@lists.mariadb.org> writes:
FWIW, I am actively considering taking on post-EOL maintenance of 10.4.x and backporting applicable 10.5.x patches to it. Reason being performance anomalies similar to the ones you have bumped into.
I think this is a good idea. I also think the MariaDB Foundation could take a role in this.
Good luck with that...
I'll be happy to assist with the backporting of patches.
I think the important thing will be to be quite restrictive about which patches are back-ported. It should only be critical fixes, mostly security fixes and regressions. There's usually a good argument for some person's favorite patch to include, but the whole purpose of this will be to provide a very stable branch, which is best facilitated by minimizing changes done.
Well, I was thinking about backporting all applicable patches from 10.5.x after 10.5.25. Backporting CVEs only is obviously a much smaller task and there haven't been any fixed since 10.4.34 / 10.5.25, so if that's you're only concern, you are probably good to stick with 10.4.34 as things currently stand.
participants (4)
-
Gordan Bobic -
kazuhiko＠nexedi.com -
Kristian Nielsen -
Marko Mäkelä