known issue Inefficient Forum Listings

[Sly_Ripper]

Continuing from this topic:

The board index (and I assume any forum listing) grabs every row from forums_forums, this could be 20k sub-forums. The board index should only grab nodes/categories with a parent ID of -1 and then any forums with those results as a parent, and sub-forums of that parent. The only reason I can think of that it's not done this way is to provide the correct "last post" info for the user's permissions. It would be nice to have an option to just limit the "last post" depth to 1 sub-forum deep and have a more performant forum.

Edited July 3, 2023 by Sly_Ripper

[Jim M]

2 hours ago, Sly_Ripper said:

this could be 20k sub-forums.

Do you have a use case which would warrant 20k sub-forums? Think that is an insanely large amount that wouldn't be commonplace.

[Sly_Ripper]

2 minutes ago, Jim M said:

Do you have a use case which would warrant 20k sub-forums? Think that is an insanely large amount that wouldn't be commonplace.

Basically, we create a sub-forum for every PlayStation game so topics stay organised. Similar to how every game on Steam has it's own sub-forum: https://steamcommunity.com/discussions/#games

[Sly_Ripper]

I considered attaching the sub-forums to clubs but forums that are part of a club are still loaded on index/forum views.

An Invision install with clubs enabled are possibly prone to this slowdown, I don't know if the club rows are evaluated or not.

Edited July 3, 2023 by Sly_Ripper

[Sly_Ripper]

@Jim M is there any chance the performance of this is improved in version 5?

[Marc]

27 minutes ago, Sly_Ripper said:

@Jim M is there any chance the performance of this is improved in version 5?

Performance of almost every area has been improved in version 5. However this wouldnt mitigate what was mentioned above. 20k forums is simply not a normal use case unfortunately

[Randy Calvert]

If you're going to list 20k forums, you're talking about a page that would scroll FOREVER and a bad user experience.  

[SoloInter]

@Sly_Ripper Can you please share your link to the board who show 20K sub forums ?

[Sly_Ripper]

On 6/25/2024 at 3:33 PM, Marc Stridgen said:

Performance of almost every area has been improved in version 5. However this wouldnt mitigate what was mentioned above. 20k forums is simply not a normal use case unfortunately

Clubs allow forums to be added by users and as I posted earlier, they are also loaded on forum index. If clubs were enabled here, I could slowdown the entire forum.
 

On 6/25/2024 at 4:22 PM, Randy Calvert said:

If you're going to list 20k forums, you're talking about a page that would scroll FOREVER and a bad user experience.  

See below

On 6/25/2024 at 4:27 PM, SoloInter said:

@Sly_Ripper Can you please share your link to the board who show 20K sub forums ?

We don’t allow the listing of the sub-forums due to the performance.

https://forum.psnprofiles.com/

[Marc]

15 hours ago, Sly_Ripper said:

Clubs allow forums to be added by users and as I posted earlier, they are also loaded on forum index. If clubs were enabled here, I could slowdown the entire forum

In practical terms if someone can sit and add 20 thousand subforums to a club before its been moderated, I would argue that performance is the least of your issues

[Sly_Ripper]

5 hours ago, Marc Stridgen said:

In practical terms if someone can sit and add 20 thousand subforums to a club before its been moderated, I would argue that performance is the least of your issues

It can be automated.

At least prevent club forums from being loaded into memory on any page not requiring them? I can work around that and tie the sub-forums to clubs.

[Marc]

It can indeed, I agree. But anyone wanting to slow your site down through automation, that isnt being stopped by your server, if going to be causing you more issues than just slowing things down

[Sly_Ripper]

1 minute ago, Marc Stridgen said:

It can indeed, I agree. But anyone wanting to slow your site down through automation, that isnt being stopped by your server, if going to be causing you more issues than just slowing things down

It’s 1 request every 2 seconds for 12 hours. This will easy avoid any rate limits  and any mods not checking every club multiple times a day.

This isn’t the point.

The point is this is a legitimate use case. The way the index loads all forums, even those in clubs is inefficient. The easiest solution is only load forums not part of a club, and on club pages only load the ones in that club.

An intern can optimise this.

[Jim M]

4 hours ago, Sly_Ripper said:

The point is this is a legitimate use case.

We do not believe 20 thousand sub-forums is a legitimate use of the software, even in the case of malicious intent. Millions of possibilities can happen in our software and all our clients use our software differently. However, it does not mean we can factor in each and every one, even when it varies the current operation of our software which is intend by our thousands of clients.

[Sly_Ripper]

16 hours ago, Jim M said:

We do not believe 20 thousand sub-forums is a legitimate use of the software, even in the case of malicious intent. Millions of possibilities can happen in our software and all our clients use our software differently. However, it does not mean we can factor in each and every one, even when it varies the current operation of our software which is intend by our thousands of clients.

Imagine Steam wanting to convert their forum to Invision and your reply is “sorry, that’s not legitimate use.”

[Marc]

33 minutes ago, Sly_Ripper said:

Imagine Steam wanting to convert their forum to Invision and your reply is “sorry, that’s not legitimate use.”

I have to admit to being a little confused by both your response there.You mention steam, however they would indeed be an unusual case if they were using a community to categories the games on their site. It would very likely be customised for their specific use case, as we have for many enterprise level clients. 
 

There are performance improvements in almost every release, however you are currently still using a release which is 5 versions behind, despite one of those releases (4.7.15) stating specifically in the release notes that that there are performance improvements for mysql 8 (of which you should be using if you want to ensure the best performance on your site)

In addition to the 5 versions you have not yet upgraded to, you asked about version 5 there, which was responded to by myself.

Quote

Performance of almost every area has been improved in version 5. However this wouldnt mitigate what was mentioned above. 20k forums is simply not a normal use case unfortunately

