Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 30911 invoked from network); 20 Apr 2009 14:38:19 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 20 Apr 2009 14:38:19 -0000 Received: (qmail 16045 invoked by uid 500); 20 Apr 2009 14:38:19 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 16010 invoked by uid 500); 20 Apr 2009 14:38:19 -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 16002 invoked by uid 99); 20 Apr 2009 14:38:19 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 20 Apr 2009 14:38:19 +0000 X-ASF-Spam-Status: No, hits=2.2 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [68.142.207.65] (HELO web31802.mail.mud.yahoo.com) (68.142.207.65) by apache.org (qpsmtpd/0.29) with SMTP; Mon, 20 Apr 2009 14:38:09 +0000 Received: (qmail 43355 invoked by uid 60001); 20 Apr 2009 14:37:47 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1240238267; bh=s2DaWyJfg72PVXdtP+YBOJOFOgIr06G1BbmrWGnagUU=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=0MFTwRAoaCD5cEN9ZwEn+Qa0pHM3Ubt6tj/7drh3ysYrXPFUtJvwGl9FXKrxNP6ecvw+Pn8/Tk7Zmm8vICp9SI+vZ0RAFnNRQ49EUE6IWnb1J5a5HOjGmKl+hta8smNa9pA1B/hHTi3VreLnlue1nwVoHuHo42KY5HX9oousoig= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=MBi2fOz3vFkS8UyChSMEnvuxFKg5jbmBU5FPev0t5oLBiLF0aoXR5K0EoIOoLCS29L3bXbn1qReIxdZpy/RG65GglYxvV8DOZm+E7wJ9kk0hFid0EOSiVS6WqwuPDJDtpTeP4Fl4VKUiv7rqEhRQUnOTXBl6F0OCr2hfNqRGUQs=; Message-ID: <408249.40495.qm@web31802.mail.mud.yahoo.com> X-YMail-OSG: LBuK3FkVM1ni2JEzCQP7ZjPsFaSCkWp7du6hDmWTzrSwGJzr3zRLtxJNaMhWVytBr1z2a_YZIRaifqDkjBmt6sgRNip3RQ64xdzVPvdQlvA7c0YJNyvk1OeK9rhcpLjNqs_XNfPSmsXVnV9rGN0Uq2KLgaoUqjisuwTmmg0FvFaZbdwQHE18y85sz8AhIso7lRenlVG6Vf4C8sAAL2seB9NeWofKF9CDPqIiiBzQxZ5bmSSdBoaybgo5k6SdfB6GnbBuT9RNF5GXWA.qAU3KGYVgMMizJpqb4ZfsZ06TmOmTwffTxBod0KleZC0kznLvaOgfAfc487G2BazkHLVw Received: from [64.81.244.91] by web31802.mail.mud.yahoo.com via HTTP; Mon, 20 Apr 2009 07:37:47 PDT X-Mailer: YahooMailRC/1277.38 YahooMailWebService/0.7.289.10 References: <882C3355DFF9D3468379DD1E8C115FC7270B827D13@EVS-RED.coloflorida.com> Date: Mon, 20 Apr 2009 07:37:47 -0700 (PDT) From: Geoff hendrey Subject: Re: Blobs & Delete speed To: Derby Discussion In-Reply-To: <882C3355DFF9D3468379DD1E8C115FC7270B827D13@EVS-RED.coloflorida.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-175154389-1240238267=:40495" X-Virus-Checked: Checked by ClamAV on apache.org --0-175154389-1240238267=:40495 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable what indexes do you have? I would definitely put an index on id. I have blo= b tables with large image files, and I'm measuring end-to-end delete (from = web browser application to server and back) at around 100 ms.=0A=0A -geoff= =0A=E2=80=9CXML? Too much like HTML. It'll never work on the Web!=E2=80=9D = =0A-anonymous =0A=0A=0A=0A=0A=0A________________________________=0AFrom: An= drew Lawrenson =0ATo: "derby-user@db.apache= .org" =0ASent: Monday, April 20, 2009 2:53:14 AM= =0ASubject: Blobs & Delete speed=0A=0A =0AHi,=0A =0AI=E2=80=99ve been exper= iencing a performance problem with=0Adeleting blobs in derby, and was wonde= ring if anyone could offer any advice.=0A =0AThis is primarily with 10.4.2.= 0 under windows and solaris, although=0AI=E2=80=99ve also tested with the n= ew 10.5.1.1 release candidate (as it has many=0Alob changes), but this make= s no significant difference.=0A =0AThe problem is that with a table contain= ing many large=0Ablobs, deleting a single row can take a long time (often o= ver a minute).=0A =0AI=E2=80=99ve reproduced this with a small test that cr= eates a=0Atable, inserts a few rows with blobs of differing sizes, then del= etes them.=0A =0AThe table schema is simple, just:=0A =0Acreate table blobt= est( id integer generated BY DEFAULT as=0Aidentity, b blob )=0A =0Aand I=E2= =80=99ve then created 7 rows with the following blob=0Asizes : 1024 bytes, = 1Mb, 10Mb, 25Mb, 50Mb, 75Mb, 100Mb.=0AI=E2=80=99ve read the blobs back, to = check they have been=0Acreated properly and are the correct size.=0A =0AThe= y have then been deleted using the sql statement ( =E2=80=9Cdelete=0Afrom b= lobtest where id =3D X=E2=80=9D ).=0A =0AIf I delete the rows in the order = I created them, average=0Atimings to delete a single row are:=0A =0A1024 by= tes: 19.5 seconds=0A1Mb: 16 seconds=0A10Mb: 18 seconds=0A25Mb: 15 seconds= =0A50Mb: 17 seconds=0A75Mb: 10 seconds=0A100Mb: 1.5 seconds=0A =0AIf I dele= te them in reverse order, the average timings to=0Adelete a single row are:= =0A100Mb: 20 seconds=0A75Mb: 10 seconds=0A50Mb: 4 seconds=0A25Mb: 0.3 secon= ds=0A10Mb: 0.25 seconds=0A1Mb: 0.02 seconds=0A1024 bytes: 0.005 seconds=0A = =0AIf I create seven small blobs, delete times are all=0Ainstantaneous.=0AI= t thus appears that the delete time seems to be related to=0Athe overall si= ze of the rows in the table more than the size of the blob being=0Aremoved.= =0AI=E2=80=99ve run the tests a few times, and the results seem reproducibl= e.=0A =0ASo, does anyone have any explanation for the performance,=0Aand an= y suggestions on how to work around it or fix it? It does make using=0Alar= ge blobs quite problematic in a production environment=E2=80=A6=0A =0AMany = thanks in advance,=0A =0A Andy --0-175154389-1240238267=:40495 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
what indexes do you have? I would definitely put an index on= id. I have blob tables with large image files, and I'm measuring end-to-en= d delete (from web browser application to server and back) at around 100 ms= .
 
