Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 64996 invoked from network); 30 Apr 2010 22:54:14 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 30 Apr 2010 22:54:14 -0000 Received: (qmail 41003 invoked by uid 500); 30 Apr 2010 22:54:13 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 40971 invoked by uid 500); 30 Apr 2010 22:54:13 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 40964 invoked by uid 99); 30 Apr 2010 22:54:13 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 30 Apr 2010 22:54:13 +0000 X-ASF-Spam-Status: No, hits=1.3 required=10.0 tests=AWL,FREEMAIL_FROM,HTML_MESSAGE,RCVD_IN_DNSWL_NONE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of david.vancouvering@gmail.com designates 209.85.211.191 as permitted sender) Received: from [209.85.211.191] (HELO mail-yw0-f191.google.com) (209.85.211.191) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 30 Apr 2010 22:54:09 +0000 Received: by ywh29 with SMTP id 29so697000ywh.14 for ; Fri, 30 Apr 2010 15:53:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:mime-version:received:in-reply-to :references:from:date:message-id:subject:to:content-type; bh=pZ+QTKw/5cSO+wuV+uIcl6qU8mqQ+656OP934vVLkGI=; b=TIbW0wseS0ogpwFIv9VlkzoB0tVJYvtnPTXeLLg+n5S15910l18x+Fij9ZrvVMUBzX rrZeeN7tw5RbEXtkR6F803jD8P9UHs7HKdgmmmvwUfpje0chSw10ylCvT0ug6Gy3xpnt u595Jp6noKKS1RY0GyGmTUcw+ax8ZAvTo//L0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :content-type; b=cXdayx0rh1H078JA0EaurBZjeh5v0BsGkfGlKLbc7fMaCYT8BEpMIdhixng1XldIdh ft6I3frbOf0i5WHklCGQrvyCezWFtwAvY85sQMMAi2Vc5a/sTR0EnfTPzcwOaqSNM8Ha 1yx420pzeIwZCRfMSAVTgEkT9Ac8bB4uo7dOI= Received: by 10.91.20.14 with SMTP id x14mr763064agi.30.1272668028312; Fri, 30 Apr 2010 15:53:48 -0700 (PDT) MIME-Version: 1.0 Received: by 10.90.95.13 with HTTP; Fri, 30 Apr 2010 15:53:28 -0700 (PDT) In-Reply-To: References: <4BDB5A0C.9040306@Sun.COM> From: David Van Couvering Date: Fri, 30 Apr 2010 15:53:28 -0700 Message-ID: Subject: Re: Very bad disk space leak in Derby To: Derby Discussion Content-Type: multipart/alternative; boundary=001485f78e8ebe9c1504857c1d3e --001485f78e8ebe9c1504857c1d3e Content-Type: text/plain; charset=ISO-8859-1 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 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 --001485f78e8ebe9c1504857c1d3e Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable That did it! =A0When I added code to check to see if the table exists (usin= g 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 fil= es 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.co= m> 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 connec= t, and ignore the exception saying it already exists if the table is alread= y there. =A0

Perhaps when I no longer issue the query, I also no longer o= pen 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 Wa= agan <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 =3D d.ITEMSET_ID AND f.FILTER_INDEX =3D d.FILTER_INDEX=
AND d.SENT =3D 0 AND d.SERVER_ID =3D 1

The FILTERS table has about 200 rows in it, and the PENDING_DELIVERIES
table has no rows. =A0So this query returns zero rows each time it is run.<= br>
My database directory is growing by about *1 MB *! every 30 seconds.

This is the *only* query running. =A0The number of rows in my tables never<= br> changes.

Inspecting further, the number of dat files under seg0 is continually
growing. =A0This 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 grow= ing?
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'? W= hat 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. =A0It 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<= br>
http://www.linkedin.c= om/in/davidvc
http= ://davidvancouvering.blogspot.com
http://twitter.com/dcouvering=
--001485f78e8ebe9c1504857c1d3e--