Contrib.social

To reduce database size, convert layout_builder__layout_section column to a hash of the content

Open on Drupal.org →
Created on 2 March 2022, over 2 years ago
Updated 19 September 2023, 10 months ago

Problem/Motivation

  • 🐛 layout_builder__layout_section column hitting database limit Needs work says that in some cases a single layout builder section record can contain >64kb of serialized data.
  • Every node revision creates a new record in the node_revision__layout_builder__layout table for each section, even for sections that have not changed.
  • This can lead to large database sizes.

Steps to reproduce

Proposed resolution

  • Create a new table that maps a hash (e.g., sha2) of the serialized section data to that data.
  • Store only that hash in the layout_builder__layout_section column.
  • An even further optimization would be to remove inline custom blocks' block_revision_id from the section data before generating the hash, and to have a separate database column containing those block revision ID values. That way, for the case where you edit the layout and the only edit made is to edit the fields of an inline custom block, then the new revision would generate the same section storage hash.

Remaining tasks

Discuss whether or not this is a good idea.

User interface changes

API changes

Data model changes

Release notes snippet

🐛 Bug report
Status

Active

Version

11.0 🔥

Component
Layout builder  →

Last updated about 9 hours ago

Created by

🇺🇸United States effulgentsia

Live updates comments and jobs are added and updated live.
  • Performance

    It affects performance. It is often combined with the Needs profiling tag.

Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

  • Comment over 1 year ago →
    🇺🇦Ukraine podarok Ukraine

    Hi.
    Thanks for this issue.
    I'm changing this from feature request to bug request because on production this is getting rough
    Example
    https://www.drupal.org/files/issues/2023-02-07/image%20%283%29.png →

    203 nodes with Layout Builder enabled, which have 3-132 revisions, 6-12 sections on every node equal 1.2Gb of table size, which is a nightmare for backup/restore and local/dev imports/builds.

    From our investigation - the majority of the sections are unchanged.
    layout_builder__layout_section column of node_revision__layout_builder__layout table contains serialised Object of respective section.

    Improvements we may have
    - Make this table/column as COMPRESSED on DB level ( more workaround, not actual fix )
    - move these sections to other table via hash/id
    - make section as revisionable entity and instead of keeping it within node_revision__layout_builder__layout table - use reference to the section revision.

    On heavy sites with a lot of Layout Builder
    nodes*revision*section = a lot of disk space and slowness.

    Bug is
    - we keep copy of the serialized object in layout_section column for every delta in every other revision - waste of space

    Solution
    - We need to avoid keeping copy of same data and use references/joins in DB going forward

  • Comment over 1 year ago →
    🇳🇴Norway steinmb
  • Comment over 1 year ago →
    🇵🇰Pakistan hmdnawaz

    Is someone working on this? No progress from the last year?

    I have the same issue. The total size of the database is 1.2GB. In which 982MB is only this table node_revision__layout_builder__layout.

  • Comment over 1 year ago →
    🇺🇦Ukraine podarok Ukraine

    re #8 - we installed node_revision_delete and set some values to keep DB size in control, but still root cause wasn't solved

  • Comment over 1 year ago →
    🇬🇧United Kingdom catch

    Bumping this to major, this could result in sites going out of disk space on some hosting plans - lots of specific restrictions on database size.

  • Comment about 1 year ago →
    solideogloria

    I think this is a good idea as well.

  • Comment about 1 year ago →
    🇵🇰Pakistan hmdnawaz

    Any update on this issue? or any workaround instead of using node_revision_delete?

  • Comment 10 months ago →
    🇲🇦Morocco ihamadet

    For now, even node_revision_delete won't delete revisions in node_revision__layout_builder__layout.
    I created a feature request to ask for this to be included in node_revision_delete: https://www.drupal.org/project/node_revision_delete/issues/3388227 ✨ Remove Layout Builder revisions Active

contrib.social Blog FAQ Discussions
Production build 0.69.0 2024