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; 27 Oct 2020 18:11:55 +0000
From debbugs-submit-bounces <at> debbugs.gnu.org Tue Oct 27 14:11:55 2020
Received: from localhost ([127.0.0.1]:45754 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>)
	id 1kXTRr-0003SY-J0
	for submit <at> debbugs.gnu.org; Tue, 27 Oct 2020 14:11:55 -0400
Received: from eggs.gnu.org ([209.51.188.92]:55096)
 by debbugs.gnu.org with esmtp (Exim 4.84_2)
 (envelope-from <othacehe@HIDDEN>) id 1kXTRq-0003SM-BL
 for 43850 <at> debbugs.gnu.org; Tue, 27 Oct 2020 14:11:54 -0400
Received: from fencepost.gnu.org ([2001:470:142:3::e]:55544)
 by eggs.gnu.org with esmtp (Exim 4.90_1)
 (envelope-from <othacehe@HIDDEN>)
 id 1kXTRh-0001Nt-TA; Tue, 27 Oct 2020 14:11:47 -0400
Received: from [2a01:e0a:19b:d9a0:50c1:4a69:300e:4531] (port=40566 helo=cervin)
 by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.82) (envelope-from <othacehe@HIDDEN>)
 id 1kXTRR-0000iS-PE; Tue, 27 Oct 2020 14:11:38 -0400
From: Mathieu Othacehe <othacehe@HIDDEN>
To: Christopher Baines <mail@HIDDEN>
Subject: Re: bug#43850: cuirass: inconsistent SQL queries execution time.
References: <87a6wyhviz.fsf@HIDDEN> <87362gg35r.fsf@HIDDEN>
 <87362986k7.fsf@HIDDEN> <87blguzdig.fsf@HIDDEN>
 <87blgo8ufs.fsf@HIDDEN> <87d014yq3t.fsf@HIDDEN>
 <871rhjyd38.fsf@HIDDEN>
Date: Tue, 27 Oct 2020 19:11:28 +0100
In-Reply-To: <871rhjyd38.fsf@HIDDEN> (Mathieu Othacehe's message of "Tue, 27
 Oct 2020 15:10:03 +0100")
Message-ID: <87lffrh73j.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
Cc: 43850 <at> debbugs.gnu.org, Ludovic =?utf-8?Q?Court=C3=A8s?= <ludo@HIDDEN>
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 (---)


> I don't really get why I/O pressure on /dev/sdb could impact /dev/sda.

Turns out /tmp is mounted on /dev/sda, so all the building and ISO
production are first written on /dev/sda before being copied to the
store in /dev/sdb.

Reducing the build activity of berlin, as Ludo proposed should help
then.

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; 27 Oct 2020 14:10:13 +0000
From debbugs-submit-bounces <at> debbugs.gnu.org Tue Oct 27 10:10:13 2020
Received: from localhost ([127.0.0.1]:45349 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>)
	id 1kXPfx-0003by-HU
	for submit <at> debbugs.gnu.org; Tue, 27 Oct 2020 10:10:13 -0400
Received: from eggs.gnu.org ([209.51.188.92]:40110)
 by debbugs.gnu.org with esmtp (Exim 4.84_2)
 (envelope-from <othacehe@HIDDEN>) id 1kXPfw-0003bj-LA
 for 43850 <at> debbugs.gnu.org; Tue, 27 Oct 2020 10:10:12 -0400
Received: from fencepost.gnu.org ([2001:470:142:3::e]:51246)
 by eggs.gnu.org with esmtp (Exim 4.90_1)
 (envelope-from <othacehe@HIDDEN>)
 id 1kXPfq-0006VA-C3; Tue, 27 Oct 2020 10:10:06 -0400
Received: from [2a01:e0a:19b:d9a0:50c1:4a69:300e:4531] (port=38332 helo=cervin)
 by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.82) (envelope-from <othacehe@HIDDEN>)
 id 1kXPfp-0000H3-96; Tue, 27 Oct 2020 10:10:05 -0400
From: Mathieu Othacehe <othacehe@HIDDEN>
To: Christopher Baines <mail@HIDDEN>
Subject: Re: bug#43850: cuirass: inconsistent SQL queries execution time.
References: <87a6wyhviz.fsf@HIDDEN> <87362gg35r.fsf@HIDDEN>
 <87362986k7.fsf@HIDDEN> <87blguzdig.fsf@HIDDEN>
 <87blgo8ufs.fsf@HIDDEN> <87d014yq3t.fsf@HIDDEN>
Date: Tue, 27 Oct 2020 15:10:03 +0100
In-Reply-To: <87d014yq3t.fsf@HIDDEN> (Christopher Baines's message of
 "Tue, 27 Oct 2020 09:28:54 +0000")
Message-ID: <871rhjyd38.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
Cc: 43850 <at> debbugs.gnu.org, Ludovic =?utf-8?Q?Court=C3=A8s?= <ludo@HIDDEN>
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 Chris,

