using H2 database for accelerated development

[Gabriel Oberreuter]

Hi, I would like to share with guys new to Ofbiz that you can gain lot of DB related speed using the H2 database rather that the default Derby one.

When building the project and creating the databases, on one of our engineers laptop the whole process took about ~15 minutes with Derby, and switching to H2 reduced that time to about ~3 minutes. That is a lot of time. On my workstation the time went from 3 minutes to ~2. (SSD and more powerful CPU).

This has proven very helpful specially since we are just starting using ofbiz and need to create the db lots of times.

Just for your info.

[Adrian Crum-3]

Before switching databases, you could try tweaking the entity engine
settings and the log settings.

Here is a diff of changes that work well for me:

Index: framework/base/config/log4j2.xml
===================================================================
--- framework/base/config/log4j2.xml (revision 1704346)
+++ framework/base/config/log4j2.xml (working copy)
@@ -40,6 +40,7 @@
          </Async>
      </Appenders>
      <Loggers>
+        <logger name="org.ofbiz.entity.jdbc.DatabaseUtil" level="warn"/>
          <logger name="org.ofbiz.base.converter.Converters" level="warn"/>
          <logger name="org.apache" level="warn"/>
          <logger name="freemarker" level="warn"/>
Index: framework/entity/config/entityengine.xml
===================================================================
--- framework/entity/config/entityengine.xml (revision 1704346)
+++ framework/entity/config/entityengine.xml (working copy)
@@ -165,6 +165,7 @@
              use-indices-unique="false"
              alias-view-columns="false"
              use-order-by-nulls="true"
+            max-worker-pool-size="-1"
              offset-style="fetch">
          <read-data reader-name="tenant"/>
          <read-data reader-name="seed"/>
@@ -220,6 +221,7 @@
              use-pk-constraint-names="false"
              use-indices-unique="false"
              alias-view-columns="false"
+            max-worker-pool-size="1"
              use-order-by-nulls="true">
          <!-- beware use-indices-unique="false" is needed because of
Derby bug with null values in a unique index -->
          <read-data reader-name="tenant"/>
@@ -249,6 +251,7 @@
          use-pk-constraint-names="false"
          use-indices-unique="false"
          alias-view-columns="false"
+        max-worker-pool-size="1"
          use-order-by-nulls="true">
          <read-data reader-name="tenant"/>
          <read-data reader-name="seed"/>

Creating the database and loading demo data takes about a minute.
Building the whole project from scratch takes about 2 minutes.

Having a SSD is a must for laptops.

Adrian Crum
Sandglass Software
www.sandglass-software.com

On 9/21/2015 1:42 PM, Gabriel Oberreuter wrote:

> Hi, I would like to share with guys new to Ofbiz that you can gain lot of DB
> related speed using the H2 database rather that the default Derby one.
>
> When building the project and creating the databases, on one of our
> engineers laptop the whole process took about ~15 minutes with Derby, and
> switching to H2 reduced that time to about ~3 minutes. That is a lot of
> time. On my workstation the time went from 3 minutes to ~2. (SSD and more
> powerful CPU).
>
> This has proven very helpful specially since we are just starting using
> ofbiz and need to create the db lots of times.
>
> Just for your info.
>
>
>
> --
> View this message in context: http://ofbiz.135035.n4.nabble.com/using-H2-database-for-accelerated-development-tp4672568.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>

[Gabriel Oberreuter]

Thanks Adrian,

We will try your settings.
Gabriel

[Ron Wheeler]

If you don't have an SSD but have some spare ram, you might put your DB
on a ramdrive if it is not too large.

I have not tried this but run Eclipse and the Java compiler off a
ramdrive and that helps a lot - faster than an SSD but I only have a 2GB
RAMDrive so it is not a huge place to play.

Ron

On 21/09/2015 5:26 PM, Adrian Crum wrote:

