What about turning on auto_vacuum on Mail’s envelope index?

A couple of days ago, I commented on the vacuum-your-mail-to-make-it-go-faster meme passing through the Mac weblogging community.

In response, Garrett Albright suggested that one could turn on auto_vacuum through a bit of a hack (nice one, too — see the comments).

I wouldn’t do that. It will work. Most of the time. And then it won’t. While there is very little data loss risk — the Envelope Index is just that, an index of all of your Mail — there is a risk and it was clearly left off in Tiger for a reason.

First, auto_vacuum was new to SQLite 3.1.0 and Tiger ships with SQLite 3.1.3, which won’t be updated unless a serious security hole is found. There have been a number of bug fixes to vacuum and vacuum related functionality since 3.1.3. In particular, there have been a handful of fixes that prevent relatively esoteric situations from corrupting a database. Some of these were directly related to auto_vacuum and some were simply catalyzed by auto_vacuum.

Secondly, it isn’t going to buy you that much performance over periodically vacuuming the database. Most of you who have manually vacuumed your database are seeing a performance boost related to the removal of 10s of months of garbage built up in the database. Running vacuum after only a week or two of Mail activity is going to yield very little benefit.

That is, the additional risk of running with auto_vacuum enabled with a 3.1.3 based SQLite database isn’t worth the very slight additional performance you’ll see on a day to day basis. Better to simply manually vacuum the database every six months or so.



8 Responses to “What about turning on auto_vacuum on Mail’s envelope index?”

  1. bbum’s weblog-o-mat » Blog Archive » Vacuuming Mail’s Envelope Index to make Mail faster (and Aperture, too) says:

    [...] I wouldn’t do that and I have explained why in a followup post. [...]

  2. dawnfather says:

    You could just set an ical event to run the script at whatever interval makes sense, e.g. the 6 months you suggest

  3. Chris says:

    The Envelope Index database is also polluted by “old data”. I migrated my mailserver several times and all old messages and mailboxes are still in the
    database:

    select count(*) from mailboxes yields a result of 350 mailboxes…

    So I deleted the file (I’m one of the brave people ;-)
    and restarted Mail.app. The resulting Envelope Index shrank from 18 MB (22 MB before vacuum) to 2 MB.

  4. aswift says:

    Tiger’s Mail does configure the database file with auto_vacuum turned on. You can verify for yourself by running the command: sqlite3 ~/Library/Mail/Envelope\ Index “pragma auto_vacuum”
    A “full” vacuum rebuilds the database file and results in a new file that is defragmented and table data is ordered on contigous pages in the database file.

  5. Jon H says:

    PZ Myers of the science blog Pharyngula is having problems with Mail and a LOT of messages (100k or thereabouts, spread through a number of folders).

    He tried the vacuum script, but is still having problems:

    “I tried the mail vacuum script — it got the envelope file down from 77 to 66 MB. So far, no performance improvement at all. Unfortunately, it’s still also slowly cranky through flushing out the trash from that big deletion, so I’ll try again in a few hours when it’s done.
    And really, that’s ridiculous. Why should it take hours to delete 10,000 messages?”

    Is there some way to tell Mail “I’m going to delete a ton of messages, so don’t go fussing for every single message”?

  6. Jon H says:

    Whoops, sorry, PZ’s message is at http://scienceblogs.com/pharyngula/2007/03/tech_assistance_please_help_me.php

  7. Frog says:

    Tiger ships with SQLite 3.1.3, which won’t be updated unless a serious security hole is found.

    OK…

    There have been a number of bug fixes to vacuum and vacuum related functionality since 3.1.3. In particular, there have been a handful of fixes that prevent relatively esoteric situations from corrupting a database. Some of these were directly related to auto_vacuum and some were simply catalyzed by auto_vacuum.

    … so how is potential database corruption not a security hole? And we wonder why Apple hasn’t yet gotten their security act together.

  8. bbum says:

    Finding FUD tasty?

    How is a feature that is turned off unless a user enables it through an unsupported / undocumented means a security hole, exactly?

    And how, exactly, is a bug that might corrupt data in a user level process in exceedingly rare conditions a security hole any more than, say, simply writing a handful of bytes into a random location in the same user owned file???

    In any case, I was actually wrong and have been meaning to post about this. Auto_vacuum is actually enabled under certain circumstances in Tiger. There are corruption bugs, still, but they are exceedingly rare.

Leave a Reply

Line and paragraph breaks automatic.
XHTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>