Multiple databases in one Mormot server

emk · 2016-03-11 22:30:09

I'm evaluating Mormot and a read part of the docs and forum.

Our existing (accounting-ERP) application use Firebird databases; now we want to enable it for "cloud". For start, we'll use SOA to execute SQL and later "upgrade" to ORM/JSON, so parts (reporting, etc) of our app I will hope to be in AJAX/browser.

So we want to host on our server machine, Mormot executable + Firebird engine + databases of our clients.

Let's say we have 200 clients(5-10 user each) with multiple databases for each client (each database is accounting-db for every of their own customers). So it can be easily 3000 databases (mostly under 100MB each) on one server.
AFAIK, If I create a separate HTTP server for each of 200 client x "few http.sys threads", I have to much thread context switching, so I think the only solution is one mormot server executable with multiple databases. How to do it? Please with sample source code.

mpv · 2016-03-12 11:05:37

For the similar task my first architecture was "one executable for all clients". But after a short production usage we switch to a "one executable (HTTP Server) per client". This allow as to safely restart a service for one client without stop all other.
Currently our production runs 7 server instance (executable) on one host with 24 thread each (168 thread in total) without problem.

In case you have 5-10 user per instance you can set a 2 thread per instance and total number of thread will be 400 - not a problem for any modern hardware. In any case most of the time your services will sleep and no "content switch" will be happens.

IMHO

Last edited by mpv (2016-03-12 11:10:09)

emk · 2016-03-12 12:17:17

Yes, it's true, 400 threads it's not a problem, but number of clients could grow.

But another thing is the problem: each client can have 20-30 databases = 3000-4000 databases, so in your scenario I should have "200 mormot server executable" x "2 threads https.sys" x "connection pools to 4000 db", which IMHO it's not viable.

I think I should have my own connection pool maintained by a background thread who connects dbs dinamically when requested and disconnect them after let's say 10 min of inactivity for that particular db.

The question is if I use my own connection pool and not the "official" TSQLDBConnectionProperties, I can still use later the ORM part of Mormot? because, for start, I'll execute method based serviced to serve SQL queries, but later I want to publish my REST API for Invoices/Orders/Customers/etc.

ab · 2016-03-12 16:00:00

30 firebird db per client.... You need refactoring here IMHO....

About the orm you should better use start from a blank sqlite3 db and do not try to use firebird at all. Use a simple data pump from existing firebird tables to orm data. And try to forget about SQL and rdbms, but define a persistence service publishing aggregates.

emk · 2016-03-12 17:50:14

No refactoring, because it's not sharding, I really need one database for each customer of my customers, because MyCustomer1..MyCustomer200 are accounting companies and keep booking for their own customers.
At any time the final customer can ask for his own database for legal purpose.

Please let me explain better:

MyCustomer1 has (1_HisOwnCustomer1db...1_HisOwnCustomer30db)
..
MyCustomer200 has (200_HisOwnCustomer1db...200_HisOwnCustomer25)

So can be 4000-6000 databases on server. NOT all databases will be online, only a few of them (150-200) because MyCustomer1 can work let's say 2hours on 1_HisOwnCustomer1db, after that can work another 3h on 1_HisOwnCustomer9db, so the connections to 1_HisOwnCustomer1db will be timed out and disconnected after ~10min (by the background thread who maintains the connection pools).

It seems that I have 2 options:
a) One mormot server executable with THttpApiServer with ~64 threads + TSQLRestServerFullMemory + my own intelligent connection pool to ALL databases.
b) One mormot executable/process for EACH MyCustomerXXX with THttpApiServer with ~2 threads + TSQLRestServerFullMemory + my own intelligent connection pool to ONLY databases of MyCustomerXXX

If I want to implement a REST API like this: GET "X_HisOwnCustomerXX/Invoices/{InvoiceID}" the both options are ok or only the first one?

PS: I made the wrong assumption that for enabling a REST API, I need use ORM part of Mormot which is tied to TSQLDBConnectionProperties, but it's not the case - I can use method based services with my own connection pool.

emk · 2016-03-12 18:29:42

GET "info/X_HisOwnCustomerXX/Invoices/{InvoiceID}" it's a better aproach

Leslie7 · 2016-03-12 21:09:12

emk,

Applying meta changes to all these databases can be painful. If you have only a single executable it may be down for a while until all updates are finished.

A single database as AB has suggested can save you a lot of headaches. I think the best would be probably to set up a selective master-slave replication where the master database has all the data and each slave database contains only client specific data. mORMot supports replication where slave servers are online only for the time of the replication . You only need a second server which can act as the slave server on behalf of all client databases. It could iterate through the client databases periodically and run the replications one by one. This way you can have the best of both architectures without the disadvantages. All clients can access the server any time. Also significantly less resources are used. You may even include both servers (Master & Slave) into a single executable.

