‘Send Scheduled Reminders’ task - Time Out and Scalability issues
When trying to run the ‘Send Scheduled Reminders’ task for lapsing members in CiviCRM, the user experiences Time Outs and the Scheduled Job fails to complete.
The query appears to be very complex, and joins the membership table to itself as well as joins to the relationships table (which is around half the size of the membership table) so it is pulling a lot of data. It actually examines hundreds of millions of rows throughout the process of running.
This is the query:
# Time: 180626 19:22:29
# User@Host: CLIENT_wpcvusr[CLIENT_wpcvusr] @ localhost [] Id: 34616
# Schema: CLIENT_civicrm Last_errno: 1160 Killed: 0
# Query_time: 345.664393 Lock_time: 0.000161 Rows_sent: 9035 Rows_examined: 237398638 Rows_affected: 0
# Bytes_sent: 425984
SET timestamp=1530037349;
SELECT cm.id AS owner_id, cm.contact_id AS owner_contact, m.id AS slave_id, m.contact_id AS slave_contact, cmt.relationship_type_id AS relation_type, rel.contact_id_a, rel.contact_id_b, rel.is_permission_a_b, rel.is_permission_b_a
FROM civicrm_membership m
LEFT JOIN civicrm_membership cm ON cm.id = m.owner_membership_id
LEFT JOIN civicrm_membership_type cmt ON cmt.id = m.membership_type_id
LEFT JOIN civicrm_relationship rel ON ( ( rel.contact_id_a = m.contact_id AND rel.contact_id_b = cm.contact_id AND rel.relationship_type_id = cmt.relationship_type_id )
OR ( rel.contact_id_a = cm.contact_id AND rel.contact_id_b = m.contact_id AND rel.relationship_type_id = cmt.relationship_type_id ) )
WHERE m.owner_membership_id IS NOT NULL AND
( rel.is_permission_a_b = 0 OR rel.is_permission_b_a = 0);
We have increased resources and Time Out settings on our server, and our server engineers also increased max_allowed_packet variable of MySQL so the queries could run faster. However, the Time Out still occurs.
So, it seems that this CiviCRM Send Scheduled Reminder task is not scaling to the current size of our member database (~12,000 membership records).