> 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 disk and exclusive lock will be taken but never trigger unbound growth. There's nothing in SQLite's external interface that could tell you how much of its cache is dirty at the moment and requires writing to the media at the end of transaction. But you can probably generate that information by yourself. You need to create your own implementation of SQLite's cache (see http://www.sqlite.org/c3ref/pcache_methods.html). Then calculate how many pages you have in the cache in total and how many pages are pinned using xFetch function and are not yet unpinned using xUnpin method. Dividing second number by first should give you a good estimate of how many pages are dirty. This is solely a mental speculation - I didn't try it by myself and I don't know SQLite's internals well enough to guarantee you that it will work as described. But from what I know it should work. Pavel On Thu, May 27, 2010 at 7:29 AM, Igor Sereda <ser...@gmail.com> wrote: > > 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 effective to have N=1000, for example. Another piece of data may > lead to 1000 INSERTs. Now, if we still have N=1000, then we'll have > 1,000,000 INSERTs in a single transaction. It's completely unpredictable. > 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. > > Do you think this makes sense? > > We could theoretically count the number of DML statements or steps, but this > would imply changing the underlying architecture of the application, so that > any plug-in or extension that accesses SQLite also reports how much data did > they change. It's not very convenient. > > Kind regards, > Igor > > > Black, Michael (IS) wrote: >> >> So only do N many records in one batch. That's the easiest thing. Forget >> about the cache and just use responsiveness to adjust how many records you >> allow at once. >> >> Pseudo-code: >> recnum=0 >> BEGIN; >> while more records >> INSERT.... >> recnum++ >> if (recnum % 1000) >> COMMIT; >> BEGIN; >> end >> COMMIT; >> > > -- > View this message in context: > http://old.nabble.com/Using-cache-stats-to-balance-transaction-size-for-optimal-performance-tp28690967p28692687.html > Sent from the SQLite mailing list archive at Nabble.com. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
