Note, however, it's when I get rid of the select statement that the database growth stops.  So it would seem that the transaction below is not the culprit...

On Fri, Apr 30, 2010 at 3:32 PM, David Van Couvering <david.vancouvering@gmail.com> wrote:
Thanks, Knut, I'd forgotten about that.

So, here are a couple of things I'm doing that may be causing problems:

- When I obtain a connection to to a database, I run a command to create the tables, independent of whether they exist or not, and then ignore the exception saying they already exist.  This was to avoid having to run a query to see if the table exists first.

- I also run the following statements to add any new modified filters.  Notice this is all done in the same transaction.  Given the fact that there are no modified filters, nothing should actually get deleted, inserted, or updated, but perhaps this is causing strange behavior in Derby?  Currently there is no index on the MODIFIED column - perhaps there needs to be one?

Thanks!

David

2010-04-30 22:24:32.011 GMT Thread[Item Set Sender,5,main] (XID = 1037890), (SESSIONID = 65), (DATABASE = C:\Vontu\Protect\scan\incremental_index/MONITOR_TARGET_1), (DRDAID = null), Begin compiling prepared statement: DELETE FROM PENDING_DELIVERIES WHERE SERVER_ID = ? AND ITEMSET_ID IN (SELECT ITEMSET_ID FROM BLOOM_FILTERS WHERE MODIFIED = 1) AND FILTER_INDEX IN (SELECT FILTER_INDEX FROM BLOOM_FILTERS WHERE MODIFIED = 1) :End prepared statement
2010-04-30 22:24:32.011 GMT Thread[Item Set Sender,5,main] (XID = 1037890), (SESSIONID = 65), (DATABASE = C:\Vontu\Protect\scan\incremental_index/MONITOR_TARGET_1), (DRDAID = null), End compiling prepared statement: DELETE FROM PENDING_DELIVERIES WHERE SERVER_ID = ? AND ITEMSET_ID IN (SELECT ITEMSET_ID FROM BLOOM_FILTERS WHERE MODIFIED = 1) AND FILTER_INDEX IN (SELECT FILTER_INDEX FROM BLOOM_FILTERS WHERE MODIFIED = 1) :End prepared statement
2010-04-30 22:24:32.011 GMT Thread[Item Set Sender,5,main] (XID = 1037890), (SESSIONID = 65), (DATABASE = C:\Vontu\Protect\scan\incremental_index/MONITOR_TARGET_1), (DRDAID = null), Executing prepared statement: DELETE FROM PENDING_DELIVERIES WHERE SERVER_ID = ? AND ITEMSET_ID IN (SELECT ITEMSET_ID FROM BLOOM_FILTERS WHERE MODIFIED = 1) AND FILTER_INDEX IN (SELECT FILTER_INDEX FROM BLOOM_FILTERS WHERE MODIFIED = 1) :End prepared statement with 1 parameters begin parameter #1: -2147483648 :end parameter 
2010-04-30 22:24:32.011 GMT Thread[Item Set Sender,5,main] (XID = 1037890), (SESSIONID = 65), (DATABASE = C:\Vontu\Protect\scan\incremental_index/MONITOR_TARGET_1), (DRDAID = null), Begin compiling prepared statement: INSERT INTO PENDING_DELIVERIES (ITEMSET_ID, FILTER_INDEX, SERVER_ID, SENT) SELECT ITEMSET_ID, FILTER_INDEX, ?, 0 FROM BLOOM_FILTERS WHERE MODIFIED = 1 :End prepared statement
2010-04-30 22:24:32.011 GMT Thread[Item Set Sender,5,main] (XID = 1037890), (SESSIONID = 65), (DATABASE = C:\Vontu\Protect\scan\incremental_index/MONITOR_TARGET_1), (DRDAID = null), End compiling prepared statement: INSERT INTO PENDING_DELIVERIES (ITEMSET_ID, FILTER_INDEX, SERVER_ID, SENT) SELECT ITEMSET_ID, FILTER_INDEX, ?, 0 FROM BLOOM_FILTERS WHERE MODIFIED = 1 :End prepared statement
2010-04-30 22:24:32.026 GMT Thread[Item Set Sender,5,main] (XID = 1037890), (SESSIONID = 65), (DATABASE = C:\Vontu\Protect\scan\incremental_index/MONITOR_TARGET_1), (DRDAID = null), Executing prepared statement: INSERT INTO PENDING_DELIVERIES (ITEMSET_ID, FILTER_INDEX, SERVER_ID, SENT) SELECT ITEMSET_ID, FILTER_INDEX, ?, 0 FROM BLOOM_FILTERS WHERE MODIFIED = 1 :End prepared statement with 1 parameters begin parameter #1: -2147483648 :end parameter 
2010-04-30 22:24:32.026 GMT Thread[Item Set Sender,5,main] (XID = 1037890), (SESSIONID = 65), (DATABASE = C:\Vontu\Protect\scan\incremental_index/MONITOR_TARGET_1), (DRDAID = null), Begin compiling prepared statement: UPDATE BLOOM_FILTERS SET MODIFIED = 0 :End prepared statement
2010-04-30 22:24:32.026 GMT Thread[Item Set Sender,5,main] (XID = 1037890), (SESSIONID = 65), (DATABASE = C:\Vontu\Protect\scan\incremental_index/MONITOR_TARGET_1), (DRDAID = null), End compiling prepared statement: UPDATE BLOOM_FILTERS SET MODIFIED = 0 :End prepared statement
2010-04-30 22:24:32.026 GMT Thread[Item Set Sender,5,main] (XID = 1037890), (SESSIONID = 65), (DATABASE = C:\Vontu\Protect\scan\incremental_index/MONITOR_TARGET_1), (DRDAID = null), Executing prepared statement: UPDATE BLOOM_FILTERS SET MODIFIED = 0 :End prepared statement
2010-04-30 22:24:32.042 GMT Thread[Item Set Sender,5,main] (XID = 1037890), (SESSIONID = 65), (DATABASE = C:\Vontu\Protect\scan\incremental_index/MONITOR_TARGET_1), (DRDAID = null), Committing
2010-04-30 22:24:32.042 GMT Thread[Item Set Sender,5,main] (XID = 1037890), (SESSIONID = 65), (DATABASE = C:\Vontu\Protect\scan\incremental_index/MONITOR_TARGET_1), (DRDAID = null), Committing
2010-04-30 22:24:32.042 GMT Thread[Item Set Sender,5,main] (XID = 1037890), (SESSIONID = 65), (DATABASE = C:\Vontu\Protect\scan\incremental_index/MONITOR_TARGET_1), (DRDAID = null), Rolling back
2010-04-30 22:24:32.042 GMT Thread[Item Set Sender,5,main] (XID = 1037894), (SESSIONID = 66), (DATABASE = C:\Vontu\Protect\scan\incremental_index/MONITOR_TARGET_1), (DRDAID = null), Committing
2010-04-30 22:24:32.042 GMT Thread[Item Set Sender,5,main] (XID = 1037894), (SESSIONID = 66), (DATABASE = C:\Vontu\Protect\scan\incremental_index/MONITOR_TARGET_1), (DRDAID = null), Rolling back
2010-04-30 22:24:32.042 GMT Thread[Item Set Sender,5,main] (XID = 1037895), (SESSIONID = 67), (DATABASE = C:\Vontu\Protect\scan\incremental_index/MONITOR_TARGET_1), (DRDAID = null), Committing



On Fri, Apr 30, 2010 at 3:12 PM, Knut Anders Hatlen <Knut.Hatlen@sun.com> wrote:
On 04/30/10 23:38, David Van Couvering wrote:
> Hi, all.
>
> I have a thread that runs the following query every 10 seconds:
>
> SELECT d.ITEMSET_ID, f.FILTER_INDEX, FROM FILTERS f, PENDING_DELIVERIES
> d WHERE f.ITEMSET_ID = d.ITEMSET_ID AND f.FILTER_INDEX = d.FILTER_INDEX
> AND d.SENT = 0 AND d.SERVER_ID = 1
>
> The FILTERS table has about 200 rows in it, and the PENDING_DELIVERIES
> table has no rows.  So this query returns zero rows each time it is run.
>
> My database directory is growing by about *1 MB *! every 30 seconds.
>
> This is the *only* query running.  The number of rows in my tables never
> changes.

Hi David,

You may already have done so, but to verify that there is no other
thread adding data, you could set the derby.language.logStatementText
property and check derby.log to see which statements are executed.

--
Knut Anders






--
David W. Van Couvering

http://www.linkedin.com/in/davidvc
http://davidvancouvering.blogspot.com
http://twitter.com/dcouvering