Re: [sqlite] Using cache stats to balance transaction size for optimal performance

On May 28, 2010, at 5:42 PM, Pavel Ivanov wrote: >> It will retry the EXCLUSIVE lock each time a page that is not >> in the cache is required by SQLite (a "cache-miss"). > > If SQLite doesn't require to read any pages but only adds new pages to > the file does it count as cache-miss? Yes.

Re: [sqlite] Using cache stats to balance transaction size for optimal performance

> It will retry the EXCLUSIVE lock each time a page that is not > in the cache is required by SQLite (a "cache-miss"). If SQLite doesn't require to read any pages but only adds new pages to the file does it count as cache-miss? Pavel On Fri, May 28, 2010 at 1:41 AM, Dan Kennedy

Re: [sqlite] Using cache stats to balance transaction size for optimal performance

On May 27, 2010, at 8:07 PM, Pavel Ivanov wrote: >> That's true, except for the case when exclusive lock fails; at >> least that's >> what Dan Kennedy says to my question from a couple of years ago: >> http://old.nabble.com/changes-in-cache-spill-locking-since-3.5.9--td20564357.html#a20564357

Re: [sqlite] Using cache stats to balance transaction size for optimal performance

> That's true, except for the case when exclusive lock fails; at least that's > what Dan Kennedy says to my question from a couple of years ago: > http://old.nabble.com/changes-in-cache-spill-locking-since-3.5.9--td20564357.html#a20564357 It's interesting feature, I didn't know about that. It

Re: [sqlite] Using cache stats to balance transaction size for optimal performance

Pavel, Thanks for the reply! I was afraid using pcache would be the only way :) As for this: Pavel Ivanov-2 wrote: > > No way. Cache won't ever grow just because you have large transaction. > It will only be spilled to disk and exclusive lock will be taken but > never trigger unbound

Re: [sqlite] Using cache stats to balance transaction size for optimal performance

> And when the amount of data changed in a single transaction is large enough, > it would cause either cache spill and exclusive lock on the database, or the > growth of cache and memory consumption. No way. Cache won't ever grow just because you have large transaction. It will only be spilled to

Re: [sqlite] Using cache stats to balance transaction size for optimal performance

To: sqlite-users@sqlite.org Subject: Re: [sqlite] Using cache stats to balance transaction size for optimal performance Michael, Thank you for your suggestion! The problem with this approach is that N would not be a constant that we could tune. As I mentioned, the amount of updates may vary

Re: [sqlite] Using cache stats to balance transaction size for optimal performance

Michael, Thank you for your suggestion! The problem with this approach is that N would not be a constant that we could tune. As I mentioned, the amount of updates may vary, depending on the data received. For example, one piece of data may lead to a single INSERT. So it would be safe and

Re: [sqlite] Using cache stats to balance transaction size for optimal performance

-users-boun...@sqlite.org on behalf of Igor Sereda Sent: Thu 5/27/2010 3:27 AM To: sqlite-users@sqlite.org Subject: [sqlite] Using cache stats to balance transaction size for optimal performance I would like each transaction to be as large as possible, but not too large to cause cache growth

[sqlite] Using cache stats to balance transaction size for optimal performance

I would like each transaction to be as large as possible, but not too large to cause cache growth or cache spill. We have a stream of incoming data, with each piece of data causing updates in SQLite database. The number of rows inserted/updated for each data record may vary. If I enclose each