Confirmed Widget with profile posts cause N+1 additional queries to database

[Kruzya]

Affected version

2.2.7

This applies also to another places in XF where bb code renders and where user is passed as context, if PermissionCombination isn't cached.
In XF\BbCode\Renderer\Html::setupRenderOptions(), XF\Entity\User::isLinkTrusted() is called, what causes fetching permission cache for required user. Profile post finder in XF\Widget\NewProfilePosts don't requests relation PermissionCombination for our user, what causes additional query for isLinkTrusted() call for every profile post:

 

[digitalpoint]

Kind of related...

While the inclusion of the relation would fix it, I've always thought that the xf_permission_combination table should be cached in the data registry. It's not terribly large, it changes very rarely and is queried multiple times a second even on a moderately trafficked site (could be 100+ times per second on a high traffic site)... every request more or less to get the guest user permission combination.

Having it in the data registry would give site owners the option of reading it from something like Redis or Memcache.

Maybe I'm missing something, but it looks like it would be fairly easy to do it... define a data registry entry, read from the data registry in \XF\PermissionCache::getGlobalPerms(), reset data registry in \XF\Job\PermissionRebuild, and that's it? Maybe I'm missing something. Would save a lot of queries on any site regardless.

 

[Jake B.]

While the inclusion of the relation would fix it, I've always thought that the xf_permission_combination table should be cached in the data registry. It's not terribly large, it changes very rarely and is queried multiple times a second even on a moderately trafficked site (could be 100+ times per second on a high traffic site)... every request more or less to get the guest user permission combination.

We ran into an issue relating to this on a fairly high traffic XF site with a very large number of nodes and ended up using this to resolve most of it: https://atelieraphelion.com/products/cache-permission-checks.89/

I don't remember now if that add-on was built originally for this site, or if Xon just recommended it to us at this point but it's been a lifesaver

 

[Xon]

While the inclusion of the relation would fix it, I've always thought that the xf_permission_combination table should be cached in the data registry. It's not terribly large, it changes very rarely and is queried multiple times a second even on a moderately trafficked site (could be 100+ times per second on a high traffic site)... every request more or less to get the guest user permission combination.

While xf_permission_combination isn't very large, xf_permission_cache_content can grow to be very large.

On-disk compression can make fairly big difference for one of my sites;

# du -hs --apparent-size /var/lib/mysql/xenforo/xf_permission_cache_content.ibd
364M    /var/lib/mysql/xenforo/xf_permission_cache_content.ibd
# du -hs /var/lib/mysql/xenforo/xf_permission_cache_content.ibd
55M     /var/lib/mysql/xenforo/xf_permission_cache_content.ibd

Not storing "false" permissions, would reduce the xf_permission_cache_content size a measurable chunk. And improve memory usage when a large number of xf_permission_cache_content entries are loaded.

This is because php's memory usage skyrockets when a very large number of xf_permission_cache_content are loaded, as php array format quite memory heavy when you've got hundreds of keys. Classes don't really save you much either.

The linked 'Cache Permission Check' add-on caches to redis all the xf_permission_cache_content rows for a permission_combination_id pruned of "empty/false" permissions which saves about 30% memory usage. That add-on also goes further and scrapes out permission table joins for a large number of entity finders, which reduces SQL query result sizes, as they just hit the redis cache instead.

Maybe I'm missing something, but it looks like it would be fairly easy to do it... define a data registry entry, read from the data registry in \XF\PermissionCache::getGlobalPerms(), reset data registry in \XF\Job\PermissionRebuild, and that's it? Maybe I'm missing something. Would save a lot of queries on any site regardless.

The tricky part is the cache invalidation. My add-on ends up extending +10 points to trigger cache invalidation in the various required ways to make it robust

 

[digitalpoint]

Ya, I really was only talking about xf_permission_combination (not xf_permission_cache_content). xf_permission_cache_content seems to be mostly queried through joins via queries you can't really get rid of.

Basically, I've never really been into the first query that happens when a guest user visits the site on every page view.

SELECT cache_value
FROM xf_permission_combination
WHERE permission_combination_id = ?