From f1f650b2544ab842e6626bb09737002d87dd6e28 Mon Sep 17 00:00:00 2001 From: Stewart Smith Date: Mon, 31 Mar 2008 12:23:32 +1100 Subject: [PATCH] Use SQL Lite databases for LocalStatus (instead of flat files) 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 -- 2.39.2