Submit Hint Search The Forums LinksStatsPollsHeadlinesRSS
14,000 hints and counting!

Speed up iPhoto '09 by vacuuming its databases Apps
My iPhoto '08 used to start in seconds despite the 30,000+ photos I kept in it. But after I installed iLife '09 and upgraded my iPhoto library, iPhoto '09 would hang for several minutes with the dreaded beach ball on startup. Even worse, though was that opening any event would trigger another hang of a minute or more, as would viewing a photo full-screen. (Once these initial hangs were over and done with, the app would work normally.)

Inside the iPhoto Library Package, there are four SQLite databases (face_blob.db, face.db, iPhotoAux.db and iPhotoMain.db). I found that by vacuuming all four of these databases, I restored iPhoto '09's performance back to something on par with its predecessor.

Open Terminal and cd into your iPhoto Library (by default, cd "~/Pictures/iPhoto Library"). Once there, execute the following command:
for dbase in *.db; do sqlite3 $dbase "vacuum;"; done
[robg adds: Although I wasn't experiencing any slowdowns, I tested this command just to make sure nothing broke ... and nothing broke.]
    •    
  • Currently 2.93 / 5
  You rated: 5 / 5 (14 votes cast)
 
[66,723 views]  

Speed up iPhoto '09 by vacuuming its databases | 40 comments | Create New Account
Click here to return to the 'Speed up iPhoto '09 by vacuuming its databases' hint
The following comments are owned by whoever posted them. This site is not responsible for what they say.
Speed up iPhoto '09 by vacuuming its databases
Authored by: kmichels on Mar 02, '09 08:32:46AM

Wow - awesome improvement. I had a startup delay of ~20 seconds before running this, and after running it, less than 5 seconds!



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: petesopete on Mar 02, '09 08:34:55AM

I don't know if I did something wrong, but it broke my library. When I start iPhoto, it says that it has to rescan the library and when it rescans, I get a message saying that the library has been used with a more recent version of iPhoto and it can't open it.



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: xwiz on Mar 02, '09 08:49:55AM

This was amazing - I timed 30 seconds before using it, then only five seconds after. Perhaps the effect was partly due to caching, however?



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: ShadowBottle on Mar 02, '09 08:50:51AM

Score. Admittedly it was only taking about 8 second to launch (with a blank column on the left and empty picture area on the right), but it's been cut down from 8 seconds to 2 seconds. Very nice.



[ Reply to This | # ]
Sorry what does it do?
Authored by: painz on Mar 02, '09 08:59:17AM

I believe it cleans up somewhat? but what will be with db in a while? Should this be repeated every other time?



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: Old Toad on Mar 02, '09 08:59:31AM

For the terminally handicapped what is "vacuuming"?



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: iRideSnow on Mar 02, '09 10:49:18AM
Speed up iPhoto '09 by vacuuming its databases
Authored by: Mitchell on Mar 02, '09 09:23:21AM

The command doesn't work in the C shell. If you're a csh user, type the command "bash" before using the for command in the hint.



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: tilmansp on Mar 02, '09 09:36:33AM

Excellent! I suspected something like this all along, but with my limited Sqlite knowledge, I only got as far as rebuilding the indexes (which helped a little). Vacuuming makes a huge difference.

I enhanced the script a little to check that iPhoto isn't running. Save this into a file like vacuum_iphoto.sh, then make it executable, and you're all set:

#!/bin/bash
pid=`ps -ef | grep iPhoto | grep -v grep`
if [ "$pid" = "" ] ; then
cd ~/Pictures/iPhoto\ Library
for dbase in *.db; do sqlite3 $dbase "vacuum;"; done
else
echo "iPhoto is currently running. Please quit iPhoto before running $0."
fi



[ Reply to This | # ]
It works!
Authored by: designbot on Mar 02, '09 12:20:54PM

Over 20 seconds to less than 5 Great hint!



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: oblahdioblidaa on Mar 02, '09 01:39:00PM

I wonder if OS X has any other databases that can be vacuumed; Mail.app and iPhoto both benefit from this.



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: rstewar on Mar 02, '09 02:55:27PM

Where is the Mail file that you vacuumed? My Mail is running fairly slowly these days.



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: rgray on Mar 02, '09 03:11:14PM

~ > Library > Mail > Envelope Index



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: palahala on Mar 05, '09 05:34:34AM

> I wonder if OS X has any other databases that can be vacuumed

I've been vacuuming my Firefox 3 places.sql file a few times, which seems to improve playback of flash movies... I keep my browsing history for a long time, and my places.sql is over 20MB. On the other hand: I still did not find a definitive answer if the vacuuming really helps there -- for some time I thought that maybe clearing the actual cache had better results. Though I could restore my large places.sql from a backup, the caches are not being backed up, so could not be restored for testing...



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: cibi3d on Mar 02, '09 03:13:49PM

Nice hint, but although it is true it has speed up a bit my iPhoto load, it seems to have became crazy my Faces DB; I mean, now I have several faces mixed as the same person and even people I hadn't tagged appeared as 'someone'. I'm not sure if it is the cause, but I saw it after applying this hint. Has anyone else had the same problem?



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: haloici0us on Mar 02, '09 05:59:12PM

I often have hangs with Aperture when using full screen.

I wonder if this might work for Aperture, but the files I believe are in question end in apdb.



[ Reply to This | # ]
How to vacuum all sqlite databases
Authored by: lincd0 on Mar 02, '09 07:40:54PM

This command vacuums all sqlite database files in your home directory and in the cache directory under /var/folders. The pathnames of the files are printed out. It does NOT check whether the database is in use, so beware.

find ~ $( getconf DARWIN_USER_CACHE_DIR ) -name *.db -exec sqlite3 {} "vacuum;" \; -print 2>&1 | grep -v missing

[ Reply to This | # ]

How to vacuum all sqlite databases
Authored by: dille on Mar 03, '09 03:29:32PM

Nifty, but not all .db files are actually SQLite databases. Expanding this to more lines to do a quick "file" on the filename before vacuuming it helps that.

Otherwise, neat. And gosh, there's a lot of databases in there.



[ Reply to This | # ]
How to vacuum all sqlite databases
Authored by: lincd0 on Mar 04, '09 08:29:01AM

Faster and easier just to let the sqlite3 command fail on non-database files and ignore the error messages.



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: mal0rd on Mar 02, '09 10:44:05PM
I wouldn't recommend this. According to SQLite's documentation:
The VACUUM command may change the ROWIDs of entries in tables that do not have an explicit INTEGER PRIMARY KEY.
Who knows if Apple uses the implicit ROWIDs? It's bad practice, but not illegal. And if they do, this command probably corrupts your data.

[ Reply to This | # ]
can this be investigated?
Authored by: bcamp1973 on Mar 03, '09 12:04:50PM

how might one check this? my iphoto 09's performance is crap. i'd really like to run this...



[ Reply to This | # ]
can this be investigated?
Authored by: palahala on Mar 05, '09 06:12:10AM

I don't use iPhoto, but an example to show some Mozilla Firefox database information (commands like .tables and .schema moz_places show information about the database; the indented text are the responses for this example):

cd ~/Library/Application Support/Firefox/Profiles/*.default/ 

sqlite3 places.sqlite

    SQLite version 3.4.0
    Enter ".help" for instructions

sqlite> .tables

    moz_anno_attributes  moz_favicons         moz_keywords       
    moz_annos            moz_historyvisits    moz_places         
    moz_bookmarks        moz_inputhistory   
    moz_bookmarks_roots  moz_items_annos    

sqlite> .schema moz_places

    CREATE TABLE moz_places (id INTEGER PRIMARY KEY, url LONGVARCHAR,
        title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0,
        hidden INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT NULL,
        favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL);
    CREATE INDEX moz_places_faviconindex ON moz_places (favicon_id);
    CREATE INDEX moz_places_frecencyindex ON moz_places (frecency);
    CREATE INDEX moz_places_hostindex ON moz_places (rev_host);
    CREATE UNIQUE INDEX moz_places_url_uniqueindex ON moz_places (url);
    CREATE INDEX moz_places_visitcount ON moz_places (visit_count);

sqlite> .exit
Something like the example above can give you an idea of the table structure used in iPhoto as well. Above, the id INTEGER PRIMARY KEY indicates that Firefox would not suffer from this ROWID problem, for that specific table moz_places. Checking all iPhoto tables like this, you might get an idea about the iPhoto database.

[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: wjv on Mar 31, '09 09:37:15AM

That should not to be a problem, since an implicit ROWID ought not to be referenced by any other table. (In essence, it's an "internal" value for SQLite.)

Just MHO, of course. Take all due precautions before VACUUMing your databases.



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: Tim.Lance on Mar 03, '09 03:34:44PM

Think it was great for iPhoto and Mail? Amazing for my iTunes library.

So much thanks to you!



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: ChurchEAC on Mar 03, '09 06:12:12PM

Which file(s) did you vaccum for iTunes?



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: gbnyc on Mar 03, '09 04:27:29PM

Is this different/better/worse than holding down Option-Command while starting up iPhoto and choosing one or more of the options?

Those options are:
Rebuild photos' small thumbnails
Rebuild all photos' thumbnails
Recover orphaned photos
Examine and repair iPhoto Library file permissions
Rebuild iPhoto Library Database from automatic backup



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: baerb8 on Mar 03, '09 10:18:53PM

this made a difference for me speed wise thanks.
however, it took me for ever to cd into iPhoto Library until i figured out the quotation marks need to be not at the beginning of the directory but like so ( at least for me that was)cd ~/"Pictures/iPhoto Library/"

that was the only way i got into my Library and was then able to apply db change successfully.



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: dille on Mar 04, '09 02:27:46PM

You can also escape those spaces with a backslash, so " " becomes "\ ". If you use tab to have Bash complete the paths for you, it'll do that automatically.



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: Kupe on Mar 04, '09 02:50:32PM

I had no problem doing CD to the iPhoto Library directory, but I am unable to execute the VACUUM command. I pasted it verbatim from this thread, but I get these errors:

for: Command not found.
dbase: Undefined variable.

What am I missing?

Kupe



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: Kupe on Mar 04, '09 04:42:36PM

Doh! I found the problem. I had tcsh running as my shell. I'm running Leopard which should default to bash, but I guess this might be an artifact from previous versions of OS X. Anyhow, switched to bash and vacuum ran fine.

Unfortunately, it did not accomplish what I had hoped, which was to speed up the new Places Map in iPhoto '09

Oh well- thanks anyhow!

Kupe



[ Reply to This | # ]
WARNING!!
Authored by: rbenezra on Mar 05, '09 04:37:43AM

Using this command did indeed speed up iPhoto but I now have noticed that about 5% of my photos give a (!) when I try to edit them. The photos are in the database but have to be re-imported to show up properly. Major hassle. I rebuilt he database/thumbnails, etc that you can do on an option/command launch but that did not help. Could this be related to the vacuuming or is this likely to have existed beforehand? On another computer, that is also running iPhoto 09 no such problem exists but I haven't vacuumed (and won't until I figure this out). Have any others seen this problem after running the vacuum command?



[ Reply to This | # ]
WARNING!! Yes Vacuuming looks like the culprit,
Authored by: rbenezra on Mar 05, '09 12:07:13PM

With further testing it does now seem the vacuuming might have done the damage. I had a SuperDuper clone which was running iPhoto 09 with the cloned library that hadn't been vacuumed and that library was fine. I would be very careful using this hint and although Robg said it didn't break anything I wonder how carefully he (or others) looked. Please post if you have a similar problem.



[ Reply to This | # ]
WARNING!! Yes Vacuuming looks like the culprit,
Authored by: robg on Mar 10, '09 10:07:28AM

I scanned my entire library, and didn't notice any "!" tagged photos, but that was about the extent of my testing.

-rob.



[ Reply to This | # ]
WARNING!!
Authored by: jvalade on Jul 17, '09 08:23:29AM

This is a problem that I've noticed for some time as a result of using iphoto diet a while back. I don't know if there's an easy way to do a batch of photos, but for individual photos - "revert to original" seems to bring the photo back.



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: wjv on Mar 31, '09 09:40:17AM
I submitted this hint about a month ago, and already my iPhoto has slowed to a crawl again! And it has not been a particularly busy month, photography-wise.

One wonders whether iPhoto's databases might not be good candidates for SQLite's auto_vacuum pragma, despite the slight overall impact on performance one would expect.

[ Reply to This | # ]

Simply amazing!
Authored by: WeyrleaderZor on Apr 01, '09 09:23:35PM

No two ways around it... iPhoto's start up is absolutely amazing! I tried this out, not because I had a long start up (under 10 seconds), but because I remember doing something like this to Mail.app some time ago on Tiger (or maybe an even earlier version). But WOW! iPhoto flew open so fast I couldn't even get my stopwatch started in time to see how much faster it was! I'm guess it's about 2 seconds... at most!

Thank you for this wonderful tip!



[ Reply to This | # ]
Excellent!
Authored by: avi4now on Apr 30, '09 04:07:19PM

This is great! My launch time went from 29 to 9 seconds! Thanks!



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: siegeld on Jun 15, '09 06:51:52PM

I'm trying to follow this procedure for "vacuuming" my iPhoto which has become incredibly slow since installing iPhoto '09.

I've changed the directory in Terminal so it says:

[Macintosh-4:~/Pictures/iPhoto Library] don%

But when I paste in:

for dbase in *.db; do sqlite3 $dbase "vacuum;"; done

terminal replies with:

for: Command not found.
dbase: Undefined variable.

What am I doing wrong?

Thanks.



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: TouringComet on Jun 16, '09 08:18:12AM

See Kupe's answer to his own post, a few posts back.



[ Reply to This | # ]
Speed up iPhoto '09 by vacuuming its databases
Authored by: wjv on Nov 06, '09 09:22:28AM
If you open iPhoto '09 version 8.1 in "library rebuild mode" by holding down cmd and opt, as detailed in this Apple knowledgebase article, then there is now an option to "Reclaim unused disk space from databases". I can't see how this can imply anything other than vacuuming the databases.

I don't know whether this option was also in 8.0; it's been a long time since I've rebuilt my iPhoto library. At any rate, I assume that using this officially supported way of vacuuming the databases should probably be used in preference to the manual way I outlined in the original hint.

[ Reply to This | # ]