Cleared pager cache

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Cleared pager cache

Brett Wilson
Hi,

I'm working on integrating sqlite into Mozilla. The problem is that
some Linux users have their profile (and hence, database) over a
network. Ignoring the safety issues for a moment, I'm trying to get
good performance. Sometimes it takes the network "a while" to read a
page, so I'm trying to read as few pages as possible.

I notice that it removes pages from the cache after each transaction.
Why is this done instead of letting them hang around for the next
transaction? When starting a new transaction, it could of check the
file for modifications and only clear the cache if the file has been
touched by another connection. This would really save us a lot of time
since in most cases there is only one connection and most of the
database should fit in the cache.

If this is doable, how much effort do you think would be required us
to change the cache clearing mechanism?

Thanks,
Brett
Reply | Threaded
Open this post in threaded view
|

Re: Cleared pager cache

D. Richard Hipp
Brett Wilson <[hidden email]> wrote:

> Hi,
>
> I'm working on integrating sqlite into Mozilla. The problem is that
> some Linux users have their profile (and hence, database) over a
> network. Ignoring the safety issues for a moment, I'm trying to get
> good performance. Sometimes it takes the network "a while" to read a
> page, so I'm trying to read as few pages as possible.
>
> I notice that it removes pages from the cache after each transaction.
> Why is this done instead of letting them hang around for the next
> transaction? When starting a new transaction, it could of check the
> file for modifications and only clear the cache if the file has been
> touched by another connection. This would really save us a lot of time
> since in most cases there is only one connection and most of the
> database should fit in the cache.
>
> If this is doable, how much effort do you think would be required us
> to change the cache clearing mechanism?
>

We have planned from the beginning to do this in version 3.
There are even provisions in the file format to support it.
But nobody has gotten around to it yet.

Your quick fix is to hold a transaction open for the duration
of your connection.  As long as a transaction is open, the
cache is not cleared.  On the other hand, if Mozilla crashes
with a transaction open, all of your changes roll back.

I will look into optimizing the cache performance as soon as
I can.
--
D. Richard Hipp <[hidden email]>