[PATCH 1/2] Use SQL Lite databases for LocalStatus (instead of flat files)

[stewart@xxxxxxxxxxxxxxxx]

From: Stewart Smith <stewart@willster.(none)>

The aim of this patch is to:
a) reduce memory footprint on large mailboxes
b) reduce CPU usage maintaining LocalStatus
c) scale better with large mailboxes

Amazingly enough it does pretty much do this.

The additional patch (updateflags) commits less often, making something
like "mark as read" 100,000 messages locally complete way faster than
committing on every flag update.

Performance:

- Tested on a single 411MB Maildir with 12,701 messages.
- Filesystems:
        - from remote XFS on raid1 (both disks on same IDE controller)
        - to local XFS
        - both are real file systems and well aged.
- Connection to IMAP server was via 802.11g directly to the imapd. The
only time the network was remotely saturated in bandwidth was when
syncing large mail messages.
- imapd was courier-imap.
- MaxConnections for the remote repository was 3.
        - NOTE: total sync time was no different between the unpatched and
patched versions, suggesting that threads for a single mailbox does
nothing to improve performance.
- preauthtunnel was ssh
- ui was TTYUI.

Disclaimer: I am no python programmer... I hack clustered database
internals... newbie python mistakes welcomed to be pointed out.

Unpatched offlineimap took:

For the initial sync (all messages):
                real    9m35.872s
                user    4m20.196s
                sys     0m39.158s

With resident memory going from 22MB to 30MB to 40MB.
With CPU usage climing from ~35% to ~70%

The second sync (doing nothing) took:
                real    0m6.846s
                user    0m2.940s
                sys     0m0.724s

WITH THE PATCH::
----------------
With this patch, offlineimap took:
                real    9m26.239s
                user    0m43.595s
                sys     0m20.113s

Real time saved: 10seconds (insignificant)
CPU time saved: 3min 40seconds user! and 19seconds of system!

For the second (doing nothing) sync:
1st run:
                real    0m11.403s
                user    0m4.408s
                sys     0m1.320s
2nd run:
                real    0m6.893s
                user    0m4.260s
                sys     0m1.392s

So it looks like the null sync may take a bit more real time and a bit
more CPU. IMHO this is an acceptable hit.

With the update flags patch (also included), marking ~13,000 messages as
read in localstatus takes essentially no time.

I also experimented with bulk committing new mail messages to
LocalStatus (every 10 and every 100 messages). It was possible to shave
~30 and ~60 seconds off the initial sync respectively. Although I don't
think the possibility of duplicate messages is really worth it at this
stage. I think there are other (better) ways to improve initial sync
performance than this.

I have been using this patch since I first mailed it back in March - and
am very happy with it (and have had no problems).
---
 offlineimap/accounts.py           |    6 +-
 offlineimap/folder/Base.py        |   32 ++++++--
 offlineimap/folder/IMAP.py        |    2 +-
 offlineimap/folder/LocalStatus.py |  156 +++++++++++++++++++++++++------------
 4 files changed, 133 insertions(+), 63 deletions(-)

