Hello, I am having difficulties optimizing a production server with MariaDb 10.6.14.

I have a development server with a basic configuration and a production server with a very correct configuration. The development server performs better on most requests compared to our production server.

Configuration details:

Development server:
Processor : Intel(R) Xeon(R) W-1250P CPU @ 4.10GHz (12 core(s))
RAM: 32G
Plesk Obsidian v18.0.54_build1800230807.10 is on the machine.

Production server:
Processeur : Intel(R) Xeon(R) Gold 6312U CPU @ 2.40GHz (48 core(s))
RAM: 256G
Plesk Obsidian v18.0.54_build1800230807.10 is on the machine.

For the installation of MariaDb 10.6.14 I added the repository : https://mirrors.ircam.fr/pub/mariadb/repo/10.6/debian

The database on the development server and the production server is the same. The development server makes backups of the production site. This is a website under Prestashop.

Configuration of the 2 servers:
I attach the 2 configurations. They are equals.

For example the following query:

SELECT SQL_NO_CACHE h.`name` as hook, m.`id_module`, h.`id_hook`, m.`name` as module, h.`live_edit`

FROM `ps_module` m

INNER JOIN ps_module_shop module_shop

ON (module_shop.id_module = m.id_module AND module_shop.id_shop = 1 AND module_shop.enable_device & 1)

INNER JOIN `ps_module_shop` ms ON ms.`id_module` = m.`id_module`

INNER JOIN `ps_hook_module` hm ON hm.`id_module` = m.`id_module`

INNER JOIN `ps_hook` h ON hm.`id_hook` = h.`id_hook`

LEFT JOIN `ps_module_group` `mg` ON mg.`id_module` = m.`id_module`

WHERE (h.name != "displayPayment") AND (hm.id_shop = 1) AND (mg.id_shop = 1 AND  mg.`id_group` IN (1))

GROUP BY hm.id_hook, hm.id_module

ORDER BY hm.`position`

Takes 3.9ms on the development server and 22.8ms on the production server.

Version information:
Development:
Server version: 10.6.14-MariaDB-1:10.6.14+maria~ubu2204 mariadb.org binary distribution
Production:
Server version: 10.6.14-MariaDB-1:10.6.14+maria~deb11 mariadb.org binary distribution

I don't know if all the necessary information is here. I can provide additional information if needed.

Thank you,

Sincerely,
Mathieu Etcheverria,