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?

 

[SyTry]

Hello,

Did you try to rebuild your caches ?

Regards, SyTry

 

[Brettflan]

I've tried the 5 "xf-rebuild" commands from the command line along with "xf-dev:rebuild-caches" and "xf:rebuild-master-data", and in the admin panel under "Rebuild caches" I've also done "Clean up permissions". It's currently hovering around 350 queries. Any other caches that might be relevant, or other ideas?

 

[SyTry]

Hum, you may ask @Chris D

 

[Brettflan]

Anyone have other ideas? This is for a very large forum, so having so many queries on a simple page load would be a problem.

The queries listed from dev mode don't have the actual values listed, hiding them with "?"; is there some way I could have those values shown? I might be able to track down why there are so many permission queries by correlating those values in the database.

 

[sbj]

Are you using any addons? Disable all of them and see if that makes a difference.

 

[Brettflan]

Tried that. They seem to add something around 4 queries, the difference between 346 and 350 queries.

 

[sbj]

I would duplicate my site on a test environment (localhost) and then make some tests. Disable all addons first, then delete node by node to see what node causes the problem on the index page. Maybe delete widgets aswell if they are displayed on index page. Everything what you see on the index page, maybe ads?

How many nodes and usergroups have you btw?

 

[Snog]

Also, if you've made any template changes try using the default (unmodified) XF style and see if the result is the same.

