iPhone/iCal sync woes: data.syncdb getting HUGE

Following up on my previous post where I explain how to "solve" the problem of uber-slow syncs with the iPhone. This problem seems to be a recurring problem and I suspect that it's caused by remote sync with iCal's calendars, somehow.

Personally, my data.syncdb file (under ~/Library/Application Support/SyncServices/Local) keeps growing, very quickly, even if I never sync my iPhone. This file is a SQLite database so it's fairly easy to see why it's this large. There's an analyzer to produce a report on the database (SQLite's download page - x86 binary direct download).

It's fairly easy to use:

cd /tmp
wget http://www.sqlite.org/sqlite3_analyzer-3.5.4-osx-x86.bin.gz
gunzip sqlite3_analyzer-3.5.4-osx-x86.bin.gz
cd ~/Library/Application\ Support/SyncServices/Local
/tmp/sqlite3_analyzer-3.5.4-osx-x86.bin data.syncdb >/tmp/sqlite.log

Have a look at the log. Mine was showing some really crazy numbers about fragmentation level and number of pages unused. If you also see these, consider cleaning up the db:

# Make sure you kill any SyncServer running
sqlite3 data.syncdb vacuum

I don't know if killing the SyncServer is actually necessary, but I know it uses this SQLite DB. Since I don't know whether SQLite properly locks the tables/rows/whatever that are in use, let's make sure nobody else fiddles with the DB while we vacuum it. Just to stay on the safe side. Update: If the database is effectively in use, you'll see a "SQL error: database is locked", so don't bother unless you see this message, in which case you know you have to kill SyncServer.

Personally, my data.syncdb went from a whopping big 825M down to 195M after vacuuming! I ran the analysis again after vacuuming and here are some of the differences between the reports before and after:

-Size of the file in bytes............. 871493632
-Bytes of user payload stored.......... 96907358    11.1%
+Size of the file in bytes............. 204472320
+Bytes of user payload stored.......... 96907358    47.4%

*** All tables and indices *******************************************

-Bytes of payload...................... 175282561   20.1%
+Bytes of payload...................... 175282561   85.7%

-Fragmentation.........................  69.4%
+Fragmentation.........................   2.1%

-Unused bytes on all pages............. 677992333   77.9%
+Unused bytes on all pages............. 14527468     7.1%

*** All tables *******************************************************

-Bytes of payload...................... 96909201    12.5%
+Bytes of payload...................... 96909201    92.2%

-Fragmentation.........................  66.2%
+Fragmentation.........................   0.74%

-Unused bytes on all pages............. 667043272   86.3%
+Unused bytes on all pages............. 2132671      2.0%

That's just incredible. How could the DB get to such a bad fragmentation level in only ONE WEEK?!

So yeah, I think this is a cleaner workaround that reseting the history of syncs with iSync, but it's still a workaround. I wonder why my DB "leaks" disk space so quickly... Which also seems to incur memory leaks in the SyncServer, because as time passes its memory footprint get bigger and bigger (up to ~300M!).