boulder icon indicating copy to clipboard operation
boulder copied to clipboard

expiration-mailer: improve query efficiency

Open jsha opened this issue 3 years ago • 0 comments

Right now, the expiration-mailer's loop is like this:

  • Query certificateStatus for all certificates expiring in a given time range where lastNagSent is sufficiently in the past.
  • For each certificate returned:
    • Call sa.SelectCertificate and parse it (to get the DNSNames).
    • SELECT from fqdnSets to determine if the certificate is already renewed.
    • If so, UPDATE certificateStatus.lastNagSent.
    • Otherwise, send mail then UPDATE certificateStatus.lastNagSent.

(Some details about grouping by account have been omitted since they don't affect the overall query pattern much).

This is an inefficient query pattern, to say the least. We can reduce the total number of queries in a variety of ways. For instance we could batch up our SelectCertificate calls and/or change them to query issuedNames instead (since we only care about the names). Similarly we could batch up the UPDATEs. And we could move the grouping by account id earlier, and have a cutoff for accounts with many certificates all expiring at once, where we don't have to get names for every certificate.

jsha avatar Jun 14 '22 21:06 jsha