Forum has slow loading, is this high cpu usage?

[ShinLim]

Here is the number of user accessing my forum at that time:



It's pretty lag, slow loading in every page, i don't know why

Here is a screenshot from top command from CLI



I'm running server has 8GB RAM and 4 CPU and using centos 7 x64

Anyone could help me with this? I would really appreciate

 

[Kerstin28]

The numbers indicate that you need more CPU power.

If you haven't already, upgrade to PHP 7 and enable the PHP opcache.

 

[MySiteGuy]

455 is not a problem at all with that kind of server - if it's dedicated. I've done more with less. First of all, the ST column number indicates it's likey a virtual machine - steal time is almost always 0 on a dedicated machine, or much, much lower. Might be due to another VM on the box.

What are the specs, so far as PHP version goes, if opcache is turned on, and database stats? Do you have addons that might have an excessive query count for the forum home (as well as not properly indexed queries)?

 

[rdn]

This is GA Real Time visitors right?
With that stats either implement Guest Full Page Caching or upgrade the server to 6 or 8 CPU.

 

[ShinLim]

FYI: i'm using vps server, and it seems like my website often get this slow loading in a certain time frame

 

[ShinLim]

With that stats either implement Guest Full Page Caching

This is why I sent you inbox @Xon

 

[rdn]

This is why I sent you inbox @Xon

Why do you need him?
Default xenforo can do full page caching already.

 

[ShinLim]

Default xenforo can do full page caching already.

Because I'm using his add-on, which is https://xenforo.com/community/resources/redis-cache-by-xon.5562/

 

[Xon]

What MySQL/MariaDB version?

 

[ShinLim]

What MySQL/MariaDB version?

You can see this

 

[Xon]

MariaDB 10.4.x has major performance regressions when running XenForo.

Please run this SQL;

set global optimizer_use_condition_selectivity=1;

Wait a few minutes then if performance doesn't fully improve run this;

set global join_cache_level=0;

Update your my.cnf (or relevant file) to add;

join_cache_level=0
optimizer_use_condition_selectivity=1

(optimizer_use_condition_selectivity=1 may be enough)

 

[Kirby]

MariaDB 10.4.x has major performance regressions when running XenForo.

Hmm ... we are running MariaDB 10.4 with XF 2.1 and 1.5 since quite tome time and I havn't noticed anything major, in fact performance has improved a bit.

What exact issues did you notice?

 

[Xon]

Hmm ... we are running MariaDB 10.4 with XF 2.1 and 1.5 since quite tome time and I havn't noticed anything major, in fact performance has improved a bit.

What exact issues did you notice?

Quite a few;

• Viewing a forum thread lists went from 0.003 seconds to 3 seconds
  • If you have a large table join to a vastly smaller table performance tanks.
  • Simply having Collaborative Threads add-on installed causes catastrophic performance loss due to 1 extra join
  • MultiPrefix tickles the behavior as well
  • Stock XenForo can cause this 3 orders of magnitude performance loss if the size difference between the xf_thread and xf_thread_post table is large enough.
• Post Rating + XenForo 1.x causes massive performance issues when viewing threads
  • Client's MariaDB instance was using 500% CPU instead of 20% CPU, before the entire site would grind to a halt.
• I have a query which causes Mariadb 10.4.x to crash, instantly.

I dumped the database and downgraded to 10.3.x as the reliable fix for https://forums.spacebattles.com/

 

[ShinLim]

I dumped the database and downgraded to 10.3.x

So should I downgrade to 10.3 or should I use "optimizer_use_condition_selectivity=1"
I'm not an expert in coding so I made a search "how to downgrade mariadb" on google but it seems there's no complete tutorial to do that, in mariadb's homepage they also said this



Atm, when i save a template like Page_container, it took more than 15-20 seconds to load a page after I save that template (I've already run sql query
set global optimizer_use_condition_selectivity=1; )

Before this thread I also added "join_cache_level=0" to my.cnf file but no use

 

[Xon]

You need to run set global join_cache_level=0; as well and give connections time to finish and be restarted. Just putting it in the my.cnf only matters once MySQL server is restarted

 

[Dretax]

I have 10.3.18, the issue just started to roll up a couple of days ago for some reasons.
SELECT xuca.*, xsgu.game_id FROM xf_user_connected_account AS xuca LEFT JOIN xf_ste

Seems so that the steam addon is causing this issue. mhm. I used
optimizer_use_condition_selectivity for now, we will see what happens.

 

[Dretax]

I still suffer this issue, and I have no idea why.

 

[Xon]

Try updating to MariaDB 10.3.21, there are some query performance regression fixes in as 10.3.18 had some bugs backported to it.

 

[Dretax]

Try updating to MariaDB 10.3.21, there are some query performance regression fixes in as 10.3.18 had some bugs backported to it.

Will give it a go.