GNOME Bugzilla – Bug 734916
Slow startup with many DB QUERY messages
Last modified: 2014-08-19 23:18:53 UTC
I finally caught a slow start with full debugging. See the attached file for the log. There were only two new messages, I believe, but it took tens of seconds for Geary to display them. The log ends when I closed Geary shortly after reading both.
Created attachment 283611 [details] Log from -d --log-network Bugzilla doesn't like big files, apparently. Here's the first part.
Created attachment 283612 [details] log, part 2
You're seeing two problems, both known (bug #725929). It's something I'm working on in the background, so to speak, attempting to isolate problem cases and fix them when I can. The fact remains there are two large problems with the database: (a) Geary used to use an INNER JOIN to find incomplete messages (needed for the ImapEngine.EmailPrefetcher). This was horribly slow and I finally determined that it quicker to simply do the join manually with two separate transactions. That commit is at https://git.gnome.org/browse/geary/commit/?id=28629ce6e2b149ea6b3ff94f27ab30237b7bcf2d The manual transactions may seem annoyingly slow, but the INNER JOIN was actually much worse and tied up the db for a long period of time. With the new strategy, the database is locked for less time and in smaller chunks of time, allowing for other operations to complete. (b) At startup Geary checks to ensure all messages have been indexed in the FTS search table. This also takes a horrible amount of time. Testing showed that merely touching the FTS (with any query) causes SQLite to do a long load that seems to touch a rather large number of pages in the db. We were unable to find a good way around this. (b) is the worse one. In your log the search load took 60s to complete, and experience shows all other db transactions are blocked until the transaction completes. My nagging suspicion now is that we need to move the FTS into a separate database file, although that creates a host of cogency issues that would need to be addressed. There might be a clever solution for (a) I'm overlooking. (b) looks much more serious; unless there's some handy SQLite flag that turns off this behavior, I worry we need to restructure the entire db to get around the issue. *** This bug has been marked as a duplicate of bug 725929 ***