Uploaded image for project: 'eZ Publish / Platform'
  1. eZ Publish / Platform
  2. EZP-24499

loading Content with many languages & attributes & locations leads to high memory usage

    Details

      Description

      With the following:

      • 18 languages
      • 29 attributes

      Loading the content will return about 160 000 rows:

      • language * location (18 * 1)
      • language * name (join on ezcontentobject_name) (18 * 1)
      • language * attributes (18 * 29)

      That's a tad much. Yes, language filtering should be used whenever possible, but it's still a lot.

      This causes following issues:

      • breaks memory limits
      • big amounts of data needed to be transferred from mysql to PHP
      • very slow

      things that could be improved

      The location join should not return more than 1 row. We might also want to exclude secondary locations, as they shouldn't matter when loading a Content.
      The name join shouldn't return more than languages rows

      In total, this query should return languages * attributes rows, not more (522 in that case).

      Query example

      SELECT
        `ezcontentobject`.`id`                                 `ezcontentobject_id`,
        `ezcontentobject`.`contentclass_id`                    `ezcontentobject_contentclass_id`,
        `ezcontentobject`.`section_id`                         `ezcontentobject_section_id`,
        `ezcontentobject`.`owner_id`                           `ezcontentobject_owner_id`,
        `ezcontentobject`.`remote_id`                          `ezcontentobject_remote_id`,
        `ezcontentobject`.`current_version`                    `ezcontentobject_current_version`,
        `ezcontentobject`.`initial_language_id`                `ezcontentobject_initial_language_id`,
        `ezcontentobject`.`modified`                           `ezcontentobject_modified`,
        `ezcontentobject`.`published`                          `ezcontentobject_published`,
        `ezcontentobject`.`status`                             `ezcontentobject_status`,
        `ezcontentobject`.`name`                               `ezcontentobject_name`,
        `ezcontentobject`.`language_mask`                      `ezcontentobject_language_mask`,
        `ezcontentobject_version`.`id`                         `ezcontentobject_version_id`,
        `ezcontentobject_version`.`version`                    `ezcontentobject_version_version`,
        `ezcontentobject_version`.`modified`                   `ezcontentobject_version_modified`,
        `ezcontentobject_version`.`creator_id`                 `ezcontentobject_version_creator_id`,
        `ezcontentobject_version`.`created`                    `ezcontentobject_version_created`,
        `ezcontentobject_version`.`status`                     `ezcontentobject_version_status`,
        `ezcontentobject_version`.`contentobject_id`           `ezcontentobject_version_contentobject_id`,
        `ezcontentobject_version`.`language_mask`              `ezcontentobject_version_language_mask`,
        `ezcontentobject_version`.`initial_language_id`        `ezcontentobject_version_initial_language_id`,
        `ezcontentobject_attribute`.`id`                       `ezcontentobject_attribute_id`,
        `ezcontentobject_attribute`.`contentclassattribute_id` `ezcontentobject_attribute_contentclassattribute_id`,
        `ezcontentobject_attribute`.`data_type_string`         `ezcontentobject_attribute_data_type_string`,
        `ezcontentobject_attribute`.`language_code`            `ezcontentobject_attribute_language_code`,
        `ezcontentobject_attribute`.`language_id`              `ezcontentobject_attribute_language_id`,
        `ezcontentobject_attribute`.`version`                  `ezcontentobject_attribute_version`,
        `ezcontentobject_attribute`.`data_float`               `ezcontentobject_attribute_data_float`,
        `ezcontentobject_attribute`.`data_int`                 `ezcontentobject_attribute_data_int`,
        `ezcontentobject_attribute`.`data_text`                `ezcontentobject_attribute_data_text`,
        `ezcontentobject_attribute`.`sort_key_int`             `ezcontentobject_attribute_sort_key_int`,
        `ezcontentobject_attribute`.`sort_key_string`          `ezcontentobject_attribute_sort_key_string`,
        `ezcontentobject_name`.`name`                          `ezcontentobject_name_name`,
        `ezcontentobject_name`.`content_translation`           `ezcontentobject_name_content_translation`,
        `ezcontentobject_tree`.`main_node_id`                  `ezcontentobject_tree_main_node_id`
      FROM `ezcontentobject`
        -- nb results: 1
        LEFT JOIN `ezcontentobject_version` ON `ezcontentobject_version`.`contentobject_id` = `ezcontentobject`.`id`
        -- nb results: 522
        LEFT JOIN `ezcontentobject_attribute`
          ON (`ezcontentobject_attribute`.`contentobject_id` = `ezcontentobject_version`.`contentobject_id` AND
              `ezcontentobject_attribute`.`version` = `ezcontentobject_version`.`version`)
        -- nb results: 18
        LEFT JOIN `ezcontentobject_tree`
          ON `ezcontentobject_tree`.`contentobject_id` = `ezcontentobject_version`.`contentobject_id`
       
        --  nb results: 18
        LEFT JOIN `ezcontentobject_name`
          ON (`ezcontentobject_name`.`contentobject_id` = `ezcontentobject_version`.`contentobject_id` AND
              `ezcontentobject_name`.`content_version` = `ezcontentobject_version`.`version`)
      WHERE (`ezcontentobject`.`id` = 3886 AND `ezcontentobject_version`.`version` = 171)

        Issue Links

          Activity

          Hide
          Florent Huck (Inactive) added a comment -

          A pull request is waiting approval : https://github.com/ezsystems/ezpublish-kernel/pull/1306

          Show
          Florent Huck (Inactive) added a comment - A pull request is waiting approval : https://github.com/ezsystems/ezpublish-kernel/pull/1306
          Hide
          Bertrand Dunogier added a comment - - edited

          The improvement is part of our backlog for the next weeks. It should make it for the next maintenance release, planned early in july. Would that work out ?

          (the fix will most likely be ready before that date)

          Show
          Bertrand Dunogier added a comment - - edited The improvement is part of our backlog for the next weeks. It should make it for the next maintenance release, planned early in july. Would that work out ? (the fix will most likely be ready before that date)
          Show
          André Rømcke added a comment - New PR: https://github.com/ezsystems/ezpublish-kernel/pull/1321
          Show
          André Rømcke added a comment - Merged: https://github.com/ezsystems/ezpublish-kernel/commit/5835473d2ea78e768f80a815ba9271e9e0d472fd
          Hide
          Miguel das Neves Jacinto (Inactive) added a comment -

          QA Approved

          Show
          Miguel das Neves Jacinto (Inactive) added a comment - QA Approved

            People

            • Assignee:
              Unassigned
              Reporter:
              Bertrand Dunogier
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 1 hour, 15 minutes
                1h 15m