Page 2 of 2

Re: How's the new server performing?

Posted: Sat Apr 22, 2017 3:47 am
by emk
OK, I've been digging around really deep inside the system using csysdig, and I just can't find the problem here.

We do have a problem with Apache spawning too many workers, using up too much RAM, and getting shut down by Linux, but that only happens every 5 minutes or so, and right now, site performance is pretty consistently terrible for me. So I doubt that's the main issue.

The database is snappy enough if I log into it directly. The server is plenty snappy as well. I can even log into the database from the server's command line with no issues. We are getting hit by a lot of crawlers, but that's not enough by itself.

So I'm stumped, and it's well past bed time. I'm going to go to sleep, and my apologies if anybody else is seeing this slowdown. We'll get this sorted out, one way or another.

Re: How's the new server performing?

Posted: Sat Apr 22, 2017 4:20 am
by smallwhite
For me, its only thread-viewing and post-submitting that are slow. User control panel, private messages and subforum-viewing (viewforum.php?f=14) are fast.

Edit: post-submitting could be slow just because the thread-viewing part is slow; I can't tell.

Re: How's the new server performing?

Posted: Sat Apr 22, 2017 4:30 am
by Arnaud
The home page is super fast, posting in a thread is slow (30 sec to 1 min)
I also stumble on a sql error
SQL ERROR [ mysqli ]
Server shutdown in progress [1053]]

Now it's fast again... :roll:

Re: How's the new server performing?

Posted: Sat Apr 22, 2017 4:42 am
by emk
Arnaud wrote:The home page is super fast, posting in a thread is slow (30 sec to 1 min)
I also stumble on a sql error
SQL ERROR [ mysqli ]
Server shutdown in progress [1053]]

Now it's fast again... :roll:

I just turned off the "hearts" extension, which seems to have been generating amazingly slow queries for some unknown reason. :-(

Code: Select all

-- Check to make sure performance monitoring is enabled:
SHOW VARIABLES LIKE 'performance_schema';
-- (If it isn't, go turn it on the RDS parameter group and reboot the database.)
-- Turn on performance monitoring.
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'
-- Reload some pages, then ask to see the slowest SQL queries:
select * from performance_schema.events_statements_history_long order by timer_wait DESC limit 5;

Here are the slowest queries:

Code: Select all

mysql> select sql_text from performance_schema.events_statements_history_long order by timer_wait DESC limit 5;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_text                                                                                                                                  |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
| 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 |
| 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 = 221 |
| 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 = 67  |
| 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 = 116 |
+-------------------------------------------------------------------------------------------------------------------------------------------+

These queries generate the per-user post heart counts that are shown to the left of each post, and they're running slowly. Not sure why. I'll investigate the database indices as soon as possible (after getting some sleep), so that we can get our hearts back.

Re: How's the new server performing?

Posted: Sat Apr 22, 2017 5:05 am
by emk
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. :lol:

Re: How's the new server performing?

Posted: Sat Apr 22, 2017 5:08 am
by Arnaud
All is working again (re PM). Thanks!

Re: How's the new server performing?

Posted: Sat Apr 22, 2017 6:15 pm
by MorkTheFiddle
Paging through logs often used to bog things down and give me an error message. Paging through Iversen's log yesterday was a very tad sluggish, but produced no error messages.

Re: How's the new server performing?

Posted: Sun Apr 23, 2017 1:55 am
by Jar-Ptitsa
Very fast and great.