Skip to:

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

PS performing worse than MySQL for a single query read-only workload

Description

Summary

A CROSS JOIN SELECT on two tables filled with composite index is performing better on MySQL Community 8.0.33 than on Percona Server 8.0.33 by an estimate of 5-8%  from a few rounds of tests using the same database server and dataset and simply switching the binaries between PS and MySQL.

Test environment

Tested using two cloud instances, a 16vCPU/16G one for the database and an 8vCPU/30G for the application server running the workload, both running CentOS.

The my.cnf was configured with:

 

Installing PS from the Percona repository:

When it was time to test with MySQL, I removed the PS binaries and installed MySQL  using their repository:

Creating target database test user:

 

Creating target database:

 

Populating the database, starting by creating a first row:

Subsequent rows created with this exponential INSERT:

Cloning this table to use as the joined table:

Running the tests

Installed Sysbench from their Github repository and created this custom script with the CROSS JOIN query:

joinit.lua

Executed Sysench from the application server using the following command:

 

 

 

Environment

None

Activity

Przemyslaw Skibinski April 8, 2024 at 11:50 AM
Edited

I've build "mysql-8.0.36" and "release-8.0.36-28" tags from sources on our "smblade04" server.
Then I populated database with your scripts (the output DB size was 48GB):

Sysbench (--threads=32 --time=900) with Percona Server 8.0.36-28 returns:

Sysbench (--threads=32 --time=900) with MySQL 8.0.36 returns:

The difference is less than 0.1%.
I expect that the issue was caused by AWS CPU allocation/throttling or GP2/GP3 storage.

Przemyslaw Skibinski November 10, 2023 at 10:19 AM

PHASE 1: Making subsequent perf test runs with the same parameters stable
I was working on this for about 2 weeks with Hrvoje. I improved scripts and config files at https://github.com/inikep/percona-qa/commits/performance-test
I implemented all stability improvements described at
https://www.notion.so/percona/Performance-benchmarking-of-MySQL-and-Percona-Server-5ceebb7145ea49aea922ab6818b590df
On my local machine last tests were pretty stable. The diffs between subsequent runs were lower than 1%.
Hrvoje has to confirm the same stability on his servers.

Fernando Laudares Carmagos October 19, 2023 at 2:04 PM

Hi ,

  1. The cloud might not be ideal, but this was not a one-off; this effort was the result of trying to reproduce a similar issue found on a production server.

  2. I put all the time I had to create this reproducible procedure. If you guys find you cannot reproduce my results on bare metal, please let me know.

Przemyslaw Skibinski October 19, 2023 at 10:33 AM

1. AWS is not the best environment for performance testing. Recently I did perf testing with MTR runs with 8.0.34 on AWS (using the same prebuild AMI image) and one instance was failing with 2-3 MTR tests and the second was fine. Repeated it two times with the same result. It had huge influence on perf times.
2. Can you tests different workloads like oltp_read_write.lua and oltp_read_only.lua ?

Steve Hoffman October 17, 2023 at 2:44 PM

I'm going to assign this to to prioritize this issue for investigation.  I don't necessarily expect a fix (unless it's obvious and easy) but more do we understand why this specific scenario performs worse compared to upstream.  

Cannot Reproduce

Details

Assignee

Reporter

Needs QA

Yes

Affects versions

Priority

Smart Checklist

Created October 5, 2023 at 2:02 PM
Updated July 22, 2024 at 6:54 AM
Resolved July 22, 2024 at 6:54 AM