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):
semaphore_wait_signal_trap 51445
mach_wait_until 3041
wait4 3001
mach_msg_trap 1999
read 796
semaphore_timedwait_signal_trap 617
sqlite3AuthContextPop 386
lseek 315
sqlite3VdbeExec 128
sqlite3BtreeMoveto 127
sqlite3BtreePrevious 91
sqlite3VdbeSerialGet 81
sqlite3BtreeKeySize 70
sqlite3GetVarint 60
sqlite3pager_unref 55
sqlite3VdbeRecordCompare 53
sqlite3OsCurrentTime 47
__memcpy 44
sqlite3GetVarint32 41
sqlite3pager_pagecount 39
sqlite3MemCompare 38
sqlite3pager_get 38
sqlite3BtreeData 37
gldGetString 34
sqlite3VdbeSerialTypeLen 30
sqlite3VdbeMemRelease 28
szone_size 20
sqlite3BtreeCloseCursor 19
__spin_lock 15
sqlite3pager_pagenumber 14
szone_free 14
dyld_stub_memcpy 13
sqlite3VdbeCursorMoveto 12
szone_malloc 12
sqlite3RunVacuum 10
sqlite3VdbeMemShallowCopy 10
objc_msgSend_rtp 9
sqlite3BtreeDataFetch 9
malloc 8
sqlite3VdbeMemFromBtree 7
sqlite3pager_lookup 7
free 6
memmove 6
fcntl 5
objc_msgSend 5
sqlite3BtreeDataSize 5
sqlite3OsRead 5
sqlite3VdbeMemMakeWriteable 5
Also how many times a particular call was made:
Total number in stack (recursive counted multiple, when >=5):
82 gldGetString
73 sqlite3AuthContextPop
54 CFArrayApplyFunction
47 semaphore_wait_signal_trap
45 NSProApplicationLoad
44 pthread_cond_wait
34 -[NSView _recursiveDisplayRectIfNeededIgnoringOpacity:isVisibleRect:rectIsVisibleRectForView:topView:]
31 mach_msg
31 mach_msg_trap
28 -[NSRecursiveLock lock]
28 -[NSView _recursiveDisplayAllDirtyWithLockFocus:visRect:]
28 -[NSView visibleRect]
28 _decodeObjectBinary
27 -[NSManagedObjectContext(_NSInternalAdditions) lockObjectStore]
25 sqlite3pager_unref
21 _recursiveDisplayInRect2
20 _CFRelease
20 _pthread_body
19 _decodeObject
17 forwardMethod
17 sqlite3BtreeKeySize
16 -[NSView _drawRect:clip:]
16 forkThreadForFunction
14 __memcpy
13 -[NSConditionLock lockWhenCondition:]
13 __CFBinaryPlistCreateObject
13 sqlite3pager_get
12 -[NSView _windowChangedKeyState]
12 __spin_lock
11 -[NSView _propagateDirtyRectsToOpaqueAncestors]
11 -[NSView hitTest:]
11 ripc_Render
11 sqlite3BtreeData
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.
tow.com 
Leave a reply to Jonathan Payne Cancel reply