From derby-user-return-9579-apmail-db-derby-user-archive=db.apache.org@db.apache.org Fri Jul 18 13:27:49 2008
Return-Path:
Delivered-To: apmail-db-derby-user-archive@www.apache.org
Received: (qmail 50530 invoked from network); 18 Jul 2008 13:27:49 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
by minotaur.apache.org with SMTP; 18 Jul 2008 13:27:49 -0000
Received: (qmail 1574 invoked by uid 500); 18 Jul 2008 13:27:47 -0000
Delivered-To: apmail-db-derby-user-archive@db.apache.org
Received: (qmail 1548 invoked by uid 500); 18 Jul 2008 13:27:47 -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 1537 invoked by uid 99); 18 Jul 2008 13:27:47 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 18 Jul 2008 06:27:47 -0700
X-ASF-Spam-Status: No, hits=-1.0 required=10.0
tests=RCVD_IN_DNSWL_LOW,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (athena.apache.org: local policy)
Received: from [192.18.6.24] (HELO gmp-eb-inf-2.sun.com) (192.18.6.24)
by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 18 Jul 2008 13:26:52 +0000
Received: from fe-emea-10.sun.com (gmp-eb-lb-2-fe1.eu.sun.com [192.18.6.10])
by gmp-eb-inf-2.sun.com (8.13.7+Sun/8.12.9) with ESMTP id m6IDRCrM001154
for ; Fri, 18 Jul 2008 13:27:12 GMT
Received: from conversion-daemon.fe-emea-10.sun.com by fe-emea-10.sun.com
(Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007))
id <0K4700201ENGUJ00@fe-emea-10.sun.com>
(original mail from Kristian.Waagan@Sun.COM) for derby-user@db.apache.org;
Fri, 18 Jul 2008 14:27:12 +0100 (BST)
Received: from [129.159.112.237] by fe-emea-10.sun.com
(Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007))
with ESMTPSA id <0K47003JIEP1V1H0@fe-emea-10.sun.com> for
derby-user@db.apache.org; Fri, 18 Jul 2008 14:27:01 +0100 (BST)
Date: Fri, 18 Jul 2008 15:27:00 +0200
From: Kristian Waagan
Subject: Re: Insert/Update Performance
In-reply-to: <18528545.post@talk.nabble.com>
Sender: Kristian.Waagan@Sun.COM
To: Derby Discussion
Message-id: <48809A24.10300@Sun.com>
Organization: Sun Microsystems Inc.
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <18528545.post@talk.nabble.com>
User-Agent: Mozilla/5.0 (X11; U; SunOS i86pc; no-NO; rv:1.8.1.14)
Gecko/20080602 Thunderbird/2.0.0.14 Mnenhy/0.7.5.0
X-Virus-Checked: Checked by ClamAV on apache.org
Paul Henry wrote:
> Hi All,
>
> We have been using an embedded derby data base within our application for
> some time now. However Recently we have added a new customer that has a
> significantly larger amount of data within the application than existing
> customers. The customer has been complaining about performance. We have
> tracked the issue to our persistance layer.
>
> We are using Hibernate layered over Derby. The database is relatively
> simple, just six or seven tables, and we have added indexes where ever
> required. So the Query speed doesnt seem to be a problem. However when
> saving a new item into the database (An item will create 1 or 2 entries in
> most of the tables and around 30 entries in 2 of the tables) we have been
> able to measure a decay in the insert time as the db grows in size.
>
> 1) With an empty db we had measured delays of 31-140 ms to write a new item
> into the DB
>
> 2) When the DB contained 1 million items (so a couple of the tables have ~30
> million rows) we measured delays of 1000-1500 ms to store a new item
>
> 3) When the DB contained 2 million items we measured delays of 1600-2000 ms
> to store a new item
>
> Similar delays were recorded when performing an update of existing data.
>
> Has anyone had any experience tuning the combination of hibernate over Derby
> for databases with a similar table size, or does anyone have any advice how
> to improve performance, whilst retaining both hibernate and derby?
Hello Paul,
A few questions first to better understand your environment.
1) What's your Derby version? JVM? Platform?
2) Is this using a single connection to the database?
3) Is auto commit enabled (default) or disabled?
4) Are you sure indexes are being used when you insert/update data?
5) Can you try to compress the tables to make sure the statistics are up
to date and see if that helps on a database where the insert/update
performance is poor?
6) Since you are describing this as a performance problem over time,
with growing database size, I don't think disk IO itself is the source
problem but can you describe what kind of IO activity you see when
inserting/updating data?
You might also want to post a bit of derby.log with statement logging
enabled, if the table structure and/or data can be made public.
regards,
--
Kristian