Confirmed users
595
edits
Firefox/Projects/Places Vacuum: Difference between revisions
m
→Better vacuum support in SQLite
(Created page with '= Overview = '''Sprint lead:''' mak '''Sprinters:''' '''Description''' :Fragmented Sqlite databases are known to make awesomebar performances bad, and cause other perf pr…') |
|||
| (4 intermediate revisions by the same user not shown) | |||
| Line 3: | Line 3: | ||
'''Sprint lead:''' mak | '''Sprint lead:''' mak | ||
'''Sprinters:''' | '''Sprinters:''' sdwilsh | ||
'''Description''' | '''Description''' | ||
| Line 45: | Line 45: | ||
== Currently investigated options == | == Currently investigated options == | ||
* '''Vacuum with a button''': discarded, we don't want users to have to rememeber they should vacuum. | |||
* '''Vacuum in background''': Possible for small databases, but for databases like places.sqlite that can take one hundred megabytes, is just not feasible | |||
* '''Vacuum on major upgrade''': This would be a good compromise, but has some drawback. First of all there is a feeling that making upgrades slower will cause users to delay them. Secondly it would be a Firefox only fix. Third it would put someting storage related in update code. | |||
* '''Vacuum on idle''': This is the currently taken in count solution. | |||
* '''Better vacuum support in SQLite''': this is the long term solution | |||
=== Vacuum on idle === | === Vacuum on idle === | ||
This approach is actually the best one, but has some glitches: | This approach is actually the best one, but has some glitches: | ||
* We need a guess if the database needs to be vacuumed, we can't detect much about it (see better vacuum section below for information), we will just try to use the freelist to detect if big changes have happened in the database and it needs to be shrinked. Current ratio could be freelist_count/page_count, we need to define which can be a good treshold to activate vacuuming. Actually we could check if 1/ | * We need a guess if the database needs to be vacuumed, we can't detect much about it (see better vacuum section below for information), we will just try to use the freelist to detect if big changes have happened in the database and it needs to be shrinked. Current ratio could be freelist_count/page_count, we need to define which can be a good treshold to activate vacuuming. Actually we could check if 1/5 of the db space is unused space. | ||
* We should not vacuum too often, indeed vacuum completely removes the freelist, that is used to enhance INSERTs speed. Vacuuming too often will cause INSERTs to be slower. we will try to avoid vacuuming more than once a month, but at least once in 2 months. | * We should not vacuum too often, indeed vacuum completely removes the freelist, that is used to enhance INSERTs speed. Vacuuming too often will cause INSERTs to be slower. we will try to avoid vacuuming more than once a month, but at least once in 2 months. | ||
* if the user is idle but watching a movie we could end up creating issues. we will use an async statement to vacuum, that will at least ensure that we run it in a separate thread, if user comes back to the browser the UI won't be locked, but anything Places related won't work and will block it. | * if the user is idle but watching a movie we could end up creating issues. we will use an async statement to vacuum, that will at least ensure that we run it in a separate thread, if user comes back to the browser the UI won't be locked, but anything Places related won't work and will block it. | ||
| Line 60: | Line 60: | ||
=== Better vacuum support in SQLite === | === Better vacuum support in SQLite === | ||
Short term we | Short term we need at least a way to tell if a database needs to be defragmented, this is actually not possible without manually parsing the database file page by page. That would be over-engineering, slow, and hard to maintain (if the file format changes). | ||
Using Places Stats to get a "magic number" able to tell us if the database size is exagerated is not possible. We can get a guess, but in many cases it will generate false positives due to a couple things (i tried generating some spreadsheet to test the thing): | |||
* external applications can add binary annotations and we can't guess what's the size of those. | |||
* actually guessing an avg size of titles and urls for different locales can be hard. | |||
* using an hard size limit is bad because mobile and other apps are going to save a smalleramount of informations. | |||
We will ask SQLite team if it's possible to get a pragma option to analyze the database and get a guess if it needs to be vacuumed. | We will ask SQLite team if it's possible to get a pragma option to analyze the database and get a guess if it needs to be vacuumed. | ||