boulder icon indicating copy to clipboard operation
boulder copied to clipboard

Improve database efficiency of cert-checker

Open jsha opened this issue 4 years ago • 1 comments

Right now cert-checker uses this query to find work:

"WHERE id > :id AND issued >= :issued AND expires >= :now ORDER BY id LIMIT :limit"

We know that MariaDB can iterate efficiently when we query by ID, and adding a LIMIT clause just makes it do extra work. And including an ORDER BY may cause it to create a temporary table. Instead we should do something like:

"WHERE id > :id

This should be cheap to execute and start streaming results right away. We can then cursor over the results and filter for expires and issued on the client side (note that the expires filter is mostly redundant with the id filter, since we select an appropriate id such that we only get unexpired certificates).

jsha avatar Oct 28 '21 20:10 jsha

Related: #5688, #5542

jsha avatar Oct 28 '21 21:10 jsha