So let's look at one of the slow queries:
Code: Select all
mysql> SELECT COUNT(pl.post_id) as count FROM (phpbb_posts_likes pl CROSS JOIN phpbb_posts p) WHERE pl.post_id = p.post_id AND p.poster_id = 137;
+-------+
| count |
+-------+
| 2146 |
+-------+
1 row in set (1.33 sec)
mysql> EXPLAIN SELECT COUNT(pl.post_id) as count FROM (phpbb_posts_likes pl CROSS JOIN phpbb_posts p) WHERE pl.post_id = p.post_id AND p.poster_id = 109;
+----+-------------+-------+--------+-------------------+---------+---------+------------------+-------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------+---------+---------+------------------+-------+------------------------------------+
| 1 | SIMPLE | pl | index | PRIMARY | PRIMARY | 8 | NULL | 99958 | Using index |
| 1 | SIMPLE | p | eq_ref | PRIMARY,poster_id | PRIMARY | 3 | phpbb.pl.post_id | 1 | Using index condition; Using where |
+----+-------------+-------+--------+-------------------+---------+---------+------------------+-------+------------------------------------+
2 rows in set (0.08 sec)
So this SQL query looks like it's attempting to count the total number of likes received by a specific poster, and it takes 1.33 seconds. But that query plan from EXPLAIN looks weird. It looks like it's scanning the phpbb_posts_likes table
first? I'm not sure; I'm much better at understanding PostgreSQL query plans than MySQL ones. But if that's the case, it would explain why things suddenly got ridiculously slow.
"Likes" are ridiculously popular:
Code: Select all
mysql> SELECT COUNT(*) FROM phpbb_posts_likes;
+----------+
| COUNT(*) |
+----------+
| 99958 |
+----------+
1 row in set (0.04 sec)
What if I just ask the database to re-analyze the "likes" table?
Code: Select all
mysql> ANALYZE TABLE phpbb_posts_likes;
+-------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+---------+----------+----------+
| phpbb.phpbb_posts_likes | analyze | status | OK |
+-------------------------+---------+----------+----------+
1 row in set (0.08 sec)
mysql> SELECT COUNT(pl.post_id) as count FROM (phpbb_posts_likes pl CROSS JOIN phpbb_posts p) WHERE pl.post_id = p.post_id AND p.poster_id = 137;
+-------+
| count |
+-------+
| 2146 |
+-------+
1 row in set (0.13 sec)
mysql> EXPLAIN SELECT COUNT(pl.post_id) as count FROM (phpbb_posts_likes pl CROSS JOIN phpbb_
+----+-------------+-------+------+-------------------+-----------+---------+-----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------+-----------+---------+-----------------+------+--------------------------+
| 1 | SIMPLE | p | ref | PRIMARY,poster_id | poster_id | 3 | const | 1038 | NULL |
| 1 | SIMPLE | pl | ref | PRIMARY | PRIMARY | 4 | phpbb.p.post_id | 2 | Using where; Using index |
+----+-------------+-------+------+-------------------+-----------+---------+-----------------+------+--------------------------+
2 rows in set (0.04 sec)
Wow, 0.13 seconds! And the query plan from EXPLAIN is now in the right order, searching for posts by poster_id first, and then counting the matching hearts.
Go figure. Not sure why the table analysis was stale. But it's looking better now. And I think that ANALYZE should be run every night? Anyway, now rdearman knows how to fix this, too, if it ever happens again.
I'm going to try turning hearts back on. EDIT: And now we're even faster than this afternoon! Ok, time for bed.