craftcms / commerce

Fully integrated ecommerce for Craft CMS.
https://craftcms.com/commerce
Other
226 stars 170 forks source link

[5.x]: 5.2.2 Variant entry field hangs up MySQL Server #3758

Open apitel opened 2 weeks ago

apitel commented 2 weeks ago

What happened?

Description

After upgrading to Craft Commerce 5.2.2 (and subsequently 5.2.2.1), we are noticing a major slowdown in Variant Queries, bringing MySQL to its knees. We have a variant select field in an entry, that, upon clicking, sends MySQL CPU usage to 100% continuously.

Issue does not occur with any other area of Craft and did not occur before upgrading to 5.2.2 (other than ~ 3x variant query slowdown from Craft 4 previously reported). Issue does not occur when adding an item to an order in CP.

Steps to reproduce

  1. Add a variant select field to an entry type
  2. Create a new entry of that type and select variant using variant select field

Expected behavior

  1. List variants on the order of seconds

Actual behavior

  1. MySQL usage goes to 100%+ and stays at 100% unless MySQL server is restarted (waited 10+ minutes) Image

Craft CMS version

Craft Pro 5.4.10.1

Craft Commerce version

5.2.2.1

PHP version

8.3.13

Operating system and version

Linux 5.15.0-124-generic

Database type and version

MySQL 8.0.39

Image driver and version

GD 8.3.13

Installed plugins and versions

-

linear[bot] commented 2 weeks ago

PT-2307 [5.x]: 5.2.2 Variant query slowness, unusable

apitel commented 2 weeks ago

Issue has been verified on multiple development, staging and production environments and there is nothing in the logs.

angrybrad commented 1 week ago

@apitel a few questions:

Assuming you still have the issue after that, and it's reproducible in different environments, can you send a database dump, your composer.json/lock files, and some steps to reproduce what you see to support@craftcms.com? We can dig in further from there.

apitel commented 1 week ago

Below is the output of SELECT VERSION(); on our production server: Image Locally, our team is running DDEV.

I can confirm the charsets and collations are set consistently across the table as utf8mb4 and utf8mb4_unicode_ci. I reran php craft db/convert-charset utf8mb4 utf8mb4_unicode_ci for good measure.

I ran mysqlcheck --optimize on our production database. The output was Table does not support optimize, doing recreate + analyze instead for each table, but it did complete successfully.

The issue described still persists. I will email the requested items to support@. Just to reiterate, this issue was new after upgrading to Commerce 5.2.2.

white-lukas commented 1 week ago

We are also seeing a serious spike in database cpu after deploying a commerce 5.2.2 update Image

nfourtythree commented 1 week ago

Hi All

Thank you for all the information provided and your patience on this issue.

We believe we have identified the cause of this issue and have pushed up a fix which will be included in the next release of Commerce.

To get this early, change your craftcms/commerce requirement in your project's composer.json to:

"require": {
  "craftcms/commerce": "5.x-dev as 5.2.2.1",
  "...": "..."
}

Then run composer update.

If you have a chance to try this out before the release and let us know if it solves the issue that would be fantastic.

Thanks

apitel commented 1 week ago

We reinstalled the vendor directory with this new dev version of Craft Commerce. All other plugins have been disabled.

We did not see any resolution. We are still experiencing the same issue as reported.

apitel commented 1 week ago

If we move with the same latest db for 5.2.2.1, but use old commerce version "5.1.3" then the field works.

We then stepped through the various versions to see when the problem was introduced using a 5.x-dev database. Our results are as follow:

5.1.3 :white_check_mark: 5.1.4 :white_check_mark: 5.2.0 :white_check_mark: 5.2.1 :white_check_mark: 5.2.2 :red_circle: 5.2.2.1 :red_circle: 5.x-dev :red_circle:

nfourtythree commented 1 week ago

Hi @apitel

We have pushed further updates for this issue into the just released Commerce 5.2.4

Could you please try updating to this version and let us know if it makes any difference to your project? We have had reports from others that this update has made a significant improvement.

Thanks!

apitel commented 1 week ago

We've deployed the latest version of Craft Commerce 5.2.4 to local development (DDEV), staging, and production environments. The issue remains the same, which is when selecting the Variant field in an entry, after 2 minutes of trying to load it results in a Server Error and times out, MySQL Server runs continuously at 100% CPU and the only resolution is to restart MySQL service; there is nothing I can see in the logs. This issue was repeated in all environments.

Image

I think this issue is distinct from the variant performance issues and was introduced in 5.2.2.