[5.x]: Product save causes long-running queries leading to minutes-long hangs

[belleymambo]

What happened?

Description

On our staging/production environment, saving a Commerce product from the control panel can take many minutes, the CP appears to hang, and the front of the site is hard to reach while the save is in progress. In the queue we see jobs such as “Generating catalog pricing” and “Updating search indexes”, but profiling suggests the real bottleneck is long-running MySQL work, not those jobs completing slowly in isolation.

On local (DDEV) with the same project, product saves complete in a reasonable time.

We traced two concrete failure modes:

1. SQLSTATE[HY000]: General error: 2006 Server has gone away during elements/apply-draft. The stack shows the failure when acquiring a DB mutex / revision work (GET_LOCK via MysqlMutex) after Product::afterPropagate() → Revisions::createRevision(), consistent with the DB connection being dropped mid-request (e.g. server wait_timeout vs a very long apply/save).

2. SHOW FULL PROCESSLIST repeatedly shows the same SELECT on elements (columns such as id, canonicalId, fieldLayoutId, uid, …) stuck in Statistics for hundreds to thousands of seconds. Several such connections can pile up at once.

We have used KILL on stuck connections. Local DB is MySQL 8; server is MariaDB 10.6, so optimizer behavior may differ from dev even with identical code.

We have some fields coming from plugins in the products, so I tried to remove them, but still no luck. We don't have a lot of products and variants (~75 total).

I've seen others issues like this one but we still have the problem. Everything is up to date.

Steps to reproduce

1. Open the control panel on a remote environment.
2. Edit a Commerce product.
3. Click Save.
4. CP and site are now stuck.

Expected behavior

Product save should complete in a short, predictable time, without blocking the site for minutes, and without MySQL sessions stuck during normal saves.

Actual behavior

• Save take minutes; CP and site feel blocked for other users.
• DB may show multiple long-lived SELECT … elements … queries in Statistics, or 2006 Server has gone away during apply-draft / revision-related steps.

Craft CMS version

5.9.17

Craft Commerce version

5.6.1

PHP version

8.2.30

Operating system and version

Linux 4.18.0-553.89.1.lve.el8.x86_64

Database type and version

MariaDB 10.6.24

Image driver and version

GD 8.2.30

Installed plugins and versions

• CKEditor 5.3.1
• Neo 5.5.7
• SEO 5.0.0
• Moneris for Craft Commerce 1.0.0 (custom payment gateway by us)

[linear]

COM-546

[belleymambo]

We fixed our problem! The solution was to reduce the value of the MySQL settings optimizer_search_depth (by default its 62). Even if it was a server mis-configuration, I still think it should be investigated.

[lukeholder]

We have a high number of production installs without this issue, so we need to assume a mis-configuration for now.

If anyone comes across this issue in the future we can look to re-open.

Thanks.