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'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

              marko Marko Mäkelä
              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.