Magento 2 slow reindex using MariaDB

[nkajic]

Version of Warden

0.15.0

Operating System and Installation Method

MacOS 15.3

Image

Magento 2 templates

Describe the Bug

Hi all,

Magento 2 configuration for MariaDB does not follow Adobe recommendations.
Specially visible on large projects.

Please see
https://experienceleague.adobe.com/en/docs/commerce-cloud-service/user-guide/configure/service/mysql
under "Configure MySQL database".

Please add following configuration for MariaDB
optimizer_switch: "rowid_filter=off"
optimizer_use_condition_selectivity: 1

Not setting those variables result in x10 or x20 x100 order of magnitude in reindexing time for Category products alone.
Also seen unproperly configured on many production webshops

More context:
Same problem occurred on different project
Mark Shust's Docker Configuration for Magento
Fix possible slow reindexing on MariaDB no. 705
markshust/docker-magento#705

To Reproduce

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

Expected Behavior

No response

Additional context

No response

[bap14]

I think this is personally just an adjustment to the default environment yaml file to add these to the mysql command. Enabling this at the image level for everyone may not be appropriate, and so I'd err on the side of caution to only enable this for environments that it makes sense for, as well as more easily allowing for custom environments to add to the same.

If we do it this way, we could create new global ~/.warden/env variables that control these items, and then update the environment .env template files for the same. This would mean more manual work during upgrades (or a way to "diff" the template env with the existing env and add any missing variables with the default value).

[nkajic]

Seems like adding

services:
  db:
    command:
      --max_allowed_packet=128M
      --optimizer_use_condition_selectivity=1
      --optimizer_switch="rowid_filter=off"
      --log_bin_trust_function_creators=1

to project's .warden/warden-env.ymlhelps, but it is not a complete fix.

Without this configuration, reindexing can take 100 times longer. Not including it or mentioning it in the documentation leaves both Warden and Magento projects significantly impaired, driving developers away from both projects simultaneously.

[bap14]

The optimizer switch is only relevant to MariaDB as MySQL doesn't have the rowid_filter value as a valid optimizer switch (see: https://dev.mysql.com/doc/refman/8.4/en/switchable-optimizations.html ). I'm still of the opinion that this should be a "magento2" variant of the mariadb image, or customized via the docker-compose environment file.