We have this problem after we migrated Dolibarr to a new Server in one of our Clients: “the page to create and invoice was extremelly slow.”
This was the Infrastructure:
Old Server:
- CentOS 7
- MySQL 5.6
- PHP 5.6
- Apache 2.4
- Dolibarr 10
New Server
- CentOS 8
- MySQL 5.7
- PHP 7
- APache 2.4
- Dolibarr 10
Both Server had more than 32GB RAM, very powerfull processors and a lot of avaliable disk space.
We migrated the platform and everything looked correct with the exception of the creation of a new invioce (Financial/New Invoice), that action took about 30 seconds to show the page to create the draft.
We run the same action (Financial/New Invoice) in the old Server and it run in about 0.20 seconds.
Then, we started to investigate what happened, because the requeriments of Dolibarr were the correct ones. And, we found a bug with the MySQL version 5.7:
Here is the link of the bug: https://bugs.mysql.com/bug.php?id=87164.
Looking for a workarround to avoid the migration to MySQL 8.0 we found this post https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_optimizer_switch where the problem was explained to be in the “optimizer_switch” set up. We tested each variable inside the optimizer and changed one by one to “OFF” checking the speed of the query untill we discovered that the parameter that provoqued the slow in the query was “block_nested_loop”
In the following link there is an explanation about this behavior: https://dev.mysql.com/doc/refman/5.6/en/bnl-bka-optimization.html (Extract from the previous URL “Some cases involving the combination of one or more subqueries with one or more left joins, particularly those returning many rows, may use BNL even though it is not ideal in such instances. This is a known issue which is fixed in MySQL 8.0. If upgrading MySQL is not immediately feasible for you, you may wish to disable BNL in the meantime by setting optimizer_switch=‘block_nested_loop=off’, to let the optimizer choose a better plan, using one or more index hints (see Section 8.9.3, “Index Hints”), or both, to improve the performance of such queries.”)
We hope we can help someone with this same headache!
Regards!
Level IT Team!
