From eb6f6ff9b8b78f92dd4244440ec714f6a3ec4e11 Mon Sep 17 00:00:00 2001
From: Joey Hess <joeyh@joeyh.name>
Date: Mon, 31 May 2021 14:56:14 -0400
Subject: [PATCH] speed up keys database writes

There seems to be no reason to check the time here. I think it was
inherited from code in Database.Fsck, which does have a reason to commit
every few minutes. Removing that syscall speeds up a git-annex init
in a repo with 100000 annexed files by about 3 seconds.

Sponsored-by: Dartmouth College's Datalad project
---
 Database/Fsck.hs                               |  4 +++-
 Database/Keys/SQL.hs                           |  9 ++-------
 ..._Scanning_for_unlocked_files_...__34__.mdwn |  3 +++
 ...3_440624874dd3697dd538655765f2b6a2._comment | 18 ++++++++++++++++++
 4 files changed, 26 insertions(+), 8 deletions(-)
 create mode 100644 doc/todo/Avoid_lengthy___34__Scanning_for_unlocked_files_...__34__/comment_13_440624874dd3697dd538655765f2b6a2._comment

diff --git a/Database/Fsck.hs b/Database/Fsck.hs
index c1e9841978..ab7a14c95e 100644
--- a/Database/Fsck.hs
+++ b/Database/Fsck.hs
@@ -88,7 +88,9 @@ addDb :: FsckHandle -> Key -> IO ()
 addDb (FsckHandle h _) k = H.queueDb h checkcommit $
 	void $ insertUnique $ Fscked k
   where
-	-- commit queue after 1000 files or 5 minutes, whichever comes first
+	-- Commit queue after 1000 changes or 5 minutes, whichever comes first.
+	-- The time based commit allows for an incremental fsck to be
+	-- interrupted and not lose much work.
 	checkcommit sz lastcommittime
 		| sz > 1000 = return True
 		| otherwise = do
diff --git a/Database/Keys/SQL.hs b/Database/Keys/SQL.hs
index 7d191bfb4c..5aed3db7b4 100644
--- a/Database/Keys/SQL.hs
+++ b/Database/Keys/SQL.hs
@@ -27,7 +27,6 @@ import Git.FilePath
 
 import Database.Persist.Sql hiding (Key)
 import Database.Persist.TH
-import Data.Time.Clock
 import Control.Monad
 import Data.Maybe
 
@@ -77,12 +76,8 @@ newtype WriteHandle = WriteHandle H.DbQueue
 queueDb :: SqlPersistM () -> WriteHandle -> IO ()
 queueDb a (WriteHandle h) = H.queueDb h checkcommit a
   where
-	-- commit queue after 1000 changes or 5 minutes, whichever comes first
-	checkcommit sz lastcommittime
-		| sz > 1000 = return True
-		| otherwise = do
-			now <- getCurrentTime
-			return $ diffUTCTime now lastcommittime > 300
+	-- commit queue after 1000 changes
+	checkcommit sz _lastcommittime = pure (sz > 1000)
 
 addAssociatedFile :: Key -> TopFilePath -> WriteHandle -> IO ()
 addAssociatedFile k f = queueDb $ do
diff --git a/doc/todo/Avoid_lengthy___34__Scanning_for_unlocked_files_...__34__.mdwn b/doc/todo/Avoid_lengthy___34__Scanning_for_unlocked_files_...__34__.mdwn
index fdd7ee7978..a20f7955ee 100644
--- a/doc/todo/Avoid_lengthy___34__Scanning_for_unlocked_files_...__34__.mdwn
+++ b/doc/todo/Avoid_lengthy___34__Scanning_for_unlocked_files_...__34__.mdwn
@@ -4,3 +4,6 @@ E.g. following idea came to mind: git-annex could add some flag/beacon file (e.g
 
 [[!meta author=yoh]]
 [[!tag projects/datalad]]
+
+> I think I've improved this all that it can reasonably be sped up,
+> so [[done]]. --[[Joey]]
diff --git a/doc/todo/Avoid_lengthy___34__Scanning_for_unlocked_files_...__34__/comment_13_440624874dd3697dd538655765f2b6a2._comment b/doc/todo/Avoid_lengthy___34__Scanning_for_unlocked_files_...__34__/comment_13_440624874dd3697dd538655765f2b6a2._comment
new file mode 100644
index 0000000000..9c0208fcd1
--- /dev/null
+++ b/doc/todo/Avoid_lengthy___34__Scanning_for_unlocked_files_...__34__/comment_13_440624874dd3697dd538655765f2b6a2._comment
@@ -0,0 +1,18 @@
+[[!comment format=mdwn
+ username="joey"
+ subject="""comment 13"""
+ date="2021-05-31T18:40:59Z"
+ content="""
+There was an unncessary check of the current time per sql insert, removing
+that sped it up by 3 seconds in my benchmark.
+
+Also tried increasing the number of inserts per sqlite transaction from 1k
+to 10k. Memory use increased to 90 mb, but no measurable speed increase.
+
+I don't see much else that can speed up the sqlite part, without going deep
+into the weeds of populating sqlite databases without using sql, or using
+multi-value inserts ([like described here](https://medium.com/@JasonWyatt/squeezing-performance-from-sqlite-insertions-971aff98eef2).
+Both would prevent using persistent to abstract sql away, and would
+only be usable in this case, not speeding up git-annex generally, 
+so not too enthused.
+"""]]
-- 
2.30.2