From derby-user-return-13550-apmail-db-derby-user-archive=db.apache.org@db.apache.org Fri Apr 29 17:10:27 2011 Return-Path: X-Original-To: apmail-db-derby-user-archive@www.apache.org Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id B665D2BFA for ; Fri, 29 Apr 2011 17:10:27 +0000 (UTC) Received: (qmail 99545 invoked by uid 500); 29 Apr 2011 17:10:27 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 99516 invoked by uid 500); 29 Apr 2011 17:10:27 -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 99509 invoked by uid 99); 29 Apr 2011 17:10:27 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 29 Apr 2011 17:10:27 +0000 X-ASF-Spam-Status: No, hits=3.1 required=5.0 tests=HTML_MESSAGE,RCVD_NUMERIC_HELO,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of Matt.Pouttu-Clarke@icrossing.com designates 38.101.13.26 as permitted sender) Received: from [38.101.13.26] (HELO santana.ic.aiall) (38.101.13.26) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 29 Apr 2011 17:10:18 +0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4325 Received: from 38.101.13.2 ([38.101.13.2]) by santana.ic.aiall ([10.13.52.26]) via Exchange Front-End Server mail.icrossing.com ([10.13.52.24]) with Microsoft Exchange Server HTTP-DAV ; Fri, 29 Apr 2011 17:09:55 +0000 User-Agent: Microsoft-Entourage/12.29.0.110113 Date: Fri, 29 Apr 2011 10:09:52 -0700 Subject: Re: help with sql query for Derby From: Matt Pouttu-Clarke To: Derby Discussion Message-ID: Thread-Topic: help with sql query for Derby thread-index: AcwGkEDaOJ4JAQBTtEKvPMbMIfgu5A== In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="B_3386916593_2112163" X-Virus-Checked: Checked by ClamAV on apache.org This is a multi-part message in MIME format. --B_3386916593_2112163 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable Hi Luis, If you want to load the whole database into memory, you have to set the = page cache size and the =ADXmx java setting: java -Dderby.storage.pageCacheSize=3D524288 -Xmx1G Derby is polite and will not take up more cache memory than the default size, even if you increase the heap. So you have to tell Derby how much memory you want allocated. This can be very handy in an embedded = database application where you don=B9t want the DB to take up all available = memory. Also, please make sure to make the distinction between an embedded in-process cache versus something like memcached. The serialization and = IPC (inter-process communication) overhead makes out-of-process databases = much slower. This explains why an in-process Java memory cache out performs memcached: http://gregluck.com/blog/archives/2007/05/comparing-memcached-and-ehcache= -pe rformance/ And if part of your study is to compare multiple Java in-memory embedded databases, please note whether the database is concurrent rather than = merely =B3thread-safe=B2. A concurrent database allows queries and updates in = parallel from multiple threads, while =B3thread-safe=B2 typically means only one = thread at a time can access or modify the embedded database. Concurrency = makes a big difference for scalability because multiple threads share the same = cache of in-process memory, so the overall cache scales across multiple = threads. In a =B3thread-safe=B2 model each process must have it=B9s own cache, = which duplicates cache entries and limits scalability of the solution. I did a performance study of multiple Java embedded databases for use in = the Hadoop framework, and found that Derby offers superior multi-threaded performance: http://mpouttuclarke.wordpress.com/2010/12/10/java-embedded-db-for-ip2loc= ati on-in-hadoop/ Hadoop has a multi-threaded option, so concurrency within embedded Derby = was a major selection criteria. Anyway, enjoy your study, and I hope this helps! Cheers, Matt On 4/28/11 7:22 PM, "Luis Ribeiro" wrote: > Dears, > =A0=A0=A0 My name is Luis Ribeiro, I`m brazilian and I study Computer = Science at > Universidade Presbiteriana Mackenzie > (http://www.mackenzie.br/portal/principal.php). I and Henrique = Teixeira (cc) > are on the last semester and we`re doing our final project. To be = honest, > we`re finishing its. It`s about Comparison between HD databases and = In-Memory > databases. > =A0=A0=A0 We`re trying to use Apache Derby to know how it works with = the simple > systems that we developed. > =A0=A0=A0 We have two doubts and I would be greatfull if you could = answer to us: > =A0=A0=A0 - Does exist some function or method that you use similar to > "GROUP_CONCAT" (from MySQL)? I want to know if I can do this on a = simple way > with Apache Derby? >=20 > Original table > ID | NAME > 1=A0 | Luis > 2=A0 | Henrique > 3=A0 | Luis >=20 > After do the query > ID | NAME > 1, 3 | Luis > 2=A0 | Henrique >=20 > =A0=A0=A0 - What we need to do to have a exceptional performance with = In Memory > mode? I`d increased jvm heap, but it didn`t work. > =A0=20 > =A0=A0=A0 I`m sorry if I was offensive with this e-mail. >=20 > =A0=A0=A0 Thank you very much. >=20 > Best regards, > Luis Ribeiro >=20 iCrossing Privileged and Confidential Information This email message is for the sole use of the intended recipient(s) and = may contain confidential and privileged information of iCrossing. Any = unauthorized review, use, disclosure or distribution is prohibited. If = you are not the intended recipient, please contact the sender by reply = email and destroy all copies of the original message. --B_3386916593_2112163 Content-Type: text/html; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable Re: help with sql query for Derby
Hi Luis,

