letsencrypt / boulder / 9390
66%

DEFAULT BRANCH: master
Ran
Jobs 1
Files 87
Run time 9s
Badge
Embed ▾
README BADGES
x

If you need to use a raster PNG badge, change the '.svg' to '.png' in the link

Markdown

Textile

RDoc

HTML

Rst
9390

cron

travis-ci

rolandshoemaker 
Change pendingAuthorizations indexes for better performance. (#3775)

There are two main queries we do against pendingAuthorizations: counting pending authzs
for rate limiting, and looking up existing authorizations for reuse. Neither of these is served
perfectly by our current (regID, expires) index. The index works well in some conditions but
not others. This change adds two more specific indexes to replace the existing ones.

The below set of EXPLAINs demonstrates the new indexes in use. Note that in order to
get MariaDB to make proper use of the new index, I had to create a local account and
fill it with pending authzs using a modified chisel.py.

The `registrationID`, `status`, `expires` index is used for the case when an account creates
and then deactivates a lot of authzs, since deactivated authzs stay in the pending table
and create performance issues. Adding an index that includes status can fix those performance
issues. The last section of statements logs below, after I `UPDATE` all the pending authorizations
to be `deactivated`, demonstrates the speed difference in the count query (check the `rows` count).

```
mysql> SHOW CREATE TABLE pendingAuthorizations \G                                                                     *************************** 1. row ***************************
       Table: pendingAuthorizations
Create Table: CREATE TABLE `pendingAuthorizations` (
  `id` varchar(255) NOT NULL,
  `identifier` varchar(255) NOT NULL,
  `registrationID` bigint(20) NOT NULL,
  `status` varchar(255) NOT NULL,
  `expires` datetime DEFAULT NULL,
  `combinations` varchar(255) NOT NULL,
  `LockCol` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `regId_expires_idx` (`registrationID`,`expires`),
  CONSTRAINT `regId_pending_authz` FOREIGN KEY (`registrationID`) REFERENCES `registrations` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> EXPLAI... (continued)

10267 of 16487 relevant lines covered (62.27%)

0.69 hits per line

Jobs
ID Job ID Ran Files Coverage
6 9390.6 (RUN="coverage" CONTAINER="netaccess") 0
62.27
 Travis Job 9390.6
Source Files on build 9390
Detailed source file information is not available for this build.