iPhone Battery Drain, dataaccessd, and Calendar.sqlitedb, Oh My!

UPDATE AT THE BOTTOM

I have an iPhone 4S and recently upgraded to iOS 6.0.1. My battery life had been middling before, but since moving to iOS 6, it had dropped dramatically. I now cannot make it through two-thirds of the day without putting my phone on a charger. I finally reached my limit yesterday and decided to figure out what was going on.

I started my hunt with some googling: ios 6 battery drain

Most of the hits are generic types of things to try. Tweak settings, reboot, restore, reinstall apps, and the dreaded, wipe and setup from scratch. I was pretty sure that last option would fix my problem but I didn't want to lose all of my apps' data and have to go through a long setup process. Being an iOS developer, I decided to peek behind the curtain and see what I could figure out.

I launched Xcode (Apple's developer environment) and then Instruments (Apple's performance monitoring tool). I connected my iPhone and started Instruments with the iOS Activity Monitor template:

This collects in real-time data about the processes running on your iOS device. The information is a lot like Mac OS X's Activity Monitor and looks like this:

My phone seemed relatively idle ("DTMobileIS" is the process that feeds data to Instruments so ignore it). But one thing I noticed is that the process "dataaccessd" had an enormous amount of CPU Time racked up. It was order of magnitudes higher than any other process. So, back to Google: dataaccessd ios battery drain

Now, we were getting somewhere – dataaccessd has been fingered before as a cause of battery issues. So, I investigated some of the hits and and came upon this Open Radar bug:

dataaccessd continuously crashing, draining battery.

With high total CPU time for my dataaccessd, it clearly wasn't crashing. However, this did ring a bell for me – I've had issues with Calendar. It tends to be very sluggish. I started playing around with Calendar while watching the Activity Monitor. 

What I found was startling. After launching Calendar, switching back and forth between a couple of the days in the month view and then locking the phone, the dataaccessd process would eat the CPU for close to a minute before settling down. I could reproduce this on demand with simple interactions with Calendar.

In an attempt to figure out what dataaccessd was doing, I used the Network template in Instruments:

What's nice about Instruments is that you can run this second template at the same time with the Activity Monitor template. When you focus on the dataaccessd process and drill down into the connections, it looks something like this:

I now recreated the problem and what appeared here was a ton of Data In and Data Out activity by dataaccessd. It was all on localhost so I presumed that what we were talking about was ultimately file I/O. 

So we are at the point where messing with Calendar causes dataaccessd to do a whole bunch of file I/O. If this happens whenever Calendar does anything (like handling Push from iCloud or firing off event alarms), I felt it is the likely cause of my battery issues.

Unfortunately, this is about as far as Apple's Developer tools will take you. You really need to be able to trace dataaccessd itself to figure out what it is doing. Instruments does have a template for this, but you can only run it on applications that can be launched. Long-term system processes like dataaccessd cannot be attached to. The inability to do this is also probably a result of Apple not wanting people poking around in the internal guts of a system process like dataaccessd.

With a little more Googling, you end up finding out that Calendar stores it's data in Library/Calendar/Calendar.sqlitedb. Apple doesn't allow you to access this file directly on the device but there's another way to get to it – through a device backup. 

My phone is set to backup over WiFi to iCloud, but if you right-click on the device in iTunes you will see an option to force a local backup. Once you do that, you can access your backup with iBackupBot, a cool tool that knows how to access and interpret your device backups. I found Calendar.sqlitedb and extracted it to my Desktop.

The first thing I noticed is that the file was close to 73MB in size! That correlated to the amount of I/O that dataaccessd appeared to be performing according to the Network template in Instruments. If dataaccessd is having to rewrite that file regularly, no wonder it's eating the CPU (and my battery).

I now decided to get into the database itself and check it out. I started Terminal, changed directory to where Calendar.sqlitedb was and started up sqlite3 to inspect it. Running .tables looks like this:

$ sqlite3 Calendar.sqlitedb
sqlite> .tables
Alarm                      Location                 
AlarmChanges               Notification             
Attachment                 NotificationChanges      
AttachmentChanges          OccurrenceCache          
Calendar                   OccurrenceCacheDays      
CalendarChanges            Participant              
CalendarItem               ParticipantChanges       
CalendarItemChanges        Recurrence               
Category                   RecurrenceChanges        
CategoryLink               ResourceChange           
EventAction                Sharee                   
EventActionChanges         ShareeChanges            
ExceptionDate              Store                    
Identity                   _SqliteDatabaseProperties
sqlite> 

So, how do I figure out which table is the problem? I started by figuring out the sizes of each table:

sqlite> select count() from Alarm;
61
sqlite> 

I did this for every table in the database until I found the culprit:

sqlite> select count() from Participant;
390883
sqlite> 

Well that doesn't seem right! The Participant table was multiple orders of magnitude larger than any other table. Now, I started looking at the data in that table:

