branch master updated: cuirass: Perform some database "optimization" at startup.

[Christopher Baines]

This is an automated email from the git hooks/post-receive script.

cbaines pushed a commit to branch master
in repository guix-cuirass.

The following commit(s) were added to refs/heads/master by this push:
     new 9265949  cuirass: Perform some database "optimization" at startup.
9265949 is described below

commit 9265949cf669b9e861bc85a1f1c3954cf4e216cc
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Mon May 25 11:08:20 2020 +0100

    cuirass: Perform some database "optimization" at startup.
    
    Add a "optimize" step that occurs when starting up the main Curiass
    process. Currently this does two things, but could be extended to do more.
    
    The "PRAGMA optimize;" command prompts SQLite to ANALYZE tables where that
    might help. The "PRAGMA wal_checkpoint(TRUNCATE);" command has SQLite 
process
    any unprocessed changes from the WAL file, then truncate it to 0 bytes. I've
    got no data to suggest this helps with performance, but I'm hoping that 
going
    from a large WAL file to a small one occasionally might be useful.
    
    * src/cuirass/database.scm (db-optimize): New procedure.
    * bin/cuirass.in (main): Run it.
---
 bin/cuirass.in           | 4 ++++
 src/cuirass/database.scm | 8 ++++++++
 2 files changed, 12 insertions(+)

diff --git a/bin/cuirass.in b/bin/cuirass.in
index fbc7c3c..7a2d5ae 100644
--- a/bin/cuirass.in
+++ b/bin/cuirass.in
@@ -124,6 +124,10 @@ exec ${GUILE:-@GUILE@} --no-auto-compile -e main -s "$0" 
"$@"
                              (min (current-processor-count) 4))))
           (prepare-git)
 
+          (unless (option-ref opts 'web #f)
+            (log-message "performing database optimizations")
+            (db-optimize))
+
           (log-message "running Fibers on ~a kernel threads" threads)
           (run-fibers
            (lambda ()
diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm
index f80585e..e81ead0 100644
--- a/src/cuirass/database.scm
+++ b/src/cuirass/database.scm
@@ -38,6 +38,7 @@
             db-init
             db-open
             db-close
+            db-optimize
             db-add-specification
             db-remove-specification
             db-get-specifications
@@ -277,6 +278,13 @@ database object."
   "Close database object DB."
   (sqlite-close db))
 
+(define* (db-optimize #:optional (db-file (%package-database)))
+  "Open the database and perform optimizations."
+  (let ((db (db-open db-file)))
+    (sqlite-exec db "PRAGMA optimize;")
+    (sqlite-exec db "PRAGMA wal_checkpoint(TRUNCATE);")
+    (db-close db)))
+
 (define (last-insert-rowid db)
   (vector-ref (car (sqlite-exec db "SELECT last_insert_rowid();"))
               0))