Re: svn commit: r1139851 - /ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/DatabaseUtil.java

[Adam Heath-2]

On 06/26/2011 01:07 PM, [hidden email] wrote:

> Author: doogie
> Date: Sun Jun 26 18:07:18 2011
> New Revision: 1139851
>
> URL: http://svn.apache.org/viewvc?rev=1139851&view=rev
> Log:
> FEATURE: When doing bulk creation of tables, foreign key indices,
> and declared indices, do the work in parallel.  Also, when the database
> does not support wildcard looking of primary key info, do the per-table
> lookup in parallel.
>
> Modified:
>      ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/DatabaseUtil.java

This is a major feature.  By default, it's not actually going to do
anything in parallel.  You need to set a 'max-worker-pool-size' to
some value(positive, or negative, which will then be multiplied by the
number of cpus), to take advantage of this.

Eventually, this per-datasource thread pool could be used to limit
concurrent queries, but that is not something I've done yet.

[Adrian Crum-3]

Some advice on using this feature (based on my previous experience with
multi-threaded table creation): Start off with small values - like 1 or
2 on a multi-CPU machine. Creating more threads does not equal better
performance - there is a threshold where more threads will slow things down.

-Adrian

On 6/26/2011 7:22 PM, Adam Heath wrote:

> On 06/26/2011 01:07 PM, [hidden email] wrote:
>> Author: doogie
>> Date: Sun Jun 26 18:07:18 2011
>> New Revision: 1139851
>>
>> URL: http://svn.apache.org/viewvc?rev=1139851&view=rev
>> Log:
>> FEATURE: When doing bulk creation of tables, foreign key indices,
>> and declared indices, do the work in parallel.  Also, when the database
>> does not support wildcard looking of primary key info, do the per-table
>> lookup in parallel.
>>
>> Modified:
>>      
>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/DatabaseUtil.java
>
> This is a major feature.  By default, it's not actually going to do
> anything in parallel.  You need to set a 'max-worker-pool-size' to
> some value(positive, or negative, which will then be multiplied by the
> number of cpus), to take advantage of this.
>
> Eventually, this per-datasource thread pool could be used to limit
> concurrent queries, but that is not something I've done yet.

[Adam Heath-2]

On 06/26/2011 03:14 PM, Adrian Crum wrote:
> Some advice on using this feature (based on my previous experience with
> multi-threaded table creation): Start off with small values - like 1 or
> 2 on a multi-CPU machine. Creating more threads does not equal better
> performance - there is a threshold where more threads will slow things
> down.

The astute will notice that this feature is not the same as the one I
mistakenly committed ages ago; this one is simpler.

>
> -Adrian
>
> On 6/26/2011 7:22 PM, Adam Heath wrote:
>> On 06/26/2011 01:07 PM, [hidden email] wrote:
>>> Author: doogie
>>> Date: Sun Jun 26 18:07:18 2011
>>> New Revision: 1139851
>>>
>>> URL: http://svn.apache.org/viewvc?rev=1139851&view=rev
>>> Log:
>>> FEATURE: When doing bulk creation of tables, foreign key indices,
>>> and declared indices, do the work in parallel. Also, when the database
>>> does not support wildcard looking of primary key info, do the per-table
>>> lookup in parallel.
>>>
>>> Modified:
>>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/DatabaseUtil.java
>>
>> This is a major feature. By default, it's not actually going to do
>> anything in parallel. You need to set a 'max-worker-pool-size' to some
>> value(positive, or negative, which will then be multiplied by the
>> number of cpus), to take advantage of this.
>>
>> Eventually, this per-datasource thread pool could be used to limit
>> concurrent queries, but that is not something I've done yet.

[Adam Heath-2]

On 06/26/2011 03:14 PM, Adrian Crum wrote:
> Some advice on using this feature (based on my previous experience with
> multi-threaded table creation): Start off with small values - like 1 or
> 2 on a multi-CPU machine. Creating more threads does not equal better
> performance - there is a threshold where more threads will slow things
> down.

