Fairly high load on web server, very slow to load some pages

[AtariAge]

Nearly two weeks ago I migrated my forum from 3.4.8 to 4.4.4.  Now that the background tasks have finished running, my web server still has a pretty high load. Looks like php-cgi is responsible for this--load is averaging around 4.0.  On IP.Board 3.x this was generally below 1.0 and it was running on a slower server to boot (I moved the site to a new server at the same time as the forum upgrade).  Additionally, I was running PHP 5.7 before and now I'm running 7.2.  And MySQL is also a much newer version, so both of those should be faster than the old versions of PHP and MySQL I was using.  And finally, the new server is running on an NVMe SSD blade, so disk access should be MUCH faster.  I am using redis for caching. This is the only site running on the server.

I have a dedicated MySQL server on another box in the same data center, same server that I was using for the old forum, so nothing's changed there.  Load on that server is also higher than I experienced with 3.4.8, and that server has 64GB of RAM, most of which is given to MySQL (nothing else is running on that server). 

Most pages load in a reasonable time, but some are quite slow.  For instance, clicking on a forum from the main index can take up to 12 seconds to load, which is pretty abysmal. 

Any insight into areas I can look at and/or how to diagnose what's consuming so many resources would be greatly appreciated.

Thank you,

 ..Al

[Adlago]

If you share a link to your site, you may get tips.

[AtariAge]

1 minute ago, Adlago said:

If you share a link to your site, you may get tips.

Good idea, not sure why I didn't include that. 😄

https://atariage.com/forums/

[Adlago]

First impression - you are using a server apache, but you probably do not manage with a files gzip compression.
Show contents of your htaccess file please.

[Adlago]

Also - your site uses protocol http/1.1 - contact your host support to activate protocol h2 for you

[Adlago]

5 hours ago, AtariAge said:

This is the only site running on the server.

And here you are wrong.

 

"Reverse IP results for atariage.com (199.193.116.116)
==============

There are 20 domains hosted on this server....."

[AtariAge]

18 minutes ago, Adlago said:

Also - your site uses protocol http/1.1 - contact your host support to activate protocol h2 for you

It's a dedicated server that I have full control over. I'll certainly setup HTTP/2 at some point.  And I'll check if gzip compression is on, but neither of those things going to make a significant difference when pages are taking 15 seconds to load. 

Here is an example of one page that consistently takes over 10 seconds to load:

https://atariage.com/forums/forum/12-atari-8-bit-computers/

4 minutes ago, Adlago said:

And here you are wrong.

 

"Reverse IP results for atariage.com (199.193.116.116)
==============

There are 20 domains hosted on this server....."

That is wrong, the only site running on this server is atariage.com.  As I said before, it's a dedicated server I leased three months ago to replace the old web server. I'm sure there were other sites running on that IP in the past.

[Adlago]

This is because lack of gzip compression create a large html file for download - and this slows loading site.
Also you do not use leverage browser cache - it also slows the loading site.
The h2 protocol will make a parallel download of JS and CSS which will speed up your site.
Do these three things and you will see a difference.

PS. Your screen proves my words - 300kB html file is too large ...

[AtariAge]

Just now, Adlago said:

This is because lack of gzip compression create a large html file for download - and this slows loading site.
Also you do not use leverage browser cache - it also slows the loading site.
The h2 protocol will make a parallel download of JS and CSS which will speed up your site.
Do these three things and you will see a difference.

Sorry, these are not the issues causing a page to take 15 seconds to load, when most other pages load relatively quickly.  There's definitely something going on with the Forums page, that's the only one I've seen that is consistently slow. The .html file for the forums page isn't going to be significantly larger than any of the other pages on the site (main index, activity view, thread view, etc.)  I agree it's worthwhile to pursue the things you mentioned, but I can do that once I discern what's causing the high load and the slow page loading (probably two separate issues there). 

The .html document for the link in my previous post is 301K.  The browser waited 15 seconds for the site to respond, and the actual transfer took 213 ms.  The main forum index is 337K, and it only took .284 seconds to transfer after waiting 3.1 seconds.  It's the "waiting" that's taking a long time, which presumably is a result of queries running and/or PHP processing the query results.  3.1 seconds is also slow for the main index, but nothing compared to the 10-15 seconds for the /forums/ pages to load.  Again, the gzip and h2 protocol aren't going to make a very noticeable difference here.

 ..Al

