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

Performance regressions with utf8mb4 vs utf8 in WordPress

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.0.21-galera
    • N/A
    • Character Sets
    • None

    Description

      When switching tables from utf8 to utf8mb4, we're seeing fairly severe performance regressions - queries that used to run in microseconds, now taking 20 seconds to run.

      We've run some tests with query and index variations, or there's a summary here.

      For a direct comparison, see:
      utf8: https://travis-ci.org/tollmanz/utf8mb4-query-time/jobs/82864656
      utf8mb4: https://travis-ci.org/tollmanz/utf8mb4-query-time/jobs/82864660

      Here's the WordPress tracking ticket. Comment #2 notes that reducing the meta_key column length to 191 (to match the length of the prefix index) fixes the performance issue, but the utf8 character set doesn't have the same problem.

      The same behaviour occurs in MySQL, as well.

      Attachments

        Issue Links

          is part of

          Task - A task that needs to be done. MDEV-32113 utf8mb3_key_col=utf8mb4_value cannot be used for ref access

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-16413 test performance of distinct range queries

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-19123 Change default charset from latin1 to utf8mb4

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Task - A task that needs to be done. MDEV-32113 utf8mb3_key_col=utf8mb4_value cannot be used for ref access

          • Major - Major loss of function.
          • Closed

          Activity

            People

              psergei Sergei Petrunia
              pento Gary Pendergast
              Votes:
              8 Vote for this issue
              Watchers:
              12 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.