I tested this on a 6-core machine, with the entire ofbiz checkout
running on a ram disk.  Speed was nice, but I didn't do any checking
to see which values were faster.  I didn't have any problems setting
the value to -1(which caused a pool size of 6), except for the
aformentioned foreign-key issue; as such, that change is not in this
commit.

>
> -Adrian
>
> On 6/26/2011 7:22 PM, Adam Heath wrote:
>> On 06/26/2011 01:07 PM, [hidden email] wrote:
>>> Author: doogie
>>> Date: Sun Jun 26 18:07:18 2011
>>> New Revision: 1139851
>>>
>>> URL: http://svn.apache.org/viewvc?rev=1139851&view=rev
>>> Log:
>>> FEATURE: When doing bulk creation of tables, foreign key indices,
>>> and declared indices, do the work in parallel. Also, when the database
>>> does not support wildcard looking of primary key info, do the per-table
>>> lookup in parallel.
>>>
>>> Modified:
>>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/DatabaseUtil.java
>>
>> This is a major feature. By default, it's not actually going to do
>> anything in parallel. You need to set a 'max-worker-pool-size' to some
>> value(positive, or negative, which will then be multiplied by the
>> number of cpus), to take advantage of this.
>>
>> Eventually, this per-datasource thread pool could be used to limit
>> concurrent queries, but that is not something I've done yet.

[Adrian Crum-3]

Okay, that -1 setting seems confusing. Maybe we need a better
explanation of how the setting works.

-Adrian

On 6/26/2011 9:24 PM, Adam Heath wrote:

> On 06/26/2011 03:14 PM, Adrian Crum wrote:
>> Some advice on using this feature (based on my previous experience with
>> multi-threaded table creation): Start off with small values - like 1 or
>> 2 on a multi-CPU machine. Creating more threads does not equal better
>> performance - there is a threshold where more threads will slow things
>> down.
>
> I tested this on a 6-core machine, with the entire ofbiz checkout
> running on a ram disk.  Speed was nice, but I didn't do any checking
> to see which values were faster.  I didn't have any problems setting
> the value to -1(which caused a pool size of 6), except for the
> aformentioned foreign-key issue; as such, that change is not in this
> commit.
>
>>
>> -Adrian
>>
>> On 6/26/2011 7:22 PM, Adam Heath wrote:
>>> On 06/26/2011 01:07 PM, [hidden email] wrote:
>>>> Author: doogie
>>>> Date: Sun Jun 26 18:07:18 2011
>>>> New Revision: 1139851
>>>>
>>>> URL: http://svn.apache.org/viewvc?rev=1139851&view=rev
>>>> Log:
>>>> FEATURE: When doing bulk creation of tables, foreign key indices,
>>>> and declared indices, do the work in parallel. Also, when the database
>>>> does not support wildcard looking of primary key info, do the
>>>> per-table
>>>> lookup in parallel.
>>>>
>>>> Modified:
>>>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/DatabaseUtil.java
>>>>
>>>
>>> This is a major feature. By default, it's not actually going to do
>>> anything in parallel. You need to set a 'max-worker-pool-size' to some
>>> value(positive, or negative, which will then be multiplied by the
>>> number of cpus), to take advantage of this.
>>>
>>> Eventually, this per-datasource thread pool could be used to limit
>>> concurrent queries, but that is not something I've done yet.
>

[Adam Heath-2]

On 06/26/2011 03:32 PM, Adrian Crum wrote:
> Okay, that -1 setting seems confusing. Maybe we need a better
> explanation of how the setting works.

You could specify -2, to mean twice the number of cpus, -5 to mean 5
times the number of cpus.  I couldn't think of a better way to do that.