If you want to load the whole database into memory, you have to set the = page cache size and the –Xmx java setting:

java -Dderby.storage.pageCacheSize=3D524288 -Xmx1G

Derby is polite and will not take = up more cache memory than the default size, even if you increase the = heap.  So you have to tell Derby how much memory you want = allocated.  This can be very handy in an embedded database = application where you don’t want the DB to take up all available = memory.

Also, please make sure to make the distinction between an embedded = in-process cache versus something like memcached.  The = serialization and IPC (inter-process communication) overhead makes = out-of-process databases much slower.  This explains why an = in-process Java memory cache out performs memcached:
http://gregluck.com/blog/archives/2007/05/comparin= g-memcached-and-ehcache-performance/

And if part of your study is to compare multiple Java in-memory embedded = databases, please note whether the database is concurrent rather than = merely “thread-safe”.  A concurrent database allows = queries and updates in parallel from multiple threads, while = “thread-safe” typically means only one thread at a time can = access or modify the embedded database.   Concurrency makes a = big difference for scalability because multiple threads share the same = cache of in-process memory, so the overall cache scales across multiple = threads.  In a “thread-safe” model each process must = have it’s own cache, which duplicates cache entries and limits = scalability of the solution.

I did a performance study of multiple Java embedded databases for use in = the Hadoop framework, and found that Derby offers superior = multi-threaded performance:
http://mpouttuclarke.wordpress.com/2010/12/10/j= ava-embedded-db-for-ip2location-in-hadoop/

Hadoop has a multi-threaded option, so concurrency within embedded Derby = was a major selection criteria.

Anyway, enjoy your study, and I hope this helps!

Cheers,
Matt


On 4/28/11 7:22 PM, "Luis = Ribeiro" <luisribeiro1989@gmail.com> = wrote:

Dears,
=A0=A0=A0 My name is Luis Ribeiro, I`m brazilian and I study Computer = Science at Universidade Presbiteriana Mackenzie (http://www.mackenzi= e.br/portal/principal.php). I and Henrique Teixeira (cc) are on the = last semester and we`re doing our final project. To be honest, we`re = finishing its. It`s about Comparison between HD databases and In-Memory = databases.
=A0=A0=A0 We`re trying to use Apache Derby to know how it works with the = simple systems that we developed.
=A0=A0=A0 We have two doubts and I would be greatfull if you could = answer to us:
=A0=A0=A0 - Does exist some function or method that you use similar to = "GROUP_CONCAT" (from MySQL)? I want to know if I can do this = on a simple way with Apache Derby?

Original table
ID | NAME
1=A0 | Luis
2=A0 | Henrique
3=A0 | Luis

After do the query
ID | NAME
1, 3 | Luis
2=A0 | Henrique

=A0=A0=A0 - What we need to do to have a exceptional performance with In = Memory mode? I`d increased jvm heap, but it didn`t work.
=A0
=A0=A0=A0 I`m sorry if I was offensive with this e-mail.

=A0=A0=A0 Thank you very much.

Best regards,
Luis Ribeiro

iCrossing Privileged and = Confidential Information
This email message is for the sole use = of the intended recipient(s) and may contain confidential and privileged = information of iCrossing. Any unauthorized review, use, disclosure or = distribution is prohibited. If you are not the intended recipient, = please contact the sender by reply email and destroy all copies of the = original message.

 --B_3386916593_2112163--