sqlite> select * from Participant limit 100;
722524|8|0|0|0|0|10|137500||2E58AAB4-170D-4118-B9E2-ACE8710B0AB6|mailto:redacted@gmail.com|0
722527|8|0|0|0|0|10|137517||ACE3112E-B096-41A7-8AA7-24C0A9F375D5|mailto:redacted@gmail.com|0
722530|8|0|0|0|0|10|137545||2D7F4627-FE6E-4674-9AAC-72A0E1471382|mailto:redacted@gmail.com|0
722533|8|0|0|0|0|10|137561||1580D42F-E479-4612-86A1-626BA44CAA0F|mailto:redacted@gmail.com|0
[…]
sqlite> 

There are a *bunch* of participants on events in my calendar for events that appear to have originated on Google (I guess either because I subscribed to a calendar there or received them in email and accepted them on to my calendar). We need to look at the schema a little bit to figure out what is going on:

sqlite> .schema Participant
CREATE TABLE Participant (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, entity_type INTEGER, type INTEGER, status INTEGER, pending_status INTEGER, role INTEGER, identity_id INTEGER, owner_id INTEGER, external_rep BLOB, UUID TEXT, email TEXT, is_self INTEGER);
CREATE INDEX ParticipantEntityType on Participant(entity_type);
CREATE INDEX ParticipantOwnerId on Participant(owner_id);
CREATE INDEX ParticipantUUID on Participant(UUID);
sqlite> 

It appears that owner_id points to a row in CalendarItem that is the owner event for each Participant. So, we try to narrow things down to see what is going on:

sqlite> select * from Participant where owner_id=137500 limit 100;
722524|8|0|0|0|0|10|137500||2E58AAB4-170D-4118-B9E2-ACE8710B0AB6|mailto:redacted@gmail.com|0
722645|8|0|0|0|0|10|137500||9C49863B-11F5-41A1-A6A4-5093445E4809|mailto:redacted@gmail.com|0
722816|8|0|0|0|0|10|137500||B6701899-1582-4574-91F2-0F0B6E826768|mailto:redacted@gmail.com|0
722937|8|0|0|0|0|10|137500||A1C476C0-C3F8-43DD-B3A9-055956001862|mailto:redacted@gmail.com|0
[…]
sqlite> 

Why in the world does one CalendarItem have so many copies of the same Participant on it? And how many times exactly you might wonder?

sqlite> select count() from Participant where owner_id=137500;
9771
sqlite> 

Whoa! Clearly there was a bug at work here. There were small counts of Participant rows with emails without "mailto:" so I figured that must have been the root of the problem. My best guess is it was fixed somewhere by somebody at some point but no code was ever written to clean up the mess it left behind in my Calendar.sqlitedb.

So, now what do we do about it? Again, a wipe and reset of the phone would probably fix this. But I wasn't interested in wasting time on that. So, I decided to try an experiment. I again backed up my phone and then made a zip archive of the backup directory in ~/Library/Application Support/MobileSync/Backup just in case things went horribly wrong. I then used iBackupBot to again extract Calendar.sqlitedb and started sqlite3 on it. I then took a chance and tried to get rid of the junk participants.

sqlite> delete from Participant where email like "mailto:%";
[...crunching away for a few seconds...]
sqlite> 

I then exited sqlite3 and now the size of Calendar.sqlitedb was just 2.2 MB! This was looking promising. I imported Calendar.sqlitedb back into my backup using iBackupBot and restored my phone from this backup.

This is where things got a little scary. iTunes restored the phone and it started rebooting – and then powered off. I powered it on and it powered itself off within about 10 seconds. I powered it on again and the same thing. At this point, I'm thinking "oh well, going to have to do a full recovery restore back to where we started" and powered the phone on again, prepared to put it into Recovery Mode. But I gave it another chance and to my surprise it finished booting!

I got Instruments running again with the Activity Monitor template and unlocked the phone. I interacted with Calendar and watched the effect. Calendar is now nice and snappy and dataaccessd runs for just a couple of seconds and then goes idle.

It's still early after this adventure so I'm not 100% positive this fixed my battery drain yet but the early indications are promising. Calendar is nice and snappy and dataaccessd no longer eats the CPU. The battery life feels subjectively better so far; it will take a couple of days to really get a sense for the change. And I haven't fully exercised Calendar to see if I haven't borked it somewhere with my database hacking but so far so good.

If anybody at Apple ends up reading this, I've kept a copy of that backup with the original Calendar.sqlitedb in case somebody wants to perform forensics on it. Also, I recommend writing a Calendar.sqlitedb "fsck" type of utility and adding it under the covers to the OS update process in order to keep this cruft at bay. You just might see the battery complaints die down.

UPDATE: My battery life has dramatically improved. Before, I had to start recharging it about 2/3's of the way through the day. Now, I can go an entire day with moderate usage and still have about 1/3 power remaining.