Cheers,
Leslie

Last edited by Leslie7 (2016-03-12 21:47:57)

Leslie7 · 2016-03-12 21:43:37

"each slave database contains only client specific data" - All data commonly used by all clients and read only to them can be put to a separate database. Much easier to maintain this way. When the client needs its database these two are handed over.

Last edited by Leslie7 (2016-03-12 21:48:24)

emk · 2016-03-12 22:51:51

@Leslie7,

Already have replication in my mind, but for the sake of question simplicity, I didn't mention it.
1. I can't use ORM(the app exists, so I'll not recode it), so I can't use mormot replication. 2. Multi master replication or merge changes configurations is out of the question.

So, my intended scenario:

Every user of MyCustomerX connects LOCALLY (on his computer thru Firebird embedded) to a READ-ONLY replicated database (of course they have only MyCustomerX databases). Every time the app needs data reads locally and load it to Firedac memory tables. At ApplyUpdates for those FDMemtables, I generate DML SQL script (with Firebird "execute block", so it's a single unit of work even it's master/detail update). That SQL script I'll send it to a method based service "ApplyUpdatesAndGetChanges" for execution. The method based service will return all changes log SINCE previous syncronization, including the just applied updates. Posted changes will be notified to other users using the same "master-database" in that moment thru a ZeroMQ or RabbitMQ server, so every notified user will call a method based service "GetChanges" .

How the "master-database" keep the changes log, you can read in excelent article "http://www.ibexpert.net/ibe/index.php?n … Replicated"
My biggest luck to apply the methodology from that article is that I already have one unique ID=Int64 for all the records, no matter which table.

It's the best architecture I can think of. It's fast (all reads are locally), it's safe (only one true master write), minimum resources use on our cloud-server and data on every client it's up to date almost instant.

What you proposed to use one big master-database and few replicated detail-database filtered by client has 2 drawbacks:
1. 6000db x 100 mb = will be already 600GB database and growing
2. I can't use "fast download database". "Fast download database" means backup now "master-database", zip it and download it quickly to client to replace "detail-database"(less than 15 MB for a full 100 MB db). This is useful when synchonization was a long time ago and changes log is to long to be asked by SQL(part by part and buffered to client), or the client database is corrupted, or computer is reinstalled etc.

This is the model I'll go to enable "cloud" to my existing app. I'm asking those questions because later, I want to publish REST API to those databases, so I want to clear my head from the start and to consider all the aspects of this implementation.

Last edited by emk (2016-03-12 22:58:24)

ab · 2016-03-12 23:02:31

Or just define one db per customer, using firebird embedded or even better sqlite3.

emk · 2016-03-12 23:57:36

Anyway one-db/FinalCustomer , or one-db/MyCustomerX, I have to write my own db connection pool.

Can someone please post a snippet of code how to make method based service REST API server (TSQLRestServerFullMemory) to enable something like this:

GET /Orders/{OrderID}/Products/{ProductID}

Please.. so I can clear my mind. Thank you.

Leslie7 · 2016-03-14 01:27:21

It seems to be a typical head office with local offices scenario. Search for "Replication use cases" in the documentation.

1. I can't use ORM(the app exists, so I'll not recode it), so I can't use mormot replication.

You don not have to change the existing code, ORM classes are just and other way to access the same data. They can coexist with any DAC, though one have to be mindful of caching. You can write a simple generator to automatically create ORM classes from database metadata. The framework handles it the other way around.

It is worth discovering what mORMot has to offer. Eg interface based services, websockets two way communication, callbacks via interfaces, live replication ...

What you proposed to use one big master-database and few replicated detail-database filtered by client has 2 drawbacks:

No, I meant one slave db for each client. But now I can see that you do not even need that since you already have the local office servers as slaves.

1. 6000db x 100 mb = will be already 600GB database and growing

You might want to check mORMot's up and coming bigdata support.
An SQLite database can be smaller than a firebird database with the same data. It is worth creating a test export to see the ratio.

2. I can't use "fast download database". "Fast download database" means backup now "master-database", zip it and download it quickly to client to replace "detail-database"(less than 15 MB for a full 100 MB db). This is useful when synchonization was a long time ago and changes log is to long to be asked by SQL(part by part and buffered to client), or the client database is corrupted, or computer is reinstalled etc.

Even from a single database you can export & compress the data you want to have on your local server. Once again interface based services can be useful here.

mORMot tends to be well optimized. You may find it's replication to be gentle enough with the bandwith and sqlite to be fast enough applying the updates. Search for "Data access benchmark" in the documentation.
Though SQLite does not support stored procedures, so it may not be suitable to easily replace the local office servers. But in the cloud either SQLite or PostgreSQL can be a better candidate because of the way they store records they tend to use less disk space and memory.