[Adlago]

Each html larger than 20kB makes the first byte big. This is the reason for a long delay.
In your site, the lack of h2 protocol slows a lot of JS loading because your images are loading slowly before the JS, and your images are not cached.
They are all complex reasons and you do not think you will find any other reason.

[AtariAge]

1 minute ago, Adlago said:

Each html larger than 20kB makes the first byte big. This is the reason for a long delay.
In your site, the lack of h2 protocol slows a lot of JS loading because your images are loading slowly before the JS, and your images are not cached.
They are all complex reasons and you do not think you will find any other reason.

So, please explain to me why this particular page of the suite loads considerably more slowly than everything else?  If what you are saying is true, I would expect all the pages on the site to load just as slowly since they are a similar combination of JS, images and CSS, but that's not the case.  What you are describing is not causing the page to take 15 seconds to load, not when the .html page is only 300K.  With my last two examples, everything was cached (images, javascript, css) except for the .html file.  You can see that in my screenshot above.

 ..Al

[Adlago]

6 minutes ago, AtariAge said:

So, please explain to me why this particular page of the suite loads considerably more slowly than everything else?  If what you are saying is true, I would expect all the pages on the site to load just as slowly since they are a similar combination of JS, images and CSS, but that's not the case.  What you are describing is not causing the page to take 15 seconds to load, not when the .html page is only 300K.  With my last two examples, everything was cached (images, javascript, css) except for the .html file.  You can see that in my screenshot above. 

 ..Al

You have made a test of your screen as a logged user. There may be a problem with your browser cache.
This page with me loads quickly - in 2-3 seconds.

[AtariAge]

Yes, the page loads quickly if you are not logged in.  It is not a problem with my browser cache.  Many others are reporting the same issue.

 ..Al

[Adlago]

Please send me a test account of PM to do a test

[AtariAge]

I've sent you a PM.

[Adlago]

Thanks,
but actually this page loads very slowly as a guest, but with a browser FF. Chrome browser is loading fast ...

[Ramsesx]

You could try to reduce the topic count per page from 50 now to 25 (default). Disabling guest access to user profiles will improve loading speed too on topic view.

For me it looks all forums are loading slow without a difference. Not related to this topic here but I've seen you have ignore topic addon for guests enabled, this doesn't makes sense.

Generally seen I would try to disable all plugins step by step to check out if the page loading issue is caused by any of them. 

[AtariAge]

Yeah, the Ignore Topics plugin is the only one that I think would have any relevancy here.  However, everything was loading fine until maybe two days ago when things started slowing down considerably on /forums/.  You are right, guests shouldn't have access to Ignore Topics so I'll certainly fix that.  And I agree that temporarily disabling all the plugins is something I should try so I can at least eliminate them as a problem.

I can also try changing the topic count per page from 50 to 25, but I don't think that's going to make much difference, and I'd prefer them set to 50. But, again, I'll test it to see if that makes any significant change in the time the pages take to load.  I've had them set to 50 ever since I've been running IP.Board (about 15 years).  Assuming that was actually a setting back then. 😄

Thanks for your suggestions, I'll report back once I try them.

 ..Al

[AtariAge]

Also, I setup the slow_query_log to report any queries taking 2 seconds or longer to execute.  In the last half hour, the only slow query I'm seeing is this one:

# Time: 2019-06-27T21:00:45.971314Z
# User@Host: XXXX[XXXX] @  [XXXXXX]  Id: 2548484
# Schema: XXXXXX  Last_errno: 0  Killed: 0
# Query_time: 15.844966  Lock_time: 0.000034  Rows_sent: 25  Rows_examined: 3319688  Rows_affected: 0
# Bytes_sent: 347
SET timestamp=1561669245;
/*IPS\Content\_Item::_comments:1861*/ SELECT pid FROM `ibf_forums_posts` AS `forums_posts` WHERE forums_posts.topic_id=242970 AND (forums_posts.queued IN(0,2)) ORDER BY post_date asc LIMIT 0,25;

That query is taking 15 seconds to run and is examining 3.3 million rows..   And I'm only seeing the slow query for two threads so far (both threads have well over 10,000 posts each):

https://atariage.com/forums/topic/242970-fpga-based-videogame-system
https://atariage.com/forums/topic/223548-the-official-neo-geo-thread/

