How to configure size of shared-cache in SQLite?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

How to configure size of shared-cache in SQLite?

Qiu Xiafei
SQLite provide a shared-cache mode (https://www.sqlite.org/sharedcache.html)
which will help to reduce cache size among multiple connections within a
process. But how to configure the size of the shared cache? I've tried PRAGMA
cache_size = 536870912. But no matter how much space i offered, it seems to
have no effect on memory consumption. So is there's a configuration to set
the size of shared-cache?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to configure size of shared-cache in SQLite?

Clemens Ladisch
Qiu Xiafei wrote:
> SQLite provide a shared-cache mode (https://www.sqlite.org/sharedcache.html)
> which will help to reduce cache size among multiple connections within a
> process. But how to configure the size of the shared cache?

There is not special shared cache; the shared-cache mode just shares the
normal cache. This cache can be configured with PRAGMA cache_size.

> I've tried PRAGMA cache_size = 536870912. But no matter how much space
> i offered, it seems to have no effect on memory consumption.

With the default page size of 4 KB, a cache size of 536870912 pages would
result in a limit of 2 TB.  It is unlikely that you have reached that.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to configure size of shared-cache in SQLite?

Keith Medcalf
In reply to this post by Qiu Xiafei
On Wednesday, 8 March, 2017 03:45, Qiu Xiafei <[hidden email]> wrote:

> SQLite provide a shared-cache mode
> (https://www.sqlite.org/sharedcache.html)
> which will help to reduce cache size among multiple connections within a
> process. But how to configure the size of the shared cache? I've tried
> PRAGMA cache_size = 536870912. But no matter how much space i offered,
> it seems to have no effect on memory consumption. So is there's a
> configuration to set the size of shared-cache?

PRAGMA cache_size sets the MAXIMUM size of the cache.  So, if you have a 100MB database, the cache will never grow to be bigger than 100MB, no matter how big you set the cache_size.

Why are you using SHARED_CACHE since it does not sound like you have memory constraints on cache size, which is the primary (only) reason you would ever want to use shared cache since the penalties for doing so are significant.

I come to the above conclusion since you are trying to set the upper cache size limit to 2 TB.  If you can afford to have 2 TB dedicated to a page cache, why do you need shared cache?




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to configure size of shared-cache in SQLite?

Jens Alfke-2

> On Mar 9, 2017, at 7:55 AM, Keith Medcalf <[hidden email]> wrote:
>
> Why are you using SHARED_CACHE since it does not sound like you have memory constraints on cache size, which is the primary (only) reason you would ever want to use shared cache since the penalties for doing so are significant.


What about for reducing I/O? If the cache is shared, presumably the number of read calls will be correspondingly reduced.

I’m considering using a shared cache to cut down on I/O, as well as memory. If I use a pool of connections for read-only queries from multiple threads, it seems like a win to have them share a cache. I’d use a separate connection with its own cache for writes. Does that make sense?

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to configure size of shared-cache in SQLite?

Keith Medcalf
On Friday, 10 March, 2017 10:57, Jens Alfke <[hidden email]> wrote:
>> On Mar 9, 2017, at 7:55 AM, Keith Medcalf <[hidden email]> wrote:

>> Why are you using SHARED_CACHE since it does not sound like you have
>> memory constraints on cache size, which is the primary (only) reason you
>> would ever want to use shared cache since the penalties for doing so are
>> significant.

> What about for reducing I/O? If the cache is shared, presumably the number
> of read calls will be correspondingly reduced.

You mean physical reads?  I suppose this would be possible, as long as the working set of all your read queries are able to fit in the cache simultaneously.  If not, you are likely to get more cache thrash with the cache being shared then if it is not shared since you are using the same cache for all connections, rather one per connection that will contain only the working set for the queries processed on that connection.
 
> I’m considering using a shared cache to cut down on I/O, as well as
> memory. If I use a pool of connections for read-only queries from multiple
> threads, it seems like a win to have them share a cache.

Maybe.  It depends if the overhead of managing a shared cache (and the possible thrashing of that cache) exceeds the cost of not using a shared cache.  That will depend on your workload.  It is easy enough to experiment, however, since you only change one parameter to enable shared cache for the connections in the pool.

> I’d use a separate connection with its own cache for writes. Does that make sense?

Yes.  However, each write will of course invalidate the cache used by the other connections (whether shared or not).  Though if it is WAL, I would suppose the cache would be invalidated only after a checkpoint ... but I am not certain of this.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to configure size of shared-cache in SQLite?

Simon Slavin-3

On 10 Mar 2017, at 9:34pm, Keith Medcalf <[hidden email]> wrote:

> You mean physical reads?  I suppose this would be possible, as long as the working set of all your read queries are able to fit in the cache simultaneously.  If not, you are likely to get more cache thrash with the cache being shared then if it is not shared since you are using the same cache for all connections, rather one per connection that will contain only the working set for the queries processed on that connection.

Two different patterns of use.  One is that the different threads/processes usually care about different rows (maybe in different tables).  In that case, shared cache is of very little benefit.  The other is when different threads/processes usually update the same parts of the files.  In that case sharing cache can provide a great improvement in throughput.

Modified for SQLite, of course, because almost every modification modifies the beginning of the database file and the beginning of the journal file.

But yes, as Keith points out, there’s no way to know which optimization(s) will benefit your particular setup without trying them.  And you shouldn’t waste a lot of time on doing anything non-standard unless a vanilla setup is too slow.  You are not trying to provide the fastest possible program; you are trying to provide a program which is fast enough.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to configure size of shared-cache in SQLite?

Simon Slavin-3
In reply to this post by Keith Medcalf

On 10 Mar 2017, at 9:34pm, Keith Medcalf <[hidden email]> wrote:

> You mean physical reads?  I suppose this would be possible, as long as the working set of all your read queries are able to fit in the cache simultaneously.  If not, you are likely to get more cache thrash with the cache being shared then if it is not shared since you are using the same cache for all connections, rather one per connection that will contain only the working set for the queries processed on that connection.

Two different patterns of use.  One is that the different threads/processes usually care about different rows (maybe in different tables).  In that case, shared cache is of very little benefit.  The other is when different threads/processes usually update the same parts of the files.  In that case sharing cache can provide a great improvement in throughput.

Modified for SQLite, of course, because almost every modification modifies the beginning of the database file and the beginning of the journal file.

But yes, as Keith points out, there’s no way to know which optimization(s) will benefit your particular setup without trying them.  And you shouldn’t waste a lot of time on doing anything non-standard unless a vanilla setup is too slow.  You are not trying to provide the fastest possible program; you are trying to provide a program which is fast enough.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to configure size of shared-cache in SQLite?

Jens Alfke-2
In reply to this post by Simon Slavin-3

> On Mar 10, 2017, at 3:32 PM, Simon Slavin <[hidden email]> wrote:
>
> Two different patterns of use.  One is that the different threads/processes usually care about different rows (maybe in different tables).  In that case, shared cache is of very little benefit.  The other is when different threads/processes usually update the same parts of the files.  In that case sharing cache can provide a great improvement in throughput.

This is the latter case — the connections would be in a pool for threads to use. So each connection will be making a random subset of the queries.
(My understanding is that the SQLite package for .NET already works this way, though I haven’t looked at it myself.)

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users