bug#43850: cuirass: inconsistent SQL queries execution time.

[Maxim Cournoyer <maxim.cournoyer@gmail.com>]

Hello,

Mathieu Othacehe <othacehe@gnu.org> writes:

> 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.
>
> 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
>
> I don't really get why I/O pressure on /dev/sdb could impact /dev/sda.
>
> Thanks,
>
> Mathieu

As an aside, running

--8<---------------cut here---------------start------------->8---
sudo sqlite3 /var/guix/db/db.sqlite vacuum
--8<---------------cut here---------------end--------------->8---

shaved off some 40 Mb from my large database file:

-rw-r--r-- 1 root root 468889600 Oct 31 00:16 db.sqlite
-rw-r--r-- 1 root root 510648320 Oct 28 23:36 db.sqlite.bak

Perhaps we should run 'vacuum' when invoking 'guix gc' or at some other
key places (where lots of data gets removed from the DB).  There's also
the auto_vacuum PRAGMA, which is not enabled currently:

--8<---------------cut here---------------start------------->8---
sqlite3 /var/guix/db/db.sqlite 'pragma auto_vacuum'
0
--8<---------------cut here---------------end--------------->8---

But the later doesn't necessarily sound like a good idea:

   Note, however, that auto-vacuum only truncates the freelist pages from
   the file. Auto-vacuum does not defragment the database nor repack
   individual database pages the way that the VACUUM command does. In fact,
   because it moves pages around within the file, auto-vacuum can actually
   make fragmentation worse. [0]

[0]:  https://www.sqlite.org/pragma.html#pragma_auto_vacuum

Maxim