Forum has slow loading, is this high cpu usage?

ShinLim

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

1573661342948.png

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

Here is a screenshot from top command from CLI

1573660757130.png

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
 
Last edited:

MySiteGuy

Formerly ftsservice
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)?
 

ShinLim

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

Xon

Well-known member
MariaDB 10.4.x has major performance regressions when running XenForo.

Please run this SQL;
SQL:
set global optimizer_use_condition_selectivity=1;
Wait a few minutes then if performance doesn't fully improve run this;
SQL:
set global join_cache_level=0;
Update your my.cnf (or relevant file) to add;
Code:
join_cache_level=0
optimizer_use_condition_selectivity=1
(optimizer_use_condition_selectivity=1 may be enough)
 
Last edited:

Kirby

Well-known member
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

Well-known member
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

Active member
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

1573960685422.png

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
 
Last edited:

Xon

Well-known member
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

Member
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.
 

Xon

Well-known member
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.
 
Top