On Mar 9, 11:10 pm, "David Crawshaw" <[EMAIL PROTECTED]> wrote:
> Derek Battams <[EMAIL PROTECTED]> wrote:
> > The average run of the worker thread produces ~1100 log entries
> ...
> > If no external process is to be called on a given run then the
> > average run time is ~2-5 seconds
>
> What you're saying doesn't make any sense:
>
> $ java -cp .:sqlitejdbc-v042-native.jar -Djava.library.path=. Test
> inserting 1000 entries took 0.018 seconds
> $ java -cp sqlitejdbc-v042-nested.jar:. Test
> inserting 1000 entries took 0.018 seconds
>
> Let's try even more:
>
> $ java -cp sqlitejdbc-v042-nested.jar:. Test
> inserting 100000 entries took 1.51 seconds
> <snip java code>
I did not mean to imply that the actual inserts were taking the ~2-5
seconds. The entire run of the worker thread takes 2-5 seconds.
Certain events during each iteration of the loop are what trigger
inserts into the log table. Therefore it could be up to 2-5 seconds
before the commit() is executed. The worker thread looks something
like this (at a very, very high level):
while(true)
{
List l = getMediaObjects();
foreach(MediaFile mf : l)
{
log.log("Processing '" + mf.getTitle()); // DB insert
if(p.shouldExecuteExternalProc(mf)) // DB inserts as certain
events of interest occur
{
log.flush(); // About to start time consuming process so
'flush' log (i.e. commit what's been inserted and release locks)
// Start process and log return value from process
}
else
log.log("Not running external proc because..."); // DB insert
}
// Determine when we're running again
log.log("Sleeping until...");
log.flush();
Thread.sleep(x);
}
Each call of log.log() checks the running count of inserts and when
the threshold is met then it triggers a commit itself. And, of
course, the log.flush() also triggers a commit. Replacing add/
executeBatch with just an executeUpdate() in your code example gives
about the same results. Removing the autoCommit(false)/commit() calls
and running your code with add/executeBatch yields the same kind of
perf hit I was seeing myself with auto commit on:
D:\sqlite3>java -cp "sqlitejdbc-v037-nested.jar;test.jar" Test
inserting 100000 entries took 2.443 seconds
Hardware: T41 Thinkpad (Pentium M 1.8Ghz, 2GB RAM, Win2K)
This test produced a ~10MB db file. If I modified your code to not
disable autoCommit I had to kill your test program after ~8 mins and
only 10% of the inserts written (db file was ~1.1MB after I killed the
run). So I think we'd agree that a transaction is definitely required
and in this case it may be up to 2-5 seconds before the transaction is
committed. So in this particular case, the timeout is just too low
and, as you say, since the JDBC interface doesn't provide a way to set
connection parameters I guess I'll just proceed with rebuilding the
driver with the modified timeout value.
> Clearly the speed issues have nothing at all to do with SQLite. I
> admit I'm running this on a Core 2 Duo, but to turn 0.018 seconds into
> 2-5 seconds would probably take a Pentium II.
You're probably right. Again, as I said above, I didn't mean to imply
that the actual insert of 1000 rows was taking the full 2-5 seconds.
> You're problem is whatever processing you're doing for each line
> inside your loop. The solution is really easy: use the addBatch() /
> executeBatch() code as in the above example. It all gets stored in
> memory, and only sent to SQLite when you call executeBatch(). The
> memory usage isn't a problem, whatever you're using as a servlet
> container is sure to be such a memory hog as to make it look
> insignificant.
Not sure if it's a 'problem', but you are correct. It's the work
being done prior to the commit that is consuming (most of) the 2-5
seconds. As noted above, the difference between addBatch() and
executeUpdate() is insignificant - at least running the test stub your
provided on my system. The reason for the timeout in the servlet
thread is because of the fact that the worker thread could take up to
5 seconds before calling commit(), which releases the locks on the
db. The actual commit, of course, takes just fractions of a second
for the ~1000 rows.
>
> Again: the only legitimate need for a long RESERVED lock in SQLite is
> an insert or update statement containing slow sub-selects that are
> doing complex joins. Slow where clauses don't count, use an index.
It's too late now, but I'll continue to scratch my head on this
tomorrow. Since committing each row as they're logged is not an
option I don't see how I can avoid keeping the lock until the log
inserts are committed by the worker thread.
Your insight is appreciated and your link to the busy handler docs
were quite helpful.
Thanks,
Derek
--~--~---------~--~----~------------~-------~--~----~
Mailing List: http://groups.google.com/group/sqlitejdbc?hl=en
To unsubscribe, send email to [EMAIL PROTECTED]
-~----------~----~----~----~------~----~------~--~---