> Before switching databases, you could try tweaking the entity engine
> settings and the log settings.
>
> Here is a diff of changes that work well for me:
>
> Index: framework/base/config/log4j2.xml
> ===================================================================
> --- framework/base/config/log4j2.xml    (revision 1704346)
> +++ framework/base/config/log4j2.xml    (working copy)
> @@ -40,6 +40,7 @@
>          </Async>
>      </Appenders>
>      <Loggers>
> +        <logger name="org.ofbiz.entity.jdbc.DatabaseUtil" level="warn"/>
>          <logger name="org.ofbiz.base.converter.Converters"
> level="warn"/>
>          <logger name="org.apache" level="warn"/>
>          <logger name="freemarker" level="warn"/>
> Index: framework/entity/config/entityengine.xml
> ===================================================================
> --- framework/entity/config/entityengine.xml    (revision 1704346)
> +++ framework/entity/config/entityengine.xml    (working copy)
> @@ -165,6 +165,7 @@
>              use-indices-unique="false"
>              alias-view-columns="false"
>              use-order-by-nulls="true"
> +            max-worker-pool-size="-1"
>              offset-style="fetch">
>          <read-data reader-name="tenant"/>
>          <read-data reader-name="seed"/>
> @@ -220,6 +221,7 @@
>              use-pk-constraint-names="false"
>              use-indices-unique="false"
>              alias-view-columns="false"
> +            max-worker-pool-size="1"
>              use-order-by-nulls="true">
>          <!-- beware use-indices-unique="false" is needed because of
> Derby bug with null values in a unique index -->
>          <read-data reader-name="tenant"/>
> @@ -249,6 +251,7 @@
>          use-pk-constraint-names="false"
>          use-indices-unique="false"
>          alias-view-columns="false"
> +        max-worker-pool-size="1"
>          use-order-by-nulls="true">
>          <read-data reader-name="tenant"/>
>          <read-data reader-name="seed"/>
>
> Creating the database and loading demo data takes about a minute.
> Building the whole project from scratch takes about 2 minutes.
>
> Having a SSD is a must for laptops.
>
> Adrian Crum
> Sandglass Software
> www.sandglass-software.com
>
> On 9/21/2015 1:42 PM, Gabriel Oberreuter wrote:
>> Hi, I would like to share with guys new to Ofbiz that you can gain
>> lot of DB
>> related speed using the H2 database rather that the default Derby one.
>>
>> When building the project and creating the databases, on one of our
>> engineers laptop the whole process took about ~15 minutes with Derby,
>> and
>> switching to H2 reduced that time to about ~3 minutes. That is a lot of
>> time. On my workstation the time went from 3 minutes to ~2. (SSD and
>> more
>> powerful CPU).
>>
>> This has proven very helpful specially since we are just starting using
>> ofbiz and need to create the db lots of times.
>>
>> Just for your info.
>>
>>
>>
>> --
>> View this message in context:
>> http://ofbiz.135035.n4.nabble.com/using-H2-database-for-accelerated-development-tp4672568.html
>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>>
>

--
Ron Wheeler
President
Artifact Software Inc
email: [hidden email]
skype: ronaldmwheeler
phone: [phone], ext 102

[Gabriel Oberreuter]

Yes, putting everything on RAM would help too. But the point here is that using the same settings and platform, H2 is faster than Derby.

[Gabriel Oberreuter]

Ron, which tool are you using for the ram driver? Are you on linux or windows? I am on Windows.
I want to give it a try, thanks!

[Peter Arnold]

Hi,

I was just Reading about RAM-Drives since this concept was new to me. I
think there are some important facts to be considered with that solution.

Here is the article:
http://www.howtogeek.com/171432/ram-disks-explained-what-they-are-and-why-yo
u-probably-shouldnt-use-one/


Regards,

Peter


---
El software de antivirus Avast ha analizado este correo electrónico en busca de virus.
http://www.avast.com

[Gabriel Oberreuter]

Thanks for the link Peter. Yes, anyone using RAMDisks should know for which cases it is a good idea for which ones it isn't.

I remember seeing a few years back a product which was Ram bundled inside a 5.25" bay (like a cd-rom), using sata v1. But having to take considerations for it, I think it is far easier to just mount a ram disk without that accesory.

[Adrian Crum-3]

I just spent some time playing with H2, and I see a major problem - its
JDBC driver supports only JDBC version 4.0. So, most of OFBiz will work
okay, but there may be some exceptions thrown when using data types from
JREs newer than 1.6.

Adrian Crum
Sandglass Software
www.sandglass-software.com

On 9/21/2015 1:42 PM, Gabriel Oberreuter wrote:

> Hi, I would like to share with guys new to Ofbiz that you can gain lot of DB
> related speed using the H2 database rather that the default Derby one.
>
> When building the project and creating the databases, on one of our
> engineers laptop the whole process took about ~15 minutes with Derby, and
> switching to H2 reduced that time to about ~3 minutes. That is a lot of
> time. On my workstation the time went from 3 minutes to ~2. (SSD and more
> powerful CPU).
>
> This has proven very helpful specially since we are just starting using
> ofbiz and need to create the db lots of times.
>
> Just for your info.
>
>
>
> --
> View this message in context: http://ofbiz.135035.n4.nabble.com/using-H2-database-for-accelerated-development-tp4672568.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>

[Ron Wheeler]

Windows 7
Dataram RAMDisk v4.1
http://www.dataram.com
I am using the free version. I have a 8GB Toshiba laptop and have set
aside 2 GB for Eclipse/STS, Java 8 JDK, Maven, DITA-OT and a build
directory for DITA.
Windows seems to be able to open a lot of windows with lots of process
even with 6 GB.

