letsencrypt / letsencrypt/boulder

Improve database efficiency of cert-checker

Open
#5,761 1 comment 0 reactions 0 assignees View on GitHub

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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.