db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject Really need some help here please. I think I am having a problem with some sort of caching in Derby
Date Tue, 27 Mar 2012 18:11:41 GMT
Derby is giving me fits but I just found a reproducible pattern.   The problem that I am having
is that queries that are totally index based without any changing of the query plan are becoming
slow.   Normally the query takes about 6 milliseconds and it quickly degrades to 20 seconds
or more.

Here is some  background.  The system is quite busy with about 40% system utilization for
Derby.  Heap used space is around 2Gb out of 4Gb available.  Java is Oracle Java 1.6 and using
the UseParallelGC parameters for garbage collection.

There are thousands of statements being executed and most of these are new statements so few
prepared statements are probably being used.   Using jvisualvm, I can see new classes being
created and loaded every second.

JvisualVM is showing a GC Activity of about 1.5% so the system is not being hammered by the
garbage collector.   Also the "old" memory is glowing really slowly.

So what I found is that if I hit the "Perform GC" button on JvisualVM to perform an explicit
garbage collection, the same query that was taking 20 seconds or more instantaneously comes
down to taking about 6 milliseconds.  Its processing time then starts to creep up back towards
20 seconds or more.

Note that the system utilization does not decrease immediately after performing the forced
garbage collection.  It is still just as busy at about 40% cpu utilization.

Just on a whim, right now, I put "CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE();" just in front
of my query (through SquirrelSQL) and executed both at the same time so that I could eliminate
the page cache as being a performance helper.   Still the same pattern.      After the system
runs for a few minutes at this really busy, many thousands of statements, trying the query
takes tens of seconds.   Force a garbage collection and the query immediately takes milliseconds
and then starts to degrade.

Another point, I also found that even when the parallel garbage collection performs a major
collection on its own (I watch this happen with jvisualvm), that the query immediately becomes
responsive right after this.

So I am wondering if there is some sort of cache in Derby that is getting really big and slow
to find something in that gets cleanup when an major garbage collection happens.

Any thoughts on this?

Any pointers to code to check or something to try in the code will be most welcome.  I am
comfortable with building Derby from source....


View raw message