Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

4000x query performance regression in 8.0

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:

 

How to reproduce inside the two docker images:

 

Environment

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

Reproducible on Mac and on an Azure VM.

Attachments

3

Smart Checklist

Activity

Show:

Julia Vural March 4, 2025 at 9:15 PM

It appears that this issue is no longer being worked on, so we are closing it for housekeeping purposes. If you believe the issue still exists, please open a new ticket after confirming it's present in the latest release.

Sveta Smirnova August 10, 2021 at 12:27 PM

Hi !

I get "Access Denied" error when try to access the dump file:

Could you please allow access?

Jira Bot August 5, 2021 at 11:57 AM

Hello ,
I'm jira-bot, Percona's automated helper script. Your bug report is important
to us but we've been unable to reproduce it, and asked you for more
information. If we haven't heard from you on this in 3 more weeks, the issue
will be automatically closed.

Lalit Choudhary July 7, 2021 at 11:04 AM

Hi  

Thank you for the report.

Can check the same with the latest 8.0 version and lets us know if see any change.

 

Derek Perkins January 8, 2020 at 6:10 AM

We are definitely seeing this same behavior in InnoDB, on every PS version from 8.0.13 to 8.0.18, so it appears to be engine agnostic.

Won't Do

Details

Assignee

Reporter

Labels

Components

Affects versions

Priority

Smart Checklist

Created February 28, 2019 at 6:10 AM
Updated March 4, 2025 at 9:15 PM
Resolved March 4, 2025 at 9:15 PM
Configure