Slow page loads when "Single term restriction" is enabled and site has many nodes

Hi,
I am using PBT with WebformPBT addon module. I have around 85000 terms and do not notice any slowdown. I would recommend to start by looking at slow query logs and analyse the queries to find out where exactly is the slowdown.

Hi,

dakku is right. The most common problem is with slow/many DB queries. I would enable MySQL query logging. Another option is to profile PHP fuction calls with Xdebug. These two methods will track down the issue.

Another helpful tool is the webprofiler from devel module.

Provide more info about your bottlenecks. Then there is higher chance for help.

Good luck.

Since version 8.x-1.35 PbT is not handling any nodes which have no taxonomy term permission. That is improving performance.

I am setting this issue fixed, since there's no activity. Please re-open, if you have any further objections.

Hi,

I am experiencing the same issue. After installing PBT and restricting access by it, the node edit/view page times out (30 second).
It affects both nodes that reference this term and nodes that has no term reference.

I have about 7000 nodes and less than 1000 terms.

Thanks in advance for your help.

Set higher php timeout by max_execution_time setting in php.ini. try 60, 90 seconds.

Thank you for your quick response. Just to clarify, I am talking about the node edit/view page. The permissions were already rebuild. it should return immediately and not need a timeout bigger than 30... Or am I missing something...

May you have updated "a lot" of related terms on your node? But I doubt it. Also disable and enable PbT, to make sure, that your issue is related to PbT and not something else.

When I disable the module, things get back to normal. and there aren't many terms or nodes. thanks.

Do you have an idea for fixing this?

Hi Bar,
Please see my suggestion above. If you can post logs either from mysql or xhprof, I can look to help you.

Without any technical information it's almost impossible to reproduce the issue let alone "fix it" :)

dakku is right. Without more technical details and only posting "it is slow", help is not possible.

Please enable MySQL query logging into a log file for 1 slow node view via PbT and upload the logfile here into this issue.

Here is the instruction for how to enable MySQL query logging: https://mobilefish.de/mysql-write-all-executed-queries-log-file

Thank you dakku and Peter. Will look into some db logs (using postgres) and be back with more info.

Closing for no activity, since longer time.

I am able to replicate this with 2,700 nodes.