>
> -Adrian
>
> On 6/26/2011 9:24 PM, Adam Heath wrote:
>> On 06/26/2011 03:14 PM, Adrian Crum wrote:
>>> Some advice on using this feature (based on my previous experience with
>>> multi-threaded table creation): Start off with small values - like 1 or
>>> 2 on a multi-CPU machine. Creating more threads does not equal better
>>> performance - there is a threshold where more threads will slow things
>>> down.
>>
>> I tested this on a 6-core machine, with the entire ofbiz checkout
>> running on a ram disk. Speed was nice, but I didn't do any checking to
>> see which values were faster. I didn't have any problems setting the
>> value to -1(which caused a pool size of 6), except for the
>> aformentioned foreign-key issue; as such, that change is not in this
>> commit.
>>
>>>
>>> -Adrian
>>>
>>> On 6/26/2011 7:22 PM, Adam Heath wrote:
>>>> On 06/26/2011 01:07 PM, [hidden email] wrote:
>>>>> Author: doogie
>>>>> Date: Sun Jun 26 18:07:18 2011
>>>>> New Revision: 1139851
>>>>>
>>>>> URL: http://svn.apache.org/viewvc?rev=1139851&view=rev
>>>>> Log:
>>>>> FEATURE: When doing bulk creation of tables, foreign key indices,
>>>>> and declared indices, do the work in parallel. Also, when the database
>>>>> does not support wildcard looking of primary key info, do the
>>>>> per-table
>>>>> lookup in parallel.
>>>>>
>>>>> Modified:
>>>>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/DatabaseUtil.java
>>>>>
>>>>
>>>> This is a major feature. By default, it's not actually going to do
>>>> anything in parallel. You need to set a 'max-worker-pool-size' to some
>>>> value(positive, or negative, which will then be multiplied by the
>>>> number of cpus), to take advantage of this.
>>>>
>>>> Eventually, this per-datasource thread pool could be used to limit
>>>> concurrent queries, but that is not something I've done yet.
>>

[Adrian Crum-3]

What would +2 do? Or is the multiplier an absolute value?

-Adrian

On 6/26/2011 9:38 PM, Adam Heath wrote:

> On 06/26/2011 03:32 PM, Adrian Crum wrote:
>> Okay, that -1 setting seems confusing. Maybe we need a better
>> explanation of how the setting works.
>
> You could specify -2, to mean twice the number of cpus, -5 to mean 5
> times the number of cpus.  I couldn't think of a better way to do that.
>
>>
>> -Adrian
>>
>> On 6/26/2011 9:24 PM, Adam Heath wrote:
>>> On 06/26/2011 03:14 PM, Adrian Crum wrote:
>>>> Some advice on using this feature (based on my previous experience
>>>> with
>>>> multi-threaded table creation): Start off with small values - like
>>>> 1 or
>>>> 2 on a multi-CPU machine. Creating more threads does not equal better
>>>> performance - there is a threshold where more threads will slow things
>>>> down.
>>>
>>> I tested this on a 6-core machine, with the entire ofbiz checkout
>>> running on a ram disk. Speed was nice, but I didn't do any checking to
>>> see which values were faster. I didn't have any problems setting the
>>> value to -1(which caused a pool size of 6), except for the
>>> aformentioned foreign-key issue; as such, that change is not in this
>>> commit.
>>>
>>>>
>>>> -Adrian
>>>>
>>>> On 6/26/2011 7:22 PM, Adam Heath wrote:
>>>>> On 06/26/2011 01:07 PM, [hidden email] wrote:
>>>>>> Author: doogie
>>>>>> Date: Sun Jun 26 18:07:18 2011
>>>>>> New Revision: 1139851
>>>>>>
>>>>>> URL: http://svn.apache.org/viewvc?rev=1139851&view=rev
>>>>>> Log:
>>>>>> FEATURE: When doing bulk creation of tables, foreign key indices,
>>>>>> and declared indices, do the work in parallel. Also, when the
>>>>>> database
>>>>>> does not support wildcard looking of primary key info, do the
>>>>>> per-table
>>>>>> lookup in parallel.
>>>>>>
>>>>>> Modified:
>>>>>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/DatabaseUtil.java
>>>>>>
>>>>>>
>>>>>
>>>>> This is a major feature. By default, it's not actually going to do
>>>>> anything in parallel. You need to set a 'max-worker-pool-size' to
>>>>> some
>>>>> value(positive, or negative, which will then be multiplied by the
>>>>> number of cpus), to take advantage of this.
>>>>>
>>>>> Eventually, this per-datasource thread pool could be used to limit
>>>>> concurrent queries, but that is not something I've done yet.
>>>
>

[Adam Heath-2]

On 06/26/2011 03:41 PM, Adrian Crum wrote:
> What would +2 do? Or is the multiplier an absolute value?

