Incredibly slow reputation query on expanded forum view

[TSP]

After an upgrade to 4.5 we experienced a significant number of queries against our reputation index table that slowed our performance. A number of users also started reporting that they could no longer view any forums, as they would never load. 

Please reply to my summary in the bottom of this post. 

Below you can see both an example of a very slow query and the explain result for that query. As you can see it needs to do a where-statement on more than 13 million rows. 

SELECT * FROM `ibf_core_reputation_index` AS `core_reputation_index` WHERE rep_class='IPS\\forums\\Topic\\Post' AND ( item_id 
IN(1427817,1422962,1256153,1427714,1283836,1427156,1326714,1102313,1069811,1284132,1234402,914294,1315147,1424425,1262919,1296796,1292774,1402927,1260889,886634,1419146,1424086,1424395,1264620,1174675) );

+----+-------------+-----------------------+------------+------+-----------------------+-----------+---------+-------+----------+----------+-------------+
| id | select_type | table                 | partitions | type | possible_keys         | key       | key_len | ref   | rows     | filtered | Extra       |
+----+-------------+-----------------------+------------+------+-----------------------+-----------+---------+-------+----------+----------+-------------+
|  1 | SIMPLE      | core_reputation_index | NULL       | ref  | rep_class,leaderboard | rep_class | 403     | const | 13110152 |    50.00 | Using where |
+----+-------------+-----------------------+------------+------+-----------------------+-----------+---------+-------+----------+----------+-------------+

 

Upon investigating I found the culprit to be the new expanded view mode in forums for the topic listing. 

When the expanded view mode is selected you set $getReactions to true and call the "Content Table Helper". Line 90 in applications/forums/modules/front/forums/forums.php:

$getReactions = $getFirstComment = $getFollowerCount = (boolean) ( \IPS\forums\Forum::getMemberListView() === 'snippet' );
		
/* Init table (it won't show anything until after the password check, but it sets navigation and titles) */
$table = new \IPS\Helpers\Table\Content( 'IPS\forums\Topic', $forum->url(), $where, $forum, NULL, 'view', isset( \IPS\Request::i()->rss ) ? FALSE : TRUE, isset( \IPS\Request::i()->rss ) ? FALSE : TRUE, NULL, $getFirstComment, $getFollowerCount, $getReactions );

 

In system/Helpers/Table/Content.php you run the following query on line 409:

foreach( \IPS\Db::i()->select( '*', 'core_reputation_index', array( 'rep_class=? AND ' . \IPS\Db::i()->in( 'item_id', $itemIds ), $class::$commentClass ) ) as $react )
{
	$reactions[ $react['item_id'] ][] = $react;
}

 

You do this in order to show this reaction-thingy in the bottom corner of the first post when the expanded view mode is selected: 

 

Summary and concerns: 

It seems to be intentional that you want to aggregate which reactions have been used within the topic. At first I thought the reactions were connected to the first post, but it's not and upon clicking on it, it makes a topic request with extra parameters. For example /?do=showReactions&reaction=3&item=1

I would argue it's not at all obvious that it refers to an aggregate of reactions across the entire item. 

Either way, you seem to have chosen the worst possible method to get that aggregate data for a very small thing. 

Your first way of attempting to solve this would maybe be to add yet another index on the core_reputation_index table. One index that might improve the situation could be aggregate_item_lookup (rep_class, item_id, reaction). And also change the query to do a distinct query or grouped count query on those columns to figure out which reactions have been used within the topic.

BUT, even with such an index the query would probably still be problematic for us. We have more than a 1000 topics in total with more than 1000 posts in them, of which 128 of them have been active during the last 30 days. 

In any case, this small bit of extra information in an alternate viewing mode is causing a lot more overhead against the database table than it's worth. In my opinion it would be best to simply choose to let this like information be tied to only the first post or remove this bit of information alltogether. 

[Guest]

On 11/24/2020 at 1:30 AM, TSP said:

A number of users also started reporting that they could no longer view any forums, as they would never load. 

This is also happening to me and my members! My site is throwing a ton of 520 or 524 errors. I get them when I try to "edit" a post or go to my private messages, for example. 

[CoffeeCake]

15 minutes ago, breatheheavy said:

My site is throwing a ton of 520 or 524 errors.

This is Cloudflare. Your web site is taking too long to respond to requests, and Cloudflare shows that error after 100 seconds by default.

https://support.cloudflare.com/hc/en-us/articles/115003011431-Troubleshooting-Cloudflare-5XX-errors

You can turn off the setting to allow for expanded forum views (and get rid of this problem @TSP is highlighting here) by going to ACP > Community > Forums > Settings and make the following setting changes:

• Default topic list view: Condensed view
• Members can choose? (Choose whether you wish to allow your members to toggle between the views. When set to 'no', members will always use the default topic list view.😞 No

[Guest]

3 minutes ago, Paul E. said:

This is Cloudflare. Your web site is taking too long to respond to requests, and Cloudflare shows that error after 100 seconds by default.

https://support.cloudflare.com/hc/en-us/articles/115003011431-Troubleshooting-Cloudflare-5XX-errors

You can turn off the setting to allow for expanded forum views (and get rid of this problem @TSP is highlighting here) by going to ACP > Community > Forums > Settings and make the following setting changes:

• Default topic list view: Condensed view
• Members can choose? (Choose whether you wish to allow your members to toggle between the views. When set to 'no', members will always use the default topic list view.😞 No

Thanks for the reply! I changed the settings but nothing changed regarding my site's loading issues unfortunately. 😕 

It happens when I try to "edit" a post or see my inbox for example. 

[CoffeeCake]

1 minute ago, breatheheavy said:

It happens when I try to "edit" a post or see my inbox for example.

This is probably an unrelated issue then. You might want to open a support request.

[Guest]

9 minutes ago, Paul E. said:

This is probably an unrelated issue then. You might want to open a support request.

Will do, thanks!

[bfarber]

Thanks for the feedback. I've raised your concern internally for further discussion.