895516 - Periodically delete/rebuild global-messages-db.sqlite if it gets too big compared to message store (eg after massive deletes)

Status: RESOLVED DUPLICATE of bug 484646

(Thunderbird :: Search, enhancement)

Description

[Jason Duell]

This is a stab in the dark, but it would be easy to implement if it's useful.

I'd been seeing thunderbird (17.07 on Linux x86-64) sit around all day using 50-100% CPU, almost non-stop.   

I noticed my global-messages-db.sqlite had gotten to be 1.1GB in size (out of a .thunderbird directory that was 2.8GB in size total).  I deleted it, and also about 1/2 of my old mail (most of it gmail IMAP bugzilla mail: I deleted it on gmail itself since that seemed faster).   Thunderbird is no longer munching my CPU, and my global-messages-db.sqlite is 98MB, out of a .thunderbird folder that's now 1.6GB. I watched the Activity Monitor to see it index everything, and it's been a day now, so it really does seem that my old global-messages-db.sqlite file was waay too big, ie it was >50% of disk space used, and is now <10%.

I don't know much about sqlite internals but it seems like something metastatized here, and is likely to the cause for the poor perf I was seeing.  If nothing else we're wasting disk space.  Would it make sense to periodically check if the file is above some % of the total profile size, and delete/rebuild it if so?

Comment 1

[Wayne Mery (:wsmwk)]

It is unclear whether something is warranted (or automated), because this is exceedingly rare. But similar things have been requested. OTOH a tool for sanity checking** would be simpler, and in all areas would aid and speed diagnosis of issues, with or without the help of "Thunderbird support".  See bug 643796 which is about folder size.  And also see bug 632791.

yes, all of these are mentioned at https://wiki.mozilla.org/Thunderbird:Testing:Memory_Usage_Problems see esp. item #11
- extraordinary sized gloda database, so wasted disk space
- high CPU

The preferred or starting approach might be to identify bad folders/messages, because these are (we think) what cause the huge gloda database and the high CPU. libmime rewrite (which is in progress) might also help. 

xref also Bug 492620 better, easier logging/diagnostics for Thunderbird


** an example sanity check might be for database to not exceed 50% of combined the size all folders.  (https://wiki.mozilla.org/Thunderbird:Testing:Memory_Usage_Problems suggests ~10% is "normal".)

Comment 2

[rsx11m]

Rebuilding global-messages-db.sqlite can be /very/ expensive, thus it shouldn't be done unless necessary. Some integrity verification and sanity checks are probably better heuristics if those can be specified, rather than just using the size of the profile and the portion that the database file occupies (unless that's a reliably predictable parameter).

Comment 3

[Wayne Mery (:wsmwk)]

Jason, 
1. did you keep a copy of the profile for someone to test with?
2. when CPU was high did you not also see other symptoms that go with high CPU?  For example slow deletes and jank during compose?

I'm bucking this down to ENH because I'd think we need to identify the real perf failure - what caused the large index, and whether the index size is itself the perf issue (it may be part of it but I doubt it's the primary source).

Comment 4

[Jason Duell]

Sadly I didn't keep a copy :(   Will do next time if I see it again.

Feel free to close bug for now if you want.

Comment 5

[WADA:World Anti-bad-Duping Agency]

FYI.
A method to check whether "wasted spaces in xxx.sqlite file" is cause of performance issue or not.
  (1) Do VACUUM manually.
> http://www.sqlite.org/lang_vacuum.html
If standard SQLite database like places.sqlite, this can be done using Firefox+SQLit Manager or batch command of SQLite.
Howveer, Gloda uses FTS3 extension.
> http://www.sqlite.org/fts3.html
Sorry but I don't know how to enable same tokenizer used by Tb in Firefox+SQLit Manager or SQLite batch, although I could use standard tokenize of FTS3 extension as dummy/faked tokenizer of global-messages-db.sqlite in order to stop "module not found error" in Query only.
If someone can add following function to GloaQuilla, we can use it for Glpda related performance problem analysis reported by general users.
(1) Disable all Gloda related activity in Tb.
(2) Do VACUUM on global-messages-db.sqlite

Comment 6

[Wayne Mery (:wsmwk)]

I had forgotton about bug 484646(In reply to Jason Duell (:jduell) from comment #4)
> Sadly I didn't keep a copy :(   Will do next time if I see it again.
> 
> Feel free to close bug for now if you want.

I think we can safely say that at least part of this is bug 484646, which I had forgotten about.