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

Please note: This is a static page, with minimal formatting, updated once a day.
Click here to see this page with the latest information and nicer formatting.

Package: guix; Reported by: Mathieu Othacehe <othacehe@HIDDEN>; dated Wed, 7 Oct 2020 16:06:02 UTC; Maintainer for guix is bug-guix@HIDDEN.

Message received at 43850 <at> debbugs.gnu.org:


Received: (at 43850) by debbugs.gnu.org; 22 Oct 2020 11:49:51 +0000
From debbugs-submit-bounces <at> debbugs.gnu.org Thu Oct 22 07:49:51 2020
Received: from localhost ([127.0.0.1]:51612 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>)
	id 1kVZ6N-0007K2-Jo
	for submit <at> debbugs.gnu.org; Thu, 22 Oct 2020 07:49:51 -0400
Received: from eggs.gnu.org ([209.51.188.92]:34308)
 by debbugs.gnu.org with esmtp (Exim 4.84_2)
 (envelope-from <othacehe@HIDDEN>) id 1kVZ6M-0007Jq-FK
 for 43850 <at> debbugs.gnu.org; Thu, 22 Oct 2020 07:49:50 -0400
Received: from fencepost.gnu.org ([2001:470:142:3::e]:41771)
 by eggs.gnu.org with esmtp (Exim 4.90_1)
 (envelope-from <othacehe@HIDDEN>) id 1kVZ6H-0007iL-5e
 for 43850 <at> debbugs.gnu.org; Thu, 22 Oct 2020 07:49:45 -0400
Received: from [2a01:e0a:19b:d9a0:50c1:4a69:300e:4531] (port=44898 helo=cervin)
 by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.82) (envelope-from <othacehe@HIDDEN>) id 1kVZ6G-0005Q4-LE
 for 43850 <at> debbugs.gnu.org; Thu, 22 Oct 2020 07:49:44 -0400
From: Mathieu Othacehe <othacehe@HIDDEN>
To: 43850 <at> debbugs.gnu.org
Subject: Re: bug#43850: cuirass: inconsistent SQL queries execution time.
References: <87a6wyhviz.fsf@HIDDEN> <87362gg35r.fsf@HIDDEN>
 <87362986k7.fsf@HIDDEN>
Date: Thu, 22 Oct 2020 13:49:43 +0200
In-Reply-To: <87362986k7.fsf@HIDDEN> (Mathieu Othacehe's message of "Tue, 20
 Oct 2020 13:45:12 +0200")
Message-ID: <87blguzdig.fsf@HIDDEN>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain
X-Spam-Score: -2.3 (--)
X-Debbugs-Envelope-To: 43850
X-BeenThere: debbugs-submit <at> debbugs.gnu.org
X-Mailman-Version: 2.1.18
Precedence: list
List-Id: <debbugs-submit.debbugs.gnu.org>
List-Unsubscribe: <https://debbugs.gnu.org/cgi-bin/mailman/options/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=unsubscribe>
List-Archive: <https://debbugs.gnu.org/cgi-bin/mailman/private/debbugs-submit/>
List-Post: <mailto:debbugs-submit <at> debbugs.gnu.org>
List-Help: <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=help>
List-Subscribe: <https://debbugs.gnu.org/cgi-bin/mailman/listinfo/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=subscribe>
Errors-To: debbugs-submit-bounces <at> debbugs.gnu.org
Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org>
X-Spam-Score: -3.3 (---)


Hello,

> I have now copied the database to a tmpfs mounted directory to make sure
> that those inconsistent duration are only caused by the I/O pressure on
> berlin.

This helps a lot. The Cuirass web service has been running smooth since
two days, without any inconsistent query times.

I'm considering using a tmpfs backed database for good. The problem is
that we would need a save/restore mechanism in case Berlin
reboots.

WDYT?

Thanks,

Mathieu




Information forwarded to bug-guix@HIDDEN:
bug#43850; Package guix. Full text available.

Message received at 43850 <at> debbugs.gnu.org:


Received: (at 43850) by debbugs.gnu.org; 20 Oct 2020 11:45:22 +0000
From debbugs-submit-bounces <at> debbugs.gnu.org Tue Oct 20 07:45:22 2020
Received: from localhost ([127.0.0.1]:43814 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>)
	id 1kUq4w-0002vu-8G
	for submit <at> debbugs.gnu.org; Tue, 20 Oct 2020 07:45:22 -0400
Received: from eggs.gnu.org ([209.51.188.92]:51582)
 by debbugs.gnu.org with esmtp (Exim 4.84_2)
 (envelope-from <othacehe@HIDDEN>) id 1kUq4u-0002ob-Eg
 for 43850 <at> debbugs.gnu.org; Tue, 20 Oct 2020 07:45:20 -0400
Received: from fencepost.gnu.org ([2001:470:142:3::e]:54254)
 by eggs.gnu.org with esmtp (Exim 4.90_1)
 (envelope-from <othacehe@HIDDEN>) id 1kUq4p-0003xo-4T
 for 43850 <at> debbugs.gnu.org; Tue, 20 Oct 2020 07:45:15 -0400
Received: from [2a01:e0a:19b:d9a0:50c1:4a69:300e:4531] (port=53436 helo=cervin)
 by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.82) (envelope-from <othacehe@HIDDEN>) id 1kUq4n-0008Q7-Lg
 for 43850 <at> debbugs.gnu.org; Tue, 20 Oct 2020 07:45:14 -0400
