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 <Kristian.Waagan@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