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.... Brett