The DITA-OT is for documentation and it thrashes the disk since it has
many phases that each read one set of small files (~120) and writes them
back out.
Getting them in memory makes a big difference when I am actively writing
or maintaining docs.

Eclipse/STS has a lot of plug-ins and putting this in RAM has made
Eclipse quite snappy which it was not when it was sitting on the
laptop's hard disk.

I don't put anything on the RAMDrive that I need to be sure that it will
be preserved over a crash. Windows 7 is pretty stable but not perfect.

I suspect that it would be good for a test database where you have lots
of tables but not much data and want to refresh it frequently. Putting
the seed data on the RAMDrive would seem to make a lot of sense since it
is made up of a lot of files that need to be opened and read.
I would make sure that I did my mods to config files and seed data using
a master copy on a physical drive so you do not lose work if the
RAMDrive suddenly disappears in a crash.

I have never had the RAMDrive fail on its own but I have had Windows
lock up  where a forced reboot was required.
I would love to put my Eclipse Workspace on the RAMDrive.
I did at first and it really made Eclipse fun to use but when I lost a
1/2 days work in a crash, I decided that I could stand a bit of a
slowdown in return for peace of mind.

This should be faster than an SSD but it is small and takes memory from
Windows.
A bigger memory configuration backed by a SSD harddrive would be my wish
for my next PC. I would probably still use a RAMDrive to get that extra
bit of snappiness in Eclipse.

Ron


On 21/09/2015 5:58 PM, Gabriel Oberreuter wrote:

> Ron, which tool are you using for the ram driver? Are you on linux or
> windows? I am on Windows.
> I want to give it a try, thanks!
>
>
>
> --
> View this message in context: http://ofbiz.135035.n4.nabble.com/using-H2-database-for-accelerated-development-tp4672568p4672575.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>

--
Ron Wheeler
President
Artifact Software Inc
email: [hidden email]
skype: ronaldmwheeler
phone: [phone], ext 102

[Ron Wheeler]

Very good article and if I had read the mail before responding to the
earlier one I could have saved a bit of writing.

You are replacing Windows ability to dynamically manage memory with your
knowledge of your processing needs and disk access patterns.
Running many tests that read a lot of static files into a clean database
seems to be a likely good case.

It also depends on how much real memory is available.
You can starve Windows if you take too much of its headroom away.


Ron

On 21/09/2015 6:26 PM, Peter Arnold wrote:

> Hi,
>
> I was just Reading about RAM-Drives since this concept was new to me. I
> think there are some important facts to be considered with that solution.
>
> Here is the article:
> http://www.howtogeek.com/171432/ram-disks-explained-what-they-are-and-why-yo
> u-probably-shouldnt-use-one/
>
>
> Regards,
>
> Peter
>
>
> ---
> El software de antivirus Avast ha analizado este correo electrónico en busca de virus.
> http://www.avast.com
>
>

--
Ron Wheeler
President
Artifact Software Inc
email: [hidden email]
skype: ronaldmwheeler
phone: [phone], ext 102

[Ron Wheeler]

On 21/09/2015 7:04 PM, Gabriel Oberreuter wrote:
> Thanks for the link Peter. Yes, anyone using RAMDisks should know for which
> cases it is a good idea for which ones it isn't.
>
> I remember seeing a few years back a product which was Ram bundled inside a
> 5.25" bay (like a cd-rom), using sata v1. But having to take considerations
> for it, I think it is far easier to just mount a ram disk without that
> accesory.

Ram bundled into a Sata drive sounds like the definition of an SSD.
You are limited by the SATA interface which is slower than RAM on the
main memory bus.


>
>
>
> --
> View this message in context: http://ofbiz.135035.n4.nabble.com/using-H2-database-for-accelerated-development-tp4672568p4672577.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>


--
Ron Wheeler
President
Artifact Software Inc
email: [hidden email]
skype: ronaldmwheeler
phone: [phone], ext 102

[Gabriel Oberreuter]

Thanks Adrian for the info, I hadn't checked that. We haven't seen problems related to h2 hdbc driver supporting only up to 4.0, but anyway, its not good to have something fundamental as the db in the "air".

Thanks Peter and Ron for the info on RAMDisks. I think that first one should increase RAM and switch to SSD, though.

[Ron Wheeler]

On 22/09/2015 8:25 AM, Gabriel Oberreuter wrote:
> Thanks Adrian for the info, I hadn't checked that. We haven't seen problems
> related to h2 hdbc driver supporting only up to 4.0, but anyway, its not
> good to have something fundamental as the db in the "air".
>
> Thanks Peter and Ron for the info on RAMDisks. I think that first one should
> increase RAM and switch to SSD, though.

