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 < [email protected]> 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 > <[email protected]>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 > -- David W. Van Couvering http://www.linkedin.com/in/davidvc http://davidvancouvering.blogspot.com http://twitter.com/dcouvering
