[buddypress-trac] [BuddyPress] #4045: Activity Feed queries fail with millions of rows

buddypress-trac at lists.automattic.com buddypress-trac at lists.automattic.com
Tue Mar 13 20:49:21 UTC 2012


#4045: Activity Feed queries fail with millions of rows
-----------------------------------+--------------------
 Reporter:  zacechola              |       Owner:
     Type:  defect (bug)           |      Status:  new
 Priority:  normal                 |   Milestone:  1.6
Component:  Activity               |     Version:  1.5.4
 Severity:  normal                 |  Resolution:
 Keywords:  2nd-opinion has-patch  |
-----------------------------------+--------------------

Comment (by zacechola):

 All the info is provided below, but essentially it takes about 7 seconds
 to build the count(*) and 12 seconds to run the other query. So, if both
 are being called during a sudden burst of update activity on the site, the
 query cache busts and we run into problems with the slow query.

 So, basically, as long as the activity is evenly spread out, we do OK with
 the patch, but as soon as we see a spike in updates, even if small, we're
 in trouble.

 Anyway, looking a bit closer, it doesn't appear that it is actually being
 written to disk, but the sorting process does take a long time.  If the
 amount of data to be sorted was paired down it would be useful.

 Explain results, bp_activity

 {{{
 mysql>
 EXPLAIN SELECT count(*)
 FROM [redacted]_bp_activity a
 USE INDEX (type) WHERE a.type IN ( 'new_blog_post' )
 AND a.hide_sitewide = 0
 AND a.type != 'activity_comment'\G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: a
          type: ref
 possible_keys: type
           key: type
       key_len: 227
           ref: const
          rows: 2105025
         Extra: Using where
 1 row in set (0.00 sec)
 }}}

 Query results

 {{{
 mysql>
 SELECT count(*)
 FROM [redacted]_bp_activity a USE INDEX (type)
 WHERE a.type IN ( 'new_blog_post' )
 AND a.hide_sitewide = 0
 AND a.type != 'activity_comment'\G
 *************************** 1. row ***************************
 count(*): 2696717
 1 row in set (7.14 sec)
 }}}
 Explain Results, user
 {{{
 mysql>
 EXPLAIN SELECT a.*, u.user_email, u.user_nicename, u.user_login,
 u.display_name
 FROM [redacted]_bp_activity a
 LEFT JOIN [redacted]_users u ON a.user_id = u.ID
 WHERE a.type IN ( 'new_blog_post' )
 AND a.hide_sitewide = 0
 AND a.type != 'activity_comment'
 ORDER BY a.date_recorded
 DESC LIMIT 0, 2\G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: a
          type: ref
 possible_keys: type,hide_sitewide
           key: hide_sitewide
       key_len: 2
           ref: const
          rows: 1919104
         Extra: Using where; Using filesort
 *************************** 2. row ***************************
            id: 1
   select_type: SIMPLE
         table: u
          type: eq_ref
 possible_keys: PRIMARY
           key: PRIMARY
       key_len: 8
           ref: [redacted].a.user_id
          rows: 1
         Extra:
 2 rows in set (0.00 sec)
 }}}
 Query Results
 {{{
 mysql>
 SELECT a.*, u.user_email, u.user_nicename, u.user_login, u.display_name
 FROM [redacted]_bp_activity a
 LEFT JOIN [redacted]_users u ON a.user_id = u.ID
 WHERE a.type IN ( 'new_blog_post' ) AND a.hide_sitewide = 0
 AND a.type != 'activity_comment'
 ORDER BY a.date_recorded
 DESC LIMIT 0, 2\G
 *************************** 1. row ***************************
 [redacted]

 2 rows in set (12.84 sec)
 }}}

 Activity indexes:

 {{{
 mysql> show index from [redacted]_bp_activity\G
 *************************** 1. row ***************************
        Table: [redacted]_bp_activity
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 3340434
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
 *************************** 2. row ***************************
        Table: [redacted]_bp_activity
   Non_unique: 1
     Key_name: date_recorded
 Seq_in_index: 1
  Column_name: date_recorded
    Collation: A
  Cardinality: 3340434
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
 *************************** 3. row ***************************
        Table: [redacted]_bp_activity
   Non_unique: 1
     Key_name: user_id
 Seq_in_index: 1
  Column_name: user_id
    Collation: A
  Cardinality: 10153
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
 *************************** 4. row ***************************
        Table: [redacted]_bp_activity
   Non_unique: 1
     Key_name: item_id
 Seq_in_index: 1
  Column_name: item_id
    Collation: A
  Cardinality: 14
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
 *************************** 5. row ***************************
        Table: [redacted]_bp_activity
   Non_unique: 1
     Key_name: secondary_item_id
 Seq_in_index: 1
  Column_name: secondary_item_id
    Collation: A
  Cardinality: 1113478
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
 *************************** 6. row ***************************
        Table: [redacted]_bp_activity
   Non_unique: 1
     Key_name: component
 Seq_in_index: 1
  Column_name: component
    Collation: A
  Cardinality: 14
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
 *************************** 7. row ***************************
        Table: [redacted]_bp_activity
   Non_unique: 1
     Key_name: type
 Seq_in_index: 1
  Column_name: type
    Collation: A
  Cardinality: 14
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
 *************************** 8. row ***************************
        Table: [redacted]_bp_activity
   Non_unique: 1
     Key_name: mptt_left
 Seq_in_index: 1
  Column_name: mptt_left
    Collation: A
  Cardinality: 14
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
 *************************** 9. row ***************************
        Table: [redacted]_bp_activity
   Non_unique: 1
     Key_name: mptt_right
 Seq_in_index: 1
  Column_name: mptt_right
    Collation: A
  Cardinality: 14
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
 *************************** 10. row ***************************
        Table: [redacted]_bp_activity
   Non_unique: 1
     Key_name: hide_sitewide
 Seq_in_index: 1
  Column_name: hide_sitewide
    Collation: A
  Cardinality: 14
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
 10 rows in set (0.23 sec)
 }}}

 Process list:

 {{{
 *************************** 4. row ***************************
      Id: 18687045
    User: root
    Host: localhost
      db: [redacted]
 Command: Query
    Time: 4
   State: Sorting result
    Info: SELECT a.*, u.user_email, u.user_nicename, u.user_login,
 u.display_name
 FROM [redacted]_bp_activity a
 LEFT JOIN [redacted]_users u ON a.user_id = u.ID
 WHERE a.type IN ( 'new_blog_post' )
 AND a.hide_sitewide = 0
 AND a.type != 'activity_comment'
 ORDER BY a.date_recorded
 DESC LIMIT 0, 2

 PRODUCES 2 rows in set (15.92 sec)
 }}}

-- 
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/4045#comment:10>
BuddyPress <http://buddypress.org/>
BuddyPress


More information about the buddypress-trac mailing list