GNU bug report logs - #43850
cuirass: inconsistent SQL queries execution time.

Previous Next

Full log

View this message in rfc822 format

From: help-debbugs <at> gnu.org (GNU bug Tracking System)
To: Mathieu Othacehe <othacehe <at> gnu.org>
Subject: bug#43850: closed (Re: bug#43850: cuirass: inconsistent SQL
 queries execution time.)
Date: Thu, 25 Mar 2021 12:47:02 +0000

[Message part 1 (text/plain, inline)]

Your bug report

#43850: cuirass: inconsistent SQL queries execution time.

which was filed against the guix package, has been closed.

The explanation is attached below, along with your original report.
If you require more details, please reply to 43850 <at> debbugs.gnu.org.

-- 
43850: http://debbugs.gnu.org/cgi/bugreport.cgi?bug=43850
GNU Bug Tracking System
Contact help-debbugs <at> gnu.org with problems

[Message part 2 (message/rfc822, inline)]

From: Mathieu Othacehe <othacehe <at> gnu.org>
To: 43850-done <at> debbugs.gnu.org
Subject: Re: bug#43850: cuirass: inconsistent SQL queries execution time.
Date: Thu, 25 Mar 2021 13:46:25 +0100

Closing as this is not any issue anymore since the switch to PostgreSQL.

Mathieu

[Message part 3 (message/rfc822, inline)]

From: Mathieu Othacehe <othacehe <at> gnu.org>
To: bug-guix <at> gnu.org
Subject: cuirass: inconsistent SQL queries execution time.
Date: Wed, 07 Oct 2020 18:04:52 +0200

Hello,

Over the last few weeks I made sure that all Cuirass SQL queries were
using indexes. As the "Builds" and "Outputs" tables can be really large,
having queries covered by indexes is imperative for consistent queries
duration.

However, I observed that some queries have inconsistent duration.

--8<---------------cut here---------------start------------->8---
2020-10-07T17:59:09 Database worker unresponsive for 5 seconds (db-get-builds-max).
2020-10-07T17:59:13 GET /build/3183151/details
2020-10-07T17:59:13 GET /eval/92
2020-10-07T17:59:14 builds request took 3.66e-4 seconds
2020-10-07T17:59:14 Database worker unresponsive for 5 seconds (db-get-builds-max).
2020-10-07T17:59:15 GET /eval/13234
2020-10-07T17:59:16 GET /build/3146487/details
2020-10-07T17:59:19 Database worker unresponsive for 5 seconds (db-get-builds-max).
--8<---------------cut here---------------end--------------->8---

"db-get-builds-max" query has the following query plan:

--8<---------------cut here---------------start------------->8---
QUERY PLAN
|--SEARCH TABLE Builds USING INDEX Builds_stoptime_id (stoptime=?)
`--SCALAR SUBQUERY 1
   `--SEARCH TABLE Builds USING INDEX Builds_evaluation_index (evaluation=?)
--8<---------------cut here---------------end--------------->8---

so it should always reasonably fast. The log seem to indicate that the
worker running one of those queries is busy for more than 10 seconds
here. My understanding of SQLite WAL mode is that writers should not
block readers. So I don't get why this query can be intermittently so
slow.

Thanks,

Mathieu
-- 
https://othacehe.org

Previous Next

GNU bug tracking system
Copyright (C) 1999 Darren O. Benham, 1997,2003 nCipher Corporation Ltd, 1994-97 Ian Jackson.