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

Too long query time in eZSubtreeNotificationRule::fetchUserList()

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 4.0.4, 4.1.1
    • Fix Version/s: 4.0.5, 4.1.2, 4.2.0alpha1
    • Component/s: Notifications
    • Labels:
      None

      Description

      Every time notification cronjob is executed after node publication, it takes a very long time (more than 2 minutes) to obtain the user list subscribed to get notification on that parent node, causing the whole site being blocked as the mysql server is not serving querys until that one finishes (locked processes can be seen on mysql processlist).

      The slow query is found on fetchUserList():

              $sql = 'SELECT DISTINCT policy.id AS policy_id, subtree_rule.user_id,
                                      user_role.limit_identifier AS limitation,
                                      user_role.limit_value AS value
                        FROM ezuser_role user_role,
                             ezsubtree_notification_rule subtree_rule,
                             ezcontentobject_tree user_tree,
                             ezcontentobject_tree user_node,
                             ezpolicy policy
                        WHERE subtree_rule.node_id IN ( ' . $db->implodeWithTypeCast( ', ', $nodeIDList, 'int' ) . ' ) AND
                              user_node.contentobject_id=subtree_rule.user_id AND
                              user_node.path_string like ' . $concatString . " AND
                              user_role.contentobject_id=user_tree.contentobject_id AND
                              ( user_role.role_id=policy.role_id AND ( policy.module_name='*' OR ( policy.module_name='content' AND ( policy.function_name='*' OR policy.function_name='read' ) ) ) )";
      
      
      Steps to reproduce

      Scenario:

      • Site with a thousand users.
      • Various roles applied to all users, each one with various content/read policies, including anonymous and several custom roles.
      • 800 users subscribed to one node.
      • publish an object in the subtree of the user's subscribed node.

      Symptoms:

      • if computing all roles applied to the users, you get as a result 6 content/read policies applied to all users subscribed to a node, the query in fetchUserList returns (users subscribed to the node * 6), in our particular case, a resulset of abot 5k records.
      • The mysql server stops serving querys due to locked processes.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              nfrp Nicolas Pastorino
              Reporter:
              nfrp Nicolas Pastorino
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: