[lxc-devel] [lxd/master] Speed up execution of update from v11 of the db

[freeekanayaka on Github]

A non-text attachment was scrubbed...
Name: not available
Type: text/x-mailbox
Size: 361 bytes
Desc: not available
URL: <http://lists.linuxcontainers.org/pipermail/lxc-devel/attachments/20181015/9f2a7db7/attachment.bin>
-------------- next part --------------
From d6b03e0465c297d4f84026e8e1a2833df92682a5 Mon Sep 17 00:00:00 2001
From: Free Ekanayaka <free.ekanayaka at canonical.com>
Date: Mon, 15 Oct 2018 19:36:01 +0200
Subject: [PATCH] Speed up execution of update from v11 of the db

Signed-off-by: Free Ekanayaka <free.ekanayaka at canonical.com>
---
 lxd/db/cluster/update.go | 23 +++++++++++++++++++++++
 1 file changed, 23 insertions(+)

diff --git a/lxd/db/cluster/update.go b/lxd/db/cluster/update.go
index 91101840a3..19c4e4efb3 100644
--- a/lxd/db/cluster/update.go
+++ b/lxd/db/cluster/update.go
@@ -80,6 +80,18 @@ func updateFromV11(tx *sql.Tx) error {
 		return errors.Wrap(err, "Failed to count rows in current tables")
 	}
 
+	// Temporarily increase the cache size and disable page spilling, to
+	// avoid unnecessary writes to the WAL.
+	_, err = tx.Exec("PRAGMA cache_size=100000")
+	if err != nil {
+		return errors.Wrap(err, "Increase cache size")
+	}
+
+	_, err = tx.Exec("PRAGMA cache_spill=0")
+	if err != nil {
+		return errors.Wrap(err, "Disable spilling cache pages to disk")
+	}
+
 	// Use a large timeout since the update might take a while, due to the
 	// new indexes being created.
 	ctx, cancel := context.WithTimeout(context.Background(), time.Minute)
@@ -528,6 +540,17 @@ CREATE VIEW profiles_used_by_ref (project, name, value) AS
 		}
 	}
 
+	// Restore default cache values.
+	_, err = tx.Exec("PRAGMA cache_size=2000")
+	if err != nil {
+		return errors.Wrap(err, "Increase cache size")
+	}
+
+	_, err = tx.Exec("PRAGMA cache_spill=1")
+	if err != nil {
+		return errors.Wrap(err, "Disable spilling cache pages to disk")
+	}
+
 	return err
 }