Open Bug 1574093 Opened 5 years ago Updated 2 years ago

vacuum all sqlite database

Categories

(MailNews Core :: Database, enhancement)

enhancement

Tracking

(Not tracked)

People

(Reporter: xavier.combelle, Unassigned)

References

(Depends on 1 open bug, Blocks 1 open bug)

Details

User Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Firefox/68.0

Steps to reproduce:

Firefox, thunderbird and my whole computer was slower and slower

Actual results:

I launch a script that vaccum all sqlite database of my home directory. (as far as I know, firefox and thunderbird, are the main users of sqlite)
And my computer magically react instantly now and is just a fresh one
I have a limited system of linux with only 4Go of RAM and xfce. So such slow behavior might not happen with a computer with a lot more ram.

Some way to vaccum all sqlite database of firefox including the ones of the extensions looks like a good idea, behind vaccum places.sqlite which is already done as far as I understand

I made same bugs report about firefox because I believe that both product have same behavior https://bugzilla.mozilla.org/show_bug.cgi?id=1574092 (that explain why I mention places.sqlite that don't apply to firefox)

For Gloda we have Bug 484646 - some day gloda may want to VACUUM or auto-VACUUM. But there isn't a bug mentioning vacuuming places. but there is a meta bug 1038296 which mentions several places issues

Mine is only 5mb. What size was yours in Thunderbird before vacuum?

Blocks: tb-places
Component: Untriaged → Database
Flags: needinfo?(xavier.combelle)
Product: Thunderbird → MailNews Core
Summary: vaccum all sqlite database → vacuum all sqlite database

I did not notice the size of .sqlite file before using it.
As I have a gmail box in which one I never succeed to delete a lot to messages that means 2 times 50000 messages and 2Go of inbox size
As such .sqlite of thunderbird become a lot than 5mb.

here is my full content of .sqlite at the root

-rw-r--r-- 1 c2c c2c 512K juil. 21 00:38 blist.sqlite
-rw-r--r-- 1 c2c c2c 224K juil. 23 09:48 content-prefs.sqlite
-rw-r--r-- 1 c2c c2c 512K août 15 09:30 cookies.sqlite
-rw-r--r-- 1 c2c c2c 5,0M juil. 22 23:33 favicons.sqlite
-rw-r--r-- 1 c2c c2c 192K juil. 21 00:39 formhistory.sqlite
-rw-r--r-- 1 c2c c2c 382M août 15 17:54 global-messages-db.sqlite
-rw-r--r-- 1 c2c c2c 1,7M août 11 10:12 kinto.sqlite
-rw-r--r-- 1 c2c c2c 96K juil. 21 00:38 permissions.sqlite
-rw-r--r-- 1 c2c c2c 5,0M août 15 09:30 places.sqlite
-rw-r--r-- 1 c2c c2c 512 juil. 21 02:10 storage.sqlite
-rw-r--r-- 1 c2c c2c 96K juil. 22 23:33 webappsstore.sqlite
-rw-r--r-- 1 c2c c2c 736K juil. 21 00:38 ./calendar-data/local.sqlite
Honestly I have no idea if the new performance is linked to the fact that firefox perform better and not thunderbird, or both.
I only notice only than my whole system work a lot better after vaccum all .sqlite file.
It might be also a some .sqlite file used.
I have also some old firefox profile where .sqlite was also vaccuum.
And otherwise a very few .sqlite file of program not running.

Flags: needinfo?(xavier.combelle)

Thanks. Without the original "pre-vacuum" list it's not possible to give a sensible answer regarding performance. In the future, please document the state before changing your environment.

But certainly the list above, post-vacuum sizes, all seem reasonable. Specifically 382M for global-messages-db.sqlite is fine or even up to a GB or two. And I think places.sqlite is created at 1MB, so 5MB should certainly not cause a problem - but we also don't know the original size.

Status: UNCONFIRMED → NEW
Ever confirmed: true

I totally agree, that it would be preferable,that I would have document, before changing my environment. If you have some way to od so after thing, (for example in some log thing of linux I would do it immediatly)
I would do it, if my vaccum would have a totally unexpected behavior.
The thing I did vaccum on the whole .sqlite is only becaus I ddi it for being better without unexpected result. So I have no way to do anything in case of unexpected result.
The only expected result in my way of thinknig was places.sqlite would improve my navigation on firefox history.
The fact that my whole system worked magically far better was totally unexpected.
And the only single difference in the before/after is the whole vaccum thing.
So I would recommend, that at least in an experimental setup, that the vaccum whole instead of places would be tested (for example in firefox experiments with big enough base ) to mesure some difference. If whole, vaccum does something, even if unexplanable, for someone other than me. just do it for everybody. I have no idea how it could have some drawback except on potential new bugs by the new codes.
Except by additional code, I really don't see any reason than anything can't change only in better.
I searched for any example of PostgresSQL VACCUM, Sqlite VACCUM, or Mysql OPTIMIZE follow up and totally failed.
This search only gave me example of huge improvemnet:
First it returned me this bug of firefox, which makes exactly same asumption than me (and at last some real code was written on this assumption, and nobody has say that there is some scenario where it could decrease performance. The fact that there is potential benefits by optimizing the database was taken to be obvious. Apparently the only debate was how to do it in the best way. As my example show, it van definitely have a huge difference (and I would say that the fact I'm in a restricted hardware like a significant users of thunderbird is a factor of potential improvmenet.
The two other items confirming what I said (on a research trying to prove the opposite was https://forum.duplicati.com/t/vacuum-performance-improvement-is-huge/5186 and https://stackoverflow.com/questions/784173/what-are-the-performance-characteristics-of-sqlite-with-very-large-database-file )
VACCUM sqlite documentation, clearly assume the same thing (potential improvement and no drawback)
sqlite has even an auto_vaccum mode, which can't be enable by default, because while reducing the file size, it can lead to extra fragmentation which has a performance impact. in this documentation, vaccum as only advantages either by reducing the size or reducing the fragmentation.
Clearly the only reason that VACCUM or OPTIMZE is not done in automate way by SQL engine which give them as a manual command is that it is costly in time, might be lock the database, and as such has to be done when there is no impact. (and as far as I know, that my whole vaccum thing was slow, it was because I used a script which scanned all my home directory. I looked a little bit on the file and did not notice even when vaccum happened. The script write all the file scanned and I don't know how to disable it, and as such I hav no idea of the size redution implide, as it is logged.
I give the script if anyone want to try it.
https://gist.github.com/xcombelle/ecfa46719cd2ac5f8aeb401b173c7901
It was stolen from https://gist.github.com/docwhat/1973799 which was created probably for similare reason of performance.

Additional thing, as far as I know, most of my performances problems start on my system when there is too much swapping and as such when there is too much memory used by some programs. There is probably some tweak to improve my system working, but if the reason is too much swapping, Vaccum on all files, avoid this swapping. <--- This is to take with a big conditional, because it can be something totally different

Blocks: 484646
No longer blocks: 484646
Depends on: 484646
Severity: normal → S3
You need to log in before you can comment on or make changes to this bug.