unofficial mirror of bug-guix@gnu.org 
 help / color / mirror / code / Atom feed
* bug#43850: cuirass: inconsistent SQL queries execution time.
@ 2020-10-07 16:04 Mathieu Othacehe
  2020-10-14 16:53 ` Mathieu Othacehe
  0 siblings, 1 reply; 11+ messages in thread
From: Mathieu Othacehe @ 2020-10-07 16:04 UTC (permalink / raw)
  To: 43850


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




^ permalink raw reply	[flat|nested] 11+ messages in thread

end of thread, other threads:[~2021-03-25 12:47 UTC | newest]

Thread overview: 11+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2020-10-07 16:04 bug#43850: cuirass: inconsistent SQL queries execution time Mathieu Othacehe
2020-10-14 16:53 ` Mathieu Othacehe
2020-10-20 11:45   ` Mathieu Othacehe
2020-10-22 11:49     ` Mathieu Othacehe
2020-10-26 22:59       ` Ludovic Courtès
2020-10-27  9:28         ` Christopher Baines
2020-10-27 14:10           ` Mathieu Othacehe
2020-10-27 18:11             ` Mathieu Othacehe
2021-03-25 12:46               ` Mathieu Othacehe
2020-10-31  4:33             ` Maxim Cournoyer
2020-11-01 18:23               ` Mathieu Othacehe

Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/guix.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).