0=1
1=1
2=2
3=3
-1=abs(-1)*num-cpus
-2=abs(-2)*num-cpus
-5=abs(-5)*num-cpus

and so on.

[Adrian Crum-3]

Okay, that makes sense. Thanks!

-Adrian

On 6/26/2011 9:43 PM, Adam Heath wrote:

> On 06/26/2011 03:41 PM, Adrian Crum wrote:
>> What would +2 do? Or is the multiplier an absolute value?
>
> 0=1
> 1=1
> 2=2
> 3=3
> -1=abs(-1)*num-cpus
> -2=abs(-2)*num-cpus
> -5=abs(-5)*num-cpus
>
> and so on.

[Adrian Crum-3]

I'm not sure changing the value when using a RAM disk would make much
difference. It seems to me multi-threading would benefit a system that
has I/O bottlenecks.

Also, the JDBC driver itself could be a bottleneck. That's why I had a
separate thread reading the entity model XML files - so one thread could
be parsing XML while another one is waiting for disk I/O.

-Adrian


On 6/26/2011 9:24 PM, Adam Heath wrote:

> On 06/26/2011 03:14 PM, Adrian Crum wrote:
>> Some advice on using this feature (based on my previous experience with
>> multi-threaded table creation): Start off with small values - like 1 or
>> 2 on a multi-CPU machine. Creating more threads does not equal better
>> performance - there is a threshold where more threads will slow things
>> down.
>
> I tested this on a 6-core machine, with the entire ofbiz checkout
> running on a ram disk.  Speed was nice, but I didn't do any checking
> to see which values were faster.  I didn't have any problems setting
> the value to -1(which caused a pool size of 6), except for the
> aformentioned foreign-key issue; as such, that change is not in this
> commit.
>
>>
>> -Adrian
>>
>> On 6/26/2011 7:22 PM, Adam Heath wrote:
>>> On 06/26/2011 01:07 PM, [hidden email] wrote:
>>>> Author: doogie
>>>> Date: Sun Jun 26 18:07:18 2011
>>>> New Revision: 1139851
>>>>
>>>> URL: http://svn.apache.org/viewvc?rev=1139851&view=rev
>>>> Log:
>>>> FEATURE: When doing bulk creation of tables, foreign key indices,
>>>> and declared indices, do the work in parallel. Also, when the database
>>>> does not support wildcard looking of primary key info, do the
>>>> per-table
>>>> lookup in parallel.
>>>>
>>>> Modified:
>>>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/DatabaseUtil.java
>>>>
>>>
>>> This is a major feature. By default, it's not actually going to do
>>> anything in parallel. You need to set a 'max-worker-pool-size' to some
>>> value(positive, or negative, which will then be multiplied by the
>>> number of cpus), to take advantage of this.
>>>
>>> Eventually, this per-datasource thread pool could be used to limit
>>> concurrent queries, but that is not something I've done yet.
>

[Adrian Crum-3]

A setting of -2 on my dual-CPU, high speed RAID array development
machine resulted in a measurable reduction in table creation time. Good job!

-Adrian

On 6/26/2011 9:43 PM, Adam Heath wrote:

> On 06/26/2011 03:41 PM, Adrian Crum wrote:
>> What would +2 do? Or is the multiplier an absolute value?
>
> 0=1
> 1=1
> 2=2
> 3=3
> -1=abs(-1)*num-cpus
> -2=abs(-2)*num-cpus
> -5=abs(-5)*num-cpus
>
> and so on.

[Adam Heath-2]

On 06/26/2011 04:19 PM, Adrian Crum wrote:
> I'm not sure changing the value when using a RAM disk would make much
> difference. It seems to me multi-threading would benefit a system that
> has I/O bottlenecks.

By running on a ram disk, IO contention is much reduced(of course).
Then using a high-core machine, allows me to test that the actual code
has no issues.

> Also, the JDBC driver itself could be a bottleneck. That's why I had a
> separate thread reading the entity model XML files - so one thread could
> be parsing XML while another one is waiting for disk I/O.

