Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-5449

4000x query performance regression in 8.0

    Details

    • Type: Bug
    • Status: Confirmation
    • Priority: High
    • Resolution: Unresolved
    • Affects Version/s: 8.0.13-4
    • Fix Version/s: None
    • Component/s: MyRocks
    • Environment:

      Docker percona/percona-server:5.7.25 vs percona/percona-server:8.0.13

      Reproducible on Mac and on an Azure VM.

      Description

       I am seeing a query pattern that triggers a ~4000x performance regression from 5.7.25 to 8.0.13 using MyRocks. For the attached slow query, 5.7 is scanning 254 rows in 10 ms, but 8.0 is scanning 8.7M rows in 43 seconds. This is for the exact same data, the exact same query and the exact same my.cnf settings.

      There are 182M rows in the table, which takes up 4 GB of disk space. Here is a link to a mysqldump of the data (1.5 GB) https://storage.googleapis.com/nozzle/linkmetrics_pinterest.sql.gz

      This is a single representative query that reliably triggers the slowdown, but we have seen similar performance across many servers and tables. What seems to be common across all of them is that:

      • it involves a range query
      • the larger the table, the fewer where clauses it takes to trigger the slowdown

      I have also included a shorter version of the query (normal-query.sql) that uses the same pattern, but runs at the same speed as 5.7.25.

      Here is the side by side explain output:

      (Percona 5.7.25) 76 rows in set (0.01 sec) | (Percona 8.0.13-4) 76 rows in set (43.40 sec)
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: linkmetrics_pinterest
         partitions: NULL
               type: range
      possible_keys: fk__linkmetrics_pinterest__urls__idx,linkmetrics_pinterest__urls__retrieved_at__idx
                key: linkmetrics_pinterest__urls__retrieved_at__idx
            key_len: 24
                ref: NULL
               rows: (Percona 5.7.25) 254 | (Percona 8.0.13-4) 8721618
           filtered: 100.00
              Extra: Using where; Using index
      

       

      How to reproduce inside the two docker images:

      # initialize the db for loading
      mysql -e "CREATE DATABASE domains"
      mysql -e "set global rocksdb_bulk_load=ON;"
      
      # install wget
      yum update -y
      yum install -y wget
      
      # download the mysqldump
      wget https://storage.googleapis.com/nozzle/linkmetrics_pinterest.sql.gz
      
      # import the test table
      zcat linkmetrics_pinterest.sql.gz | mysql domains
      
      # remove the data file to keep the Docker image smaller
      rm linkmetrics_pinterest.sql.gz
      
      # turn off bulk load
      mysql -e "set global rocksdb_bulk_load=OFF;"
      
      # clean up after yum
      yum clean all
      rm -rf /var/cache/yum
      

       

        Smart Checklist

          Attachments

          1. my (1).cnf
            7 kB
          2. normal-query.sql
            3 kB
          3. slow-query.sql
            7 kB

            Issue Links

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  derekperkins Derek Perkins
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  3 Start watching this issue

                  Dates

                  • Created:
                    Updated: