[buddypress-trac] [BuddyPress] #3659: Recently Active widget doesn't scale

buddypress-trac at lists.automattic.com buddypress-trac at lists.automattic.com
Thu Oct 6 17:17:17 UTC 2011

#3659: Recently Active widget doesn't scale
 Reporter:  mpvanwinkle77  |      Owner:
     Type:  defect         |     Status:  new
 Priority:  normal         |  Milestone:  Awaiting Review
Component:  Core           |    Version:  1.2.10
 Severity:  normal         |   Keywords:  needs-patch
 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.

Ticket URL: <https://buddypress.trac.wordpress.org/ticket/3659>
BuddyPress <http://buddypress.org/>

More information about the buddypress-trac mailing list