Skip to content

slow queries against civicrm_paymentprocessor_webhook (needs indices or garbage cleanup?)

Summary

I'm seeing two slow queries in mysql for the table civicrm_paymentprocessor_webhook:

SELECT `a`.`id` AS `id`, `a`.`payment_processor_id` AS `payment_processor_id`, `a`.`event_id` AS `event_id`, `a`.`trigger` AS `trigger`, `a`.`created_date` AS `created_date`, `a`.`processed_date` AS `processed_date`, `a`.`status` AS `status`, `a`.`identifier` AS `identifier`, `a`.`message` AS `message`, `a`.`data` AS `data` FROM civicrm_paymentprocessor_webhook a WHERE (`a`.`payment_processor_id` = "15") AND (`a`.`processed_date` IS NULL);

SELECT `a`.`id` AS `id`, `a`.`payment_processor_id` AS `payment_processor_id`, `a`.`event_id` AS `event_id`, `a`.`trigger` AS `trigger`, `a`.`created_date` AS `created_date`, `a`.`processed_date` AS `processed_date`, `a`.`status` AS `status`, `a`.`identifier` AS `identifier`, `a`.`message` AS `message`, `a`.`data` AS `data` FROM civicrm_paymentprocessor_webhook a WHERE (`a`.`payment_processor_id` = "15") AND (`a`.`identifier` = "[REDACTED]::") AND (`a`.`processed_date` IS NULL);
  • This table currently has 226239 rows, and EXPLAIN on both queries reports a large number or ROWS and USING WHERE; (sorry, can't get to the EXPLAIN now because I've added indeces to address performance; see below).
  • Large number of rows may be a result of card-testing behavior mentioned in https://chat.civicrm.org/civicrm/pl/97n6d6dpjjgpzj84axptak8mxh

Impact:

These slow queries were causing very high memory and CPU usage from MySQL, leading to site unresponsiveness or painful slowness in response.

Mitigating actions taken:

Adding two indeces on this table has resolved the slow-query issue; these queries are now running rapidly, memory and CPU usage have returned to normal, and server is appropriately responsive.

ALTER TABLE `civicrm_paymentprocessor_webhook` ADD INDEX `processed_date` (`processed_date`);
ALTER TABLE `civicrm_paymentprocessor_webhook` ADD INDEX `identifier` (`identifier`);

Questions:

  • Should this table be the subject of garbage cleanup to prevent its growing to a large size?
  • If so, is such cleanup already in place, or may it be done manually without negative consequences, or is a patch welcome for such automated cleanup?