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():
- 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.
- 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.