XF 2.1 346 queries on index

[Brettflan]

For some reason even with all addons disabled, our staging forum is currently making around 346 queries on loading the forum index. This number of queries is basically the same either logged in on one browser as an admin and logged out in another browser. My test forum on a local VM with nearly the same setup only needs around 21 queries on the index page.

Spoiler: Example query log from #150 - #155

• SELECT cache_value
  FROM xf_permission_cache_content
  WHERE permission_combination_id = ?
  AND content_type = ?
  AND content_id = ?
  Params: 547, node, 295
  Run Time: 0.000045
  

  Select Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
  SIMPLE								Impossible WHERE noticed after reading const tables

  2. XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 94
  3. XF\Db\AbstractAdapter->query() in src/XF/Db/AbstractAdapter.php at line 150
  4. XF\Db\AbstractAdapter->fetchOne() in src/XF/PermissionCache.php at line 73
  5. XF\PermissionCache->getContentPerms() in src/XF/PermissionSet.php at line 51
  6. XF\PermissionSet->hasContentPermission() in src/XF/Entity/User.php at line 936
  7. XF\Entity\User->hasNodePermission() in src/XF/Entity/AbstractNode.php at line 31
  8. XF\Entity\AbstractNode->canView() in src/XF/Entity/Node.php at line 52
  9. XF\Entity\Node->canView() in src/XF/Mvc/Entity/AbstractCollection.php at line 320
  10. XF\Mvc\Entity\AbstractCollection->XF\Mvc\Entity\{closure}()
  11. array_filter() in src/XF/Mvc/Entity/AbstractCollection.php at line 189
  12. XF\Mvc\Entity\AbstractCollection->filter() in src/XF/Mvc/Entity/AbstractCollection.php at line 321
  13. XF\Mvc\Entity\AbstractCollection->filterViewable() in src/XF/Repository/Node.php at line 144
  14. XF\Repository\Node->filterViewable() in src/XF/Repository/Node.php at line 20
  15. XF\Repository\Node->getNodeList() in src/XF/Pub/Controller/Forum.php at line 50
  16. XF\Pub\Controller\Forum->actionList() in src/XF/Mvc/Dispatcher.php at line 321
  17. XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 248
  18. XF\Mvc\Dispatcher->dispatchFromMatch() in src/XF/Mvc/Dispatcher.php at line 100
  19. XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 50
  20. XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2177
  21. XF\App->run() in src/XF.php at line 390
  22. XF::runApp() in index.php at line 20

• SELECT cache_value
  FROM xf_permission_cache_content
  WHERE permission_combination_id = ?
  AND content_type = ?
  AND content_id = ?
  Run Time: 0.000042

• SELECT cache_value
  FROM xf_permission_cache_content
  WHERE permission_combination_id = ?
  AND content_type = ?
  AND content_id = ?
  Run Time: 0.000041

• SELECT cache_value
  FROM xf_permission_cache_content
  WHERE permission_combination_id = ?
  AND content_type = ?
  AND content_id = ?
  Run Time: 0.000041

• SELECT cache_value
  FROM xf_permission_cache_content
  WHERE permission_combination_id = ?
  AND content_type = ?
  AND content_id = ?
  Run Time: 0.000042

• SELECT cache_value
  FROM xf_permission_cache_content
  WHERE permission_combination_id = ?
  AND content_type = ?
  AND content_id = ?
  Run Time: 0.000041

The vast majority of the queries are like those above, "SELECT cache_value FROM xf_permission_cache_content". Without addons in the picture, and with a nearly identical test forum setup needing a much more reasonable ~21 queries, I'm wondering why this one needs such a huge number of queries, apparently related to permissions, regardless of whether it's a guest or logged in admin user.

Any ideas why it's making so many permission queries?

 

[Snog]

I've run that and every other option in "Rebuild caches" to no effect

Have you tried going to the site's install system and rebuilding master data?

That does rebuild the permissions. But I've never looked at exactly what it does when it rebuilds permissions.

 

[Brettflan]

You can check it by disable the listener in the config. I think you will see the quires will decrease. If so, then must be a buggy addon which making extra quires.

If you using custom theme then try to use the default theme. There is possibly custom theme making unnecessary quires.

As per earlier in the thread, those have been covered with no effect. It's using the default theme, with no template modifications.

Have you tried going to the site's install system and rebuilding master data?

That does rebuild the permissions. But I've never looked at exactly what it does when it rebuilds permissions.

OK. I'd tried that quite a while back on the main server, and I'd run the "xf:rebuild-master-data" PHP command a few days ago on this test forum, which I think does essentially the same thing.
Just tried "Rebuild master data" again through the install system, no effect other than it being back to 370 queries on the first load of the forum index, then back to 367 queries on the second load after it caches whatever was in those 3 queries.

 

[Snog]

OK. I'd tried that quite a while back on the main server, and I'd run the "xf:rebuild-master-data" PHP command a few days ago on this test forum, which I think does essentially the same thing.
Just tried "Rebuild master data" again through the install system, no effect other than it being back to 370 queries on the first load of the forum index, then back to 367 queries on the second load after it caches whatever was in those 3 queries.

I wish I could be of more help. I've seen this happen for existing nodes, but never for non-existent nodes.

The only thing I can think of that might cause it is some kind of ungodly database query cache that's retaining the old nodes and returning that old info to XF when it gets the node list. That's assuming you've uninstalled all add-ons and disabled any template modifications that have been done.

 

