Thursday, July 21, 2011

Contact Sync and Database.sqlite3 corruption bring Mail.app and my fast iMac to its knees

Sometime over the past two weeks my relatively new iMac seemed to lose steam. It was slow to respond, lots of beachballs. Felt like it was occupied, but Activity Monitor didn't show anything obvious.

I think OS X was caught in a sync loop involving Spanning Sync, OS X iSync and MobileMe sync all synchronizing between OS X Address Book, my iPhone, MobileMe Address Book, and a subsection of Google Contacts. That setup has worked well for about a year, but it's fallen apart now.

Looking at the Spanning Sync logs a few contacts were being updated constantly. Nothing special about them I can see.

I'm afraid my Contacts Unification program has died. With MobileMe on the way out, and with Google frantically tweaking Contacts for their social/G+ initiatives, and Apple doing its own Lion and iCloud contact management I don't think this is going to come back. I'll need another approach.

For the moment I've set MobileMe sync to manual and disabled Spanning Sync. I may do a manual sync every few days. My iMac is working again.

Synchronization is Hell.

Update 7/22/11: This morning I received a warning from Carbon Copy Cloner about a physical read error with Library/PubSub/Database/Database.sqlite3. Interesting ...

07/22 08:25:58 rsync: read errors mapping "/Users/jfaughnan 1/Library/PubSub/Database/Database.sqlite3": Input/output error (5)

07/22 08:26:22 ERROR: Users/jfaughnan 1/Library/PubSub/Database/Database.sqlite3 failed verification -- update discarded. (51)

Disk Utility didn't find any physical errors, so I assume Database.sqlite3 has been corrupted.

I put Database.sqlite3 in the trash, logged out and back in, then deleted it. Mail.app recreated it, went from 7MB to 2MB. Safari and Mail.app both felt much faster. I then downloaded the most recent version of Onyx, restarted as Admin, then ran all the cleanup and automation scripts. At the moment the machine feels about 10 times faster.

Even though sqlite3 is a core part of OS X Data Management, but it's hard to find much about how it's actually used in most apps. I found one page that listed apps that use SQLite databases, but those apps are listed as storing data in different locations. I found a couple of references [1] that suggest this is where Mail.app and Safari store their RSS feeds.

One article mentioned using this command to fix this sqlite database:

/usr/bin/sqlite3 ~/Library/PubSub/Database/Database.sqlite3 vacuum

Using that search string brought up more interesting articles:

The second link is from 2009, and it sounds very much like what has been happening to me in OS X 10.6 ...

I am syncing, on several Macs and several user accounts, information such as Calender, Mail Rules, Bookmarks, etc. After recently updating to Safari 4.01 and OS X 10.5.7, all of a sudden I had all very bad Safari performance -- including constant freeze situations. After being ready to delete the impacted user, I realized that there were some issues with the database.sqlite3 file in the Library » PubSub » Database folder. The PubSub folder is used for tracking RSS feeds, and it seems my problem resided there.

After deleting the database.sqlite file, and resetting the Mobile Me information, the freezes and crashes stopped...

Deleting this file did not actually remove the feeds from Mail.app and Safari.

Looking at my feeds I see I've been tracking my Google Reader shared item feed. That is an enormous feed; I think I've been pushing the limits of what Mail.app RSS subscription can handle. I deleted all of my Feed subscriptions from Safari and Mail.app, then I delted all the data in ~/Library/PubSub/Feeds and I deleted Database.sqlite3 (again). On logout/login it was recreated with a size of 74kb (empty). Mail.app is now blindingly fast and my system is healthy again.

I won't be using Mail.app's feed reader features any more. They don't scale. I use Google Reader, but to archive some of my feeds I'm going to buy a dedicated standalone feed reader.

Update 7/24/11: I created an archive of my Address Book, cleared the iSync database then restarted MobileMe. It told me it was going to add 843 records. Coincidentally, that's how many I have. I went ahead anyway, since it's surprisingly easy to save and restore Address Book archives (I have about fifty versions saved). In fact I ended up with 843 that look correct on spot check. Curiously, both MobileMe and my laptop say I have 842 contacts. So there's something broken somewhere in iSync. It's easy to see why Apple dropped iSync (so far) from Lion. Synchronization is, honestly, and truly, Hell. This has underappreciated implications for health care interoperability incidentally.

No comments: