[wp-trac] [WordPress Trac] #55911: Slow query because Admin Bar loads all options on all user's sites

[WordPress Trac]

#55911: Slow query because Admin Bar loads all options on all user's sites
------------------------------------+-----------------------------
 Reporter:  iandunn                 |      Owner:  (none)
     Type:  enhancement             |     Status:  new
 Priority:  normal                  |  Milestone:  Awaiting Review
Component:  Toolbar                 |    Version:  3.1
 Severity:  normal                  |   Keywords:
  Focuses:  multisite, performance  |
------------------------------------+-----------------------------
 ==== 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.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/55911>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform