You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by Sanjeev Gupta <su...@sanjeevg.com> on 2012/06/21 20:57:18 UTC
Ofbiz with MySQL or PostgreSQL
I'm looking to decide on the database to be used with OfBiz in the production
environment. Any thoughts on which database works well with Ofbiz - MySQL or
PostgreSQL and why ?
-----
Rgds
Sanjeev
www.sanjeevg.com
@sanjeevgcom
--
View this message in context: http://ofbiz.135035.n4.nabble.com/Ofbiz-with-MySQL-or-PostgreSQL-tp4633893.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: Ofbiz with MySQL or PostgreSQL
Posted by Jacques Le Roux <ja...@les7arts.com>.
I second on all Ruth and Scott said. Keeping it short: don't use MySQL if you can use PostgreSQL
You can use pgpool-II as safe replication tool
http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling
Jacques
From: "Ruth Hoffman" <rh...@aesolves.com>
> As Scott mentions there are many parts of the OFBiz data model (and associated business logic) that use a Timestamp within
> database tables/entities as a primary-key.
>
> Another example: If you are using any of the content management logic (blog, content publication and RSS feed come to mind) you
> could have issues.
>
> Even business logic as seemingly trivial as allowing a user to have multiple shipping addresses (see the PartyContactMechPurpose
> entity) is effected since a Timestamp is used as part of the fields that make up the primary-key. FYI - I've seen this one in
> action. It wasn't immediately obvious what was going on until I looked directly in the database and saw the data.
>
> Regards,
> Ruth
>
> On 6/25/12 10:12 AM, Scott Gray wrote:
>> It can also be a problem in tables like InventoryItemStatus or basically anywhere in the system that depends on being able to
>> retrieve the latest record when it's possible for two records to have been created within the same second. Also anywhere that
>> depends on a timestamp as part of the primary key and possible that similar records will be created within the same second
>> (ProductAverageCost is another one like ServerHit). And in general losing that millisecond precision can make it difficult at
>> times to know the order in which a set of actions took place when looking through the data.
>>
>> Regards
>> Scott
>>
>> On 24/06/2012, at 2:10 PM, Brett Palmer wrote:
>>
>>> The only place I have found this to be a problem is in the server hit
>>> entity. The server hit functionality is configurable. We disable that
>>> functionality. We write custom applications using the ofbiz framework and
>>> don't do a lot of ecommerce apps so its not a problem for us.
>>>
>>> Brett
>>> On Jun 22, 2012 11:48 PM, "Sanjeev Gupta" <su...@sanjeevg.com> wrote:
>>>
>>>> Thanks Bertt
>>>> How do you manage with the timestamp problem mentioned by Ruth.
>>>>
>>>> SkipDever
>>>> Is replication native in 9.1 or though add-on components ?
>>>>
>>>> -----
>>>> Rgds
>>>> Sanjeev
>>>> www.sanjeevg.com
>>>> @sanjeevgcom
>>>> --
>>>> View this message in context:
>>>> http://ofbiz.135035.n4.nabble.com/Ofbiz-with-MySQL-or-PostgreSQL-tp4633893p4633954.html
>>>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>>>>
>>
>
>
Re: Ofbiz with MySQL or PostgreSQL
Posted by Ruth Hoffman <rh...@aesolves.com>.
As Scott mentions there are many parts of the OFBiz data model (and
associated business logic) that use a Timestamp within database
tables/entities as a primary-key.
Another example: If you are using any of the content management logic
(blog, content publication and RSS feed come to mind) you could have
issues.
Even business logic as seemingly trivial as allowing a user to have
multiple shipping addresses (see the PartyContactMechPurpose entity) is
effected since a Timestamp is used as part of the fields that make up
the primary-key. FYI - I've seen this one in action. It wasn't
immediately obvious what was going on until I looked directly in the
database and saw the data.
Regards,
Ruth
On 6/25/12 10:12 AM, Scott Gray wrote:
> It can also be a problem in tables like InventoryItemStatus or basically anywhere in the system that depends on being able to retrieve the latest record when it's possible for two records to have been created within the same second. Also anywhere that depends on a timestamp as part of the primary key and possible that similar records will be created within the same second (ProductAverageCost is another one like ServerHit). And in general losing that millisecond precision can make it difficult at times to know the order in which a set of actions took place when looking through the data.
>
> Regards
> Scott
>
> On 24/06/2012, at 2:10 PM, Brett Palmer wrote:
>
>> The only place I have found this to be a problem is in the server hit
>> entity. The server hit functionality is configurable. We disable that
>> functionality. We write custom applications using the ofbiz framework and
>> don't do a lot of ecommerce apps so its not a problem for us.
>>
>> Brett
>> On Jun 22, 2012 11:48 PM, "Sanjeev Gupta" <su...@sanjeevg.com> wrote:
>>
>>> Thanks Bertt
>>> How do you manage with the timestamp problem mentioned by Ruth.
>>>
>>> SkipDever
>>> Is replication native in 9.1 or though add-on components ?
>>>
>>> -----
>>> Rgds
>>> Sanjeev
>>> www.sanjeevg.com
>>> @sanjeevgcom
>>> --
>>> View this message in context:
>>> http://ofbiz.135035.n4.nabble.com/Ofbiz-with-MySQL-or-PostgreSQL-tp4633893p4633954.html
>>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>>>
>
Re: Ofbiz with MySQL or PostgreSQL
Posted by Scott Gray <sc...@hotwaxmedia.com>.
It can also be a problem in tables like InventoryItemStatus or basically anywhere in the system that depends on being able to retrieve the latest record when it's possible for two records to have been created within the same second. Also anywhere that depends on a timestamp as part of the primary key and possible that similar records will be created within the same second (ProductAverageCost is another one like ServerHit). And in general losing that millisecond precision can make it difficult at times to know the order in which a set of actions took place when looking through the data.
Regards
Scott
On 24/06/2012, at 2:10 PM, Brett Palmer wrote:
> The only place I have found this to be a problem is in the server hit
> entity. The server hit functionality is configurable. We disable that
> functionality. We write custom applications using the ofbiz framework and
> don't do a lot of ecommerce apps so its not a problem for us.
>
> Brett
> On Jun 22, 2012 11:48 PM, "Sanjeev Gupta" <su...@sanjeevg.com> wrote:
>
>> Thanks Bertt
>> How do you manage with the timestamp problem mentioned by Ruth.
>>
>> SkipDever
>> Is replication native in 9.1 or though add-on components ?
>>
>> -----
>> Rgds
>> Sanjeev
>> www.sanjeevg.com
>> @sanjeevgcom
>> --
>> View this message in context:
>> http://ofbiz.135035.n4.nabble.com/Ofbiz-with-MySQL-or-PostgreSQL-tp4633893p4633954.html
>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>>
RE: Ofbiz with MySQL or PostgreSQL
Posted by Skip <sk...@thedevers.org>.
Replication is native since 9.1. There has been a (very difficult to
configure) addon for some time.
Check this link:
http://www.postgresql.org/docs/9.1/static/high-availability.html
-----Original Message-----
From: Brett Palmer [mailto:brettgpalmer@gmail.com]
Sent: Saturday, June 23, 2012 7:10 PM
To: user@ofbiz.apache.org
Subject: RE: Ofbiz with MySQL or PostgreSQL
The only place I have found this to be a problem is in the server hit
entity. The server hit functionality is configurable. We disable that
functionality. We write custom applications using the ofbiz framework and
don't do a lot of ecommerce apps so its not a problem for us.
Brett
On Jun 22, 2012 11:48 PM, "Sanjeev Gupta" <su...@sanjeevg.com> wrote:
> Thanks Bertt
> How do you manage with the timestamp problem mentioned by Ruth.
>
> SkipDever
> Is replication native in 9.1 or though add-on components ?
>
> -----
> Rgds
> Sanjeev
> www.sanjeevg.com
> @sanjeevgcom
> --
> View this message in context:
>
http://ofbiz.135035.n4.nabble.com/Ofbiz-with-MySQL-or-PostgreSQL-tp4633893p4
633954.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>
Re: Ofbiz with MySQL or PostgreSQL
Posted by 李明洋 <cn...@gmail.com>.
OK,thank you
在 2012-6-24 上午11:44，"Brett Palmer" <br...@gmail.com>写道：
> In the framework/webapp/config/ directory there is a serverstats.properties
> file that you can turn off and on stats.
>
> For example:
>
> stats.persist.REQUEST.hit=false
> stats.persist.EVENT.hit=false
> stats.persist.VIEW.hit=false
> stats.persist.ENTITY.hit=false
> stats.persist.SERVICE.hit=false
>
> The above properties will turn off persisting the stats for ofbiz which is
> where you get constraint errors in mysql because the server stats use a
> timestamp as a primary key.
>
>
> Brett
>
>
> On Sat, Jun 23, 2012 at 9:07 PM, 李明洋 <cn...@gmail.com> wrote:
>
> > ho
> >
> > how to disable server hit?
> > 在 2012-6-24 上午10:10，"Brett Palmer" <br...@gmail.com>写道：
> >
> > > The only place I have found this to be a problem is in the server hit
> > > entity. The server hit functionality is configurable. We disable that
> > > functionality. We write custom applications using the ofbiz framework
> > and
> > > don't do a lot of ecommerce apps so its not a problem for us.
> > >
> > > Brett
> > > On Jun 22, 2012 11:48 PM, "Sanjeev Gupta" <su...@sanjeevg.com>
> > wrote:
> > >
> > > > Thanks Bertt
> > > > How do you manage with the timestamp problem mentioned by Ruth.
> > > >
> > > > SkipDever
> > > > Is replication native in 9.1 or though add-on components ?
> > > >
> > > > -----
> > > > Rgds
> > > > Sanjeev
> > > > www.sanjeevg.com
> > > > @sanjeevgcom
> > > > --
> > > > View this message in context:
> > > >
> > >
> >
> http://ofbiz.135035.n4.nabble.com/Ofbiz-with-MySQL-or-PostgreSQL-tp4633893p4633954.html
> > > > Sent from the OFBiz - User mailing list archive at Nabble.com.
> > > >
> > >
> >
>
Re: Ofbiz with MySQL or PostgreSQL
Posted by Brett Palmer <br...@gmail.com>.
In the framework/webapp/config/ directory there is a serverstats.properties
file that you can turn off and on stats.
For example:
stats.persist.REQUEST.hit=false
stats.persist.EVENT.hit=false
stats.persist.VIEW.hit=false
stats.persist.ENTITY.hit=false
stats.persist.SERVICE.hit=false
The above properties will turn off persisting the stats for ofbiz which is
where you get constraint errors in mysql because the server stats use a
timestamp as a primary key.
Brett
On Sat, Jun 23, 2012 at 9:07 PM, 李明洋 <cn...@gmail.com> wrote:
> ho
>
> how to disable server hit?
> 在 2012-6-24 上午10:10，"Brett Palmer" <br...@gmail.com>写道：
>
> > The only place I have found this to be a problem is in the server hit
> > entity. The server hit functionality is configurable. We disable that
> > functionality. We write custom applications using the ofbiz framework
> and
> > don't do a lot of ecommerce apps so its not a problem for us.
> >
> > Brett
> > On Jun 22, 2012 11:48 PM, "Sanjeev Gupta" <su...@sanjeevg.com>
> wrote:
> >
> > > Thanks Bertt
> > > How do you manage with the timestamp problem mentioned by Ruth.
> > >
> > > SkipDever
> > > Is replication native in 9.1 or though add-on components ?
> > >
> > > -----
> > > Rgds
> > > Sanjeev
> > > www.sanjeevg.com
> > > @sanjeevgcom
> > > --
> > > View this message in context:
> > >
> >
> http://ofbiz.135035.n4.nabble.com/Ofbiz-with-MySQL-or-PostgreSQL-tp4633893p4633954.html
> > > Sent from the OFBiz - User mailing list archive at Nabble.com.
> > >
> >
>
RE: Ofbiz with MySQL or PostgreSQL
Posted by 李明洋 <cn...@gmail.com>.
ho
how to disable server hit?
在 2012-6-24 上午10:10，"Brett Palmer" <br...@gmail.com>写道：
> The only place I have found this to be a problem is in the server hit
> entity. The server hit functionality is configurable. We disable that
> functionality. We write custom applications using the ofbiz framework and
> don't do a lot of ecommerce apps so its not a problem for us.
>
> Brett
> On Jun 22, 2012 11:48 PM, "Sanjeev Gupta" <su...@sanjeevg.com> wrote:
>
> > Thanks Bertt
> > How do you manage with the timestamp problem mentioned by Ruth.
> >
> > SkipDever
> > Is replication native in 9.1 or though add-on components ?
> >
> > -----
> > Rgds
> > Sanjeev
> > www.sanjeevg.com
> > @sanjeevgcom
> > --
> > View this message in context:
> >
> http://ofbiz.135035.n4.nabble.com/Ofbiz-with-MySQL-or-PostgreSQL-tp4633893p4633954.html
> > Sent from the OFBiz - User mailing list archive at Nabble.com.
> >
>
RE: Ofbiz with MySQL or PostgreSQL
Posted by Brett Palmer <br...@gmail.com>.
The only place I have found this to be a problem is in the server hit
entity. The server hit functionality is configurable. We disable that
functionality. We write custom applications using the ofbiz framework and
don't do a lot of ecommerce apps so its not a problem for us.
Brett
On Jun 22, 2012 11:48 PM, "Sanjeev Gupta" <su...@sanjeevg.com> wrote:
> Thanks Bertt
> How do you manage with the timestamp problem mentioned by Ruth.
>
> SkipDever
> Is replication native in 9.1 or though add-on components ?
>
> -----
> Rgds
> Sanjeev
> www.sanjeevg.com
> @sanjeevgcom
> --
> View this message in context:
> http://ofbiz.135035.n4.nabble.com/Ofbiz-with-MySQL-or-PostgreSQL-tp4633893p4633954.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>
RE: Ofbiz with MySQL or PostgreSQL
Posted by Sanjeev Gupta <su...@sanjeevg.com>.
Thanks Bertt
How do you manage with the timestamp problem mentioned by Ruth.
SkipDever
Is replication native in 9.1 or though add-on components ?
-----
Rgds
Sanjeev
www.sanjeevg.com
@sanjeevgcom
--
View this message in context: http://ofbiz.135035.n4.nabble.com/Ofbiz-with-MySQL-or-PostgreSQL-tp4633893p4633954.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
RE: Ofbiz with MySQL or PostgreSQL
Posted by Skip <sk...@thedevers.org>.
Postgres from 9.1 has good replication
-----Original Message-----
From: Brett Palmer [mailto:brettgpalmer@gmail.com]
Sent: Thursday, June 21, 2012 10:04 PM
To: user@ofbiz.apache.org
Subject: Re: Ofbiz with MySQL or PostgreSQL
Sanjeev,
We've used MySQL for several years now. I think most ofbiz user prefer
Postgres. We like Postgres but the one feature that keeps us with MySQL is
its replication feature. Replication in MySQL is very good and easy
to administer. We use them for running reports which helps us scale our
solution. We have a data warehouse with a fact table of over 30+ million
rows and mysql does a good job with it.
Brett
On Thu, Jun 21, 2012 at 8:47 PM, Sanjeev Gupta
<su...@sanjeevg.com>wrote:
> Thanks Ruth.
> I've also been told that the demo data upload in Postgres takes half as
> time
> as MySQL - so I'm guessing that the production env performance should also
> be better with Postgres.
>
> -----
> Rgds
> Sanjeev
> www.sanjeevg.com
> @sanjeevgcom
> --
> View this message in context:
>
http://ofbiz.135035.n4.nabble.com/Ofbiz-with-MySQL-or-PostgreSQL-tp4633893p4
633907.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>
Re: Ofbiz with MySQL or PostgreSQL
Posted by Brett Palmer <br...@gmail.com>.
Sanjeev,
We've used MySQL for several years now. I think most ofbiz user prefer
Postgres. We like Postgres but the one feature that keeps us with MySQL is
its replication feature. Replication in MySQL is very good and easy
to administer. We use them for running reports which helps us scale our
solution. We have a data warehouse with a fact table of over 30+ million
rows and mysql does a good job with it.
Brett
On Thu, Jun 21, 2012 at 8:47 PM, Sanjeev Gupta <su...@sanjeevg.com>wrote:
> Thanks Ruth.
> I've also been told that the demo data upload in Postgres takes half as
> time
> as MySQL - so I'm guessing that the production env performance should also
> be better with Postgres.
>
> -----
> Rgds
> Sanjeev
> www.sanjeevg.com
> @sanjeevgcom
> --
> View this message in context:
> http://ofbiz.135035.n4.nabble.com/Ofbiz-with-MySQL-or-PostgreSQL-tp4633893p4633907.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>
Re: Ofbiz with MySQL or PostgreSQL
Posted by Sanjeev Gupta <su...@sanjeevg.com>.
Thanks Ruth.
I've also been told that the demo data upload in Postgres takes half as time
as MySQL - so I'm guessing that the production env performance should also
be better with Postgres.
-----
Rgds
Sanjeev
www.sanjeevg.com
@sanjeevgcom
--
View this message in context: http://ofbiz.135035.n4.nabble.com/Ofbiz-with-MySQL-or-PostgreSQL-tp4633893p4633907.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: Ofbiz with MySQL or PostgreSQL
Posted by Ruth Hoffman <rh...@aesolves.com>.
Hi Sanjeev:
If you use MySQL in its current state, be aware of Timestamp truncation.
Since OFBiz uses timestamps in many places as part of a primary-key, you
can (and do) loose precision and ultimately you may not be able to find
records. You may not even know this is happening until it is too late.
I've been told this has been "fixed" in the lasted (beta?) release of
the JDBC driver.
Personally, I prefer Postgres. Never had a problem and the tools for
maintaining it are the best.
Regards,
Ruth Hoffman (http://www.aesolves.com)
On 6/21/12 2:57 PM, Sanjeev Gupta wrote:
> I'm looking to decide on the database to be used with OfBiz in the production
> environment. Any thoughts on which database works well with Ofbiz - MySQL or
> PostgreSQL and why ?
>
> -----
> Rgds
> Sanjeev
> www.sanjeevg.com
> @sanjeevgcom
> --
> View this message in context: http://ofbiz.135035.n4.nabble.com/Ofbiz-with-MySQL-or-PostgreSQL-tp4633893.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>