Skip to:
Content

Opened 3 years ago

Last modified 4 months ago

#3659 new defect (bug)

Recently Active widget doesn't scale

Reported by: mpvanwinkle77 Owned by:
Milestone: Future Release Priority: normal
Severity: normal Version: 1.2.10
Component: Core Keywords: needs-patch
Cc: joesell89

Description

Hello, I don't have a solution for this yet, but I wanted to add it to the mix. The Recently Active (and Who's Online too I think) widgets don't scale well. I have a site with 98,000+ registered users and this query used by the widget was just killing the site:

SELECT DISTINCT u.ID as id, u.user_registered, u.user_nicename, u.user_login, u.display_name, u.user_email , um.meta_value as last_activity FROM wp_users u 
LEFT JOIN wp_usermeta um ON um.user_id = u.ID 
WHERE u.spam = 0 
AND u.deleted = 0 AND u.user_status = 0 AND um.meta_key = 'last_activity' 
ORDER BY um.meta_value 
DESC LIMIT 0, 18;

Clearly the scale of our community is a big factor. But we have an entire server dedicated solely to MySQL and three for httpd ... and even with these resources we were getting crushed.

I'm trying to think of ways to scale this better. I'm considering creating a field in the options table to store IDs of logged in users and then running those IDs through get_userdata() which should at least be getting cached by W3 Total Cache. This approach would come with a little more PHP overhead but hopefully spare my database server the burden of the direct user query.

In general I think some modifications to bp_core_get_users() ( i.e. BP_Core_User ) could solve the problem. Perhaps adding caching logic? Or perhaps querying just the wp_usermeta table for user_id's and then populating each one with get_userdata() ?

So anyway, just wanted to start the discussion and get feedback.

Change History (3)

comment:1 boonebgorges3 years ago

  • Milestone changed from Awaiting Review to Future Release

Thanks for the report.

perhaps querying just the wp_usermeta table for user_id's and then populating each one with get_userdata()

That solution would be pretty efficient if you were using the right kind of persistent object caching, but I think it would be pretty inefficient if you weren't, since it creates many queries where previously there was just one. However, that would need some testing, since the queries would then be of a much simpler sort.

This query is simple enough where we could fairly easily rewrite it to take advantage of the relatively new WP_User class, which has full wp_cache support already built in. See #3127 for a broader discussion. Though in this case, unlike in #3127, we wouldn't need to join against any BP tables, so it's much more feasible in this case than elsewhere in BP_Core_User.

comment:2 joesell894 months ago

  • Cc joesell89 added

I wish I could be a solution to this problem but I don't have the know how.

I do want to say that this is an issue that I am currently trying to deal with. I think the widget is important to some sites because it shows users that there are others active on the site even if they aren't saying anything.

However, my host (WP Engine) have helped me to identify this as the single biggest performance drain on my site. I don't even have a very big site (3,700 members).

I can't come up with a solution but I do want to say that it is important on my site and I would be more than happy to test any solutions that anyone can come up with.

comment:3 boonebgorges4 months ago

This is really a subset of the larger issue with last_activity being stored in usermeta. See #5128.

Note: See TracTickets for help on using tickets.