re: Core Data, SQLite, and Housekeeping
re: Core Data, SQLite, and Housekeeping
- Subject: re: Core Data, SQLite, and Housekeeping
- From: Ben Trumbull <email@hidden>
- Date: Wed, 5 Mar 2008 14:47:22 -0800
... so that's fine, but SQLite's documentation says of auto_vacuum:
"When the auto-vacuum flag is 1 (full), the freelist pages are moved
to the end of the file and the file is truncated to remove the
freelist pages at every commit. Note, however, that auto-vacuum only
truncates the freelist pages from the file. Auto-vacuum does not
defragment the database nor repack individual database pages the way
that the VACUUM command does. In fact, because it moves pages around
within the file, auto-vacuum can actually make fragmentation worse."
Okay, well that sucks. So now the question is this: does Core Data
implicitly run VACUUM? If so, when? How often? Unfortunately googling
and archive-searching did not produce any obvious answers to this
particular question. My guess is that the answer is, "Yes, at every
save," but I'd like to be sure and I'm not familiar enough with SQLite
to know how to test this myself. Any takers?
No, Core Data does not implicitly run a manual vacuum. Such a vacuum
recreates the database file from scratch and then (conceptually)
swaps it for the current database file. It can be extremely
expensive for large files or files on network mounts, although for
most files it's reasonable but slow.
The framework can't really know when it's an acceptable time for your
app to pause for several to many seconds to do this kind of house
keeping. Also, small to medium sized databases with proper indices
are very fast, and the benefit to a vacuum back to a clean state is
low.
Running a manual vacuum at every save would be crazy. Even if you
were running very large saves for a gigantic import operation, you'd
rather vacuum at the end of the application specific import. That
way you could have multiple threads/processes running the import, and
the added concurrency would utterly overwhelm any small benefit to
defragmentation.
For larger databases, reducing internal fragmentation can make a
significant difference, especially on lower end hardware/laptops.
Laptops typically use much slower drives, and often have less RAM, so
they pay a heavy penalty for fragmentation. On modern desktop
hardware, the kernel does a pretty amazing job so it can be
challenging to observe this problem. Even a vanilla config Mac Pro
is a monster compared to most laptops.
Since the vacuum command recreates the file, it also reduces external
fragmentation which is usually a more significant problem (i.e.
fragmentation of the file across the device).
If you have any performance data showing problems with internal db
fragmentation (i.e. sqlite3 dbname 'vacuum' fixes it, but cp
doesn't), we'd love to hear about it.
bugreport.apple.com
--
-Ben
_______________________________________________
Cocoa-dev mailing list (email@hidden)
Please do not post admin requests or moderator comments to the list.
Contact the moderators at cocoa-dev-admins(at)lists.apple.com
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden