You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by David Van Couvering <da...@vancouvering.com> on 2010/04/30 23:38:15 UTC
Very bad disk space leak in Derby
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.
Inspecting further, the number of dat files under seg0 is continually
growing. This never stops - a QA person has seen their database directory
grow from 20MB to 5GB!
I tried compressing the table - no change.
I upgraded from 10.4 to 10.5, and the database initially was back to a few
MB, but then over time it started growing again.
This is *very* disconcerting - basically it makes Derby unusable in
production. It is not an option for me to stop running this query.
I am hoping you have some suggestions, I am a bit at a loss.
Thanks!
David
--
David W. Van Couvering
http://www.linkedin.com/in/davidvc
http://davidvancouvering.blogspot.com
http://twitter.com/dcouvering
Re: Very bad disk space leak in Derby
Posted by David Van Couvering <da...@gmail.com>.
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 <Kn...@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
>
--
David W. Van Couvering
http://www.linkedin.com/in/davidvc
http://davidvancouvering.blogspot.com
http://twitter.com/dcouvering
Re: Very bad disk space leak in Derby
Posted by David Van Couvering <da...@gmail.com>.
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 <Kn...@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
Re: Very bad disk space leak in Derby
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
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
Re: Very bad disk space leak in Derby
Posted by David Van Couvering <da...@gmail.com>.
That did it! When I added code to check to see if the table exists (using
DatabaseMetaData) before issuing the create table, the problem went away.
It appears that even though I get the exception saying the table doesn't
exist, Derby still creates "hidden" dat files that don't seem to get
removed.
Just goes to show me that I should not break my own rule to not depend on
exceptions to control non-exceptional behavior in my system. :)
David
On Fri, Apr 30, 2010 at 3:40 PM, David Van Couvering <
david.vancouvering@gmail.com> wrote:
> Yes, definitely, the number of dat files is growing.
>
> As I mentioned to Knut, I try to issue a CREATE TABLE each time I connect,
> and ignore the exception saying it already exists if the table is already
> there.
>
> Perhaps when I no longer issue the query, I also no longer open a
> connection, and therefore also no longer issue these spurious create table
> statements.
>
> I'll look into that, thanks, very good tip.
>
> Fun chatting with you guys! :)
>
> David
>
>
> On Fri, Apr 30, 2010 at 3:30 PM, Kristian Waagan <Kr...@sun.com>wrote:
>
>> On 30.04.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.
>>>
>>> Inspecting further, the number of dat files under seg0 is continually
>>> growing. This never stops - a QA person has seen their database
>>> directory grow from 20MB to 5GB!
>>>
>>
>> Hi David,
>>
>> Can you confirm that the number of dat files in the seg0-directory are
>> growing?
>> If that's the case, are you sure you don't have a query adding new tables
>> and/or indexes to the database?
>> Do you see many tables if you fire up ij and issue 'show tables'? What
>> about 'show indexes'?
>> (I think the values for an empty database are 22 and 72)
>>
>>
>> Regards,
>> --
>> Kristian
>>
>>
>>
>>> I tried compressing the table - no change.
>>>
>>> I upgraded from 10.4 to 10.5, and the database initially was back to a
>>> few MB, but then over time it started growing again.
>>>
>>> This is *very* disconcerting - basically it makes Derby unusable in
>>> production. It is not an option for me to stop running this query.
>>>
>>> I am hoping you have some suggestions, I am a bit at a loss.
>>>
>>> Thanks!
>>>
>>> David
>>>
>>>
>>>
>>> --
>>> 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
>
--
David W. Van Couvering
http://www.linkedin.com/in/davidvc
http://davidvancouvering.blogspot.com
http://twitter.com/dcouvering
Re: Very bad disk space leak in Derby
Posted by Kathey Marsden <km...@sbcglobal.net>.
On 4/30/2010 3:40 PM, David Van Couvering wrote:
>
> Fun chatting with you guys! :)
>
Wonderful to see you back in the Derby community. Hope to see you back
on the dev list soon. :-)
Re: Very bad disk space leak in Derby
Posted by David Van Couvering <da...@gmail.com>.
BTW, given that I as a user can check to see if a table already exists,
maybe the CREATE TABLE code could do the same, rather than rely on a unique
key violation?
On Mon, May 3, 2010 at 2:25 PM, Mike Matrigali <mi...@sbcglobal.net>wrote:
> what are the names of the new files after the transaction that
> tried to create the existing table them commits? If they are still c*.dat
> then there
> is definitely something wrong. If they start with a different letter
> then it could be that derby is acting as expected and just did not get
> around to cleaning them
> yet. I believe it does this at checkpoint time. The assumption being
> that normally this case is just drop table and not needed to be optimized.
>
> I have not looked at the code but my guess is that create table is
> counting on a unique key violation to tell whether a table exists
> or not. To do this it has to do an insert and to do the insert it
> needs the name of the file which only exists after the file is
> created.
>
> David Van Couvering wrote:
>
>> Yes, it does sound like a bug. I'll log a JIRA
>>
>> On Fri, Apr 30, 2010 at 6:50 PM, Bryan Pendleton <
>> bpendleton.derby@gmail.com <ma...@gmail.com>> wrote:
>>
>> As I mentioned to Knut, I try to issue a CREATE TABLE each time
>> I connect, and ignore the exception saying it already exists if
>> the table is already there.
>>
>> If this is leaking a conglomerate each time (creating a .dat file but
>> never deleting it), that seems like a bug to me.
>>
>> thanks,
>>
>> bryan
>>
>>
>>
>>
>> --
>> 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
Re: Very bad disk space leak in Derby
Posted by David Van Couvering <da...@gmail.com>.
I am sorry to say I didn't save the database directory and have since worked
around the problem by checking to see if the table exists before creating
it, so I don't know the names of the files. If it's important, I'll comment
out my changes for now and recreate the problem and tell you what I'd see.
However, it definitely never got cleaned up. Our QA person had his system
up for a few days and it went from a few MB to 5GB and never went back down.
Shutting down and rebooting the database also had no effect. The only time
I ever saw it shrink back down was when I upgraded from 10.4 to 10.5
David
On Mon, May 3, 2010 at 2:25 PM, Mike Matrigali <mi...@sbcglobal.net>wrote:
> what are the names of the new files after the transaction that
> tried to create the existing table them commits? If they are still c*.dat
> then there
> is definitely something wrong. If they start with a different letter
> then it could be that derby is acting as expected and just did not get
> around to cleaning them
> yet. I believe it does this at checkpoint time. The assumption being
> that normally this case is just drop table and not needed to be optimized.
>
> I have not looked at the code but my guess is that create table is
> counting on a unique key violation to tell whether a table exists
> or not. To do this it has to do an insert and to do the insert it
> needs the name of the file which only exists after the file is
> created.
>
> David Van Couvering wrote:
>
>> Yes, it does sound like a bug. I'll log a JIRA
>>
>> On Fri, Apr 30, 2010 at 6:50 PM, Bryan Pendleton <
>> bpendleton.derby@gmail.com <ma...@gmail.com>> wrote:
>>
>> As I mentioned to Knut, I try to issue a CREATE TABLE each time
>> I connect, and ignore the exception saying it already exists if
>> the table is already there.
>>
>> If this is leaking a conglomerate each time (creating a .dat file but
>> never deleting it), that seems like a bug to me.
>>
>> thanks,
>>
>> bryan
>>
>>
>>
>>
>> --
>> 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
Re: Very bad disk space leak in Derby
Posted by Mike Matrigali <mi...@sbcglobal.net>.
what are the names of the new files after the transaction that
tried to create the existing table them commits? If they are still
c*.dat then there
is definitely something wrong. If they start with a different letter
then it could be that derby is acting as expected and just did not get
around to cleaning them
yet. I believe it does this at checkpoint time. The assumption being
that normally this case is just drop table and not needed to be optimized.
I have not looked at the code but my guess is that create table is
counting on a unique key violation to tell whether a table exists
or not. To do this it has to do an insert and to do the insert it
needs the name of the file which only exists after the file is
created.
David Van Couvering wrote:
> Yes, it does sound like a bug. I'll log a JIRA
>
> On Fri, Apr 30, 2010 at 6:50 PM, Bryan Pendleton
> <bpendleton.derby@gmail.com <ma...@gmail.com>> wrote:
>
> As I mentioned to Knut, I try to issue a CREATE TABLE each time
> I connect, and ignore the exception saying it already exists if
> the table is already there.
>
>
> If this is leaking a conglomerate each time (creating a .dat file but
> never deleting it), that seems like a bug to me.
>
> thanks,
>
> bryan
>
>
>
>
> --
> David W. Van Couvering
>
> http://www.linkedin.com/in/davidvc
> http://davidvancouvering.blogspot.com
> http://twitter.com/dcouvering
Re: Very bad disk space leak in Derby
Posted by David Van Couvering <da...@gmail.com>.
Yes, it does sound like a bug. I'll log a JIRA
On Fri, Apr 30, 2010 at 6:50 PM, Bryan Pendleton <bpendleton.derby@gmail.com
> wrote:
> As I mentioned to Knut, I try to issue a CREATE TABLE each time I connect,
>> and ignore the exception saying it already exists if the table is already
>> there.
>>
>
> If this is leaking a conglomerate each time (creating a .dat file but
> never deleting it), that seems like a bug to me.
>
> thanks,
>
> bryan
>
--
David W. Van Couvering
http://www.linkedin.com/in/davidvc
http://davidvancouvering.blogspot.com
http://twitter.com/dcouvering
Re: Very bad disk space leak in Derby
Posted by Bryan Pendleton <bp...@gmail.com>.
> As I mentioned to Knut, I try to issue a CREATE TABLE each time I
> connect, and ignore the exception saying it already exists if the table
> is already there.
If this is leaking a conglomerate each time (creating a .dat file but
never deleting it), that seems like a bug to me.
thanks,
bryan
Re: Very bad disk space leak in Derby
Posted by David Van Couvering <da...@gmail.com>.
Yes, definitely, the number of dat files is growing.
As I mentioned to Knut, I try to issue a CREATE TABLE each time I connect,
and ignore the exception saying it already exists if the table is already
there.
Perhaps when I no longer issue the query, I also no longer open a
connection, and therefore also no longer issue these spurious create table
statements.
I'll look into that, thanks, very good tip.
Fun chatting with you guys! :)
David
On Fri, Apr 30, 2010 at 3:30 PM, Kristian Waagan <Kr...@sun.com>wrote:
> On 30.04.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.
>>
>> Inspecting further, the number of dat files under seg0 is continually
>> growing. This never stops - a QA person has seen their database
>> directory grow from 20MB to 5GB!
>>
>
> Hi David,
>
> Can you confirm that the number of dat files in the seg0-directory are
> growing?
> If that's the case, are you sure you don't have a query adding new tables
> and/or indexes to the database?
> Do you see many tables if you fire up ij and issue 'show tables'? What
> about 'show indexes'?
> (I think the values for an empty database are 22 and 72)
>
>
> Regards,
> --
> Kristian
>
>
>
>> I tried compressing the table - no change.
>>
>> I upgraded from 10.4 to 10.5, and the database initially was back to a
>> few MB, but then over time it started growing again.
>>
>> This is *very* disconcerting - basically it makes Derby unusable in
>> production. It is not an option for me to stop running this query.
>>
>> I am hoping you have some suggestions, I am a bit at a loss.
>>
>> Thanks!
>>
>> David
>>
>>
>>
>> --
>> 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
Re: Very bad disk space leak in Derby
Posted by Kristian Waagan <Kr...@Sun.COM>.
On 30.04.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.
>
> Inspecting further, the number of dat files under seg0 is continually
> growing. This never stops - a QA person has seen their database
> directory grow from 20MB to 5GB!
Hi David,
Can you confirm that the number of dat files in the seg0-directory are
growing?
If that's the case, are you sure you don't have a query adding new
tables and/or indexes to the database?
Do you see many tables if you fire up ij and issue 'show tables'? What
about 'show indexes'?
(I think the values for an empty database are 22 and 72)
Regards,
--
Kristian
>
> I tried compressing the table - no change.
>
> I upgraded from 10.4 to 10.5, and the database initially was back to a
> few MB, but then over time it started growing again.
>
> This is *very* disconcerting - basically it makes Derby unusable in
> production. It is not an option for me to stop running this query.
>
> I am hoping you have some suggestions, I am a bit at a loss.
>
> Thanks!
>
> David
>
>
>
> --
> David W. Van Couvering
>
> http://www.linkedin.com/in/davidvc
> http://davidvancouvering.blogspot.com
> http://twitter.com/dcouvering