From: Mathieu Othacehe <othacehe@HIDDEN>
To: 43850 <at> debbugs.gnu.org
Subject: Re: bug#43850: cuirass: inconsistent SQL queries execution time.
References: <87a6wyhviz.fsf@HIDDEN> <87362gg35r.fsf@HIDDEN>
Date: Tue, 20 Oct 2020 13:45:12 +0200
In-Reply-To: <87362gg35r.fsf@HIDDEN> (Mathieu Othacehe's message of "Wed, 14
 Oct 2020 18:53:20 +0200")
Message-ID: <87362986k7.fsf@HIDDEN>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain
X-Spam-Score: -2.3 (--)
X-Debbugs-Envelope-To: 43850
X-BeenThere: debbugs-submit <at> debbugs.gnu.org
X-Mailman-Version: 2.1.18
Precedence: list
List-Id: <debbugs-submit.debbugs.gnu.org>
List-Unsubscribe: <https://debbugs.gnu.org/cgi-bin/mailman/options/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=unsubscribe>
List-Archive: <https://debbugs.gnu.org/cgi-bin/mailman/private/debbugs-submit/>
List-Post: <mailto:debbugs-submit <at> debbugs.gnu.org>
List-Help: <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=help>
List-Subscribe: <https://debbugs.gnu.org/cgi-bin/mailman/listinfo/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=subscribe>
Errors-To: debbugs-submit-bounces <at> debbugs.gnu.org
Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org>
X-Spam-Score: -3.3 (---)


Hello,

> this should improve the situation, even if I still observe some
> inconsistent execution duration.

I tried to use the two following pragma:

--8<---------------cut here---------------start------------->8---
PRAGMA synchronous = OFF
PRAGMA mmap_size = 10737418240
--8<---------------cut here---------------end--------------->8---

hoping that mmaping the Cuirass database and disabling synchronisation
would help, but there were still inconsistent duration.

I have now copied the database to a tmpfs mounted directory to make sure
that those inconsistent duration are only caused by the I/O pressure on
berlin.

Thanks,

Mathieu




Information forwarded to bug-guix@HIDDEN:
bug#43850; Package guix. Full text available.

Message received at 43850 <at> debbugs.gnu.org:


Received: (at 43850) by debbugs.gnu.org; 14 Oct 2020 16:53:30 +0000
From debbugs-submit-bounces <at> debbugs.gnu.org Wed Oct 14 12:53:30 2020
Received: from localhost ([127.0.0.1]:52906 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>)
	id 1kSk1p-0000Kf-Qr
	for submit <at> debbugs.gnu.org; Wed, 14 Oct 2020 12:53:30 -0400
Received: from eggs.gnu.org ([209.51.188.92]:34014)
 by debbugs.gnu.org with esmtp (Exim 4.84_2)
 (envelope-from <othacehe@HIDDEN>) id 1kSk1o-0000KQ-BZ
 for 43850 <at> debbugs.gnu.org; Wed, 14 Oct 2020 12:53:28 -0400
Received: from fencepost.gnu.org ([2001:470:142:3::e]:32822)
 by eggs.gnu.org with esmtp (Exim 4.90_1)
 (envelope-from <othacehe@HIDDEN>) id 1kSk1j-0007w2-1a
 for 43850 <at> debbugs.gnu.org; Wed, 14 Oct 2020 12:53:23 -0400
Received: from [2a01:cb18:832e:5f00:1013:8f9a:f686:d929] (port=37156
 helo=cervin)
 by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.82) (envelope-from <othacehe@HIDDEN>) id 1kSk1i-00034V-Li
 for 43850 <at> debbugs.gnu.org; Wed, 14 Oct 2020 12:53:22 -0400
