This is an obfuscated issue -- apparently based on the circuitous path we had to take to arrive here:
We encountered a message from Query Monitor indicating that WPML was running several hundred queries that loaded all the options for all sites on a WP 9.9.9 multisite network on every page load rather than loading only the options for the individual site it was active on in a multisite network.
We reported this to WPML:
Why does an individual site on a multisite network need to autoload options for every site on the multisite network instead of just for the individual subsite?
One of our networks has over 60 sites on it, which means every page load is autoloading options for 59 sites that have nothing to do with the one subsite.
This seems very inefficient.
Our developer reports:
"I took a look at the query monitor on the homepage and searching through it, found that the plugin sitepress-multilingual-cms autoloads the wp_options table for each site on the network which is roughly 60 queries and 10k+ rows.
If all options tables are loaded all the time (not sure if this only affects admin users, will look into this later) then tidying them up could have a big impact on performance. There is still around 5.5MB of data loaded on each request just from this wp_options table. Currently, 60 similar tables are being loaded (again, not sure yet if it's just as admin or for all visitors) which even if they are only 1MB each is at least 60MB of memory and unnecessary processing."
Is this the expected behavior on a multisite network?
“This news is surprising/unexpected and our senior supporters would like more information on the query used to make this observation/claim.
Normally, it does not sound like something WPML would/should do as each site is independent and does not need options from any other site. As you can see here
https://core.trac.wordpress.org/browser/tags/5.0.3/src/wp-includes/option.php#L197 query loads data from current option site option table only.
If you can provide a screenshot of the queries that would be very helpful.”
We had our developer dig a bit deeper and found that:
“…while it’s reported by Query Monitor as the sitepress-multilingual-cms being responsible (see attached screenshot); following it back the actual culprit which is the WPMUDEV’s Ultimate Branding plugin’s maintenance feature.
From looking at the ub_maintenance class, it’s instantiated so it can see if it’s enabled or not, but doing this requires it to load its options triggering the unnecessary call to ub_maintenance::get_sites() to populate the settings page section “Sites”. When I commented the $options[‘sites’] array out the calls were no longer made on the frontend, but that part of the settings disappeared as expected.
It seems like wrapping this array with a condition that checks it is on the maintenance settings page would solve the original issue in the Ultimate Branding plugin of loading unnecessary options for all network sites on every page load on the front end.”
We reported to Query monitor that it identified the incorrect plugin for the issue it reported:
"So Query Monitor has helped us find a problem, but it did so by identifying the wrong culprit plugin. I just wanted you to know in the event an update might be able to improve the accuracy of Query Monitor reports even more."
My question to WPMUDEV is if we can expect an update to Ultimate Branding that prevents it from autoloading all options for all sites on network on every page load?
This issue also seems to be causing us hosting issues at Page.ly