Hi Kristian, On Sun, Jul 9, 2023 at 6:38 PM Kristian Nielsen <knielsen@knielsen-hq.org> wrote:
Hi Marko,
I'd like to ask your opinion/review of the below patch for MDEV-31482:
https://github.com/MariaDB/server/commit/dbe5c20b755b87f67d87990c3baabc86666... https://jira.mariadb.org/browse/MDEV-31482
Sorry for the delay. In July, I was mostly on vacation.
As an optimisation, auto-increment lock waits where _not_ reported, but this turns out to be a bug. This patch just removes the exception so that such waits are reported like other waits.
Even if the logic had been right, it could be a good idea to avoid conditional branches in modern superscalar CPUs. https://www.intel.com/content/dam/support/us/en/documents/processors/mitigat... A fairly recent example is https://jira.mariadb.org/browse/MDEV-30567 where an attempt to create "more readable" code had unnecessarily replaced simple bitwise arithmetics with conditional branches.
My only concern is the possible performance impact of this. I'm hoping it will not be very big, because in many cases the auto-increment lock is not taken for replicated transactions? My understanding is:
- It is not taken for INSERT INTO t VALUES (...) (and maybe not for multi-row "INSERT ... VALUES (...), (...), ..." ?)
- SELECT ... INSERT is not safe for statement-based replication, so will be logged as row in MIXED mode and give a warning in STATEMENT.
My understanding is that the auto-increment locks are only needed for statement-based replication and nothing else. Unlike other InnoDB locks, they are not held until transaction commit, but released at the end of each statement. Here are some related tickets: https://jira.mariadb.org/browse/MDEV-19577 Replication does not work with innodb_autoinc_lock_mode=2 https://jira.mariadb.org/browse/MDEV-27844 Set innodb_autoinc_lock_mode=2 by default, and deprecate it The purpose of innodb_autoinc_lock_mode=2 is to disable auto-increment locking. I would like to remove the auto-increment locks altogether. I understood that we could do that in statement-based replication if the binlog event group was extended to the individual AUTO_INCREMENT values that were assigned for the rows.
But I wanted to hear you if you know any case and/or MariaDB version where a significant amount of auto-increment lock waits could be expected in parallel replication? And if so, maybe this fix should be pushed only to later MariaDB versions?
I hardly know the replication in MySQL or MariaDB, and I have not been involved in discussions on performance tests that would involve replication. I think that we may need to improve on that front. Since you asked, from my point of view, it might make sense to skip this change in 10.4 and 10.5. There were some substantial changes to InnoDB locking in 10.6, and nothing since then. Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB plc