Bug #13079
Timeouts occur when trying to access *Manage>Physical Storage* on Ubuntu 18.04
Status: | Verified | Start date: | 05/30/2019 | |
---|---|---|---|---|
Priority: | High | Due date: | ||
Assignee: | - | % Done: | 0% | |
Category: | Installation | |||
Target version: | Release 2.5.1 | |||
Google Code Legacy ID: | Tested version: | 2.5 | ||
Sponsored: | No | Requires documentation: |
Description
Timeouts when trying to access Manage>Physical storage are due to a MYSQL 5.7 optimization setting that defaults to ON when running on Ubuntu 18.04
There is a MySQL bug report here that seems to be this exact issue:
https://bugs.mysql.com/bug.php?id=69721
Test query used to trigger behaviour in MySQL console:
select * from physical_object LEFT JOIN physical_object_i18n source ON (physical_object.ID=source.id AND source.CULTURE=physical_object.SOURCE_CULTURE AND source.CULTURE <> 'en') ;
SET SESSION optimizer_switch='block_nested_loop=off'; <-- fixes the issue
Setting this to 'off' will allow access to the manage physical storage pages - but this configuration change will be lost if MySQL is restarted.
It seems to be directly related to the 'left join' combined with the compound ON clause. This test query is based on lib/QubitCultureFallback.class.php criteria added in the physical storage browse query. I expect this slow behaviour will be encountered anywhere we are using QubitCultureFallback.class.php.
History
#1 Updated by David Hume almost 3 years ago
- Assignee set to Santiago Collazo
#3 Updated by Dan Gillean almost 3 years ago
- Category set to Installation
- Status changed from New to QA/Review
2.5 docs updated in the following commit: https://github.com/artefactual/atom-docs/commit/f48b7675fbb0f1c6a071bc6935701ef9397ee69b
#4 Updated by David Hume almost 3 years ago
Worked with Dan on above,
Pending deploy-private modifications for bionic and internal 2.5 ansible site configurations also including optimizer_switch='block_nested_loop=off'
setting, so I believe all Percona 5.7 bases now covered.
#5 Updated by Dan Gillean almost 3 years ago
- Status changed from QA/Review to Verified
- Assignee deleted (
Santiago Collazo)