Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28445

Secondary index locking invokes costly trx_sys.get_min_trx_id()

    XMLWordPrintable

    Details

      Description

      As noted in MDEV-21423, InnoDB is unnecessarily performing full traversal of the lock-free trx_sys.rw_trx_hash when enforcing locks on secondary indexes.

      Apart from some recovery code, there are two sources of trx_sys.rw_trx_hash traversal, which will be affected by MDEV-20630:

      1. cloning a read view
        • in trx_sys.snapshot_ids() executed via ReadViewBase::snapshot() and ReadView::open()
        • in trx_sys.clone_oldest_view() executed via purge_sys.clone_oldest_view()
      2. determining the minimum transaction identifier in any read view

      In MySQL 8.0.29, trx_rw_min_trx_id() was partly removed as redundant and partly replaced with a simpler operation.

      I created something similar, replacing trx_sys.get_min_trx_id() with trx_sys.find_same_or_older(). I expect it to improve performance of operations that involve secondary indexes. The new hash table traversal trx_sys.find_same_or_older() employs short circuit evaluation, in a strictly read-only operation (not even acquiring any mutex).

      This change did not affect performance (for the better or worse) in Axel Schwenke's benchmark test battery, so I do not think that we can claim that this cleanup fixes MDEV-21423. Nevertheless, I think that the cleanup is useful and it could benefit some other types workloads, possibly with more conflicts between secondary index record locks.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.