[Snog]

One thing that you can check if you have debug/development enabled is the XF node query.

What's returned?


If it says more than 2 rows in your case, something is REALLY wrong.

 

[Brettflan]

Interesting, there are still 364 rows in the xf_node table, all the old categories and forums, so I'd say there is indeed something very wrong here. The other nodes were all deleted through the admin panel, with no errors given.
How do they still exist in the database there but not show up anywhere? The node list in the admin panel only shows the remaining 1 category and 1 forum under it. And none of the "Rebuild caches" methods do anything to fix it. And regardless of all of those nodes still existing in the database, I still don't understand how they can result in that many queries on the forum index.

Since the original forum that is having this problem is just a temporary staging forum, and won't be used for anything other than a reference for various settings and layout when the real forum is converted over from SMF, this whole situation isn't in itself a big problem.
However, if something like this happens again with the real forum later, with the query count on the forum index ballooning out of control and no way to fix it, this could turn into a real problem. I'd definitely like to work out how to fix it before then.

 

[Jake B.]

Interesting, there are still 364 rows in the xf_node table, all the old categories and forums, so I'd say there is indeed something very wrong here. The other nodes were all deleted through the admin panel, with no errors given.

Sounds like there could be an add-on that touches deletion of nodes that broke part of it

How do they still exist in the database there but not show up anywhere? The node list in the admin panel only shows the remaining 1 category and 1 forum under it. And none of the "Rebuild caches" methods do anything to fix it. And regardless of all of those nodes still existing in the database, I still don't understand how they can result in that many queries on the forum index.

All nodes reside in multiple tables, depending on the type. For example, a forum node will be in xf_node and xf_forum, the xf_node table contains all of the base meta information (name, description, etc) while the xf_forum table contains things specific to forums. For some reason when you've deleted your nodes through the admin panel it likely only deleted them from xf_forum (or xf_category, xf_page, etc depending on node type). Why exactly that happened it'd be hard to tell. Could you provide a list of your add-ons? Maybe one will stand out as having interefered with that process.

 

[Brettflan]

List of addons:

[TH] User Improvements 1.3.0 Patch Level 8
Developer: ThemeHouse Support
[XD] Collapsible Sections 1.0.0
Developer: XDinc Support
[XTR] Featured Threads Slider 1.1.3
Developer: XENTR | XenForo Add-ons - Styles Support
Ban thread 1.4
Developer: XF2 Addons
BlackTea/SteamAuth 1.7.9
Developer: Studio70 FAQ Support
DL6 - Tag Cloud Widget 1.0.0
Developer: DL6
Editor & BB Code Manager 1.2.0 Patch Level 2
Developer: Lukas W. FAQ Support
Forum moderators 1.4
Developer: XF2 Addons
Moderator Essentials 2.2.8
Developer: Atelier Aphelion Support
Multi Prefix 2.5.9
Developer: Xon Support
Question Threads 2.1.2
Developer: CMTV My addons & tutorials Support me!
Remove quick thread 1.2
Developer: XF2 Addons
Shadow ban posts 1.4
Developer: XF2 Addons
Social Groups 2.1.12
Developer: Snog
Time Spent Online 2.0.7 (slightly modified by me for 2.1 compat)
Developer: Yugensoft
Warning Improvements 2.2.10
Developer: Xon Support
XenForo Enhanced Search 2.1.1
Developer: XenForo Ltd. FAQ Support

We also were using the portal demo from the official XF developer documentation on how to create an addon (not sure if we disabled it before or after most of the node deletion), and a couple of minor addons I've created, neither of which touches anything related to nodes at all.

 

[Snog]

Just to rule out the Social Groups add-on...

Did you have any groups created that had forums and if yes, how many groups with forums were there?

I'm asking because the group system doesn't touch any other nodes except for those that are a part of groups.

 

[Snog]

The question that needs to be answered is why are permission combinations for nodes that don't exist being called in the first place.

Unless, if when you look at the node table the nodes really do exist and for some reason they aren't being displayed in the forum or admin area.

Either way, I really think this is beyond help in the forum and you should open a ticket with XenForo if you haven't already.

 

[Brettflan]

Just to rule out the Social Groups add-on...

Did you have any groups created that had forums and if yes, how many groups with forums were there?

I'm asking because the group system doesn't touch any other nodes except for those that are a part of groups.

No, to my knowledge most of the now phantom nodes were deleted before your addon was even installed. So I don't think it had any bearing on it.

Either way, I really think this is beyond help in the forum and you should open a ticket with XenForo if you haven't already.

Yeah, I still do need to open a ticket.

 

[Joe Link]

@Brettflan did you ever get this figured out?

 

[Brettflan]

This showed up on a test forum converted over from SMF where a lot of content had been deleted through the admin panel, and it's possible an addon caused it. However, when we later recreated the converted test forum and did pretty much the same things, the problem didn't show back up. It also didn't show up on our real forum when we converted it, so I still don't know what the problem was. Other than it probably being caused by a huge amount of deleted content (nodes, posts, etc) and likely an addon having somehow prevented that content from being properly deleted.

We did later have a similar problem with inflated query count caused by an interaction between the Optimized List Queries addon and the "[tl] Social Groups" addon, which ended up being caused by a badly optimized bit of code in the "[tl] Social Groups" addon. That was fixed in that addon over a year ago, though.

 

[Joe Link]

Good to hear! This thread got me looking around and I see a couple issues I'm going to have to research.