After enabling Content Access module the performance of views (based on solr) worsened considerably to a point that they were timing out.
We found multiple and always growing number of mysqld processes to a point where the server practically stopped running.
This in turn was caused by multiple never completing queries (hundreds of seconds!) such as
SELECT COUNT(*) AS "expression" FROM (SELECT 1 AS "expression" FROM "node_field_data" "node_field_data" WHERE ("node_field_data"."type" IN ('strategy')) AND (EXISTS (SELECT "na"."nid" AS "nid" FROM "node_access" "na" WHERE ((("gid" IN ('0')) AND ("realm" = 'all')) OR (("gid" IN ('14')) AND ("realm" = 'content_access_author')) OR (("gid" IN ('2', '4')) AND ("realm" = 'content_access_roles'))) AND ("na"."grant_view" >= '1') AND (("node_field_data"."nid" = "na"."nid"))))) "subquery"
See attached screenshot as well:
Eventually had to switch off Content Access module, but we will gladly test it again if there is a solution to this problem.
| Comment | File | Size | Author |
|---|---|---|---|
| Screenshot 2022-06-09 235505.jpg | 91.13 KB | vacilando |
Comments
Comment #2
gisleMaintainer here.
Unfortunately, I have limited time available to care for this baby. I have a single sponsor, and I need to focus on making it work for use cases that is important for the sponsor.
For everything else, it is in the hands of the community.
If you're a developer, patches and/or merge requests are most welcome. If you're not a developer, you can hire one to get your use case prioritized.
As maintainer, I can promise to commit fixes in timely fashion, but only after the community gets the issue resolved (i.e. gets it to RTBC).
Comment #3
anybodyComment #4
gisleAll bug fixes go into the most recent branch.
Comment #5
steven jones commentedI think that this issue is basically describing the node access system. It will have poor performance if you have lots and lots of nodes, there's no real way around that.
That slow query that's noted in the OP is generated by the node access system rather than content access itself.
Content access returns the records, and defines the grants for a user, but we don't define how and when the actual SQL queries run, so while we play a part, we're not the main reason for the described slowness.
Sorry that it's been years, hope you got a response/resolution. Feel free to re-open with more information that we could use to track down the root cause of the slow queries.