From: Mathieu Othacehe <othacehe@HIDDEN>
To: 43850 <at> debbugs.gnu.org
Subject: Re: bug#43850: cuirass: inconsistent SQL queries execution time.
References: <87a6wyhviz.fsf@HIDDEN>
Date: Wed, 14 Oct 2020 18:53:20 +0200
In-Reply-To: <87a6wyhviz.fsf@HIDDEN> (Mathieu Othacehe's message of "Wed, 07
 Oct 2020 18:04:52 +0200")
Message-ID: <87362gg35r.fsf@HIDDEN>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain
X-Spam-Score: -2.3 (--)
X-Debbugs-Envelope-To: 43850
X-BeenThere: debbugs-submit <at> debbugs.gnu.org
X-Mailman-Version: 2.1.18
Precedence: list
List-Id: <debbugs-submit.debbugs.gnu.org>
List-Unsubscribe: <https://debbugs.gnu.org/cgi-bin/mailman/options/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=unsubscribe>
List-Archive: <https://debbugs.gnu.org/cgi-bin/mailman/private/debbugs-submit/>
List-Post: <mailto:debbugs-submit <at> debbugs.gnu.org>
List-Help: <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=help>
List-Subscribe: <https://debbugs.gnu.org/cgi-bin/mailman/listinfo/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=subscribe>
Errors-To: debbugs-submit-bounces <at> debbugs.gnu.org
Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org>
X-Spam-Score: -3.3 (---)


Hello,

I pushed and deployed several patches that:

- update metrics in a single transaction
- register builds in a single transaction
- use a single write database worker, queuing queries and submitting
them by batches (in a single transaction).
- optimize some SQLite parameters (decrease WAL size, use more RAM
caching)

this should improve the situation, even if I still observe some
inconsistent execution duration.

I still have a few improvement ideas such as pre-allocating a large
database file to decrease fragmentation and running periodic vacuums.

Thanks,

Mathieu




Information forwarded to bug-guix@HIDDEN:
bug#43850; Package guix. Full text available.

Message received at submit <at> debbugs.gnu.org:


Received: (at submit) by debbugs.gnu.org; 7 Oct 2020 16:05:06 +0000
From debbugs-submit-bounces <at> debbugs.gnu.org Wed Oct 07 12:05:06 2020
Received: from localhost ([127.0.0.1]:57792 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>)
	id 1kQBwA-0007Vu-G7
	for submit <at> debbugs.gnu.org; Wed, 07 Oct 2020 12:05:06 -0400
Received: from lists.gnu.org ([209.51.188.17]:40864)
 by debbugs.gnu.org with esmtp (Exim 4.84_2)
 (envelope-from <othacehe@HIDDEN>) id 1kQBw6-0007VV-70
 for submit <at> debbugs.gnu.org; Wed, 07 Oct 2020 12:05:05 -0400
Received: from eggs.gnu.org ([2001:470:142:3::10]:35432)
 by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256)
 (Exim 4.90_1) (envelope-from <othacehe@HIDDEN>) id 1kQBw3-0007I8-K7
 for bug-guix@HIDDEN; Wed, 07 Oct 2020 12:05:01 -0400
Received: from fencepost.gnu.org ([2001:470:142:3::e]:47221)
 by eggs.gnu.org with esmtp (Exim 4.90_1)
 (envelope-from <othacehe@HIDDEN>) id 1kQBw2-0001iz-1Z
 for bug-guix@HIDDEN; Wed, 07 Oct 2020 12:04:59 -0400
Received: from [2a01:e0a:19b:d9a0:4192:d6b6:52f:260e] (port=51108 helo=cervin)
 by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.82) (envelope-from <othacehe@HIDDEN>) id 1kQBvy-0005oC-Nj
 for bug-guix@HIDDEN; Wed, 07 Oct 2020 12:04:54 -0400
From: Mathieu Othacehe <othacehe@HIDDEN>
To: bug-guix@HIDDEN
Subject: cuirass: inconsistent SQL queries execution time.
Date: Wed, 07 Oct 2020 18:04:52 +0200
Message-ID: <87a6wyhviz.fsf@HIDDEN>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain
X-Spam-Score: -2.3 (--)
X-Debbugs-Envelope-To: submit
X-BeenThere: debbugs-submit <at> debbugs.gnu.org
X-Mailman-Version: 2.1.18
Precedence: list
List-Id: <debbugs-submit.debbugs.gnu.org>
List-Unsubscribe: <https://debbugs.gnu.org/cgi-bin/mailman/options/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=unsubscribe>
List-Archive: <https://debbugs.gnu.org/cgi-bin/mailman/private/debbugs-submit/>
List-Post: <mailto:debbugs-submit <at> debbugs.gnu.org>
List-Help: <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=help>
List-Subscribe: <https://debbugs.gnu.org/cgi-bin/mailman/listinfo/debbugs-submit>, 
 <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=subscribe>
Errors-To: debbugs-submit-bounces <at> debbugs.gnu.org
Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org>
X-Spam-Score: -3.3 (---)


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




Acknowledgement sent to Mathieu Othacehe <othacehe@HIDDEN>:
New bug report received and forwarded. Copy sent to bug-guix@HIDDEN. Full text available.
Report forwarded to bug-guix@HIDDEN:
bug#43850; Package guix. Full text available.
Please note: This is a static page, with minimal formatting, updated once a day.
Click here to see this page with the latest information and nicer formatting.
Last modified: Thu, 22 Oct 2020 12:00:02 UTC

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