Hi,
I am facing an scaling issue in my application.First let me give a basic idea about the application and how it works.
First there is an master database where info about the all the clients is stored.
Then, for each of my client I create a 2 separate databases on signup(on the fly), one for his live data and the other for his transactional data.
Now, the number of model classes for the live databases are around 150 each. So, the time consumed by SessionFactory to build is pretty large.But, as it happens only the first time, I can live with it.
But, the memory consumed by the SessionFactory is pretty huge.
Now when I have 10 clients signed up and live, I will have a total of : 1 (master database) + 2*10 (individual client) = 21 sessionfactory objects in memory, and that too 10 of them being pretty heavy (150 classes each ).
Can someone please help me with some strategy of lowering my memory footprint?
Also is there any way of having an single SessionFactory instance live for my 10 clients (structure of db is same), like changing the connection made to the actual database in mysql on the fly, (keeping all other parameters like classes, username, password etc. same) ?? I couldn't find any

Thanks

I would say you MUST have one session factory for everyone connecting to one database. Having one session factory per each client would lead you to nightmare. Just use hibernate to get a new session instead of creating new factory for each of the users.

Thanks for the reply r.
I think you didn't get my question. I do use a single sessionfactory and hence single session for each client (and users of that client).
But, i have different databases for each of my clients. So how can I manage this???

P.S : Each client has many users.

Why won't you use "SessionFactory#openSession(Connection connection)" method on one and the same SessionFactory instance?

Just to make sure I understand... So this is a typical multi-tenant architecture? Specifically you have chosen to physically split the clients (either into separate databases or separate schema per client within the same database I could not tell)?

To be honest I did not understand the "2 separate databases [for each client]" part. I think the most confusing part there to me was the distinction you made between "transactional" (OLTP) and "live". Usually those two are the same. Usually the distinction is between OLTP and OLAP style data. At any rate, whatever distinction you make here may or may not be relevant. The biggest thing is the visibility between these things.

When connected to the "transaction database" for client#1 can you see the "master database" (and do you need to be able to from Hibernate)? Can you see the "live database" for client#1? See here means query against, write to, etc. Different database vendors call this something different assuming you have multiple physical databases. Oracle calls them Database Links, SQL Server and Sybase call it Linked Servers, etc. I do not know MySQL well enough to know its capabilities. But for sure if I were developing what you described I would strive to make sure that everything I needed to be able to see from client#1's "transactional database" was set up in such a fashion. Same for their "live database". This really has nothing to do with Hibernate nor JDBC access specifically; it applies to all access methods. Then in JDBC (and therefore Hibernate) you'd be able to reference all the tables you need for client#1 from a single Connection.

From there you have a couple of options, but they depend on your answers to my questions above.

Sorry for the confusion. I think, the following might clear it..

Specifically you have chosen to physically split the clients (either into separate databases or separate schema per client within the same database I could not tell)?
-- The schema for every client is the same. (the around 150 model classes that I described before), Just needed to physically demarcate Client's data.

To be honest I did not understand the "2 separate databases [for each client]" part
-- By transactional database, I meant, for every transaction on Live DB, there is an entry/log in the transactional DB. For e.g. If an business object is created, a corresponding entry is made into the Transactional DB, the same being for updation and deletion of all the business objects.

When connected to the "transaction database" for client#1 can you see the "master database" (and do you need to be able to from Hibernate)? Can you see the "live database" for client#1?
-- Yes. This is because, we don't specifically hit the transactional DB (except for few services which involve history tracking etc. ), but for every write to our live database, we have listeners writing into Transactional DB. So, we need to be always connected to Live and Transactional DB.

I hope this answers all your questions.
Waiting for the available options that you mentioned.

Well here you kind of contradict what you said previously. Its vitally important to know whether everything you need to service the requests in regards to a particular client is available via a single JDBC Connection.

That being said, this question comes up enough that I'll write up something about handling multi-tenency in Hibernate.

Then use "SessionFactory#openSession(Connection connection)" as I said. That is do the following:

1. Construct one "SessionFactory" for all the clients. Just one and no more.
2. Create separate connection to different databases for each client. That is one Connection object per client.
3. Use "openSession" method to create separate Session object for each client.

You will have one SessionFactory only and whole bunch of sessions. This should scale very well.