518812 - global-messages-db.sqlite can become rather large, what to do?

Status: RESOLVED DUPLICATE of bug 535137

(Thunderbird :: Search, enhancement)

Description

[Russell East]

I have about 3Gb mail messages stored.  After indexing, global-messages-db.sqlite is about 700Mb on my machine.  

My TB profile is subject to automatic backups on a per-directory basis, so there's no way to not backup this file, but backup the other data, such as address book.

Is there any chance to

a) break up this index into smaller fragments that don't all change frequently I dunno, maybe somehow use Sqlite Attached Databases?

and/or

b) move this and perhaps other ephemeral files into a profile sub-directory, so it can be elided from backups

Comment 1

[Michael Habbe]

Hi.

On my system reguarly an automatic backup is running for all files marked with the archive-bit. 
Some mail-accounts are placed on a fileserver which are not backupped (for example Mailings OpenOffice, Apache, ...)
My global-messages-db.sqlite is about 450 MB, this takes some time.... and this file always has this bit set.

The best way i think is probably to deactivate this new function.
(I dont know how to do so)
Because when i search for a certain message i accept a longer wait time because i dont search messages all the time.

Comment 2

[Ludovic Hirlimann [:Usul]]

(In reply to comment #1)
> Hi.
> 
> On my system reguarly an automatic backup is running for all files marked with
> the archive-bit. 

Maybe we could remove that bit, Andrew thoughts ?

> The best way i think is probably to deactivate this new function.
> (I dont know how to do so)
> Because when i search for a certain message i accept a longer wait time because
> i dont search messages all the time.

If you deactivate - you'll be back at seraching like TB2, right. deactivation is done through , Tools -> Option -> Advanced untick global indexer. You'll also need to remove the sql file yourself.

Comment 3

[Andrew Sutherland [:asuth] (he/him)]

(In reply to comment #2)
> (In reply to comment #1)
> > Hi.
> > 
> > On my system reguarly an automatic backup is running for all files marked with
> > the archive-bit. 
> 
> Maybe we could remove that bit, Andrew thoughts ?

If it turns out that most of the backup software for windows uses the archive bit to determine what to backup, it sounds like a promising avenue.  If someone wants to implement it and write unit tests, it sounds like the kind of patch that would make it in.

Comment 4

[Victor Letoh]

I've several profiles stored on a network share on a home server (I've several profiles myself, family member, etc.). This feature is simply KILLING for having profiles on a network and it seems to eliminate the chance the roaming feature will be re-established ever...

Comment 5

[Victor Letoh]

PS. I don't consider this as an "enhancement" issue but just a plain bug.

Comment 6

[rmvbfk]

Two random comments:

1. This file should be placed under local, not roaming data. The cache, updates, safe browsing data etc are stored under local, so if this is something similar is should be moved there too.

On Vista/7 this is C:\Users\User\AppData\Local\Thunderbird\
as opposed to C:\Users\User\AppData\Roaming\Thunderbird\
which is for persistent stuff.

2. The sqlite database has a page size of 1024 bytes. 4096 should perform a little bit better (Firefox uses 4096 for all of its databases).

Comment 7

[Moe K]

I'm having the very same issue. With OS X's Time Machine, you can exclude stuff from backups only on a per-directory basis, so if I want to backup my mail and other aspects of my profile, I have to lose a lot of space on my backup drive every hour unnecessarily. 

I've just tried moving the sqlite file and making a symlink to it in the original location, but it didn't work (no search results, link wasn't overwritten with a new index either).

(In reply to comment #6)
> 1. This file should be placed under local, not roaming data. The cache,
> updates, safe browsing data etc are stored under local, so if this is something
> similar is should be moved there too.

That's a good idea, but it doesn't help backups and there's no OS X equivalent. Putting it in a subfolder (in local on Windows) on the other hand would solve all of this.

Comment 8

[Moe K]

Just reading the comments a little closer than I did yesterday when posting comment #7. I'd like to reiterate that this is not strictly a Windows problem: the file gets large and causes backup-related issues regardless of platform.

Solutions involving changing the archive bit as discussed in comments 1-3 are useful for Windows users but don't help others.

Comment 9

[WADA:World Anti-bad-Duping Agency]

(In reply to comment #0)
For "global-messages-db.sqlite can become rather large" in your bug summry.
Are you saying that file size of global-messages-db.sqlite is far larger than it should be? Or merely that file size was larger than you thought?

For big file size due to no compaction(VACUUM).
> Bug 538493 Provide Capability to Compress All SQLite Database
For non-deleted big global-messages-db.sqlite even after Gloda is disabled.
> Bug 552764 When Gloda (Indexing) is deactivated, automatically reset/empty its global-messages-db.sqlite
For user specified global-messages-db.sqlite location. 
> Bug 535137 File location of index (global-messages-db.sqlite)

Fix of Bug 535137 will be required to disable backup by "per directory setting" of your backup software.

To all complaint poster about archive bit and backup:

As file for SQL data base, file backup while application is running is usually useless, because file backup which is obtained by external backup software while application is running, is usually dirty data(data in a file is inconsistent, data can be updated while backup software is reading). For recovery from backup, clean image copy at a point and log file(journal) is usually required to recover data base from backup.

Exclusive open of global-messages-db.sqlite(prohibit read open by others while Tb is running) is a way to disable backup of global-messages-db.sqlite by backup software which bases only on archive bit and/or file timestamp.
I don't know SQLite3 has such option or not. Even if exsts, I don't know Tb can use such option or not.

Comment 10

[Russell East]

I see what you're asking, and Bug 535137 is expressing more succintly what I wanted, so you could mark one of these two as duplicate.   I personally enjoy using gloda , so I'm simply asking for another directory location for global-messages-db.sqlite to be placed away from tbird profile directory in some well-known user-temp folder.   I'm also asking that any other non-database (non-essential) files, e.g. Cache maybe, and how about lock files (just guessing) be treated the same way.   As a result, we can arrange to backup the tbird profile area and be assured only essential data is protected.   Thanks

Comment 11

[WADA:World Anti-bad-Duping Agency]

per comment #10 by bug opener, closing as dup of bug 535137.

To all complaint posters about archive bit and backup, excluding bug opener:

If you believe that change of Tb is mandatory for your backup software which bases only on archive bit and/or timestamp of file even though file for SQL database, please open separate bug for enhancement.