> I think Ricardo mentioned that the machine running Cuirass uses an SSD
> for the root filesystem, so moving the database there may help?

Looks like the database was already on the SSD before my tmpfs
experiment.

--8<---------------cut here---------------start------------->8---
mathieu@berlin ~$ df -h
Filesystem      Size  Used Avail Use% Mounted on
none             95G     0   95G   0% /dev
/dev/sda1       916G  321G  549G  37% /
/dev/sdb1        37T   34T  2.6T  94% /gnu
tmpfs            95G  8.0K   95G   1% /dev/shm
tmpfs            10G  2.4G  7.7G  24% /var/lib/cuirass_tmpfs
--8<---------------cut here---------------end--------------->8---

I don't really get why I/O pressure on /dev/sdb could impact /dev/sda.

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; 27 Oct 2020 09:29:01 +0000
From debbugs-submit-bounces <at> debbugs.gnu.org Tue Oct 27 05:29:01 2020
Received: from localhost ([127.0.0.1]:42893 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>)
	id 1kXLHp-0006Ra-33
	for submit <at> debbugs.gnu.org; Tue, 27 Oct 2020 05:29:01 -0400
Received: from mira.cbaines.net ([212.71.252.8]:39958)
 by debbugs.gnu.org with esmtp (Exim 4.84_2)
 (envelope-from <mail@HIDDEN>) id 1kXLHn-0006RS-3O
 for 43850 <at> debbugs.gnu.org; Tue, 27 Oct 2020 05:28:59 -0400
Received: from localhost (92.41.179.191.threembb.co.uk [92.41.179.191])
 by mira.cbaines.net (Postfix) with ESMTPSA id DBEDC27BBF2;
 Tue, 27 Oct 2020 09:28:57 +0000 (GMT)
Received: from capella (localhost [127.0.0.1])
 by localhost (OpenSMTPD) with ESMTP id 031037c7;
 Tue, 27 Oct 2020 09:28:55 +0000 (UTC)
References: <87a6wyhviz.fsf@HIDDEN> <87362gg35r.fsf@HIDDEN>
 <87362986k7.fsf@HIDDEN> <87blguzdig.fsf@HIDDEN> <87blgo8ufs.fsf@HIDDEN>
User-agent: mu4e 1.4.13; emacs 27.1
From: Christopher Baines <mail@HIDDEN>
To: Ludovic =?utf-8?Q?Court=C3=A8s?= <ludo@HIDDEN>, Mathieu Othacehe
 <othacehe@HIDDEN>
Subject: Re: bug#43850: cuirass: inconsistent SQL queries execution time.
In-reply-to: <87blgo8ufs.fsf@HIDDEN>
Date: Tue, 27 Oct 2020 09:28:54 +0000
Message-ID: <87d014yq3t.fsf@HIDDEN>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="=-=-=";
 micalg=pgp-sha512; protocol="application/pgp-signature"
X-Spam-Score: -0.0 (/)
X-Debbugs-Envelope-To: 43850
Cc: 43850 <at> debbugs.gnu.org
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: -1.0 (-)

--=-=-=
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


Ludovic Court=C3=A8s <ludo@HIDDEN> writes:

> Hi,
>
> Mathieu Othacehe <othacehe@HIDDEN> skribis:
>
>>> 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.
>
> Interesting.
>
>> 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.
>
> Hmm sounds risky, no?
>
> I wonder if we could instead ensure no I/O-intensive workload runs that
> machine.  I=E2=80=99m thinking in particular of the derivations that prod=
uce
> ISO/qcow images that are not offloaded but maybe should.
>
> WDYT?  Do you think that=E2=80=99d be enough?  Or is tmpfs our only hope?

I think Ricardo mentioned that the machine running Cuirass uses an SSD
for the root filesystem, so moving the database there may help?