I don't know where exactly that query is being invoked.  But presumably it's when viewing these threads, since it's only returning 25 rows, which is how many posts I have shown per page when viewing a thread.  And these threads both take a while to load.  So I suppose these are additional examples of slow-loading pages.

I'm kind of surprised I'm not seeing slow queries related to loading of the forums page. Which suggests that PHP is taking a long time to execute that page..

 ..Al

[AtariAge]

Here's another query I've seen a few times that's taking nearly 30 seconds..

# Query_time: 28.477905  Lock_time: 0.000185  Rows_sent: 12  Rows_examined: 4373053  Rows_affected: 0
# Bytes_sent: 7437
SET timestamp=1561675681;
/*IPS\Content\Search\Mysql\_Query::search:350*/ SELECT main.* FROM `ibf_core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply')
 ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\blog\\Entry','IPS\\blog\\Entry\\Comment') ) OR ( index_class IN('IPS\\gallery\\Image','IPS\\gallery\\Image\\Commen
t','IPS\\gallery\\Image\\Review') ) OR ( index_class IN('IPS\\gallery\\Album\\Item','IPS\\gallery\\Album\\Comment','IPS\\gallery\\Album\\Review') ) OR ( index_class IN('IPS\\calendar\\E
vent','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR index_class='IPS\\cms\\Pages\\PageItem' OR ( index_class IN('IPS\\nexus\\Package\\Item','IPS\\nexus\\Package\\
Review') ) ) AND ( ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) AND index_is_last_comment=1 ) OR ( index_class='IPS\\forums\\Topic\\Post' AND index_i
s_last_comment=1 ) OR ( ( index_class IN('IPS\\blog\\Entry','IPS\\blog\\Entry\\Comment') ) AND index_is_last_comment=1 ) OR ( ( index_class IN('IPS\\gallery\\Image','IPS\\gallery\\Image
\\Comment','IPS\\gallery\\Image\\Review') ) AND (( index_container_class IN('IPS\\gallery\\Category') ) OR ( index_class IN('IPS\\gallery\\Image\\Comment','IPS\\gallery\\Image\\Review')
 )) AND index_is_last_comment=1 ) OR ( ( index_class IN('IPS\\gallery\\Album\\Item','IPS\\gallery\\Album\\Comment','IPS\\gallery\\Album\\Review') ) AND index_is_last_comment=1 ) OR ( ( 
index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) AND index_is_last_comment=1 ) OR ( index_class='IPS\\cms\\Pages\\PageItem' AND in
dex_is_last_comment=1 ) OR ( ( index_class IN('IPS\\nexus\\Package\\Item','IPS\\nexus\\Package\\Review') ) AND index_is_last_comment=1 ) ) AND ( index_permissions = '*' OR ( FIND_IN_SET
(26,index_permissions) OR FIND_IN_SET('m40689',index_permissions) OR FIND_IN_SET('ca',index_permissions) ) ) AND index_hidden=0 AND ((( index_class IN('IPS\\forums\\Topic','IPS\\forums\
\Topic\\Post') ) and ( index_item_id IN(291235,291183,287634,286521,285055,284357,284076,271552,271546,271165,270665,270521,270119,270107,270093,270080,269909,269828,269766,269752,26974
8,269729,269526,269502,269406,269401,269287,269033,268966,268768,268433,268426,268262,268255,268225,267758,267283,267118,266764,266518,266480,266465,265085,264614,263467,260764,259845,2
59236,258970,258876,258855,255982,255830,255766,254827,254611,252217,252210,251710,250709,248336,243854,240169,239719,239395,238529,236518,235178,227970,216511,201294,173494,80878) ))) 
AND index_date_updated>1530139651 ORDER BY index_date_commented DESC LIMIT 0,25;

Looks like this one is going through the entire posts table (I don't know if "Rows_examined" means it actually looked at each row during this query).

 ..Al

[Adlago]

9 hours ago, AtariAge said:

https://atariage.com/forums/topic/242970-fpga-based-videogame-system

In this page you have mistakenly created links for loading images
These queries return 404 error

[bfarber]

14 hours ago, AtariAge said:

Here's another query I've seen a few times that's taking nearly 30 seconds..

# Query_time: 28.477905  Lock_time: 0.000185  Rows_sent: 12  Rows_examined: 4373053  Rows_affected: 0
# Bytes_sent: 7437
SET timestamp=1561675681;
/*IPS\Content\Search\Mysql\_Query::search:350*/ SELECT main.* FROM `ibf_core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply')
 ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\blog\\Entry','IPS\\blog\\Entry\\Comment') ) OR ( index_class IN('IPS\\gallery\\Image','IPS\\gallery\\Image\\Commen