So, yes there have been improvements in performance in almost every area of the platform for version 5, even if I have still reminded you there that 20k forums is simply not a normal use case. 

I understand your frustration, however continually stating your point in different manners there will not change he reality in any way. And when items being mentioned are also skipped over, it won't really do you any favours. 

[Sly_Ripper]

36 minutes ago, Marc Stridgen said:

I have to admit to being a little confused by both your response there.You mention steam, however they would indeed be an unusual case if they were using a community to categories the games on their site. It would very likely be customised for their specific use case, as we have for many enterprise level clients. 
 

There are performance improvements in almost every release, however you are currently still using a release which is 5 versions behind, despite one of those releases (4.7.15) stating specifically in the release notes that that there are performance improvements for mysql 8 (of which you should be using if you want to ensure the best performance on your site)

In addition to the 5 versions you have not yet upgraded to, you asked about version 5 there, which was responded to by myself.

So, yes there have been improvements in performance in almost every area of the platform for version 5, even if I have still reminded you there that 20k forums is simply not a normal use case. 

I understand your frustration, however continually stating your point in different manners there will not change he reality in any way. And when items being mentioned are also skipped over, it won't really do you any favours. 

We have one sub-forum for discussion for each game, this is as expected from a forum.

None of the releases fix this issue. MySQL 8 does not fix this issue as the issue is the queries used which loads every forum in the DB into memory to be processed by PHP. (Even club forums when you view the forum index.)

This forum has the same issue but they’ve used your Pages app to render your forum code with the queries fixed. Which means keeping it updated for every release.

All I’m asking for now is that sub-forums in clubs not be loaded on forum index for the main listing (and they’re possibly loaded to process breadcrumbs in topics.) Along with only loading sub-forums of a club that belong to that club. This would allow me to work around the main performance issue without you having to make any invasive changes.

[Jim M]

10 minutes ago, Sly_Ripper said:

All I’m asking for now is that sub-forums in clubs not be loaded on forum index for the main listing

This is already a Club setting:

[Sly_Ripper]

22 hours ago, Jim M said:

This is already a Club setting:

 

Even with this enabled, the queries still pull ALL forums into memory ('club_id IS NULL' is needed). Can this be considered a bug now?

SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_forums.id  LEFT JOIN `core_members` ON core_members.member_id=forums_forums.last_poster_id WHERE (sub_can_post=0 OR min_posts_view<=21) AND (( FIND_IN_SET(3,perm_view) OR FIND_IN_SET(4,perm_view) OR FIND_IN_SET('m1',perm_view) OR FIND_IN_SET('ca',perm_view) OR FIND_IN_SET('cm',perm_view) OR FIND_IN_SET('c1',perm_view) OR FIND_IN_SET('cm1',perm_view) ) OR perm_view='*' ) ORDER BY position

 

All permissions are loaded:

SELECT perm_type_id FROM `core_permission_index`  STRAIGHT_JOIN `forums_forums` ON forums_forums.min_posts_view<=22 AND core_permission_index.perm_type_id=forums_forums.id WHERE core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND (( FIND_IN_SET(3,perm_2) OR FIND_IN_SET(4,perm_2) OR FIND_IN_SET('m1',perm_2) OR FIND_IN_SET('ca',perm_2) OR FIND_IN_SET('cm',perm_2) OR FIND_IN_SET('c1',perm_2) OR FIND_IN_SET('cm1',perm_2) ) OR perm_2='*' )

 

Edited June 29 by Sly_Ripper

[Sly_Ripper]

Additionally, when viewing a topic, all forums are loaded into memory:

SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_forums.id  LEFT JOIN `core_members` ON core_members.member_id=forums_forums.last_poster_id WHERE (sub_can_post=0 OR min_posts_view<=22) AND (( FIND_IN_SET(3,perm_view) OR FIND_IN_SET(4,perm_view) OR FIND_IN_SET('m1',perm_view) OR FIND_IN_SET('ca',perm_view) OR FIND_IN_SET('cm',perm_view) OR FIND_IN_SET('c1',perm_view) OR FIND_IN_SET('cm1',perm_view) ) OR perm_view='*' ) ORDER BY position

 

[Marc]

On 6/29/2024 at 1:40 PM, Sly_Ripper said:

 

Even with this enabled, the queries still pull ALL forums into memory ('club_id IS NULL' is needed). Can this be considered a bug now?

SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_forums.id  LEFT JOIN `core_members` ON core_members.member_id=forums_forums.last_poster_id WHERE (sub_can_post=0 OR min_posts_view<=21) AND (( FIND_IN_SET(3,perm_view) OR FIND_IN_SET(4,perm_view) OR FIND_IN_SET('m1',perm_view) OR FIND_IN_SET('ca',perm_view) OR FIND_IN_SET('cm',perm_view) OR FIND_IN_SET('c1',perm_view) OR FIND_IN_SET('cm1',perm_view) ) OR perm_view='*' ) ORDER BY position

 

All permissions are loaded:

SELECT perm_type_id FROM `core_permission_index`  STRAIGHT_JOIN `forums_forums` ON forums_forums.min_posts_view<=22 AND core_permission_index.perm_type_id=forums_forums.id WHERE core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND (( FIND_IN_SET(3,perm_2) OR FIND_IN_SET(4,perm_2) OR FIND_IN_SET('m1',perm_2) OR FIND_IN_SET('ca',perm_2) OR FIND_IN_SET('cm',perm_2) OR FIND_IN_SET('c1',perm_2) OR FIND_IN_SET('cm1',perm_2) ) OR perm_2='*' )

 

Thank you for bringing this issue to our attention! I can confirm this should be further reviewed and I have logged an internal bug report for our development team to investigate and address as necessary, in a future maintenance release.