letsencrypt / letsencrypt/boulder
Improve database efficiency of cert-checker
Nobody has claimed this yet.
- Dominant language
- Go
- Stars
- 5.8k
- Forks
- 649
- Avg merge
- 3d 23h
- Merged PRs (30d)
- 24
Description
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).
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Locate cert-checker’s work query and the code that consumes its results. Compare the current MariaDB query with the proposed id-only query, then verify that client-side filtering preserves the issued and expires conditions while streaming results efficiently.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, mariadb
- Domain
- backend, databases
- Issue type
- Refactor
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100