Slow query because Admin Bar loads all options on all user's sites

[iandunn]

Problem

The Admin Bar displays a list of sites that the user has a role on. To get that data, it calls get_blogs_of_user() when it initializes.

That's not a big deal on a single site, because everything is already cached in memory. On Multisite, though, it can result in looping through all of the sites that a user has a role on, and calling switch_to_blog(). That calls wp_load_alloptions() on each site, which can be a slow database request.

The issue gets worse the more sites the user has a role on, and the more data stored in the wp_options table of those sites.

It looks like this dates back to the Admin Bar's creation in r15671.

Call Stack
#	Time	Memory	Function	Location
1	0.0001	362896	{main}( )	.../index.php:0
2	0.0001	363232	require( '/wp-blog-header.php )	.../index.php:17
3	1.8896	17408184	require_once( '/wp-includes/template-loader.php )	.../wp-blog-header.php:19
4	1.8896	17408184	do_action( $hook_name = 'template_redirect' )	.../template-loader.php:13
5	1.8896	17408560	WP_Hook->do_action( $args = [0 => ''] )	.../plugin.php:476
6	1.8896	17408560	WP_Hook->apply_filters( $value = '', $args = [0 => ''] )	.../class-wp-hook.php:331
7	1.8896	17409720	_wp_admin_bar_init( '' )	.../class-wp-hook.php:307
8	1.8897	17409864	WP_Admin_Bar->initialize( )	.../admin-bar.php:49
9	1.8897	17409904	get_blogs_of_user( $user_id = 33690, $all = ??? )	.../class-wp-admin-bar.php:47
10	2.3312	15098272	WP_Site->__get( $key = 'blogname' )	.../user.php:994
11	2.3312	15098272	WP_Site->get_details( )	.../class-wp-site.php:237
12	2.3312	15098272	switch_to_blog( $new_blog_id = '206', $deprecated = ??? )	.../class-wp-site.php:323
13	2.3313	15098216	do_action( $hook_name = 'switch_blog', ...$arg = variadic('206', 1366, 'switch') )	.../ms-blogs.php:563
14	2.3313	15098592	WP_Hook->do_action( $args = [0 => '206', 1 => 1366, 2 => 'switch'] )	.../plugin.php:476
15	2.3313	15098592	WP_Hook->apply_filters( $value = '', $args = [0 => '206', 1 => 1366, 2 => 'switch'] )	.../class-wp-hook.php:331
16	2.3313	15098968	wp_switch_roles_and_user( $new_site_id = '206', $old_site_id = 1366 )	.../class-wp-hook.php:309
17	2.3313	15098968	WP_Roles->for_site( $site_id = '206' )	.../ms-blogs.php:659
18	2.3313	15098968	WP_Roles->get_roles_data( )	.../class-wp-roles.php:328
19	2.3314	15098968	get_option( $option = 'wc_206_user_roles', $default = [] )	.../class-wp-roles.php:370
20	2.3314	15098968	wp_load_alloptions( $force_cache = ??? )	.../option.php:167

Potential Solution

At first glance, it seems like wp_admin_bar_my_sites_menu() is the only caller (in Core), and that it only needs a few pieces of data (site name, url, etc). Those could maybe be cached in a transient rather than doing a switch_to_blog() loop on every request.

There's also some duplicated functionality between WP_Admin_Bar and wp_admin_bar_my_sites_menu(), where the latter does a 2nd switch_to_blog() loop over those same sites. That would need to be cached too to avoid the same problem.

It may be better to refactor them so that the class isn't doing anything on init, and instead waits for a caller to request the data, and then it provides all the data the caller needs, to avoid the caller having to do its own loop.

[SergeyBiryukov]

Related: #31746, #32472.

[iandunn]

Duplicate of #31746.

Ah, I missed those when searching, thanks!

It looks like https://core.trac.wordpress.org/ticket/31746#comment:20 would probably solve this, so I'll go ahead and close as a duplicate. If the solution doesn't work here, then we can always reopen.

[iandunn]

Er, I spoke too soon. Optimizing the meta query in get_blogs_of_user() would help this indirectly, but I don't think it'd prevent the switch_to_blog() > ... > wp_load_alloptions() loop . That's triggered by get_blogs_of_user() > $site->id > WP_Site::get_details().

There's also the 2nd switch_to_blog() loop triggered by wp_admin_bar_my_sites_menu(), which would just re-introduce the problem even if get_blogs_of_user() had fast meta queries and didn't indirectly call switch_to_blog().

So I think there are 3 things that need to happen:

1. Optimize the meta query in get_blogs_of_user() - #31746
2. Optimize the $site->foo calls in get_blogs_of_user() - #31746 or this ticket?
3. Optimize the switch_to_blog() calls in wp_admin_bar_my_sites_menu() - this ticket

cc @nerrad

[desrosj]

Re-adding the Awaiting Review milestone.