Open Bug 831578 Opened 11 years ago Updated 2 years ago

Ability for Sqlite.jsm to automatically disconnect idle connections

Categories

(Toolkit :: Storage, enhancement, P3)

enhancement

Tracking

()

People

(Reporter: gps, Unassigned)

Details

We've been talking about reducing the memory usage of Firefox Health Report and one of the ideas that came up was to have the SQLite connection automatically disconnect after periods of idle. If we are going to implement this, we might as well do it in Sqlite.jsm so others can leverage it.

I'm thinking we could pass a parameter when opening a new connection that defines the "disconnect after idle" time span. The connection instance will install a timer. The timer is reset on every operation. If the timer hits 0 and there are no in-flight statements, the connection will be silently closed. If there is a new request to perform database activity, the connection is silently and automagically reopened and the operation is performed.

We could also use the mechanism for managing connection open/close state to lazy open the connection if we so wanted.

Thoughts?
I don't think it's useful to "disconnect", the connection doesn't consume much memory by itself and reconnecting may cause jank (open is still on main-thread).

Instead what would be useful is:
1. finalize and remove statements unused by N minutes
2. PRAGMA shrink_memory
(In reply to Marco Bonardo [:mak] from comment #1)
> I don't think it's useful to "disconnect", the connection doesn't consume
> much memory by itself and reconnecting may cause jank (open is still on
> main-thread).

Jank on open is a good point.

If I go to about:memory, I see some rather large numbers:

13,750,832 B (01.66%) -- places.sqlite
947,080 B (00.11%) -- cookies.sqlite
833,800 B (00.10%) -- healthreport.sqlite

If I drill down, most of that is in "cached used."

> Instead what would be useful is:
> 1. finalize and remove statements unused by N minutes
> 2. PRAGMA shrink_memory

This sounds like a good compromise!

Out of curiosity, would "PRAGMA shrink_memory" minimize the "cache used" size as reported by about:memory? What is "cache used" anyway?
Flags: needinfo?(mak77)
(In reply to Gregory Szorc [:gps] from comment #2)
> If I go to about:memory, I see some rather large numbers:
> 
> 13,750,832 B (01.66%) -- places.sqlite
> 947,080 B (00.11%) -- cookies.sqlite
> 833,800 B (00.10%) -- healthreport.sqlite
> 
> If I drill down, most of that is in "cached used."

Yes, we ha(d)ve a plan to do the same things I suggested here directly in Storage, StatementCache will take care of finalizing unused statements and the connection will invoke shrink_memory on idle (or when it will be unused by some time, TBD).

Note that those "large" numbers are totally under our control, we can enlarge or shrink the used memory at our pleasure, the current value is just what we found being performant enough for our needs (as usual it's a memory/performance compromise).
We control those values through the cache_size pragma. All databases afaik use a max cache of 4MB, but the Places autocomplete connection, that uses 6MB (I tried reducing it but autocomplete was becoming too slow).
The places.sqlite size you read there is sum of cache for all the connections we have on the database (it's 4 iirc, so 6+4+4+4 = max 18MB)

> > Instead what would be useful is:
> > 1. finalize and remove statements unused by N minutes
> > 2. PRAGMA shrink_memory
> 
> This sounds like a good compromise!
> 
> Out of curiosity, would "PRAGMA shrink_memory" minimize the "cache used"
> size as reported by about:memory? What is "cache used" anyway?

It should indeed minimize the cache-used while finalizing statements should reduce stmt-used
cache-used measures the currently used cache size, this is an area of memory SQLite uses to cache temporarily pages (copied from the disk file) to speed up next operations hitting the same database pages.
Flags: needinfo?(mak77)
Component: General → Storage
Type: defect → enhancement
Priority: -- → P3
Severity: normal → S3
You need to log in before you can comment on or make changes to this bug.