It is worth testing your usage scenarios with mORMot for speed and resource consumption before making design decisions.

emk · 2016-03-14 16:38:50

I can't use sqllite because I have a lot of stored procedures.

I know that mormot is very refined, that's why I stopped here.

I read quickly the SAD but I still have question how to do some stuff.

I want to achieve 3 objectives:

1. To put data in cloud and to use the same thick windows app (with small modifications) - I'll use the scenario from previous post - localdb + firebird embedded + method based service on server
2. Translate to a new web app for reporting/"light use" with the excelent Elevate Web Builder - I'll use also method based service (EWB AJAX requests seems to be fine with method based services).
3. Make a REST API for Orders, Invoices, Customers,... to 3rd party app interactions. I read a response in forum that can be achieved also with method based service but I can't figure out how enable something like this:

GET /Orders/{OrderID}/Products/{ProductID}

I read the Method based services chapter, I know that I have to create a TSQLModel 'Orders'- pass it to create method TSQLRestServerFullMemory- pass it to a THttpApiServer but I don't have a working example how to do it.

[qoute=Leslie7]It is worth testing your usage scenarios with mORMot for speed and resource consumption before making design decisions.[/qoute]

Yes, it's true, but I need working examples for all 3 objectives (I have solutions for the first 2), so in that way I know if I can go to the way of one big db, multiple processes + multiple dbs, I have to know how URI will be influenced,..etc It will be really helpful if someone posts some code for enabling:

GET /Orders/{OrderID}/Products/{ProductID}

ab · 2016-03-14 18:35:54

With method-based services, you could use a void TSQLModel, then just define a published Orders() method, to handle the incoming requests.
This will by-pass the ORM routing, and you should use your own routing, which is pretty easy thanks to the numerous fields and methods of the Ctxt parameter of the method.
See also the threading options for such methods.

cybexr · 2016-12-22 08:51:25

emk
I don't know whether your CustomerX must be work OFFLINE. if the answer is NO, I think keep ALL data in one RDBMS is a better idea，based on current firebird DB-schema then add 2 column "CustomerX", "FinalCustomer1" to all tables， pump all your tables to RDBMS, then you can work with one HTTPServer + one RESTServerDB，one mormot-exe serves 200 con-current will be a litte case. Then you can free from handle such question : db replication, DDL sync, db broken ...
As for your FinalCustomer1's legal REQ, think about mormot's Redirect feature, it may be helpful to implement a dinstinct FinalCustomer's DB.

emk · 2016-12-23 14:27:27

@cybexr,
In a linked post ( http://synopse.info/forum/viewtopic.php?id=3704 ), @ab says better than I could do it:

Having separated databases has several advantages:
- easier to backup or purge
- easier to change your hosting (you can change one client location from one server to another)
- more agile for scaling (cloud-like hosting without the cloud)
- you can replicate each database in several nodes (using mORMot replication features) to implement a real-time backup or offline work
- safer design (data of several clients would never mix)
- may be mandatory for regulatory purposes
- is a very good selling point: your client will have its own database!

cybexr · 2016-12-24 02:30:23

@emk, got it . Separate DB can make offline work easier, architeture is about make choice, you get something meanwhile you lose something

mORMot Open Source

#1 2016-03-11 22:30:09

Multiple databases in one Mormot server

#2 2016-03-12 11:05:37

Re: Multiple databases in one Mormot server

#3 2016-03-12 12:17:17

Re: Multiple databases in one Mormot server

#4 2016-03-12 16:00:00

Re: Multiple databases in one Mormot server

#5 2016-03-12 17:50:14

Re: Multiple databases in one Mormot server

#6 2016-03-12 18:29:42

Re: Multiple databases in one Mormot server

#7 2016-03-12 21:09:12

Re: Multiple databases in one Mormot server

#8 2016-03-12 21:43:37

Re: Multiple databases in one Mormot server

#9 2016-03-12 22:51:51

Re: Multiple databases in one Mormot server

#10 2016-03-12 23:02:31

Re: Multiple databases in one Mormot server

#11 2016-03-12 23:57:36

Re: Multiple databases in one Mormot server

#12 2016-03-14 01:27:21

Re: Multiple databases in one Mormot server

#13 2016-03-14 16:38:50

Re: Multiple databases in one Mormot server

#14 2016-03-14 18:35:54

Re: Multiple databases in one Mormot server

#15 2016-12-22 08:51:25

Re: Multiple databases in one Mormot server

#16 2016-12-23 14:27:27

Re: Multiple databases in one Mormot server

#17 2016-12-24 02:30:23

Re: Multiple databases in one Mormot server

Board footer