RE: dropwizard in a multi-tenant environment

[S Ahmed]

When dealing with a multi-tenant environment, many issues can come up if you don't set things up correctly.

I was wondering if those of you who have large scale DW deployments, how do you go about setting up DW in terms of multiple db connections?

1. Say you have 10 databases to connect to, do you create 10 session contexts?

2. If you set your active connections to high, say 100, then if you have 10 servers that means you have 10x100 active connections into mysql.  What have you found to be a reasonable maximum?

3. Is it easier to somehow direct traffic to a group of DW servers that all connect to a single instance?  ie. spread traffic at the dns level somehow based on subdomain or the like.

[Nathan Fisher]

Hi,

Are you trying to architect your platform or integrate with an existing brownfield project?  Are the databases shards for scaling or are they distinct datasets?

My quick and dirty response with the questions you've asked thus far;

1. If you're connecting to that many DB's I think you're no longer in the land of micro-service architecture.

2. You'd need to test it with your queries.  Define the desired performance 

3. Use a reverse-proxy or load balancer, DNS load balancing should be your last resort (http://www.tenereillo.com/GSLBPageOfShame.htm).

Kind Regards,

Nathan

--
You received this message because you are subscribed to the Google Groups "dropwizard-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dropwizard-us...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
Nathan Fisher

 w: http://junctionbox.ca/

[S Ahmed]

Hi Nathan,

Those are some good points.

>1. 

It is true, my idea was to combine multiple services together initially and then when traffic warrants break them out into separate DW services.  The reason being is that I might realize later on that certain services can or can't be broken out (based on my initial guesses).

But I do want to have some sort of ClusterStrategy object that will give me references to any external services I might need like mysql, redis etc., so I can in the future when I need to add additional mysql servers I can just modify this class that is used throughout my application already.

[Gökhan Berberoğlu]

Hi Nathan,

We're investigating DW for a new project..  We were planning to use Hibernate's separate database per tenant approach, possibly with DW.. Your answer  #1 answer is not clear to me, do you think DW is not ok for multi-tenant / multi-db environments ?

I assumed since multi-tenancy is natively supported with hibernate it should just work with DW, however after OP's post I realize that won't be the case.. I cannot find any information regarding DW & multi-tenancy, which is quite worrying.. 

Can you elaborate your answer please ? 

thanks!

[Nathan Fisher]

Hi Gökhan,

There's no reason you cannot do it with Dropwizard (that I'm aware of). I just don't think it's the target fit for dropwizard. The primary focus of it is restful web-services that are easily deployed and monitored. Consider the context and organisation where it was built (Yammer/back end services) and that will give you a sense of what its target application is.

All that being said dropwizard has done a pretty good job of not limiting your options. Folks have successfully integrated a number of components into dropwizard that weren't included (eg spring, various DB orms, templating engines, etc). So the can in my mind is yes.

Questions I would pose;

1) how many databases and clients are you looking at?

2) is this likely to grow in the future, by how much?

3) is the schema uniform across databases?

4) what's the expected RPS per client/database and is it relatively uniform?

5) do you have sla latency requirements?

6) do you have sla availability requirements?

Kind Regards,

Nathan

For more options, visit https://groups.google.com/d/optout.

[Graham O'Regan]

Dropwizard doesn’t support Hibernate’s multi-tenancy feature OOTB, when sessions are created using @UnitOfWork the tenant’s identifier isn’t known so it cannot be passed to the SessionFactory’s options. If you wanted to add support you could modify the UnitOfWorkRequestDispatcher to extract the tenant’s 

identifier from the HttpContext and pass it through when the session is created?

[Gökhan Berberoğlu]

Hi Nathan,

thanks for the quick reply!

I wanted to understand exacly why do you think it was not the fit for it, but after spending a few days on the case I think I was confused by OP's question.. I understand if you need simultaneous db connection to 10 different db session simultaneously then it's questionable for micro-service architecture.

Our solution is not targeted to consumers so number of tenants will be low, in hundreds for a long time. Tenants will share most of the tables seperated by "tenantid" column, but for some big / bulk data like "audit trails" there are requirements to store in customer specific databases. 

So in a our "multi-tenant" setup, a given time there will be two db connection at most: one for shared db (the customer accounts etc) and one for tenant db (all tenant specific data).. I can easy inject a different db session (with jdbi) to per-tenant dao's.. We'll create a simple PoC to validate the idea. I'm almost sure it's doable but my concern was if DW's design decisions or future roadmap (if any..) is targeting different type of use-cases.

To unsubscribe from this group and stop receiving emails from it, send an email to dropwizard-user+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Gökhan Berberoğlu]