All that is good.  I can verify that running these things in parallel
speeds up both derby and oracle.  It was initially developed on
oracle, which doesn't have the aformentioned deadlock issue with
foreign-keys.

We can come back to your staged idea.  I have a helper class
locally(without test cases yet), for batched staging, and shared
pooling(hard to explain this latter part).

I also want to speed up service def parsing, ecas can run in
parallel(they have to anyways, considering that there could have been
multiple calling threads initially).

[Adam Heath-2]

Adrian Crum <[hidden email]> wrote:

>A setting of -2 on my dual-CPU, high speed RAID array development
>machine resulted in a measurable reduction in table creation time. Good
>job!
>
>-Adrian
>
>On 6/26/2011 9:43 PM, Adam Heath wrote:
>> On 06/26/2011 03:41 PM, Adrian Crum wrote:
>>> What would +2 do? Or is the multiplier an absolute value?
>>
>> 0=1
>> 1=1
>> 2=2
>> 3=3
>> -1=abs(-1)*num-cpus
>> -2=abs(-2)*num-cpus
>> -5=abs(-5)*num-cpus
>>
>> and so on.

That is not what the final value should be; eventually the per-datasource thread pool will be used to reduce database overloading.  In these cases, -1 is the right value, because other threads will be doing page rendering.
--
Sent from my Android phone with K-9 Mail. Please excuse my brevity.

[Adam Heath-2]

Adrian Crum <[hidden email]> wrote:

>A setting of -2 on my dual-CPU, high speed RAID array development
>machine resulted in a measurable reduction in table creation time. Good
>job!
>
>-Adrian
>
>On 6/26/2011 9:43 PM, Adam Heath wrote:
>> On 06/26/2011 03:41 PM, Adrian Crum wrote:
>>> What would +2 do? Or is the multiplier an absolute value?
>>
>> 0=1
>> 1=1
>> 2=2
>> 3=3
>> -1=abs(-1)*num-cpus
>> -2=abs(-2)*num-cpus
>> -5=abs(-5)*num-cpus
>>
>> and so on.

As I mentioned before, I have tested this on oracle(initial development) and Derby, and both are faster.  Haven't done postgres or mysql.  I have further plans to load multiple delegators in parallel.
--
Sent from my Android phone with K-9 Mail. Please excuse my brevity.

[Adrian Crum-3]

On the other hand, I tried -1 and 2 settings on an 4 CPU system with a
single hard disk and they made no difference.

-Adrian

On 6/26/2011 10:32 PM, Adrian Crum wrote:

> A setting of -2 on my dual-CPU, high speed RAID array development
> machine resulted in a measurable reduction in table creation time.
> Good job!
>
> -Adrian
>
> On 6/26/2011 9:43 PM, Adam Heath wrote:
>> On 06/26/2011 03:41 PM, Adrian Crum wrote:
>>> What would +2 do? Or is the multiplier an absolute value?
>>
>> 0=1
>> 1=1
>> 2=2
>> 3=3
>> -1=abs(-1)*num-cpus
>> -2=abs(-2)*num-cpus
>> -5=abs(-5)*num-cpus
>>
>> and so on.

[Adam Heath-2]

On 06/30/2011 08:49 AM, Adrian Crum wrote:
> On the other hand, I tried -1 and 2 settings on an 4 CPU system with a
> single hard disk and they made no difference.

How much L2-cache on the cpu(s) was there?  Java quite often is larger
than the L2-cache, which then means it's waiting on slower memory.

> On 6/26/2011 10:32 PM, Adrian Crum wrote:
>> A setting of -2 on my dual-CPU, high speed RAID array development
>> machine resulted in a measurable reduction in table creation time.
>> Good job!
>>
>> -Adrian
>>
>> On 6/26/2011 9:43 PM, Adam Heath wrote:
>>> On 06/26/2011 03:41 PM, Adrian Crum wrote:
>>>> What would +2 do? Or is the multiplier an absolute value?
>>>
>>> 0=1
>>> 1=1
>>> 2=2
>>> 3=3
>>> -1=abs(-1)*num-cpus
>>> -2=abs(-2)*num-cpus
>>> -5=abs(-5)*num-cpus
>>>
>>> and so on.