Under the hood, Aperture stores information on an image in two places. The first is in a SQLite3 database, and the second is in an assortment of plist files. One would venture that this approach is used for data redundancy purposes and that the data is copied between the two methods on a frequent basis. If you choose to rebuild your Aperture Library by holding the option-key on startup, Aperture will recreate the SQLite3 database using the information stored in the plist files.
When discussing Aperture’s speed, people have rightfully focused on the computer’s processing power, the video card, or the speed of the hard drive. We should also take a look at Apple’s decision to use SQLite. From the SQLite website:
We are aware of no other embedded SQL database engine that supports as much concurrency as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. Other embedded SQL database engines typically only allow a single process to connect to the database at once.
I’m no SQLite expert by any means, but I wonder if this is the cause for Aperture’s close friendship with the the SBBOD (Spinning Beach Ball of Death). I was at Eric’s house yesterday and we were trying to use Aperture to export an image for a magazine. Eric was importing images into his system while making simple adjustments and annotations to the image and we must have experienced the SBBOD half a dozen times for over 30 seconds each time! It got so annoying that Eric fired up Capture One on his PC, editing the file, developed it, sharpened the file in Photoshop, and annotated in BreezeBrowser in the time of one SBBOD session.
I’m working on Timeature right now and have successfully duplicate the “database locked” problem that some of my users are experiencing. The way I have coded up Timeature is that it will keep trying to write to the database after a short delay for ten attempts. Whenever this happens, I quickly jump back to Aperture to see if I can perform any editing operation. Invariably, I get the SBBOD. When is disappears, however, Timeature is able to complete its changes. I have no idea what Aperture is writing to the database, but it must be a lot of information, as evidence by the amount of SBBOD we are all seeing.
This leads me to believe that one of the biggest bottlenecks in Aperture is its use of SQLite3 as a backend database. I’m not sure what Apple will be doing to remedy this issue. Perhaps the use of plist files is a preliminary step in the right direction. By tuning when the application writes and refers to the plist file instead of the database, perhaps we can see some immediate speed improvements. Long-term, perhaps the solution is to find a database engine that supports concurrent writes?
I’ve uploaded the results from running sample() on Aperture while the database is locked. Perhaps this can shed some more light on the slowdowns. Some interesting portions from the sample reading include how long the process was waiting for each action to complete (aggregate time):
Sort by top of stack, same collapsed (when >= 5):
Also how many times a particular call was made:
Total number in stack (recursive counted multiple, when >=5):
34 -[NSView _recursiveDisplayRectIfNeededIgnoringOpacity:isVisibleRect:rectIsVisibleRectForView:topView:]
28 -[NSRecursiveLock lock]
28 -[NSView _recursiveDisplayAllDirtyWithLockFocus:visRect:]
28 -[NSView visibleRect]
27 -[NSManagedObjectContext(_NSInternalAdditions) lockObjectStore]
16 -[NSView _drawRect:clip:]
13 -[NSConditionLock lockWhenCondition:]
12 -[NSView _windowChangedKeyState]
11 -[NSView _propagateDirtyRectsToOpaqueAncestors]
11 -[NSView hitTest:]
Lots of waiting and obviously lots of SQLite calls. Any thoughts?
Update 11/9/2006: Found some more interesting information on Apple’s Developer Website on CoreDate and SQLite. My emphasis in bold:
Saving to a SQLite Store
When writing data, SQLite ensures that the bytes are actually written through to the drive platter. Since fsync does not make the guarantee this on Mac OS X, SQLite sends a F_FULLFSYNC request to the kernel. This causes the kernel to flush all buffers to the drives and causes the drives to flush their track caches. Without this, there is a significantly large window of time within which data will reside in volatile memoryâ€”and in the event of system failure you risk data corruption. The same calls are not made for XML or Binary storesâ€”since they are atomic, there is a much lower likelihood of data loss that involves corruption of the file, especially since the writes are typically atomic and the old file is not deleted until the new has been successfully written.
This behavior means that, in some situations, saving even a small set of changes to an SQLite store can considerably longer than saving to, for example, an XML store (for example, where saving to an XML file might take less than a hundredth of a second, saving to an SQLite store may take almost half a second). Since SQLite is updating part of a file, and loss of that partial update would be catastrophic, Core Data (through SQLite) goes to greater lengths to ensure that the bytes are really written.
You can set the defaults key com.apple.CoreData.SQLiteDebugSynchronous to one of three values, 0 (OFF), 1 (NORMAL), or 2 (FULL)
FULL: all disk syncing is performed via the fctl FULL_FSYNC commandâ€”a costly but rock-solid “write my bits to disk” operation.
We got burned in SQLite comparisons against Linux and such – since sqlite on other platforms used the fsync() operation to sync bits to disk (a cheaper but less robust disk syncing operation).
One could launch Aperture with
com.apple.CoreData.SQLiteDebugSynchronous set to 0 to see if this improves performance. I’m hesitant to do this on my primary library, because one little mistake might cause the whole SQLite database to become corrupted. My SQLite database file is already 630MB in size. I can easily see how locking and editing a small part of that file can be fast for an individual change, but really long when you take in account dozens or hundreds of changes happening at the same or similar time.