If some changes were made incorrectly to templates (where the change expects something that isn't available to the template), that could trigger database queries to meet the expectations of the template changes.

 

[Brettflan]

Thanks for the suggestions, I'll give those a shot when I get a chance and post back.
We currently have 63 nodes and 22 user groups in this staging forum. There are about the same number of user groups, and a lot more nodes (rather than fewer), in the similar test VM forum which is showing the more reasonable ~21 queries per forum index page load.

 

[Brettflan]

OK, I've made a copy of the site and done further testing on the copy. I deleted all but a single category and a single forum under it, with only 2 threads and 4 posts left. I deleted all but 4 user groups. I deleted all but 2 users. I disabled all widgets which displayed on the forum index. I went through and deleted and disabled a bunch of other stuff in the admin panel as well, including a couple of extra navigation entries.
I went the extra step of uninstalling every addon, not just disabling them. There are no template modifications, and it is using the Default style (others have been deleted). There are no ads.
I then ran every Cron job for updating various stuff, and ran everything on the "Rebuild caches" page. I also ran all 6 command-line rebuild options.

Somehow, the forum index is now up to 371 queries, with just a single category and forum, whether logged in as a super admin or logged out as a guest.

I did find that when I added a custom secondary index page (on the original staging forum, not the test copy of it) set to display 3 specific categories, with 10 forums and 3 child forums under those categories, the number of queries on that page went up to 52, so there does seem to be some clear correlation there with the number of nodes displayed. And yet on the test copy of the site, after deleting down to 1 category and 1 forum, the forum index is at 371 queries somehow.

Anyone have any other ideas? I can provide a copy of the database if anyone else wants to take a crack at figuring it out.

 

[imno007]

I don't have the answer for you, sorry, but I can tell you it's not because of the number of nodes. I have dozens of addon's installed, some that also display on the forum index, and I have over a hundred nodes, and I still only have 54 queries on my index.

 

[sbj]

I went the extra step of uninstalling every addon, not just disabling them.

I believe there still can be faulty addons which can leave faulty code, even after uninstalling them.

The last thing which comes to my mind is to disable config options 1 by 1, specially the enableListeners one, to be sure that no addon has left faulty code.

Config.php options | Manual | XenForo

End-user documentation for XenForo

xenforo.com

Set each of them 1 by 1 to false and check the results. Although I don't think it should make a difference, but just to be sure. You should also restart the server after each edit (if localhost, just start and stop the server).

Anyone have any other ideas? I can provide a copy of the database if anyone else wants to take a crack at figuring it out.

Well other than that, if you have an active license, you can create a ticket to ask XF if they can help you out. Slavik from the XF team is a specialist in importing stuff, so anything what has to do with databases, he should be able to figure it out.

Or hire someone from outside to take a look into this. There is a section here on XF for that kind of stuff. I am not sure how free he is, but when it comes to queries, @Xon is the first one who comes to my mind. He is super busy though and I am not sure if he can be hired (and his rates should be high).

You need some developer to track down the queries for you.

 

[Xon]

@Brettflan I'ld recommend ensuring you have caching enabled (via memcache or redis). You get much higher query counts with it enabled.

Do you have any widgets setup which are pulling threads/posts?

Try my (free) addon;Optimize List Queries it tweaks a few things and if it works then probably need to report an XF bug.

 

[Brettflan]

I believe there still can be faulty addons which can leave faulty code, even after uninstalling them.

The last thing which comes to my mind is to disable config options 1 by 1, specially the enableListeners one, to be sure that no addon has left faulty code.

Config.php options | Manual | XenForo

End-user documentation for XenForo

xenforo.com

Set each of them 1 by 1 to false and check the results. Although I don't think it should make a difference, but just to be sure. You should also restart the server after each edit (if localhost, just start and stop the server).

OK, I tried adding all of those to my config, and tried disabling them one by one. I also have development and designer modes enabled; I tried disabling designer mode as well (development mode needed to see query count). I restarted the server (it's a CentOS VM) and it made no difference. Still at 371 queries on the forum index.

@Brettflan I'ld recommend ensuring you have caching enabled (via memcache or redis). You get much higher query counts with it enabled.

Do you have any widgets setup which are pulling threads/posts?

Try my (free) addon;Optimize List Queries it tweaks a few things and if it works then probably need to report an XF bug.

I re-enabled the 'Apc' provider in XF config (using APCu), and it knocks down the query count from 371 to 370.
I had all widgets which were shown on the forum index disabled; I've just gone ahead and further actually deleted every single widget; no difference to the query count.

That addon is definitely something we'll want be using. I cleared those config options from above which disabled various stuff, then installed this addon. It did get the query count down from 370 to 367.

For reference, a screenshot of the current stripped down test forum, with 367 queries and practically no content left:



We do have an active license, so I'll see about opening a support ticket at this point.

 

[sbj]

Well, keep us updated, I am curious and want to know what the problem is, if you ever figure it out of course.

 

[Snog]

I can tell you WHAT is causing it, but I can't tell you WHY it's happening.

The permission combinations are missing for your user in the database table.

Params: 547, node, 295

If you look at the xf_permission_cache_content table using PHPMyAdmin, you'll find that the permission_combination_id 547 for node 295 is missing. And I'd bet if you look at your user info that your user uses permission combination 547.

 

[Paul B]

Running the clean up permissions may resolve the issue.

 

[Brettflan]

I can tell you WHAT is causing it, but I can't tell you WHY it's happening.

The permission combinations are missing for your user in the database table.

If you look at the xf_permission_cache_content table using PHPMyAdmin, you'll find that the permission_combination_id 547 for node 295 is missing. And I'd bet if you look at your user info that your user uses permission combination 547.

Interesting. There indeed are missing entries in xf_permission_cache_content with the specified permission_combination_id and content_id combinations in those queries. The only remaining nodes that actually exist are IDs 471 (the category) and 473 (the forum). Why is it running permission checks against so many nodes which no longer exist?

Running the clean up permissions may resolve the issue.

I've run that and every other option in "Rebuild caches" to no effect. I just ran that specific one again, no difference. Still at 367 queries.

Any other way to clean up those bad/old cache lookups to xf_permission_cache_content for non-existent nodes? I also would have expected "Clean up permissions" to fix this sort of thing.

EDIT:
I should also note that xf_permission_cache_content does still have 758 rows (1.5 MB of data), most of which still are referencing a bunch of nodes that no longer exist, regardless of the ones referenced in those queries which aren't currently in that table.

 

[JohnLogar]

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.