You are right that RAM and SSD will likely be a bigger improvement but
the RAMDrive is free and can be done in 15 minutes and should not
require any changes to your OFBiz configuration other than the folder name.
It may be fast enough to get you through the part of your testing where
you are reloading the seed data frequently where opening files eats more
I/O that reading the actual data.


>
>
> --
> View this message in context: http://ofbiz.135035.n4.nabble.com/using-H2-database-for-accelerated-development-tp4672568p4672619.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>


--
Ron Wheeler
President
Artifact Software Inc
email: [hidden email]
skype: ronaldmwheeler
phone: [phone], ext 102

[Peter Arnold]

In order to not have to load to frequently the whole demo-data set which is
being continuously modified during implementation, you can also make use of:
Web Tools  -> Import / Export -> XML Data Import
                                -> XML Data Import Directory
                                -> XML Data Import Readers

Personally I have not used the second and third option but I do use the
first one which saves you sometimes from reloading the whole dataset.
Reloading everything is easier (and slower), but I think that by using XML
Data Import you are getting to know the data model in a better way and you
can safe some time.

This is not directly related to the database selection discussed in this
thread but it is certainly related to accelerated development.


Regards,
Peter
                       


---
El software de antivirus Avast ha analizado este correo electrónico en busca de virus.
http://www.avast.com

[Warnock, Matt]

Personally I like using ramdisk for current project with rsync cron job to
write it back to hard drive (or ssd) every hour or so just in case. (In
case I forget to run that same script at reasonable stopping points.) I saw
build time go from 26 mins down to 2 minutes, just changing from hdd to
ssd. Havent tested ramdisk that way yet.
On Sep 22, 2015 7:51 AM, "Peter Arnold" <[hidden email]> wrote:

> In order to not have to load to frequently the whole demo-data set which is
> being continuously modified during implementation, you can also make use
> of:
> Web Tools  ->   Import / Export ->      XML Data Import
>                                 ->      XML Data Import Directory
>                                 ->      XML Data Import Readers
>
> Personally I have not used the second and third option but I do use the
> first one which saves you sometimes from reloading the whole dataset.
> Reloading everything is easier (and slower), but I think that by using XML
> Data Import you are getting to know the data model in a better way and you
> can safe some time.
>
> This is not directly related to the database selection discussed in this
> thread but it is certainly related to accelerated development.
>
>
> Regards,
> Peter
>
>
>
> ---
> El software de antivirus Avast ha analizado este correo electrónico en
> busca de virus.
> http://www.avast.com
>
>

[Gabriel Oberreuter]

Thanks,

We use webtools, and for now we are not using the demo data. We only load seed and seed-initial, and we have started loading other data from scratch. It is a good excersise to learn all what its required to get a minimal setup running.

A script is being build to migrate all data from the old system: this script gets all current data from our current rtp, format it in a proper XML ofbiz-way, a post it automatically to a ofbiz instance.

To avoid loading data more than once, I have included a servlet in ofbiz which returns, for example, all paid invoices, all products, and so on, in json format, so our migration script can query ofbiz first and filter out those entries. The idea is to post to ofbiz only the new or updated data from the current system.

The script is being built so it works with a freshly instance of ofbiz. The instance needs only to be loaded with: seed, seed-initial and create-admin-user-login ant target. So we can easily generate and start new ofbiz instances without much effort.

[taher]

By the way webtools is not the only way to load data. If you want
automation you can call ant targets for loading individual data files which
makes your development much faster than using web tools.

Taher Alkhateeb
On Sep 22, 2015 12:44 PM, "Warnock, Matt" <[hidden email]> wrote:

> Personally I like using ramdisk for current project with rsync cron job to
> write it back to hard drive (or ssd) every hour or so just in case. (In
> case I forget to run that same script at reasonable stopping points.) I saw
> build time go from 26 mins down to 2 minutes, just changing from hdd to
> ssd. Havent tested ramdisk that way yet.
> On Sep 22, 2015 7:51 AM, "Peter Arnold" <[hidden email]> wrote:
>
> > In order to not have to load to frequently the whole demo-data set which
> is
> > being continuously modified during implementation, you can also make use
> > of:
> > Web Tools  ->   Import / Export ->      XML Data Import
> >                                 ->      XML Data Import Directory
> >                                 ->      XML Data Import Readers
> >
> > Personally I have not used the second and third option but I do use the
> > first one which saves you sometimes from reloading the whole dataset.
> > Reloading everything is easier (and slower), but I think that by using
> XML
> > Data Import you are getting to know the data model in a better way and
> you
> > can safe some time.
> >
> > This is not directly related to the database selection discussed in this
> > thread but it is certainly related to accelerated development.
> >
> >
> > Regards,
> > Peter
> >
> >
> >
> > ---
> > El software de antivirus Avast ha analizado este correo electrónico en
> > busca de virus.
> > http://www.avast.com
> >
> >
>