bug#43588: cuirass: Slow SQL queries.

[Mathieu Othacehe]

Hello,

I just added SQL queries logging to Cuirass. I also adapted the Guix
shepherd service accordingly and deployed it on berlin.

The results are already interesting:

--8<---------------cut here---------------start------------->8---
SELECT E.id, E.status, E.timestamp, E.checkouttime, E.evaltime, B.total, 
B.succeeded, B.failed, B.scheduled FROM (SELECT id, status, timestamp, 
checkouttime, evaltime FROM Evaluations WHERE (id='16009' )) E LEFT JOIN 
(SELECT rowid, evaluation, SUM(status=0) as succeeded, SUM(status>0) as failed, 
SUM(status<0) as scheduled, SUM(status>-100) as total FROM Builds GROUP BY 
evaluation) B ON B.evaluation=E.id ORDER BY E.id ASC 95.38
SELECT E.id, E.status, E.timestamp, E.checkouttime, E.evaltime, B.total, 
B.succeeded, B.failed, B.scheduled FROM (SELECT id, status, timestamp, 
checkouttime, evaltime FROM Evaluations WHERE (id='5407' )) E LEFT JOIN (SELECT 
rowid, evaluation, SUM(status=0) as succeeded, SUM(status>0) as failed, 
SUM(status<0) as scheduled, SUM(status>-100) as total FROM Builds GROUP BY 
evaluation) B ON B.evaluation=E.id ORDER BY E.id ASC 65.88
SELECT E.id, E.status, E.timestamp, E.checkouttime, E.evaltime, B.total, 
B.succeeded, B.failed, B.scheduled FROM (SELECT id, status, timestamp, 
checkouttime, evaltime FROM Evaluations WHERE (id='8255' )) E LEFT JOIN (SELECT 
rowid, evaluation, SUM(status=0) as succeeded, SUM(status>0) as failed, 
SUM(status<0) as scheduled, SUM(status>-100) as total FROM Builds GROUP BY 
evaluation) B ON B.evaluation=E.id ORDER BY E.id ASC 86.23
--8<---------------cut here---------------end--------------->8---

Those three queries that originate from "db-get-evaluation-summary"
procedure take more than 1 minute to run (65.88, 86.23 and 95.38
seconds). Even if concurrent database reading is permitted, the number
of database workers is finite and some of the observed infamous "504"
errors are the result of database worker starvation.

If you want to help me optimize those queries, you can have a look to
"/var/log/cuirass-sql.log" and "/var/log/cuirass-web-sql.log" on berlin,
or request those files.

Thanks,

Mathieu

-- 
https://othacehe.org