OFBiz Multi-tenancy
Locked
 
|
Hello OFBizers,
I am evaluating a proposal for development of a SaaS application using OFBiz framework. As I understood from wiki page<https://cwiki.apache.org/confluence/display/OFBIZ/Multitenancy+support> for every tenant a separate data instance is created. The requirement for us is to be able to scale up to ten thousand tenants and more. Typically each tenant will have 1 to 10 users. This gives me all indication that Shared Database and Shared Schema is the way to go in our use case. My question to OFBiz experts is: 1. Is there any other better design to achieve the use case as stated above? 2. Is there any implementation or guideline available for such a use case anywhere? Best Regards Prashant |
|
|
Hi Prashant,
Scaling up your setup to tens of thousands of tenants must be surely a long term goal. Even when considering the number of users being between 1 to 10 for any typical tenant. What would you expect the not-so-typical tenant to have? So, as to you questions: Re 1. Yes, there are others designs that might suit your needs better such as shared database-shared design or even shared database-separate schema. Unfortunately, these variants are not implemented in current feature set of OFBiz, but I might be mistaken. Re 2. As these variants are not implemented (again, I might be mistaken) you won't find any implementation guideline or documentation at the OFBiz site. But googling might help you find what you need. Regards, Pierre Smits *ORRTIZ.COM <http://www.orrtiz.com>* Services & Solutions for Cloud- Based Manufacturing, Professional Services and Retail & Trade http://www.orrtiz.com |
Administrator
|
No Pierre, you are not mistaken, only separate DB by tenant is implemented in OFBiz
Prashant, I'd recommend this article as reference for implementing another way http://msdn.microsoft.com/en-us/library/aa479086.aspx HTH Jacques Le 26/02/2014 14:14, Pierre Smits a écrit : > Hi Prashant, > > Scaling up your setup to tens of thousands of tenants must be surely a long > term goal. Even when considering the number of users being between 1 to 10 > for any typical tenant. What would you expect the not-so-typical tenant to > have? > > So, as to you questions: > Re 1. Yes, there are others designs that might suit your needs better such > as shared database-shared design or even shared database-separate schema. > Unfortunately, these variants are not implemented in current feature set of > OFBiz, but I might be mistaken. > > Re 2. As these variants are not implemented (again, I might be mistaken) > you won't find any implementation guideline or documentation at the OFBiz > site. But googling might help you find what you need. > > Regards, > > Pierre Smits > > *ORRTIZ.COM <http://www.orrtiz.com>* > Services & Solutions for Cloud- > Based Manufacturing, Professional > Services and Retail & Trade > http://www.orrtiz.com > |
|
|
Thank You Pierre Smits and Jacques.
I really appreciate Jacques sharing the link. To answer Pierre Smits *(**What would you expect the not-so-typical tenant to have?)* The most common SaaS plan will be 5 users for a tenant. The maximum users for a tenant will be approximately 100. In case we take up this implementation(Shared Database, Shared Schema) in OFBiz framework, we would also like to contribute back to community. For all those who want to be involved in design discussion please reply back to me (unicast). This is to form a discussion group and facilitate the contribution. Other suggestions are most welcome. Best Regards Prashant On Wed, Feb 26, 2014 at 7:06 PM, Jacques Le Roux < [hidden email]> wrote: > No Pierre, you are not mistaken, only separate DB by tenant is implemented > in OFBiz > > Prashant, I'd recommend this article as reference for implementing another > way > http://msdn.microsoft.com/en-us/library/aa479086.aspx > > HTH > > Jacques > > > Le 26/02/2014 14:14, Pierre Smits a écrit : > > Hi Prashant, >> >> Scaling up your setup to tens of thousands of tenants must be surely a >> long >> term goal. Even when considering the number of users being between 1 to 10 >> for any typical tenant. What would you expect the not-so-typical tenant to >> have? >> >> So, as to you questions: >> Re 1. Yes, there are others designs that might suit your needs better such >> as shared database-shared design or even shared database-separate schema. >> Unfortunately, these variants are not implemented in current feature set >> of >> OFBiz, but I might be mistaken. >> >> Re 2. As these variants are not implemented (again, I might be mistaken) >> you won't find any implementation guideline or documentation at the OFBiz >> site. But googling might help you find what you need. >> >> Regards, >> >> Pierre Smits >> >> *ORRTIZ.COM <http://www.orrtiz.com>* >> Services & Solutions for Cloud- >> Based Manufacturing, Professional >> Services and Retail & Trade >> http://www.orrtiz.com >> >> > |
|
Administrator
|
The simplest way to share would be to create a Jira issue.
Actually we even defined a procedure some years ago: 1) Discuss intial requirements and such in the dev ML [hidden email] 2) When settled create * either a specific page in Confluence wiki (due to spam, you need now to ask for a contributor access) https://cwiki.apache.org/confluence/display/OFBIZ/Home * directly a Jira issue if things are simple enough (could not be the case here...) https://issues.apache.org/jira/secure/Dashboard.jspa?selectPageId=12310800 I might be interested by this... HTH Jacques Le 26/02/2014 17:05, Prashant Sankhla a écrit : > Thank You Pierre Smits and Jacques. > I really appreciate Jacques sharing the link. > > To answer Pierre Smits *(**What would you expect the not-so-typical tenant > to have?)* > The most common SaaS plan will be 5 users for a tenant. The maximum users > for a tenant will be approximately 100. > > In case we take up this implementation(Shared Database, Shared Schema) in > OFBiz framework, we would also like to contribute back to community. For > all those who want to be involved in design discussion please reply back to > me (unicast). This is to form a discussion group and facilitate the > contribution. Other suggestions are most welcome. > > Best Regards > Prashant > > > > > > On Wed, Feb 26, 2014 at 7:06 PM, Jacques Le Roux < > [hidden email]> wrote: > >> No Pierre, you are not mistaken, only separate DB by tenant is implemented >> in OFBiz >> >> Prashant, I'd recommend this article as reference for implementing another >> way >> http://msdn.microsoft.com/en-us/library/aa479086.aspx >> >> HTH >> >> Jacques >> >> >> Le 26/02/2014 14:14, Pierre Smits a écrit : >> >> Hi Prashant, >>> Scaling up your setup to tens of thousands of tenants must be surely a >>> long >>> term goal. Even when considering the number of users being between 1 to 10 >>> for any typical tenant. What would you expect the not-so-typical tenant to >>> have? >>> >>> So, as to you questions: >>> Re 1. Yes, there are others designs that might suit your needs better such >>> as shared database-shared design or even shared database-separate schema. >>> Unfortunately, these variants are not implemented in current feature set >>> of >>> OFBiz, but I might be mistaken. >>> >>> Re 2. As these variants are not implemented (again, I might be mistaken) >>> you won't find any implementation guideline or documentation at the OFBiz >>> site. But googling might help you find what you need. >>> >>> Regards, >>> >>> Pierre Smits >>> >>> *ORRTIZ.COM <http://www.orrtiz.com>* >>> Services & Solutions for Cloud- >>> Based Manufacturing, Professional >>> Services and Retail & Trade >>> http://www.orrtiz.com >>> >>> |
|
|
In reply to this post by Prashant Sankhla
Shared Database and Shared Schema initially sounds great, until you realize
what a pain it will be to do backups/restores of individual tenants. Eventually, a tenant will mess up it's data and will ask you to restore back to date/time. Now you are in a real pickle, unless each tenant has it's own DB and you have a solid point-in-time DB recovery process. Something to think about. On Wed, Feb 26, 2014 at 4:45 AM, Prashant Sankhla <[hidden email]>wrote: > Hello OFBizers, > > I am evaluating a proposal for development of a SaaS application using > OFBiz framework. As I understood from wiki > page< > https://cwiki.apache.org/confluence/display/OFBIZ/Multitenancy+support> > for > every tenant a separate data instance is created. > The requirement for us is to be able to scale up to ten thousand tenants > and more. Typically each tenant will have 1 to 10 users. > > This gives me all indication that Shared Database and Shared Schema is the > way to go in our use case. > > My question to OFBiz experts is: > > 1. Is there any other better design to achieve the use case as stated > above? > 2. Is there any implementation or guideline available for such a use > case anywhere? > > > Best Regards > Prashant > |
|
Administrator
|
Thanks Mike,
That's indeed an interesting perspective! Jacques Le 26/02/2014 20:12, Mike a écrit : > Shared Database and Shared Schema initially sounds great, until you realize > what a pain it will be to do backups/restores of individual tenants. > Eventually, a tenant will mess up it's data and will ask you to restore > back to date/time. Now you are in a real pickle, unless each tenant has > it's own DB and you have a solid point-in-time DB recovery process. > Something to think about. > > > On Wed, Feb 26, 2014 at 4:45 AM, Prashant Sankhla <[hidden email]>wrote: > >> Hello OFBizers, >> >> I am evaluating a proposal for development of a SaaS application using >> OFBiz framework. As I understood from wiki >> page< >> https://cwiki.apache.org/confluence/display/OFBIZ/Multitenancy+support> >> for >> every tenant a separate data instance is created. >> The requirement for us is to be able to scale up to ten thousand tenants >> and more. Typically each tenant will have 1 to 10 users. >> >> This gives me all indication that Shared Database and Shared Schema is the >> way to go in our use case. >> >> My question to OFBiz experts is: >> >> 1. Is there any other better design to achieve the use case as stated >> above? >> 2. Is there any implementation or guideline available for such a use >> case anywhere? >> >> >> Best Regards >> Prashant >> |
|
|
Here is another interesting tidbit regarding restoring to Point-In-Time
recovery (PITR) --and-- databases. Mysql allows PITR at the DB level, where postgresql is an all/nothing (i.e. ALL databases) PITR recovery. So, if you want to do multi-tenant on a big gigantic DB server, the way to go is mysql. If you choose to use postgres, you will have to create a mini postgres server (possibly running on a different port/VM) for each tenant. On Wed, Feb 26, 2014 at 1:35 PM, Jacques Le Roux < [hidden email]> wrote: > Thanks Mike, > > That's indeed an interesting perspective! > > Jacques > > Le 26/02/2014 20:12, Mike a écrit : > > Shared Database and Shared Schema initially sounds great, until you >> realize >> what a pain it will be to do backups/restores of individual tenants. >> Eventually, a tenant will mess up it's data and will ask you to restore >> back to date/time. Now you are in a real pickle, unless each tenant has >> it's own DB and you have a solid point-in-time DB recovery process. >> Something to think about. >> >> >> On Wed, Feb 26, 2014 at 4:45 AM, Prashant Sankhla <[hidden email] >> >wrote: >> >> Hello OFBizers, >>> >>> I am evaluating a proposal for development of a SaaS application using >>> OFBiz framework. As I understood from wiki >>> page< >>> https://cwiki.apache.org/confluence/display/OFBIZ/Multitenancy+support> >>> for >>> every tenant a separate data instance is created. >>> The requirement for us is to be able to scale up to ten thousand tenants >>> and more. Typically each tenant will have 1 to 10 users. >>> >>> This gives me all indication that Shared Database and Shared Schema is >>> the >>> way to go in our use case. >>> >>> My question to OFBiz experts is: >>> >>> 1. Is there any other better design to achieve the use case as stated >>> above? >>> 2. Is there any implementation or guideline available for such a use >>> case anywhere? >>> >>> >>> Best Regards >>> Prashant >>> >>> |
|
|
In reply to this post by Mike Z
From a business perspective, if wanting to resell OFBiz hosting, wont the perspective of maintaining individual DB's for each client quickly become a mega-complex undertaking with accompanying high maintenance expences? I can only imagine trying to maintain 1,000 DB's for a small client pool of 1,000 clients wouldn't be the easiest thing to do, so I don't think the ecomics supports the business model of large pool of individual DB tenants easily.
Just my humble thoughts..... CC -----Original Message----- From: Mike [mailto:[hidden email]] Sent: Wednesday, February 26, 2014 2:12 PM To: user Subject: Re: OFBiz Multi-tenancy Shared Database and Shared Schema initially sounds great, until you realize what a pain it will be to do backups/restores of individual tenants. Eventually, a tenant will mess up it's data and will ask you to restore back to date/time. Now you are in a real pickle, unless each tenant has it's own DB and you have a solid point-in-time DB recovery process. Something to think about. On Wed, Feb 26, 2014 at 4:45 AM, Prashant Sankhla <[hidden email]>wrote: > Hello OFBizers, > > I am evaluating a proposal for development of a SaaS application using > OFBiz framework. As I understood from wiki page< > https://cwiki.apache.org/confluence/display/OFBIZ/Multitenancy+support > > > for > every tenant a separate data instance is created. > The requirement for us is to be able to scale up to ten thousand > tenants and more. Typically each tenant will have 1 to 10 users. > > This gives me all indication that Shared Database and Shared Schema is > the way to go in our use case. > > My question to OFBiz experts is: > > 1. Is there any other better design to achieve the use case as stated > above? > 2. Is there any implementation or guideline available for such a use > case anywhere? > > > Best Regards > Prashant > |
|
|
just for sharing
here is an about multi-tenant architecture on force.com / salesforce.com https://wiki.developerforce.com/page/Multi_Tenant_Architecture We are currently customizing ofbiz to cater multi-tenant using shared DB which resulting to below process flow Use Case 1. Tenant A as the master Tenant 2. Tenant B, Tenant C as the child Tenant that has parties relationship to Tenant A Tenant A can view all data from Tenant B and Tenant C Tenant B can view its own data. Tenant C can view its own data. e.x. Product Feature (existing) Product Feature Role (new entity) and then create a view-entity joining the two tables in the screen/form, we changes all the entity calls to this new view-entity. And then we create an intermediate screen, forcing end-user to choose its "organization/tenant". And thus this partyId will pass into the view-entity. Thus, we are using the ProductFeatureRole to control the data is belong to which tenant. And, we use the party relationship to define which tenant can view whom tenant data. Thus, we heading to the direction of One ofbiz instance, one tenant(Master tenant), multiple organization (child tenant) But, having said that, IMHO, i still favor the multi instance(application and database) approach to cater multi tenant. IMHO, Because i think thats the way to go for cloud. But of course, the costing on the cloud service provider are a factor. On Thu, Feb 27, 2014 at 9:08 AM, Carlos Cruz <[hidden email]>wrote: > From a business perspective, if wanting to resell OFBiz hosting, wont > the perspective of maintaining individual DB's for each client quickly > become a mega-complex undertaking with accompanying high maintenance > expences? I can only imagine trying to maintain 1,000 DB's for a small > client pool of 1,000 clients wouldn't be the easiest thing to do, so I > don't think the ecomics supports the business model of large pool of > individual DB tenants easily. > > Just my humble thoughts..... > > CC > > -----Original Message----- > From: Mike [mailto:[hidden email]] > Sent: Wednesday, February 26, 2014 2:12 PM > To: user > Subject: Re: OFBiz Multi-tenancy > > Shared Database and Shared Schema initially sounds great, until you > realize what a pain it will be to do backups/restores of individual tenants. > Eventually, a tenant will mess up it's data and will ask you to restore > back to date/time. Now you are in a real pickle, unless each tenant has > it's own DB and you have a solid point-in-time DB recovery process. > Something to think about. > > > On Wed, Feb 26, 2014 at 4:45 AM, Prashant Sankhla <[hidden email] > >wrote: > > > Hello OFBizers, > > > > I am evaluating a proposal for development of a SaaS application using > > OFBiz framework. As I understood from wiki page< > > https://cwiki.apache.org/confluence/display/OFBIZ/Multitenancy+support > > > > > for > > every tenant a separate data instance is created. > > The requirement for us is to be able to scale up to ten thousand > > tenants and more. Typically each tenant will have 1 to 10 users. > > > > This gives me all indication that Shared Database and Shared Schema is > > the way to go in our use case. > > > > My question to OFBiz experts is: > > > > 1. Is there any other better design to achieve the use case as stated > > above? > > 2. Is there any implementation or guideline available for such a use > > case anywhere? > > > > > > Best Regards > > Prashant > > > > -- Disclaimer : This E-mail is intended only for the use of the individual or entity named above and may contain information that is confidential. If you are not the intended recipients, please immediately notify us by return email and delete it from your system. Any unauthorised dissemination, distribution or copying of this email is strictly prohibited. Thank You. |
|
|
>Shared Database and Shared Schema initially sounds great, until you realize what a pain it will be to do backups/restores of individual tenants.
> Eventually, a tenant will mess up it's data and will ask you to restore back to date/time. Now you are in a real pickle, unless each tenant has it's own DB and you have a solid point-in-time DB recovery process. Mike is correct here. Shared DB is not going to work with Multi-tenant architecture. Here is an excellent article from an Enterprise DB guy regarding Multi-tenancy: http://rhaas.blogspot.com/2010/07/multi-tenancy-and-virtualization.html >Mysql allows PITR at the DB level, where postgresql is an all/nothing (i.e.ALL databases) PITR recovery. So, if you want to do multi-tenant on a >big gigantic DB server, the way to go is mysql. Mike, you have probably missed an important feature of Postgres (Write-ahead logging) which is years ahead of Oracle and MySql in PITR. Following articles are explain WAL feature in detail: http://www.postgresql.org/docs/9.3/static/continuous-archiving.html http://blog.ganneff.de/blog/2008/02/15/postgresql-continuous-archivin.html > If you choose to use postgres, you will have to create a mini postgres server (possibly running on a different port/VM) for each tenant. Please go through Multi-tenancy vs virtualization http://rhaas.blogspot.com/2010/07/multi-tenancy-and-virtualization.html Many organizations such as enterprize-db and hub.org are serving thousands of customers each having different databases and schemas with a single postgres instance. In my opinion, choosing postgres is not a bad idea for multi-tenant architecture. Regards: Ejaz Ahmed |
|
|
Hi all,
Thank you for submitting links to documents related to the subject. Of course, for each the criteria might vary and weigh differently, and the options available in current feature set of OFBiz are limited. But in whole, the cost of operations are key. These cost of operations not only include the hardware and software cost, but also the effort of maintaining the environment. While setting up a new tenant is easy, and maintaining the happy flow (uptime and backup), the crucial factors in this are the performance of the persistence engine and recovery cost in case of unexpected data loss. As many (on the internet) seem to be agreeing on the recovery cost in the case of a shared database-shared schema approach can be expected to be high due to the intricacies of the beast as opposed to a 'separate database-separate schema' setup. Yes, multiple databases might lead to a performance overhead and higher maintenance cost, but a side-by-side comparison of the various aspects of each approach (separate db-separate schema vs shared db-separate schema vs shared db-shared schema) is something that would surely help in assessing what would be the best approach in various scenarios. If that would be available, then a sound roadmap could be devised for this subject. Regards, Pierre Smits *ORRTIZ.COM <http://www.orrtiz.com>* Services & Solutions for Cloud- Based Manufacturing, Professional Services and Retail & Trade http://www.orrtiz.com |
|
|
Just my 0.02 here:
My understanding is still that the delegator handling might not be robust enough on OOTB OFBiz. When it comes to backend access every too often I have issues to see proper data or even logging in. Respective JIRA is open. Multiple DB needs to be the solution. Such is also leveraging a depend-nothing concept better than shared DB. Sharding multiple OFBiz instances to manage the traffic will be also easier, not speaking about upgradding ti dedicated instances etc. Re postgres I feel tenant-level backups into XML using OFBiz on-board tools might anyways be more efficient. The subset of entities that are populated into OFBiz by Tenant use are pretty clear. Regards Carsten Gesendet von unterwegs mit BlackBerry® Webmail. -----Original Message----- From: Pierre Smits <[hidden email]> Date: Thu, 27 Feb 2014 12:57:54 To: <[hidden email]> Reply-To: [hidden email] Subject: Re: OFBiz Multi-tenancy Hi all, Thank you for submitting links to documents related to the subject. Of course, for each the criteria might vary and weigh differently, and the options available in current feature set of OFBiz are limited. But in whole, the cost of operations are key. These cost of operations not only include the hardware and software cost, but also the effort of maintaining the environment. While setting up a new tenant is easy, and maintaining the happy flow (uptime and backup), the crucial factors in this are the performance of the persistence engine and recovery cost in case of unexpected data loss. As many (on the internet) seem to be agreeing on the recovery cost in the case of a shared database-shared schema approach can be expected to be high due to the intricacies of the beast as opposed to a 'separate database-separate schema' setup. Yes, multiple databases might lead to a performance overhead and higher maintenance cost, but a side-by-side comparison of the various aspects of each approach (separate db-separate schema vs shared db-separate schema vs shared db-shared schema) is something that would surely help in assessing what would be the best approach in various scenarios. If that would be available, then a sound roadmap could be devised for this subject. Regards, Pierre Smits *ORRTIZ.COM <http://www.orrtiz.com>* Services & Solutions for Cloud- Based Manufacturing, Professional Services and Retail & Trade http://www.orrtiz.com |
|
|
It would be better to create a JIRA issue for this requirement gathering.
Pierre, There are many articles on separate db-separate schema vs shared db-separate schema vs shared db-shared schema approach. Here is an extract from http://cloudcomputing.sys-con.com/node/1610582 Attribute Separate Database Separate Schema Separate Rows Tenants are large enterprise customers who could store large amounts of data in TB or 100s of GB Good Fair Poor Tenants are individual consumers with low or moderate storage of data like a social networking site Poor Fair Good Rapid provisioning is the key, risk of losing business exists if the response is not quick, customers select the service on the fly Fair Fair Good Customers can opt out of (Cancel ) service at a faster rate and space reclamation is not a concern Good Good Fair Customer can opt out of (Cancel) service at a faster rate and space reclamation is a concern Good Good Poor The business logic of your SaaS application is highly customized with respect to the Tenant Good Good Poor Application is prone to database locks Good Fair Poor Security and Legal Requirements require data separation, even if the application controls it Good Fair Poor Performance tuning is a concern and reports performance should be based on the volume of data Good Fair Poor Administration and maintenance of Schema and database code is a concern Poor Poor Good Scalability is a concern Good Fair Poor Frequent Changes to the Application Possible Poor Poor Good Data is mission critical and Point In Time Recovery is needed in case of a crash Good Fair Poor Looking at above VS chart, it is clear that separate database is going to fit the requirements of ofbiz. Regards: Ejaz Ahmed > Subject: Re: OFBiz Multi-tenancy > To: [hidden email] > From: [hidden email] > Date: Thu, 27 Feb 2014 12:31:06 +0000 > > Just my 0.02 here: > > My understanding is still that the delegator handling might not be robust enough on OOTB OFBiz. When it comes to backend access every too often I have issues to see proper data or even logging in. Respective JIRA is open. > > Multiple DB needs to be the solution. Such is also leveraging a depend-nothing concept better than shared DB. Sharding multiple OFBiz instances to manage the traffic will be also easier, not speaking about upgradding ti dedicated instances etc. > > Re postgres I feel tenant-level backups into XML using OFBiz on-board tools might anyways be more efficient. The subset of entities that are populated into OFBiz by Tenant use are pretty clear. > > Regards > > > Carsten > > Gesendet von unterwegs mit BlackBerry® Webmail. > > -----Original Message----- > From: Pierre Smits <[hidden email]> > Date: Thu, 27 Feb 2014 12:57:54 > To: <[hidden email]> > Reply-To: [hidden email] > Subject: Re: OFBiz Multi-tenancy > > Hi all, > > Thank you for submitting links to documents related to the subject. > > Of course, for each the criteria might vary and weigh differently, and the > options available in current feature set of OFBiz are limited. > > But in whole, the cost of operations are key. These cost of operations not > only include the hardware and software cost, but also the effort of > maintaining the environment. While setting up a new tenant is easy, and > maintaining the happy flow (uptime and backup), the crucial factors in this > are the performance of the persistence engine and recovery cost in case of > unexpected data loss. > As many (on the internet) seem to be agreeing on the recovery cost in the > case of a shared database-shared schema approach can be expected to be high > due to the intricacies of the beast as opposed to a 'separate > database-separate schema' setup. > > Yes, multiple databases might lead to a performance overhead and higher > maintenance cost, but a side-by-side comparison of the various aspects of > each approach (separate db-separate schema vs shared db-separate schema vs > shared db-shared schema) is something that would surely help in assessing > what would be the best approach in various scenarios. > > If that would be available, then a sound roadmap could be devised for this > subject. > > Regards, > > > Pierre Smits > > *ORRTIZ.COM <http://www.orrtiz.com>* > Services & Solutions for Cloud- > Based Manufacturing, Professional > Services and Retail & Trade > http://www.orrtiz.com |
|
|
I was not aware that mail man forbids html display. This table is distorted in plain text emails. It can be viewed here:
http://cloudcomputing.sys-con.com/node/1610582 Regards: Ejaz Ahmed > From: [hidden email] > To: [hidden email] > Subject: RE: OFBiz Multi-tenancy > Date: Thu, 27 Feb 2014 15:08:55 +0000 > > It would be better to create a JIRA issue for this requirement gathering. > > Pierre, > > There are many articles on separate db-separate schema vs shared db-separate schema vs > shared db-shared schema approach. Here is an extract from http://cloudcomputing.sys-con.com/node/1610582 > > > Attribute > > > > Separate Database > > > > Separate Schema > > > > Separate Rows > > > > > > Tenants are large enterprise customers who could store large amounts of data in TB or 100s of GB > > > > Good > > > > Fair > > > > Poor > > > > > > Tenants are individual consumers with low or moderate storage of data like a social networking site > > > > Poor > > > > Fair > > > > Good > > > > > > Rapid provisioning is the key, risk of losing business exists if the > response is not quick, customers select the service on the fly > > > > Fair > > > > Fair > > > > Good > > > > > > Customers can opt out of (Cancel ) service at a faster rate and space reclamation is not a concern > > > > Good > > > > Good > > > > Fair > > > > > > Customer can opt out of (Cancel) service at a faster rate and space reclamation is a concern > > > > Good > > > > Good > > > > Poor > > > > > > The business logic of your SaaS application is highly customized with respect to the Tenant > > > > Good > > > > Good > > > > Poor > > > > > > Application is prone to database locks > > > > Good > > > > Fair > > > > Poor > > > > > > Security and Legal Requirements require data separation, even if the application controls it > > > > Good > > > > Fair > > > > Poor > > > > > > Performance tuning is a concern and reports performance should be based on the volume of data > > > > Good > > > > Fair > > > > Poor > > > > > > Administration and maintenance of Schema and database code is a concern > > > > Poor > > > > Poor > > > > Good > > > > > > Scalability is a concern > > > > Good > > > > Fair > > > > Poor > > > > > > Frequent Changes to the Application Possible > > > > Poor > > > > Poor > > > > Good > > > > > > Data is mission critical and Point In Time Recovery is needed in case of a crash > > > > Good > > > > Fair > > > > Poor > > Looking at above VS chart, it is clear that separate database is going to fit the requirements of ofbiz. > > > Regards: > > Ejaz Ahmed > > > > > > > Subject: Re: OFBiz Multi-tenancy > > To: [hidden email] > > From: [hidden email] > > Date: Thu, 27 Feb 2014 12:31:06 +0000 > > > > Just my 0.02 here: > > > > My understanding is still that the delegator handling might not be robust enough on OOTB OFBiz. When it comes to backend access every too often I have issues to see proper data or even logging in. Respective JIRA is open. > > > > Multiple DB needs to be the solution. Such is also leveraging a depend-nothing concept better than shared DB. Sharding multiple OFBiz instances to manage the traffic will be also easier, not speaking about upgradding ti dedicated instances etc. > > > > Re postgres I feel tenant-level backups into XML using OFBiz on-board tools might anyways be more efficient. The subset of entities that are populated into OFBiz by Tenant use are pretty clear. > > > > Regards > > > > > > Carsten > > > > Gesendet von unterwegs mit BlackBerry® Webmail. > > > > -----Original Message----- > > From: Pierre Smits <[hidden email]> > > Date: Thu, 27 Feb 2014 12:57:54 > > To: <[hidden email]> > > Reply-To: [hidden email] > > Subject: Re: OFBiz Multi-tenancy > > > > Hi all, > > > > Thank you for submitting links to documents related to the subject. > > > > Of course, for each the criteria might vary and weigh differently, and the > > options available in current feature set of OFBiz are limited. > > > > But in whole, the cost of operations are key. These cost of operations not > > only include the hardware and software cost, but also the effort of > > maintaining the environment. While setting up a new tenant is easy, and > > maintaining the happy flow (uptime and backup), the crucial factors in this > > are the performance of the persistence engine and recovery cost in case of > > unexpected data loss. > > As many (on the internet) seem to be agreeing on the recovery cost in the > > case of a shared database-shared schema approach can be expected to be high > > due to the intricacies of the beast as opposed to a 'separate > > database-separate schema' setup. > > > > Yes, multiple databases might lead to a performance overhead and higher > > maintenance cost, but a side-by-side comparison of the various aspects of > > each approach (separate db-separate schema vs shared db-separate schema vs > > shared db-shared schema) is something that would surely help in assessing > > what would be the best approach in various scenarios. > > > > If that would be available, then a sound roadmap could be devised for this > > subject. > > > > Regards, > > > > > > Pierre Smits > > > > *ORRTIZ.COM <http://www.orrtiz.com>* > > Services & Solutions for Cloud- > > Based Manufacturing, Professional > > Services and Retail & Trade > > http://www.orrtiz.com > > |
|
|
In reply to this post by Ejaz Ahmed
>>Mysql allows PITR at the DB level, where postgresql is an all/nothing
(i.e.ALL databases) PITR recovery. So, if you want to do multi-tenant on a >big gigantic DB server, the way to go is mysql. >Mike, you have probably missed an important feature of Postgres (Write-ahead logging) which is >years ahead of Oracle and MySql in PITR. Following articles are explain WAL feature in detail: I'm intimately familiar with postgresql's WAL files, and their limitations. The WALs are system-wide, not DB specific. You can always do a normal dump of a postgres DB, which is a snapshot at the time of the dump, but point-in-time recovery is different. For example, everything was running great until junior DBA accidentally dropped the 'user' table at 1:05PM. A point-in-time recovery allows you to restore to 1:04PM, right before it happened. If I was running a SAAS and provided that capability, then that is an added value of that service. On Wed, Feb 26, 2014 at 10:53 PM, Ejaz Ahmed <[hidden email]> wrote: > >Shared Database and Shared Schema initially sounds great, until you > realize what a pain it will be to do backups/restores of individual tenants. > > Eventually, a tenant will mess up it's data and will ask you to restore > back to date/time. Now you are in a real pickle, unless each tenant has > it's own DB and you have a solid point-in-time DB recovery process. > > Mike is correct here. Shared DB is not going to work with Multi-tenant > architecture. Here is an excellent article from an Enterprise DB guy > regarding Multi-tenancy: > http://rhaas.blogspot.com/2010/07/multi-tenancy-and-virtualization.html > > > >Mysql allows PITR at the DB level, where postgresql is an all/nothing > (i.e.ALL databases) PITR recovery. So, if you want to do multi-tenant on a > >big gigantic DB server, the way to go is mysql. > > Mike, you have probably missed an important feature of Postgres > (Write-ahead logging) which is years ahead of Oracle and MySql in PITR. > Following articles are explain WAL feature in detail: > > http://www.postgresql.org/docs/9.3/static/continuous-archiving.html > http://blog.ganneff.de/blog/2008/02/15/postgresql-continuous-archivin.html > > > If you choose to use postgres, you will have to create a mini postgres > server (possibly running on a different port/VM) for each tenant. > Please go through Multi-tenancy vs virtualization > http://rhaas.blogspot.com/2010/07/multi-tenancy-and-virtualization.html > > Many organizations such as enterprize-db and hub.org are serving > thousands of customers each having different databases and schemas with a > single postgres instance. > > In my opinion, choosing postgres is not a bad idea for multi-tenant > architecture. > > > Regards: > > Ejaz Ahmed > > > > > > > |
|
|
Mike,
You are right that postgres out of the box does not provide this solution but you can still achieve this by: 1. Setup a hot standby server (through WAL logs) 2. When a table is dropped on master at time x, replay the standby server to time y just before the table was dropped. 3. pg_dump the the table from standby server which was dropped from the master. 4. Restore it back to the master. The above process is arguably time consuming. However the commercial toolse for this are also available like potgres iDataAgent and xDB replication server. Regards: Ejaz Ahmed |
|
Administrator
|
In reply to this post by c.schinzer-2
Inline...
Le 27/02/2014 13:31, [hidden email] a écrit : > Just my 0.02 here: > > My understanding is still that the delegator handling might not be robust enough on OOTB OFBiz. When it comes to backend access every too often I have issues to see proper data or even logging in. Respective JIRA is open. Do you know the Jira number? I'm surprised by this. Because I have recently worked on a relatively big project (3 Nginx front end servers, 5 web apps servers, 3 Postgres DB servers of which 2 read only slaves handled by PGPool II) during 2,5 years (based on something near R11.04) and I never experienced any problems when the DB was correctly settled (happened once that new DBAs broke it) Jacques > > Multiple DB needs to be the solution. Such is also leveraging a depend-nothing concept better than shared DB. Sharding multiple OFBiz instances to manage the traffic will be also easier, not speaking about upgradding ti dedicated instances etc. > > Re postgres I feel tenant-level backups into XML using OFBiz on-board tools might anyways be more efficient. The subset of entities that are populated into OFBiz by Tenant use are pretty clear. > > Regards > > > Carsten > > Gesendet von unterwegs mit BlackBerry® Webmail. > > -----Original Message----- > From: Pierre Smits <[hidden email]> > Date: Thu, 27 Feb 2014 12:57:54 > To: <[hidden email]> > Reply-To: [hidden email] > Subject: Re: OFBiz Multi-tenancy > > Hi all, > > Thank you for submitting links to documents related to the subject. > > Of course, for each the criteria might vary and weigh differently, and the > options available in current feature set of OFBiz are limited. > > But in whole, the cost of operations are key. These cost of operations not > only include the hardware and software cost, but also the effort of > maintaining the environment. While setting up a new tenant is easy, and > maintaining the happy flow (uptime and backup), the crucial factors in this > are the performance of the persistence engine and recovery cost in case of > unexpected data loss. > As many (on the internet) seem to be agreeing on the recovery cost in the > case of a shared database-shared schema approach can be expected to be high > due to the intricacies of the beast as opposed to a 'separate > database-separate schema' setup. > > Yes, multiple databases might lead to a performance overhead and higher > maintenance cost, but a side-by-side comparison of the various aspects of > each approach (separate db-separate schema vs shared db-separate schema vs > shared db-shared schema) is something that would surely help in assessing > what would be the best approach in various scenarios. > > If that would be available, then a sound roadmap could be devised for this > subject. > > Regards, > > > Pierre Smits > > *ORRTIZ.COM <http://www.orrtiz.com>* > Services & Solutions for Cloud- > Based Manufacturing, Professional > Services and Retail & Trade > http://www.orrtiz.com > > > |
«
Return to OFBiz - User
|
1 view|%1 views
| Free forum by Nabble | Edit this page |