Index results by build id and pkg id

[riastradh]

The following index dramatically speeds up common queries:

CREATE INDEX results_i_build_pkg ON results (build_id, pkg_id);

It increases the size of the database by about 1/3, but compare, e.g., the GetResultsInCategory query (bottleneck of https://releng.netbsd.org/bulktracker/build/645/meta-pkgs):

• Without index:

  % time sqlite3 ./BulkTracker.db.~1~ "select r.*, p.* from results r join pkgs p on (r.pkg_id == p.pkg_id) where p.category = 'meta-pkgs/' and r.build_id = 645"
  ...
  sqlite3 ./BulkTracker.db.~1~   2.32s user 0.40s system 99% cpu 2.732 total

• With index:

  % time sqlite3 ./BulkTracker.db "select r.*, p.* from results r join pkgs p on (r.pkg_id == p.pkg_id) where p.category = 'meta-pkgs/' and r.build_id = 645"
  ...
  sqlite3 ./BulkTracker.db   0.01s user 0.01s system 98% cpu 0.015 total

GetSingleResultByPkgName (bottleneck of https://releng.netbsd.org/bulktracker/pkg/17227701):

• Without index:

  % time sqlite3 ./BulkTracker.db.~1~ "select r.*, p.* from results r, pkgs p where r.build_id == 644 and r.pkg_id == p.pkg_id and r.pkg_name = 'libreoffice-24.2.1.2nb2'"                           
  17225614|644|7533|libreoffice-24.2.1.2nb2|2||3|7533|misc/|libreoffice
  sqlite3 ./BulkTracker.db.~1~   2.31s user 0.46s system 99% cpu 2.801 total

• With index:

  % time sqlite3 ./BulkTracker.db "select r.*, p.* from results r, pkgs p where r.build_id == 644 and r.pkg_id == p.pkg_id and r.pkg_name = 'libreoffice-24.2.1.2nb2'"
  17225614|644|7533|libreoffice-24.2.1.2nb2|2||3|7533|misc/|libreoffice
  sqlite3 ./BulkTracker.db   0.02s user 0.01s system 97% cpu 0.024 total

Given the amount of CPU time mollari is spending in bulktracker, I think this couple hundred megabytes of space is worth it.

[riastradh]

Another index that may be worthwhile to speed up GetSingleResultByPkgName, to look up failed dependencies on a package results detail page of an indirect-failed package like https://releng.netbsd.org/bulktracker/pkg/17645166 (especially once https://github.com/bsiegert/BulkTracker/issues/56 is fixed so it can display more than one failed dependency at a time):

CREATE INDEX results_i_build_pkgname ON results (build_id, pkg_name);

I haven't measured how much space it takes or how much it speeds up queries, though -- just guessing by code inspection. (Should measure these before implementing it.)

[riastradh]

Another index that may be worthwhile to speed up GetPkgsBreakingMostOthers, to show on a bulk build details page like https://releng.netbsd.org/bulktracker/build/658 which packages break most others, by narrowing the search down in advance to which builds are broken:

CREATE INDEX results_i_build_pkg_broken ON results (build_id, pkg_id)
    WHERE build_status > 0;

[bsiegert]

The following index dramatically speeds up common queries:

CREATE INDEX results_i_build_pkg ON results (build_id, pkg_id);

It increases the size of the database by about 1/3, but compare, e.g., the GetResultsInCategory query (bottleneck of https://releng.netbsd.org/bulktracker/build/645/meta-pkgs):

I added this index just now. Will look into the others. Thank you!

[riastradh]

The list of results for a particular package like https://releng.netbsd.org/bulktracker/lang/rust is bottlenecked on GetAllPkgResults, and the results_i_build_pkg index doesn't help because it wants to look up the pkg id first, not the build id first. Could add an opposite index:

CREATE INDEX results_i_pkg_build ON results (pkg_id, build_id);

But maybe it would be better to just use three separate indices on each of the two or three relevant columns -- a cursory glance suggests that will work just as well for all the queries I checked, and cost less space than both results_i_build_pkg and results_i_pkg_build combined (about 1.1 GB vs 1.2 GB for the whole database):

CREATE INDEX results_i_build ON results (build_id);
CREATE INDEX results_i_pkg ON results (pkg_id);
-- plus maybe:
CREATE INDEX results_i_pkgname ON results (pkg_name);

Might be worthwhile to systematically examine all the queries to see which ones are improved by indices -- I have been spot-checking by mousing around the web site and noticing when things are slow, and I may have missed this slowness last time around because of the caching layer. All of the queries I spot-checked (GetResultsInCategory, GetAllPkgResults, GetSingleResultByPkgName, getPkgsBrokenBy) were quick with results_i_build + results_i_pkg, and some were slower with just results_i_build_pkg or with just results_i_pkg_build.

[riastradh]

I measured over 21sec to get an answer back for https://releng.netbsd.org/bulktracker/www/firefox115 today. It would be nice if this were a little more responsive -- adding one of the indices I suggested would make it essentially instantaneous, rather than eating half a minute of 100% CPU to show the latest build results.

[riastradh]

I did some further investigation, and it looks like the indexing may not be the problem here. Computing the GetAllPkgResults query takes <30ms on my laptop with the current index as is. (And running all the queries through EXPLAIN QUERY PLAN or the sqlite3 shell's .expert command doesn't turn up any low-hanging fruit for other indices, so never mind about my other index suggestions.)

I guessed maybe the loop in API.PkgResults to filter results to the most recent is slow -- it is almost certainly better to do the filtering in SQL rather than in the SQL caller, like this:

-- name: GetPkgResults :many
SELECT r.result_id, r.pkg_name, r.build_status, r.breaks, b.build_id, b.platform, datetime(MAX(b.build_ts)) AS build_ts, b.branch, b.compiler, b.build_user
FROM results r, builds b
WHERE r.build_id == b.build_id AND r.pkg_id == ?
GROUP BY b.platform, b.branch, b.compiler, b.build_user
ORDER BY b.build_ts DESC;

But on my laptop, it's only an improvement of ~25ms (77ms vs 49ms, 66ms vs 43ms, 64ms vs 35ms, in three trials). So, while that's nearly half the time of /json/pkgresults/N, that still might not explain it either.

[bsiegert]

Maybe what you saw was a concurrent request? If the app was busy adding a new result to the DB at the time, that would explain the slowness.

[riastradh]

Can you run the bulktracker under a profiler to see where the hot spots are? The very long delays make browsing it for reviewing failures somewhat difficult.

[bsiegert]

I can, once I'll be back from vacation. Sorry for the slowness in the meantime.

[bsiegert]

For the GetAllPkgResults query, I found that it was doing a full table scan on results:

QUERY PLAN
|--SCAN r
|--SEARCH b USING INTEGER PRIMARY KEY (rowid=?)
`--USE TEMP B-TREE FOR ORDER BY

I added this index:

CREATE INDEX pkg_id ON results (pkg_id);

And it turns the query plan into

QUERY PLAN
|--SEARCH r USING INDEX pkgresults (pkg_id=?)
|--SEARCH b USING INTEGER PRIMARY KEY (rowid=?)
`--USE TEMP B-TREE FOR ORDER BY

This helps tremendously with a query like in https://releng.netbsd.org/bulktracker/www/firefox.

Maybe the composite index

CREATE INDEX results_i_build_pkg ON results (build_id, pkg_id);

should go and be replaces with a pure build_id index.

[bsiegert]

Looking at this:

sqlite> explain query plan select r.*, p.* from results r, pkgs p where r.build_id == 644 and r.pkg_id ==
 p.pkg_id and r.pkg_name = 'libreoffice-24.2.1.2nb2';
QUERY PLAN
|--SEARCH TABLE results AS r USING INDEX results_i_build_pkg (build_id=?)
`--SEARCH TABLE pkgs AS p USING INTEGER PRIMARY KEY (rowid=?)

It seems to me that an index on just build_id would be enough.