Skip to:

Opened 9 years ago

Closed 9 years ago

#5950 closed defect (bug) (fixed)

Editing Pages / Posts in wp-admin creates 50,000 SQL Queries

Reported by: wcvendors's profile wcvendors Owned by: djpaul's profile djpaul
Milestone: 2.2 Priority: normal
Severity: normal Version: 2.1
Component: Activity Keywords: has-patch


Crazy, I know! :)

My BuddyPress site has around 49,000 members. When I go to edit a page, or a post, as admin, there are 49,377 queries executing according to Query Monitor (a free WP Plugin).

The queries being loaded are one for every single member on the site.

Time: 0.44 ms
Query: SELECT id, user_id, field_id, value, last_updated FROM wp_bp_xprofile_data WHERE field_id = 1 AND user_id IN (XXXXX) (XXXXX changes based on user ID being looked up
Function: require_once, require_once, do_action('admin_enqueue_scripts'), call_user_func_array, bp_admin_enqueue_scripts, do_action('bp_admin_enqueue_scripts'), call_user_func_array, bp_activity_mentions_script, do_action('bp_activity_mentions_prime_results'), call_user_func_array, bp_friends_prime_mentions_results, bp_core_get_user_displayname, bp_core_get_user_displaynames, BP_XProfile_ProfileData::get_value_byid

This means about 60-80 seconds to edit a single page or a single post in wp-admin. The rest of wp-admin is fine.

Latest WP and latest BP are installed.

When viewing the source of edit page or edit post, I see it is adding data from all of my "Friends" on the site (Which are 49,000 members, all members are automatic friends).

var BP_Suggestions = {"friends":[{"ID":"----","image":"http:\/\/\/avatar\/964cd5d52e2eb1c31427e49202ed5d16?d=http:\/\/\/wp-content\/themes\/buddyboss\/images\/wpb-avatar.png&s=50&r=X","name":"----"}

I dont know how to fix this, or why editing a post or a page wants to load my entire friends list

Any suggestions or advice or a bug fix would be GREATLY appreciated!



Attachments (2)

5950.patch (987 bytes) - added by imath 9 years ago.
5950.02.patch (1.7 KB) - added by imath 9 years ago.

Download all attachments as: .zip

Change History (14)

#1 @imath
9 years ago

  • Component changed from Core to Activity
  • Keywords dev-feedback added
  • Milestone changed from Awaiting Review to 2.2

Hi wcvendors

Thanks for your feedback. I confirm a query is added for each friend you have.

i suggest to :

  • Wait for DJPaul's opinion about it to see how we can deal with this in 2.2
  • In the meantime, you can neutralize at mentions in WP Admin on your config by creating the bp-custom.php file at the root of /wp-content/plugins and put this code in it.
Last edited 9 years ago by imath (previous) (diff)

9 years ago

#2 @imath
9 years ago

  • Keywords has-patch added

bp_core_get_user_displayname() is generating the extra queries in bp_friends_prime_mentions_results()

5950.patch suggests to avoid these extra queries by using $user->display_name if it exists, else it defaults to user_nicename.

#3 follow-up: @DJPaul
9 years ago

Few thoughts:

I think we need something like wp_is_large_network that checks if a user has "many" friends, and if so, we return out of bp_friends_prime_mentions_results. It's intended to only pre-cache friend lookups for quick results, but not to generate a large query, or if Friends are being used in a non-default way.

We can use $user->display_name instead of bp_core_get_user_displayname(...) if the xprofile sync is enabled (! bp_disable_profile_sync() ). This seems like a great improvement.

I am hesitant to use $user->user_nicename because I am pretty sure it isn't always the same as xprofile's name field. If you wanted to do this, I think we will need to create a whole lot of user accounts with different types of user name etc, and check which variant of those formats appear in the @mention picker UI, with and without username compatibility mode being enabled, to check that everything remains sane and logical. It's kind of a pain to do. :)

#4 @r-a-y
9 years ago

A short-term fix is to also disable suggestions support from the WP dashboard unless you're on the BP Groups admin page.

9 years ago

#5 in reply to: ↑ 3 @imath
9 years ago

Replying to DJPaul:

I think we need something like wp_is_large_network

I suggest to use friends_get_total_friend_count( $user_id ) in 5950.02.patch i put the limit at 100 friends, but we can increase this limit.

We can use $user->display_name instead of bp_core_get_user_displayname(...) if the xprofile sync is enabled (! bp_disable_profile_sync() ). This seems like a great improvement.

I use $user->display_name, if not set fallback to bp_core_get_user_displayname(...)

r-a-y, thanks for your feedback, you're right, the gist i shared with wcvendors was suggesting to disallow mentions scripts if on WP Admin till we fix this. I'm not sure we need to check for groups administration as filtering bp_activity_maybe_load_mentions_scripts to false in WP Admin shouldn't have an impact on the "add member auto-complete" feature.

#6 @wcvendors
9 years ago

Thank you everyone for the flurry of comments and suggestions on this.

Patching the admin side might work for editing posts/pages, of course, however when you view a members profile on the front-end, those still take a few minutes each to load. (PS -- This is a dedicated 24 core Xeon with 64GB Memory, and it only runs this site. I cringe to think of a smaller site on GoDaddy and the page load times!)

Disabling the "at" mentions all together on the site temporarily would set me on fire by the hell wrath of members who use it all the time on the forums, wall posts, and comments. ;) They'd kill me! :)

Disabling the mentions caching if friends list exceeds 250 (or so?) would be a pretty great idea.

#7 @DJPaul
9 years ago

Spoke with JJJ at WCSF and we decided is_large_network(users) will suffice (that's > 10k users) with the idea that anyone with a vast number of users and friends would probably want to implement the @mentions lookups in some other way. I think this is OK to try and see how it goes.

#8 @wcvendors
9 years ago

Sounds good to me. :)

#9 @DJPaul
9 years ago

Turns out aforementioned function is multisite only, which is very annoying.

#10 @DJPaul
9 years ago

  • Keywords dev-feedback removed
  • Priority changed from high to normal
  • Severity changed from critical to normal

#11 @djpaul
9 years ago

In 9101:

Mentions: for pre-cached result, use displayname if profile sync is enabled.

This should cut down on numbers of SQL queries made in certain
situations where a user on a site has many friends (whose user details
are not in WP’s object cache).

See #5950

#12 @djpaul
9 years ago

  • Owner set to djpaul
  • Resolution set to fixed
  • Status changed from new to closed

In 9102:

Mentions: don't prime mention results if the site/user has many users/friends.

Sites with huge amounts of users (and therefore probably many potential
friendships) can cause the primed result generation to be slow due to
the number of potential user name lookups/queries made.

Multisite has a function to help us decide if it’s a large network, but
it’s not available for regular WordPress, so we also check the logged
in user’s total friend count. (150 is Dunbar's number.)

In conjunction with the previous commit, this change should help make
the pre-cached result generation more performant.

Fixes #5950. Props imath for original patch.

Note: See TracTickets for help on using tickets.