diff --git a/offlineimap/accounts.py b/offlineimap/accounts.py
index 8e96347..2a6d848 100644
--- a/offlineimap/accounts.py
+++ b/offlineimap/accounts.py
@@ -205,13 +205,13 @@ def syncfolder(accountname, remoterepos, remotefolder, 
localrepos,
     ui.syncingfolder(remoterepos, remotefolder, localrepos, localfolder)
     ui.loadmessagelist(localrepos, localfolder)
     localfolder.cachemessagelist()
-    ui.messagelistloaded(localrepos, localfolder, 
len(localfolder.getmessagelist().keys()))
+    ui.messagelistloaded(localrepos, localfolder, 
localfolder.getmessagecount())
 
     # If either the local or the status folder has messages and there is a UID
     # validity problem, warn and abort.  If there are no messages, UW IMAPd
     # loses UIDVALIDITY.  But we don't really need it if both local folders are
     # empty.  So, in that case, just save it off.
-    if len(localfolder.getmessagelist()) or len(statusfolder.getmessagelist()):
+    if localfolder.getmessagecount() or statusfolder.getmessagecount():
         if not localfolder.isuidvalidityok():
             ui.validityproblem(localfolder)
            localrepos.restore_atime()
@@ -228,7 +228,7 @@ def syncfolder(accountname, remoterepos, remotefolder, 
localrepos,
     ui.loadmessagelist(remoterepos, remotefolder)
     remotefolder.cachemessagelist()
     ui.messagelistloaded(remoterepos, remotefolder,
-                         len(remotefolder.getmessagelist().keys()))
+                         remotefolder.getmessagecount())
 
 
     #
diff --git a/offlineimap/folder/Base.py b/offlineimap/folder/Base.py
index 9b29ab3..9694e65 100644
--- a/offlineimap/folder/Base.py
+++ b/offlineimap/folder/Base.py
@@ -129,6 +129,24 @@ class BaseFolder:
         You must call cachemessagelist() before calling this function!"""
         raise NotImplementedException
 
+    def uidexists(self,uid):
+        """Returns true if uid exists"""
+       mlist = self.getmessagelist()
+       if uid in mlist:
+               return 1
+       else:
+               return 0
+       return 0
+
+    def getmessageuidlist(self):
+        """Gets a list of UIDs.
+        You may have to call cachemessagelist() before calling this 
function!"""
+       return self.getmessagelist().keys()
+
+    def getmessagecount(self):
+        """Gets the number of messages."""
+        return len(self.getmessagelist().keys())
+
     def getmessage(self, uid):
         """Returns the content of the specified message."""
         raise NotImplementedException
@@ -237,7 +255,7 @@ class BaseFolder:
         and once that succeeds, get the UID, add it to the others for real,
         add it to local for real, and delete the fake one."""
 
-        uidlist = [uid for uid in self.getmessagelist().keys() if uid < 0]
+        uidlist = [uid for uid in self.getmessageuidlist() if uid < 0]
         threads = []
 
         usethread = None
@@ -294,11 +312,10 @@ class BaseFolder:
         them to dest."""
         threads = []
         
-       dest_messagelist = dest.getmessagelist()
-        for uid in self.getmessagelist().keys():
+        for uid in self.getmessageuidlist():
             if uid < 0:                 # Ignore messages that pass 1 missed.
                 continue
-            if not uid in dest_messagelist:
+            if not dest.uidexists(uid):
                 if self.suggeststhreads():
                     self.waitforthread()
                     thread = InstanceLimitedThread(\
@@ -321,11 +338,10 @@ class BaseFolder:
         Look for message present in dest but not in self.
         If any, delete them."""
         deletelist = []
-       self_messagelist = self.getmessagelist()
-        for uid in dest.getmessagelist().keys():
+        for uid in dest.getmessageuidlist():
             if uid < 0:
                 continue
-            if not uid in self_messagelist:
+            if not self.uidexists(uid):
                 deletelist.append(uid)
         if len(deletelist):
             UIBase.getglobalui().deletingmessages(deletelist, applyto)
@@ -348,7 +364,7 @@ class BaseFolder:
         addflaglist = {}
         delflaglist = {}
         
-        for uid in self.getmessagelist().keys():
+        for uid in self.getmessageuidlist():
             if uid < 0:                 # Ignore messages missed by pass 1
                 continue
             selfflags = self.getmessageflags(uid)
diff --git a/offlineimap/folder/IMAP.py b/offlineimap/folder/IMAP.py
index 05edaf0..d179ede 100644
--- a/offlineimap/folder/IMAP.py
+++ b/offlineimap/folder/IMAP.py
@@ -55,7 +55,7 @@ class IMAPFolder(BaseFolder):
         return self.accountname
 
     def suggeststhreads(self):
-        return 1
+        return 0
 
     def waitforthread(self):
         self.imapserver.connectionwait()
diff --git a/offlineimap/folder/LocalStatus.py 
b/offlineimap/folder/LocalStatus.py
index 4bb83ee..90ad3c8 100644
--- a/offlineimap/folder/LocalStatus.py
+++ b/offlineimap/folder/LocalStatus.py
@@ -19,21 +19,68 @@
 from Base import BaseFolder
 import os, threading
 
+from pysqlite2 import dbapi2 as sqlite
+
 magicline = "OFFLINEIMAP LocalStatus CACHE DATA - DO NOT MODIFY - FORMAT 1"
+newmagicline = "OFFLINEIMAP LocalStatus NOW IN SQLITE, DO NOT MODIFY"
 
 class LocalStatusFolder(BaseFolder):
+    def __deinit__(self):
+        self.save()
+        self.cursor.close()
+        self.connection.close()
+
     def __init__(self, root, name, repository, accountname):
         self.name = name
         self.root = root
         self.sep = '.'
         self.filename = os.path.join(root, name)
         self.filename = repository.getfolderfilename(name)
-        self.messagelist = None
+        self.messagelist = {}
         self.repository = repository
         self.savelock = threading.Lock()
         self.doautosave = 1
         self.accountname = accountname
         BaseFolder.__init__(self)
+       self.dbfilename = self.filename + '.sqlite'
+
+       # MIGRATE
+       if os.path.exists(self.filename):
+               self.connection = sqlite.connect(self.dbfilename)
+               self.cursor = self.connection.cursor()
+               self.cursor.execute('CREATE TABLE status (id INTEGER PRIMARY 
KEY, flags VARCHAR(50))')
+               if self.isnewfolder():
+                   self.messagelist = {}
+                   return
+               file = open(self.filename, "rt")
+               self.messagelist = {}
+               line = file.readline().strip()
+               assert(line == magicline)
+               for line in file.xreadlines():
+                   line = line.strip()
+                   uid, flags = line.split(':')
+                   uid = long(uid)
+                   flags = [x for x in flags]
+                   flags.sort()
+                   flags = ''.join(flags)
+                   self.cursor.execute('INSERT INTO status (id,flags) VALUES 
(?,?)',
+                               (uid,flags))
+               file.close()
+               self.connection.commit()
+               os.rename(self.filename, self.filename + ".old")
+               self.cursor.close()
+               self.connection.close()
+
+       # create new
+       if not os.path.exists(self.dbfilename):
+               self.connection = sqlite.connect(self.dbfilename)
+               self.cursor = self.connection.cursor()
+               self.cursor.execute('CREATE TABLE status (id INTEGER PRIMARY 
KEY, flags VARCHAR(50))')
+       else:
+               self.connection = sqlite.connect(self.dbfilename)
+               self.cursor = self.connection.cursor()
+
+
 
     def getaccountname(self):
         return self.accountname
@@ -42,7 +89,7 @@ class LocalStatusFolder(BaseFolder):
         return 0
 
     def isnewfolder(self):
-        return not os.path.exists(self.filename)
+        return not os.path.exists(self.dbfilename)
 
     def getname(self):
         return self.name
@@ -58,86 +105,93 @@ class LocalStatusFolder(BaseFolder):
 
     def deletemessagelist(self):
         if not self.isnewfolder():
-            os.unlink(self.filename)
+            self.cursor.close()
+            self.connection.close()
+            os.unlink(self.dbfilename)
 
     def cachemessagelist(self):
-        if self.isnewfolder():
-            self.messagelist = {}
-            return
-        file = open(self.filename, "rt")
-        self.messagelist = {}
-        line = file.readline().strip()
-        if not line and not line.read():
-            # The status file is empty - should not have happened,
-            # but somehow did.
-            file.close()
-            return
-        assert(line == magicline)
-        for line in file.xreadlines():
-            line = line.strip()
-            uid, flags = line.split(':')
-            uid = long(uid)
-            flags = [x for x in flags]
-            self.messagelist[uid] = {'uid': uid, 'flags': flags}
-        file.close()
+        return
 
     def autosave(self):
         if self.doautosave:
             self.save()
 
     def save(self):
-        self.savelock.acquire()
-        try:
-            file = open(self.filename + ".tmp", "wt")
-            file.write(magicline + "\n")
-            for msg in self.messagelist.values():
-                flags = msg['flags']
-                flags.sort()
-                flags = ''.join(flags)
-                file.write("%s:%s\n" % (msg['uid'], flags))
-            file.flush()
-            os.fsync(file.fileno())
-            file.close()
-            os.rename(self.filename + ".tmp", self.filename)
-
-            try:
-                fd = os.open(os.path.dirname(self.filename), os.O_RDONLY)
-                os.fsync(fd)
-                os.close(fd)
-            except:
-                pass
-
-        finally:
-            self.savelock.release()
+        self.connection.commit()
 
     def getmessagelist(self):
+        if self.isnewfolder():
+            self.messagelist = {}
+            return
+
+        self.messagelist = {}
+        self.cursor.execute('SELECT id,flags from status')
+        for row in self.cursor:
+            flags = [x for x in row[1]]
+            self.messagelist[row[0]] = {'uid': row[0], 'flags': flags}
+
         return self.messagelist
 
+    def uidexists(self,uid):
+        self.cursor.execute('SELECT id FROM status WHERE id=:id',{'id': uid})
+       for row in self.cursor:
+            if(row[0]==uid):
+                return 1
+        return 0
+
+    def getmessageuidlist(self):
+       self.cursor.execute('SELECT id from status')
+       r = []
+       for row in self.cursor:
+            r.append(row[0])
+        return r
+
+    def getmessagecount(self):
+       self.cursor.execute('SELECT count(id) from status');
+       row = self.cursor.fetchone()
+        return row[0]
+
     def savemessage(self, uid, content, flags, rtime):
         if uid < 0:
             # We cannot assign a uid.
             return uid
 
-        if uid in self.messagelist:     # already have it
+        if self.uidexists(uid):     # already have it
             self.savemessageflags(uid, flags)
             return uid
 
         self.messagelist[uid] = {'uid': uid, 'flags': flags, 'time': rtime}
+        flags.sort()
+        flags = ''.join(flags)
+        self.cursor.execute('INSERT INTO status (id,flags) VALUES (?,?)',
+                            (uid,flags))
         self.autosave()
         return uid
 
     def getmessageflags(self, uid):
-        return self.messagelist[uid]['flags']
+        self.cursor.execute('SELECT flags FROM status WHERE id=:id',
+                            {'id': uid})
+       for row in self.cursor:
+            flags = [x for x in row[0]]
+            return flags
+       return flags
 
     def getmessagetime(self, uid):
         return self.messagelist[uid]['time']
 
     def savemessageflags(self, uid, flags):
-        self.messagelist[uid]['flags'] = flags
+        self.messagelist[uid] = {'uid': uid, 'flags': flags}
+        flags.sort()
+        flags = ''.join(flags)
+        self.cursor.execute('UPDATE status SET flags=? WHERE id=?',(flags,uid))
         self.autosave()
 
     def deletemessage(self, uid):
-        if not uid in self.messagelist:
+        if not self.uidexists(uid):
             return
-        del(self.messagelist[uid])
-        self.autosave()
+
+       if uid in self.messagelist:
+            del(self.messagelist[uid])
+
+        self.cursor.execute('DELETE FROM status WHERE id=:id', {'id': uid})
+        return
-- 
1.5.3.GIT