WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 4 years ago

Last modified 3 years ago

#35096 closed defect (bug) (wontfix)

Every option call results in a database query on multisite

Reported by: buley Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: Options, Meta APIs Keywords:
Focuses: Cc:
PR Number:

Description

get_option() (among other functions) calls wp_load_alloptions():
https://core.trac.wordpress.org/browser/tags/4.4/src/wp-includes/option.php#L27

wp_load_alloptions() is never cached on multisite: https://core.trac.wordpress.org/browser/tags/4.4/src/wp-includes/option.php#L174

This combines to result in many dozens unnecessary calls to the database on multisite installs when using options, for example 1 database query per get_avatar(). options should be cached, or at the very least be filterable such that I can cache them myself.

Attachments (2)

Screen Shot 2015-12-14 at 9.41.12 PM.png (185.9 KB) - added by buley 4 years ago.
alloptions_is_slow
Screen Shot 2015-12-14 at 9.49.19 PM.png (43.3 KB) - added by buley 4 years ago.
lots_o_calls

Download all attachments as: .zip

Change History (12)

@buley
4 years ago

alloptions_is_slow

#1 @SergeyBiryukov
4 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to duplicate
  • Status changed from new to closed

Duplicate of #20021.

#2 @buley
4 years ago

  • Resolution duplicate deleted
  • Status changed from closed to reopened

Yes reported as part of #20021, which was closed 4 years ago. This is definitely a bug. Just look at the code.

Last edited 4 years ago by SergeyBiryukov (previous) (diff)

#3 @SergeyBiryukov
4 years ago

  • Focuses multisite added
  • Milestone set to Awaiting Review

From comment:1:ticket:20021:

On a MultiSite installation, wp_load_alloptions() does not cache the results in the object cache

Only when is_multisite() AND defined( 'WP_INSTALLING' ) are the options not cached. On a local multisite installation the number of queries didn't increase when calling wp_load_alloptions() or get_option( 'rewrite_rules' ) multiple times.

It seems like something else must be going on to cause your issue.

I could not reproduce either, get_option( 'rewrite_rules' ) or get_avatar() does not result in additional queries on my Multisite install.

#4 @buley
4 years ago

The conditions are not obvious. As noted in the other ticket they are also hard to script because of the WP_INSTALLING flag. I believe it has to do with the size of options object growing beyond the limits of Object-Cache backends. When the set fails, there's no fallback to a memory cache. The default object size of e.g. memcached is 1M and its very common for plugins to accidentally add autoloading options (https://core.trac.wordpress.org/ticket/18244) so you could see the options table growing beyond this size in many situations on the large multisite installs affected by this bug.

Version 0, edited 4 years ago by buley (next)

#5 @danielbachhuber
4 years ago

@buley This is on a site using Memcached as a persistent object cache backend, right?

If so, when you exceed your bucket size, WordPress will repeatedly attempt to prime your alloptions cache because the data never makes it into Memcached. When you use an object cache drop-in, the behavior of the object cache becomes the responsibility of the drop-in, not WordPress. Most drop-ins don't have fail back behavior as the WP Redis drop-in does.

#6 @buley
4 years ago

That's the case, thanks for the clarity. In the case of object-cache dropin failure, which seems easy and I know to be common, it would seem prudent for core not to get this alloptions amnesia. It's seems wasteful and unnecessary to run 250 of the same query when no options have changed in the milliseconds between them. It's a failure case, and the onus is on the object-cache.php drop in not to fail, but WordPress fails spectacularly when it happens and I believe this is unnecessary.

I should have remembered my lesson when we lost weeks to this at Forbes (the consultants came out very rich but didn't find the cause either) but I just ran into it again and lost another week. I'm no amateur - and there turned out to be nothing wrong with my code - just that a bunch of WooCommerce plugins started autoloading and finally pushed me beyond the 1mb limit. It's horrible, horrible problem to debug when it happens.

#7 @danielbachhuber
4 years ago

  • Focuses multisite removed
  • Milestone Awaiting Review deleted
  • Resolution set to wontfix
  • Status changed from reopened to closed
  • Version 4.4 deleted

it would seem prudent for core not to get this alloptions amnesia. It's seems wasteful and unnecessary to run 250 of the same query when no options have changed in the milliseconds between them.

Because the object cache drop-in wholly replaces core's internal object cache, core has no knowledge of object cache failure when the drop-in fails. It sucks, but it's the tough reality of using a drop-in replacement.

I'd encourage you to open a ticket with the maintainer of your object cache drop-in to discuss the possibility of having an internal fail back procedure. Feel free to cc me to loop me into the conversation.

#8 @buley
4 years ago

When caching failures cause hundreds of unnecessary queries, and when WordPress is unable to make itself aware of cache failures in order to fix itself, the caching model is fundamentally broken and needs to be fixed. Something as simple as a DNS problem becomes a single point of failure. It's 2016 - how about a success callback?

Last edited 4 years ago by buley (previous) (diff)

#9 @danielbachhuber
4 years ago

When caching failures cause hundreds of unnecessary queries, and when WordPress is unable to make itself aware of cache failures in order to fix itself, the caching model is fundamentally broken and needs to be fixed.

Sure — but the onus is on the object cache drop-in, not WordPress core. Drop-ins are drop-in replacements for WordPress core functionality, and are super-user features (aka not for the faint of heart).

#10 @schrapel
3 years ago

@danielbachhuber this sounds like exactly what is happening. It is made worse that our database locks up when alloptions get queried 250 times on a page by each user and we have a decent amount of concurrent users.

The database result then returns null and puts values in notoptions that should not be there such as home, siteurl, user+roles. This puts the site in a redirect loop and disables the ability for users to log in.

Note: See TracTickets for help on using tickets.