Contrib.social

Performance degradation

Open on Drupal.org →
Created on 8 August 2023, over 1 year ago

Problem/Motivation

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 cause

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.

Goal

The goal is that by adding and using the module there won't be any noticeable slowdown.

Steps to reproduce

[... to be filled in later ...]

Proposed resolution

Refactor the module.

Remaining tasks

User interface changes

API changes

Data model changes

It is a bigger undertaking, and most probably involves internal data model changes.

Workaround

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.

📌 Task
Status

Active

Version

3.1

Component

Code

Created by

🇪🇪Estonia ragnarkurm

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

  • Issue created by @ragnarkurm
  • Comment over 1 year ago →
    🇪🇪Estonia ragnarkurm
  • Comment over 1 year ago →
    🇪🇪Estonia ragnarkurm
  • Comment over 1 year ago →
    🇩🇪Germany marcoliver Neuss, NRW, Germany

    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.

  • Status changed to Postponed: needs info about 1 year ago
  • Comment about 1 year ago →
    🇩🇪Germany marcoliver Neuss, NRW, Germany

    Marking as postponed until there are actionable Steps to Reproduce.

  • Comment 3 days ago →
    🇺🇸United States crutch

    We are getting some page timeouts with similar logs. It seems to only happen when it is yet to be cached. Upon refresh of the url, after the "unexpected error..." message on a white page, it will load. I've not tried the patch yet.

    Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[HY000]: General error: 3024 Query execution was interrupted, maximum statement execution time exceeded: SELECT DISTINCT "t"."nid" AS "nid" FROM "taxonomy_index" "t" WHERE "t"."nid" NOT IN (:db_condition_placeholder_0, :db_condition_placeholder_1, :db_condition_placeholder_2, :db_condition_placeholder_3, :db_condition_placeholder_4, :db_condition_placeholder_5, :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, :db_condition_placeholder_24, :db_condition_placeholder_25, :db_condition_placeholder_26); Array ( [:db_condition_placeholder_0] => 7569 [:db_condition_placeholder_1] => 7570 [:db_condition_placeholder_2] => 7571 [:db_condition_placeholder_3] => 7572 [:db_condition_placeholder_4] => 7573 [:db_condition_placeholder_5] => 7574 [:db_condition_placeholder_6] => 7575 [:db_condition_placeholder_7] => 7576 [:db_condition_placeholder_8] => 7626 [:db_condition_placeholder_9] => 7976 [:db_condition_placeholder_10] => 7977 [:db_condition_placeholder_11] => 8453 [:db_condition_placeholder_12] => 8806 [:db_condition_placeholder_13] => 1645 [:db_condition_placeholder_14] => 1646 [:db_condition_placeholder_15] => 1647 [:db_condition_placeholder_16] => 1648 [:db_condition_placeholder_17] => 1649 [:db_condition_placeholder_18] => 1650 [:db_condition_placeholder_19] => 1651 [:db_condition_placeholder_20] => 1652 [:db_condition_placeholder_21] => 7577 [:db_condition_placeholder_22] => 7882 [:db_condition_placeholder_23] => 7890 [:db_condition_placeholder_24] => 8475 [:db_condition_placeholder_25] => 9211 [:db_condition_placeholder_26] => 7588 ) in Drupal\permissions_by_term\Service\AccessStorage->getUnrestrictedNids() (line 732 of /drupal/web/modules/contrib/permissions_by_term/src/Service/AccessStorage.php).

contrib.social Blog FAQ Discussions
Production build 0.71.5 2024