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

Purge-related index page merge success rate is low

    XMLWordPrintable

Details

    Description

      Each time the purge thread is deleting one record, it drops optimistic deletion if the size of the data in the page is below the merge threshold.
      It then falls back to pessimistic deletion.
      After MDEV-29835, this is more expensive as the index would be exclusively locked more often, if not always (MDEV-38814), in such cases.
      The attached test tries inserts 10k entries in a table with a single INT NOT NULL primary key, and deletes the upper half, then the remaining even ids, then in random order the remaining rows. It then performs the same operations on a table with also a second column of the same type, which is covered by a secondary index.
      The same pattern is seen in all operations.
      Results in 10.4 are not stable, but in the more recent server versions seems stable.
      By attempting merge as soon as the current page is not filled enough, there are several cases in which no sibling page is eligible for merge. In such cases, the pessimistic fallback fails in performing the merge, and a subsequent optimistic deletion on the same page would still fail, and if no change occurred in sibling pages, also the pessimistic fallback would still fail.
      This comment from marko in MDEV-39272 suggested that such merge attempts could be re-organized to be performed once per batch.
      In any case, it seems that, to lessen the impact of MDEV-29835 on index maintenance during purge, some restructuring of the maintenance operations in such threads should be performed, to reduce the tested merge failure rate and therefore reduce the pessimistic delete fallbacks and the index lock upgrades during purge.

      Attachments

        1. index_maintenance_purge.result
          2 kB
          Alessandro Vetere
        2. index_maintenance_purge.test
          11 kB
          Alessandro Vetere

        Issue Links

          Activity

            People

              alessandro.vetere Alessandro Vetere
              alessandro.vetere Alessandro Vetere
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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