Details
-
Bug
-
Resolution: Fixed
-
Medium
-
4.0.4, 4.1.1
-
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.