t','IPS\\gallery\\Image\\Review') ) OR ( index_class IN('IPS\\gallery\\Album\\Item','IPS\\gallery\\Album\\Comment','IPS\\gallery\\Album\\Review') ) OR ( index_class IN('IPS\\calendar\\E
vent','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR index_class='IPS\\cms\\Pages\\PageItem' OR ( index_class IN('IPS\\nexus\\Package\\Item','IPS\\nexus\\Package\\
Review') ) ) AND ( ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) AND index_is_last_comment=1 ) OR ( index_class='IPS\\forums\\Topic\\Post' AND index_i
s_last_comment=1 ) OR ( ( index_class IN('IPS\\blog\\Entry','IPS\\blog\\Entry\\Comment') ) AND index_is_last_comment=1 ) OR ( ( index_class IN('IPS\\gallery\\Image','IPS\\gallery\\Image
\\Comment','IPS\\gallery\\Image\\Review') ) AND (( index_container_class IN('IPS\\gallery\\Category') ) OR ( index_class IN('IPS\\gallery\\Image\\Comment','IPS\\gallery\\Image\\Review')
 )) AND index_is_last_comment=1 ) OR ( ( index_class IN('IPS\\gallery\\Album\\Item','IPS\\gallery\\Album\\Comment','IPS\\gallery\\Album\\Review') ) AND index_is_last_comment=1 ) OR ( ( 
index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) AND index_is_last_comment=1 ) OR ( index_class='IPS\\cms\\Pages\\PageItem' AND in
dex_is_last_comment=1 ) OR ( ( index_class IN('IPS\\nexus\\Package\\Item','IPS\\nexus\\Package\\Review') ) AND index_is_last_comment=1 ) ) AND ( index_permissions = '*' OR ( FIND_IN_SET
(26,index_permissions) OR FIND_IN_SET('m40689',index_permissions) OR FIND_IN_SET('ca',index_permissions) ) ) AND index_hidden=0 AND ((( index_class IN('IPS\\forums\\Topic','IPS\\forums\
\Topic\\Post') ) and ( index_item_id IN(291235,291183,287634,286521,285055,284357,284076,271552,271546,271165,270665,270521,270119,270107,270093,270080,269909,269828,269766,269752,26974
8,269729,269526,269502,269406,269401,269287,269033,268966,268768,268433,268426,268262,268255,268225,267758,267283,267118,266764,266518,266480,266465,265085,264614,263467,260764,259845,2
59236,258970,258876,258855,255982,255830,255766,254827,254611,252217,252210,251710,250709,248336,243854,240169,239719,239395,238529,236518,235178,227970,216511,201294,173494,80878) ))) 
AND index_date_updated>1530139651 ORDER BY index_date_commented DESC LIMIT 0,25;

