[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