How's the new server performing?

Discuss technical problems and features here
User avatar
emk
Black Belt - 1st Dan
Posts: 1620
Joined: Sat Jul 18, 2015 12:07 pm
Location: Vermont, USA
Languages: English (N), French (B2+)
Badly neglected "just for fun" languages: Middle Egyptian, Spanish.
Language Log: viewtopic.php?f=15&t=723
x 6330
Contact:

Re: How's the new server performing?

Postby emk » Sat Apr 22, 2017 3:47 am

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.
0 x

User avatar
smallwhite
Black Belt - 2nd Dan
Posts: 2386
Joined: Mon Jul 06, 2015 6:55 am
Location: Hong Kong
Languages: Native: Cantonese;
Good: English, French, Spanish, Italian;
Mediocre: Mandarin, German, Swedish, Dutch.
.
x 4876

Re: How's the new server performing?

Postby smallwhite » Sat Apr 22, 2017 4:20 am

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.
Last edited by smallwhite on Sat Apr 22, 2017 4:32 am, edited 1 time in total.
1 x
Dialang or it didn't happen.

Arnaud
Blue Belt
Posts: 984
Joined: Sat Jul 18, 2015 11:57 am
Location: Paris, France
Languages: Native: French
Intermediate: English, Russian, Italian
Tourist : Breton, Greek, Chinese, Japanese, German, Spanish, Latin
Language Log: viewtopic.php?t=1524
x 2172

Re: How's the new server performing?

Postby Arnaud » Sat Apr 22, 2017 4:30 am

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:
1 x

User avatar
emk
Black Belt - 1st Dan
Posts: 1620
Joined: Sat Jul 18, 2015 12:07 pm
Location: Vermont, USA
Languages: English (N), French (B2+)
Badly neglected "just for fun" languages: Middle Egyptian, Spanish.
Language Log: viewtopic.php?f=15&t=723
x 6330
Contact:

Re: How's the new server performing?

Postby emk » Sat Apr 22, 2017 4:42 am

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.
2 x

User avatar
emk
Black Belt - 1st Dan
Posts: 1620
Joined: Sat Jul 18, 2015 12:07 pm
Location: Vermont, USA
Languages: English (N), French (B2+)
Badly neglected "just for fun" languages: Middle Egyptian, Spanish.
Language Log: viewtopic.php?f=15&t=723
x 6330
Contact:

Re: How's the new server performing?

Postby emk » Sat Apr 22, 2017 5:05 am

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:
3 x

Arnaud
Blue Belt
Posts: 984
Joined: Sat Jul 18, 2015 11:57 am
Location: Paris, France
Languages: Native: French
Intermediate: English, Russian, Italian
Tourist : Breton, Greek, Chinese, Japanese, German, Spanish, Latin
Language Log: viewtopic.php?t=1524
x 2172

Re: How's the new server performing?

Postby Arnaud » Sat Apr 22, 2017 5:08 am

All is working again (re PM). Thanks!
2 x

User avatar
MorkTheFiddle
Black Belt - 2nd Dan
Posts: 2114
Joined: Sat Jul 18, 2015 8:59 pm
Location: North Texas USA
Languages: English (N). Read (only) French and Spanish. Studying Ancient Greek. Studying a bit of Latin. Once studied Old Norse. Dabbled in Catalan, Provençal and Italian.
Language Log: https://forum.language-learners.org/vie ... 11#p133911
x 4824

Re: How's the new server performing?

Postby MorkTheFiddle » Sat Apr 22, 2017 6:15 pm

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.
1 x
Many things which are false are transmitted from book to book, and gain credit in the world. -- attributed to Samuel Johnson

User avatar
Jar-Ptitsa
Brown Belt
Posts: 1000
Joined: Sun Jul 19, 2015 1:13 pm
Location: London
Languages: Belgian French (N)

I can speak: Dutch, German, English, Spanish and understand Italian, Portuguese, Wallonian, Afrikaans, but not always correctly.
x 652

Re: How's the new server performing?

Postby Jar-Ptitsa » Sun Apr 23, 2017 1:55 am

Very fast and great.
0 x
-w- I am Jar-ptitsa and my Hawaiian name is ʻā ʻaia. Please correct my mistakes in all the languages. Thank you very much.
: 1 / 50 Spanish grammar
: 5 / 50 Spanish vocabulary


Return to “Technical Support and Feature Requests”

Who is online

Users browsing this forum: No registered users and 2 guests