-geoff
=E2=80= =9CXML? Too much = like HTML. It'll never work on the Web!=E2=80=9D
-anonymous


From: Andrew Lawrenson <andrew.lawrenson@copp= ereye.com>
To: "derby-user@db.apache.org" <= ;derby-user@db.apache.org>
Sent= : Monday, April 20, 2009 2:53:14 AM
Subject: Blobs & Delete speed

=0A= =0A=0A=0A =0A =0A= =0A=0A=0A=0A
=0A=0A

Hi,

= =0A=0A

 

=0A=0A

I=E2= =80=99ve been experiencing a performance problem with=0Adeleting blobs in d= erby, and was wondering if anyone could offer any advice.

=0A=0A

 

=0A=0A

This is primarily = with 10.4.2.0 under windows and solaris, although=0AI=E2=80=99ve also teste= d with the new 10.5.1.1 release candidate (as it has many=0Alob changes), b= ut this makes no significant difference.

=0A=0A

=  

=0A=0A

The problem is that with a table co= ntaining many large=0Ablobs, deleting a single row can take a long time (of= ten over a minute).

=0A=0A

 

=0A=0A

I=E2=80=99ve reproduced this with a small test that crea= tes a=0Atable, inserts a few rows with blobs of differing sizes, then delet= es them.

=0A=0A

 

=0A=0A

The table schema is simple, just:

=0A=0A

=  

=0A=0A

create table blobtest( id integer = generated BY DEFAULT as=0Aidentity, b blob )

=0A=0A

 

=0A=0A

and I=E2=80=99ve then created 7= rows with the following blob=0Asizes : 1024 bytes, 1Mb, 10Mb, 25Mb, 50Mb, = 75Mb, 100Mb.

=0A=0A

I=E2=80=99ve read the blobs b= ack, to check they have been=0Acreated properly and are the correct size. =0A=0A

 

=0A=0A

Th= ey have then been deleted using the sql statement ( =E2=80=9Cdelete=0Afrom = blobtest where id =3D X=E2=80=9D ).

=0A=0A

 = ;

=0A=0A

If I delete the rows in the order I crea= ted them, average=0Atimings to delete a single row are:

=0A=0A

 

=0A=0A

1024 bytes: 19.5 se= conds

=0A=0A

1Mb: 16 seconds

=0A=0A

10Mb: 18 seconds

=0A=0A

25Mb: 15 s= econds

=0A=0A

50Mb: 17 seconds

=0A=0A

75Mb: 10 seconds

=0A=0A

100Mb: 1.5= seconds

=0A=0A

 

=0A=0A

If I delete them in reverse order, the average timings to=0Adelete = a single row are:

=0A=0A

100Mb: 20 seconds

= =0A=0A

75Mb: 10 seconds

=0A=0A

50Mb: 4 seconds

=0A=0A

25Mb: 0.3 seconds

= =0A=0A

10Mb: 0.25 seconds

=0A=0A

1Mb: 0.02 seconds

=0A=0A

1024 bytes: 0.005 = seconds

=0A=0A

 

=0A=0A

If I create seven small blobs, delete times are all=0Ainstantaneous.=

=0A=0A

It thus appears that the delete time seem= s to be related to=0Athe overall size of the rows in the table more than th= e size of the blob being=0Aremoved.

=0A=0A

I=E2= =80=99ve run the tests a few times, and the results seem reproducible.

= =0A=0A

 

=0A=0A

So, d= oes anyone have any explanation for the performance,=0Aand any suggestions = on how to work around it or fix it?  It does make using=0Alarge blobs = quite problematic in a production environment=E2=80=A6

=0A=0A

 

=0A=0A

Many thanks in adva= nce,

=0A=0A

 

=0A=0A

   Andy

=0A=0A

 

=0A=0A=0A=0A
--0-175154389-1240238267=:40495--