On web statistics, we see that our site page load time decreases steadily over time as more and more nodes, and revisions are added.
Some pages have a median load time 40sec, many others have 10sec, etc. We have <10,000 nodes.
The database is heavily burdened. We already have a beefy database but still struggle to keep the site continuously up even without heavy traffic.
A lot of analyses were all pointing towards massive node_access and related queries. In some cases, the SELECT is half-a-megabyte in size. One of the queries looks like this:
SELECT 1 AS \"expression\"\nFROM\n\"node_access\" \"node_access\"\nWHERE (\"grant_view\" >= :db_condition_placeholder_0) AND (((\"nid\" = :db_condition_placeholder_1) AND (\"langcode\" = :db_condition_placeholder_2)) OR (\"nid\" = :db_condition_placeholder_3)) AND (((\"gid\" IN (:db_condition_placeholder_4)) AND (\"realm\" = :db_condition_placeholder_5)) OR ((\"gid\" IN (:db_condition_placeholder_6, :db_condition_placeholder_7, :db_condition_placeholder_8, :db_condition_placeholder_9, :db_condition_placeholder_10, :db_condition_placeholder_11, :db_condition_placeholder_12, :db_condition_placeholder_13, :db_condition_placeholder_14, :db_condition_placeholder_15, :db_condition_placeholder_16, :db_condition_placeholder_17, :db_condition_placeholder_18, :db_condition_placeholder_19, :db_condition_placeholder_20, :db_condition_placeholder_21, :db_condition_placeholder_22, :db_condition_placeholder_23, ...
The query contains easily 5000+ placeholders, and it could take 0.2sec per query.
Now, if there is a language selector or something else that deals with multiple nodes, these queries are run for each node.
The goal is that by adding and using the module there won't be any noticeable slowdown.
[... to be filled in later ...]
Refactor the module.
It is a bigger undertaking, and most probably involves internal data model changes.
See the core patch. Can't add the patch to Drupal core, since the core team rejects optimizations and points toward module optimization and using custom hooks and tables.
Postponed: needs info
3.1
Code
Hi ragnarkurm, if you have the time, could you please fill in the Steps to reproduce section? I pumped a database full with access-restricted nodes but I was not able to produce a significant slowdown.
Marking as postponed until there are actionable Steps to Reproduce.