Looks like this one is going through the entire posts table (I don't know if "Rows_examined" means it actually looked at each row during this query).

 ..Al

Actually, this is a search query (hitting the search index, not the posts table).

For an especially large site, you may wish to switch over to Elasticsearch which will offload searching to an external service more optimized for this purpose (not only is it faster, but it will provide better and more relevant results).

There are a lot of factors at play here so it's challenging to say "It's definitely X". For instance, you just upgraded and some of your URLs are going to be different than they were in 3.x. This means search engine spiders who are indexing your site will see these changes and be getting redirected all over the place when checking the URLs in their index. It can definitely take some time for search engines to go through everything on the site and update the URLs they have indexed, and you may see more traffic (and thus load) on your site in the mean time.

If you were using something like Sphinx in 3.x to improve searching, then that would have been lost in the 4.x upgrade and may be having an impact.

Any third party addons can also introduce changes to performance, so it's always good to test for a little while with third party addons disabled.

The changes that adlago are suggesting can also make an impact. For instance, if images, js and CSS are not cached properly in the user's browsers, then that means every page load those files must be re-requested from the server. This may seem to be a small thing, but those extra requests can ultimately add up and result in the server spending more effort re-serving static resources to users, taking resources away from processing the dynamic (PHP) portions of the request.

[AtariAge]

Hi Brandon, and thank you for your response.

Yes, I'm definitely going to use Elasticsearch.  I already have it installed on the server, but when I tried to configure it for the forum I must have had one of the parameters wrong as I  getting tons of errors in the log.  I immediately switched it back to MySQL, and of course it had to restart building the search index (which seems to take a full day).  I didn't want to try configuring Elasticsearch again until I look at it a bit more carefully.  This will be one of the first things I do this week when I get home (I'm out of town right now). 

I was using Sphinx in 3.x, and that was a huge improvement over MySQL in terms of resources used, speed of search results (it was immensely faster), and quality of search results.  Not sure if the benefits will be quite as noticeable when using Elasicsearch, but if it take some load off the MySQL server, I'm all for it.  I also need to figure out how to do two-character searches again, which is necessary on my forum (was able to do this with Sphinx easily enough).

I will certainly look into the changes Adlago suggested, I just don't feel they are going to make such a significant impact overall.  It already looks like the images, JS and CSS are being cached in the browser based on my testing, and you can see that in my screenshot above.  I'm not really keen on turning on gzip compression at the moment (if is off), given I need to work on reducing load on the web server first.  I need to see if I can find some threads here where people discuss performance optimization with regards to 4.x.  I certainly need to make sure my MySQL configuration is optimal.  The database server has 64GB of RAM and is the only thing running on that dedicated box, so it should be able to cache the entire database in memory (or at least most of it).  And I need to make sure PHP is configured properly, as PHP seems to be pushing the load up pretty high--I think this may be the main cause of slowdowns (PHP script processing).  And I am using redis already.   This is probably going to be an iterative process to fine tune the servers.

I did discover that some plugins I was using were affecting page loads, in particular for the /forums/ pages I started this thread about.  While they are still not as fast as they could be, I'm no longer seeing anywhere near the 15+ second loads.  After I brought this to their attention, the authors worked to improve their plugins (they were both new custom plugins, so I was basically testing them on a live site), so that's all good now.  I'm still seeing some slow page loads in areas, and I filed a support ticket on one that I'm able to reproduce consistently (and without any plugins enabled).

Thanks again for your advice,

 ..Al

[Thomas P]

16 hours ago, AtariAge said:

I will certainly look into the changes Adlago suggested, I just don't feel they are going to make such a significant impact overall. 

They do, believe him. 😉

Quote

This is because lack of gzip compression create a large html file for download - and this slows loading site.
Also you do not use leverage browser cache - it also slows the loading site.
The h2 protocol will make a parallel download of JS and CSS which will speed up your site.
Do these three things and you will see a difference.

 

[AtariAge]

I've made several changes to the server to help with performance, and I think I've made some good progress on that front:

• Switched from php-cgi to php-fpm.  This made a huge difference in the server load, which is now sitting at 1.0 or less on average.  Previously it would often be sitting above 3.0.  However, when I first did this, Apache would quit pretty quickly.  I had to make update several Apache settings to get it stable (mainly MaxClients to 2500 and MaxRequestWorkers to 2000.  Also had to increase the PHP-FPM pool options a bit). 
• Installed PHP OPCache extension.  Have not modified any of the default settings.
• Setup Elasticsearch, which is running on the web server versus the standalone database server.  Elasticsearch data is sitting on a fast SSD NVMe drive, and I get 10,000 results back instantly, and the results look better as well. This should help reduce load on the database server.
• Invision discovered that several of my database tables, including large tables like the posts, topics and messages tables, were using MyISAM instead of InnoDB.  Which is extremely strange, since the default-storage-engine is set to InnoDB, and all the other tables were InnoDB (there were about ten set to MyISAM).  I have no idea at all how that happened, but I converted them to InnoDB. I was a bit shocked to see those tables were MyISAM.

Gzip compression was already enabled, so no need to do anything there.  I'm also using Redis.  And it looks like the browser is caching CSS and JS just fine.  Two additional things I still need to do:

• Investigate and setup HTTP/2.
• Replace MySQL 5.7 with MariaDB 10.2 or 10.3.

Open to any other suggestions as well.

Thank you,

 ..Al