--=-=-=
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQKlBAEBCgCPFiEEPonu50WOcg2XVOCyXiijOwuE9XcFAl+X6FZfFIAAAAAALgAo
aXNzdWVyLWZwckBub3RhdGlvbnMub3BlbnBncC5maWZ0aGhvcnNlbWFuLm5ldDNF
ODlFRUU3NDU4RTcyMEQ5NzU0RTBCMjVFMjhBMzNCMEI4NEY1NzcRHG1haWxAY2Jh
aW5lcy5uZXQACgkQXiijOwuE9XeKAA//W42HvAHLrMfMRdUSWjwc/r7CGqtznUtu
FvRaNWC3wuZEF8G3PVxWg6+4Hql7FjoT1m5c79/EOKwiOM2vfPs4djKAHRwdWNZe
xCevpKcuLuYV8lqergrmPUgZVaNYAZ1kZm9ysQHwnrreokuVaaRJMOJBkOglnlAG
a9mNAHyWR+SXQdPgm0FGDWcaI6k6r9LsqYorKu3xxgwIP1kcPQFWn2b0WpHMMyUu
o/VSNvZX+X/P2fpzH7yaPCpjV90iOwvPSbvF/OlwRemVGKt7wHvUdJu8Vyxb3L9y
6S2UXXAqnvOPs9X0yNnAQntYVkqPVu/Fz2kkSDFGIV6Y2xs82aGVGNSijvPf+AIx
pRzzVx4XFyvCUMSLm7JHyxIwBAm/Tj/EVeOeIrKaZrCf3mAB2D9VjDej2oTcmcK2
h0p9mP4du6kNb6ee/qfFLB8dxD2z5kYjlUIPLk9thmDvNSfum7Lh29JJewiGul0Z
gwN49Ghalc5Ch0otIm3bGBGucsDfCgrKtLUTYjWbOMqv0PZTcKvQj8X/m3yyqcbO
pTK1PsbjfA9BVOdaXxDDxJvZH2Vl8gr4EjxI7Gq0bzgxV6C6I4xwKYh3YAP7hBsG
PgRQ5LsBXYMWA50NesziZDTjr4A5TqGXfJDGsZdd4YqdJCgIlujDqQs/9QsOVeFh
9dvkk47YyIA=
=54te
-----END PGP SIGNATURE-----
--=-=-=--




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; 26 Oct 2020 23:00:04 +0000
From debbugs-submit-bounces <at> debbugs.gnu.org Mon Oct 26 19:00:04 2020
Received: from localhost ([127.0.0.1]:41962 helo=debbugs.gnu.org)
	by debbugs.gnu.org with esmtp (Exim 4.84_2)
	(envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>)
	id 1kXBTA-0000yH-JC
	for submit <at> debbugs.gnu.org; Mon, 26 Oct 2020 19:00:04 -0400
Received: from eggs.gnu.org ([209.51.188.92]:52384)
 by debbugs.gnu.org with esmtp (Exim 4.84_2)
 (envelope-from <ludo@HIDDEN>) id 1kXBT9-0000xE-2d
 for 43850 <at> debbugs.gnu.org; Mon, 26 Oct 2020 19:00:03 -0400
Received: from fencepost.gnu.org ([2001:470:142:3::e]:37224)
 by eggs.gnu.org with esmtp (Exim 4.90_1)
 (envelope-from <ludo@HIDDEN>) id 1kXBT3-00051S-OJ
 for 43850 <at> debbugs.gnu.org; Mon, 26 Oct 2020 18:59:57 -0400
Received: from [2a01:e0a:1d:7270:af76:b9b:ca24:c465] (port=37664 helo=ribbon)
 by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.82) (envelope-from <ludo@HIDDEN>)
 id 1kXBSy-0003xQ-M1; Mon, 26 Oct 2020 18:59:56 -0400
From: =?utf-8?Q?Ludovic_Court=C3=A8s?= <ludo@HIDDEN>
To: Mathieu Othacehe <othacehe@HIDDEN>
Subject: Re: bug#43850: cuirass: inconsistent SQL queries execution time.
References: <87a6wyhviz.fsf@HIDDEN> <87362gg35r.fsf@HIDDEN>
 <87362986k7.fsf@HIDDEN> <87blguzdig.fsf@HIDDEN>
X-URL: http://www.fdn.fr/~lcourtes/
X-Revolutionary-Date: 5 Brumaire an 229 de la =?utf-8?Q?R=C3=A9volution?=
X-PGP-Key-ID: 0x090B11993D9AEBB5
X-PGP-Key: http://www.fdn.fr/~lcourtes/ludovic.asc
X-PGP-Fingerprint: 3CE4 6455 8A84 FDC6 9DB4  0CFB 090B 1199 3D9A EBB5
X-OS: x86_64-pc-linux-gnu
Date: Mon, 26 Oct 2020 23:59:51 +0100
In-Reply-To: <87blguzdig.fsf@HIDDEN> (Mathieu Othacehe's message of "Thu, 22
 Oct 2020 13:49:43 +0200")
Message-ID: <87blgo8ufs.fsf@HIDDEN>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
X-Spam-Score: -2.3 (--)
X-Debbugs-Envelope-To: 43850
Cc: 43850 <at> debbugs.gnu.org
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 (---)

Hi,

Mathieu Othacehe <othacehe@HIDDEN> skribis:

>> 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.

Interesting.

> 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.

Hmm sounds risky, no?

I wonder if we could instead ensure no I/O-intensive workload runs that
machine.  I=E2=80=99m thinking in particular of the derivations that produce
ISO/qcow images that are not offloaded but maybe should.

WDYT?  Do you think that=E2=80=99d be enough?  Or is tmpfs our only hope?

Thanks,
Ludo=E2=80=99.




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; 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: Tue, 27 Oct 2020 18:15:01 UTC

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