thank for pointing me UnitOfWorkRequestDispatcher.. will definitelly try.   

To unsubscribe from this group and stop receiving emails from it, send an email to dropwizard-user+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[S Ahmed]

That was one of my use cases also, most of the tables are shared between the tenants BUT some tables will have potentially millions of rows and I wanted to have seperate tables per customer for these cases which would reside on a different db (or schema if I use postgresql).

[Lee Carraher]

Have you had any luck getting multi tenancy to work?

I tried editing io.dropwizard.hibernate.UnitOfWorkRequestDispatcher as 
public void dispatch(Object resource, HttpContext context) {
        //final Session session = sessionFactory.openSession();
        final Session session = sessionFactory.withOptions().tenantIdentifier("Tenant1").openSession();

Which fails since the datasource is not configured for multi tenant, which lead me to add:
        configuration.setProperty("hibernate.multiTenancy","SCHEMA");
        configuration.setProperty(AvailableSettings.MULTI_TENANT_IDENTIFIER_RESOLVER, "com.company.CurrentTenantIdentifierResolverImpl");
        configuration.setProperty("hibernate.tenant_identifier_resolver","com.company.MultiTenantConnectionProviderImpl");
to buildSessionFactory io.dropwizard.hibernate.SessionFactoryFactory

and implemented CurrentTenantIdentifierResolverImpl and MultiTenantConnectionProviderImpl as in
http://blog.sandeepgupta.info/2014/07/making-application-multi-tenant-with.html
(stripping out any spring only stuff)
however i still get a null pointer somewhere deep in the stack trace that i can't resolve.

Has anyone had any luck getting multitenancy to work?

[Yun Zhi Lin]

We are also getting Null pointer exception. It may be the same issue.

It appears that every time "hibernate.multiTenancy" is set, either the dialect or JdbcService will give NullPointer exception. The error can be narrowed down to line 1887 to 1888 in org.hibernate.cfg.Configuration.java:

final Dialect dialect = serviceRegistry.getService( JdbcServices.class ).getDialect();
dialect.contributeTypes( typeContributions, serviceRegistry );

[dan...@trunkplatform.com]

We found workaround for this. We created a new annotation called MultiTenantUnitOfWork and MultiTenantUnitOfWorkApplicationListener that is based on UnitOfWork and UnitOfWorkApplicationListener.

The difference is inside the MultiTenantUnitOfWorkApplicationListener onEvent method:

        @Override

        public void onEvent(RequestEvent event) {

            if (event.getType() == RequestEvent.Type.RESOURCE_METHOD_START) {

                this.unitOfWork = this.methodMap.get(event.getUriInfo()

                        .getMatchedResourceMethod().getInvocable().getDefinitionMethod());

                if (unitOfWork != null) {

                    this.session = this.sessionFactory.openSession();

                    setSchema(); //set the schema to the connection

                    try {

                        configureSession();

                        ManagedSessionContext.bind(this.session);

                        beginTransaction();

                    } catch (Throwable th) {

                        this.session.close();

                        this.session = null;

                        ManagedSessionContext.unbind(this.sessionFactory);

                        throw th;

                    }

                }

            } else if (event.getType() == RequestEvent.Type.RESOURCE_METHOD_FINISHED) {

                if (this.session != null) {

                    try {

                        commitTransaction();

                    } catch (Exception e) {

                        rollbackTransaction();

                        this.<RuntimeException>rethrow(e);

                    } finally {

                        this.session.close();

                        this.session = null;

                        ManagedSessionContext.unbind(this.sessionFactory);

                    }

                }

            } else if (event.getType() == RequestEvent.Type.ON_EXCEPTION) {

                if (this.session != null) {

                    try {

                        rollbackTransaction();

                    } finally {

                        this.session.close();

                        this.session = null;

                        ManagedSessionContext.unbind(this.sessionFactory);

                    }

                }

            }

        }

        private void setSchema() {

            this.session.doWork(connection -> {

                String schemaStatement = null;

                final String tenantSchema = <YOUR_SCHEMA>;

                switch (dataSourceFactory.getProperties().get("hibernate.dialect")) {

                    case "org.hibernate.dialect.PostgreSQL9Dialect":

                        schemaStatement = String.format("SET SEARCH_PATH = %s", tenantSchema);

                        break;

                    default:

                        schemaStatement = String.format("SET SCHEMA = %s", tenantSchema);

                }

                PreparedStatement preparedStatement = connection.prepareStatement(schemaStatement);

                preparedStatement.execute();

            });

        }

The idea is to use the connection from the opened session and set the schema by using the connection. But the fallback is you need to specify each of the SQL statement because most of the jdbc connections do not implement setSchema() method that might be useful.

[Vaibhav Dhawan]

Hi, 

While this post is ancient now, i am wondering if there is a solution for this. I am in a situation where in i need to achieve multi tenancy and each tenant would have a separate database. My DW services use hibernate

Thanks

[Yun Zhi Lin]

Have you tried Daniel's code snippet above your post, which was a working solution using hibernate.

[Vaibhav Dhawan]

Thank you for the reply.

Yes, i did look at the snippet posted by Daniel. However, i am looking to achieve multi tenancy at the database. Hence, i will need to store these different configurations in the yml file and then re-write most of dropwizard hibernate right from HibernateBundle to AbstractDao?

-Vaibhav

[Jean Michel]

While I haven't done it with DW per se, I've done it with Spring Boot, which is kinda similar. Most of what needs to be done, though, is handled by Hibrnate. Take a look at org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider:

   https://docs.jboss.org/hibernate/orm/4.2/devguide/en-US/html/ch16.html

-JM

[Sean Marshall]

Vaibhav,

Are you tied to Hibernate for your solution? I have successfully setup multi-tenancy (multi-tenant schemas) with Dropwizard, HikariCP, jOOQ, and PostgreSQL. I also use FlywayDB to handle schema versioning to auto-update each tenant schema.

Let me know if you are interested in understanding this approach.

Regards,

Sean Marshall

[Vaibhav Dhawan]

Hi Sean, 

Yes, i am tied up to a hibernate solution. There are 100 odd services that are already on production pointing to a single database. Migrating to jOOQ and PostgreSQL would be as complex as migrating to Spring boot. 

I did fork the jOOQ bundle present here https://github.com/tbugrara/dropwizard-jooq and went through the approach already. 

I tried to follow a similar approach and started customizing dropwizard hibernate. Facing exceptions deep in the stack that i cant figure out currently. 

Best, 

Vaibhav

[Phil Busch]

This sounds like a logical place to split your application - you will pay lots of overhead managing this solution IMO. Have you considered a microservice paradigm, with one service per database schema?

[schwa...@gmail.com]

I agree with Phil. This sounds like a logical place to split the application into micro services.

[Vaibhav Dhawan]

Thank your guys for pitching in. 

Agree with you guys. I am planning to split these into micro services. Not sure if the application will allow me to do 1 schema per service but i will split them up logically. 

A little complexity on top of the volume of services is that it is supposed to be a SaaS application and few customers will like to keep the database on their servers (albeit in the same structure that the services need them). 

Hence, i will need a way out to have a router somewhere in the services which routes to the correct client database (and then different schemas with in that database depending upon the micro-service).

Best, 

Vaibhav