The root cause seems to be the implementation of hook_node_grants. The current implementation:
1. Looks up the terms the user is allowed to access
2. And then for every node...
2a. Looks up the terms attached to the node
2b. Loads each term (to get it's langcode)
2c. Then loads the langcode for the node
3. Then queries the node_access table to find all the node records

I think the result of this call is supposed to be cached...but if it is never able to finish in the first place, that doesn't help.

Attached is what the query log looks like when loading a view of the site showing 45 nodes (it didn't finish, it timed out after 30 seconds). You can see it spends a lot of time going back and forth with the taxonomy_index table.

I'm spending some time today and tomorrow to try and think of a better implementation for this. I think to scale better, the node_access table should be storing either the user ID or taxonomy ID as the grant ID. This way, when calculating the grants for a request, it doesn't have to look up every node.

Basing the grant ID on the term ID is a significant performance improvement (page is loading in 2 seconds whereas before it hit the 30 second timeout)...but I'm struggling to figure out how to implement single_term_restriction using that approach. Current alternative would be to use the user id as the grant ID and "pre-calculate" access for all nodes...but open to other suggestions.

With a fresh set of eyes this morning, I'm realizing the current implementation is only slow if single_term_restriction is enabled. I don't need this setting enabled, so my issue is solved by disabling the setting.

Thanks for reporting this. We need to check this issue.

Just thinking about this today, and you could potentially do all the access checking for single_term_restriction directly in the database in a single query--this would keep the module highly performant no matter the number of nodes or terms.

Here's a sample query I only lightly tested in my local environment:

SELECT nid 
  # Group all nodes by nid and with the smallest result;
  # this yields a list of nids with a result of whether the user can/cannot access
  FROM (SELECT nid, 
           MIN(IFNULL(result, 0)) access
      FROM {taxonomy_index} ti
          # Get all the terms used by PbT
           INNER JOIN (SELECT tid 
                 FROM {permissions_by_term_user}
                 UNION 
                 SELECT tid 
                 FROM {permissions_by_term_role}) pbt 
               ON pbt.tid = ti.tid 
          # Add a "result" column to place a 1 next to each term the user can access
           LEFT JOIN (SELECT tid,
                  1 result
                FROM {permissions_by_term_user}
                WHERE uid = :uid
                UNION
                SELECT tid,
                  1 result
                FROM {permissions_by_term_role}
                WHERE rid IN (:rid[])) ubpt 
              ON ubpt.tid = ti.tid 
      GROUP BY nid) acl
  WHERE access

I realize the query is a bit complex, but here's the basic idea:
- Find all nodes that are protected by Permissions by Term and the term ID creating the restriction
- For each term ID, add a result of 1 if the user can access that term
And then here's the magic:
- Group the table by nid and select the minimum result (using 0 if the value is null) -- this reduces the table to only display each nid once with a result of 0 if the user could not access any of the terms restricting the node
- Select all the nid where the result was 1 -- resulting in a list of nids that are protected by PbT where the user has access to all terms

This exact same logic can be used when single_term_restriction is disabled--but MIN would be swapped with MAX.

Wow, very good hint. I will find some time for this during next week.

mediabounds++

This would result in a re-write of the AccessCheck class.

The following method must be modified:
- canUserAccessByNodeId
- isAccessAllowedByDatabase (here you could possibly replace the calls of isAnyPermissionSetForTerm(), isTermAllowedByUserRole(), isTermAllowedByUserId() with one query)

Mind that the langcode is also affected via this method.

This refactoring definitely needs an automated test. Do not just refactor without writing a test.

@mediabounds: Sub-queries can make the performance even slower. I am thinking about creating a test-case for this query for proving an performance impact. Are you sure, that the performance would be better via using your query? May you could assist here in writing kernel base tests for ensuring a performance benefit.

Closing this issue for no activity/interest.

Same to me:
600 Nodes
some users, 12 roles and around 5 Term-Relations each Nodetype (some multiple)

The Timefactor is around 10 !
Views-Pages with 2, 3 or 0.7 seconds now take 25, 21 or 9.2 seconds.

Thanks in advance and greetings from Cologne

Addendum: this is with option Single term restriction OFF

Wow that checkbox is MASSIVELY debilitating. This issue desperately needs a patch.

If PbT is slowing down your performance on views pages, then you can switch of the node access records option in the settings. Afterwards your nodes and entities (with Permissions by Entity sub-module enabled) will be restricted. For tweaking your views etc., you can use the API.

Posting my patch here since my issue was marked as a duplicate (https://www.drupal.org/project/permissions_by_term/issues/3036281). This could use some testing work if anyone wants to take a stab at it. This patch rewrites the getTidsByNid() function to only fetch terms on the node one is saving, instead of looping over every node that has a term attached to it. With this rewrite, the expensive getAllNidsToTidsPairsFromDatabase() function can be removed, along with the need to cache the entire result set.

@bkildow: Thanks for the patch. I have slightly improved your patch for using the cache. Released the improvements in version 8.x-2.8. See https://www.drupal.org/project/permissions_by_term/releases/8.x-2.8.

I was using the site with quick page loads, saw the 8.x-2.8 update about 15 minutes ago, applied it, ran cron, flushed the caches and I am now seeing the behavior reported in this original issue, with the 30-40 second page loads. I reverted it back to 8.x-2.7 and it is back to normal now. No time to get into deep diagnostics today, but this is:

Drupal 8.6.10
PHP 7.3.0 with 256M
MySQL 5.6.39
618 nodes
9 vocabularies
387 terms
2 users
3 roles
https by SSLforfree.com
GoDaddy
Chrome 72.0.3626.119 64 bit with a note I just noticed to relaunch to complete updating (either to this or from it)

@Roy of Cranberry: Thanks for testing and your feedback! It's always important to share.

I have found out, that I have implemented a variable reset which caused bypassing of caching. I have fixed that in version 8.x-2.9: https://www.drupal.org/project/permissions_by_term/releases/8.x-2.9.

Would you please test this version?

@Peter Majmesku: That fixed it for me. I will update my other site that is using pbt now as well. Nice work. Thank you.

@Roy of Cranberry: I am glad to read that. Closing this issue.

Just installed the module for testing and encountered the same problems with slow page (node) loads.

Was able to repeatedly reproduce it with the following steps:
• Install Permissions by Term
• Create access control vocabulary and set allowed role on term permissions
• Added access control term to node
• Assigned allowed role to user account
• "Require all terms granted" (unchecked)
• "Permission mode" CHECKED
• "Disable node access records" (unchecked)

Results:
• View node takes 3 minutes +/- to load
• Edit node takes 3 minutes +/- to load
• Other vocabulary term options on content type disappear from node edit form.

Uninstall Permissions by Term:
• Node view/edit load times drop to 1-2 seconds
• Other vocabulary term options on content type reappear on node edit form.

Environment:
• Drupal 8.6
• Permissions by Term 8.x-2.10
• 29,000 nodes +/-
• 147 terms

Experiencing this issue as well about a minute to load with 16k+ nodes.