2

My company develops different web applications for different clients.
For each new client (or important project), we use a new server with a new MySQL database.

This way, an application can access its database only and the database is directly linked with the project (as it is in the same server so basically in the same directories). Until now it has not shown any particular problem.

As we are changing our methods, my colleagues wonder if we could use instead only one database with all projects in it and use some master/slave replication.

Our different databases have some tables in common, but with different data as they belong to their respective apps.

I did some research on the subject :
I find mostly posts that recommend using a database for each application for these reasons :

  • Distinct log files and backups for each project
  • Better scalability as we can inspect the needs for each database
  • Simple to use from application without having to change data or create views, etc.

Most of what I read about this is between 5 and 10 years old so I'm not sure if the disadvantages of the other solution are still relevant.

I struggle to find precise information and advantages about the unique-database solution and the use of Master/Slave replication for this kind of cases.

I'm new to this idea : I understand the concept of master/slave here but I don't have technical knowledge about it.

(Could using a unique database be beneficial ?)

Should we have :

  • The database of an app on the same server than the app,
  • A single server for all databases, and keep the apps in other hosts ?

Note that we are using MySQL but PostgreSQL is a possible option if we chose to use a single database with different schemas for each app.

Charles Okolms
  • 45
  • 1
  • 8

3 Answers3

4

What you have read (even though it's oldish) is still very relevant!

Your problem is called multi-tenancy - to do it or not - the consensus appears to be "not", but there is a wide divergence of views on this issue - you can "partially multi-tenant" - i.e. keep virtually unchanging reference tables in common for example - however the same issues as outlined below apply to "partial" multi-tenancy.

My advice - use separate databases if at all possible!

  • If one app goes down, at least the others will still work, with a central database, if one app brings the server down, you're bunched (nothing will work).

  • Also, for migrations, if you need to, say, migrate one of your apps (size/speed/whatever problems - or you wish to dip your toe into the cloud testing with one app), then you will have difficulty separating out your apps, whereas if you use separate systems, you won't have any difficulties on that score!

If you'd care to explore this in more detail, I have answered questions on this issue before - check them out (1, 2 and 3 and links therein). Obviously cost and the nature of the expertise readily available to you is an issue so it's worth checking out your options before coming to a decision - you

Sometimes the wise heads are indeed on old shoulders! :-)

Another recommendation - use PostgreSQL if at all possible! It is vastly superior to MySQL in a myriad of ways - SQL compliance, JSON, Geospatial, CHECK constraints, SET operators ... long list... you won't regret it!

Vérace
  • 30,923
  • 9
  • 73
  • 85
0

Avoid a single database. My recommendaion is: start with separate databases on the same server - to reduce the costs, including maintenance costs. If workload increases, you can setup a new machine and move some databases. If only one application's workload increases, you can move only that one. To do this, it is important to monitor the workloads of different applications. So I recommend to install User Statistics plugin, from Percona.

And yes, the good way to distribute the workload and face crashes is to use replication or a cluster (replication is much simpler). Nowadays, we need to have no single points of failure. With MySQL you will also have another way to reduce costs: if you have applications on 3 servers, you can replicate all those databases to 1 slave - this is called multisource replication.

Other benefits of using multiple databases include:

  • ALTER TABLE on big tables can be painful
  • Delete 1 customer's data will be easy and fast
  • Different customers can have different configurations (give up some speed for reliability, or the opposite)
  • Monitoring some metrics will be much easier
  • Big tables are slower
Federico Razzoli
  • 1,769
  • 9
  • 24
0

You should distinguish the database from the server.

Single physical machine aka host can run number of servers that are DBMS engines. You can run few MySQLs, few PostgreSQLs, few MongoDBs and few Redises on the same machine at the same time. But you can run single dedicated engine.

Each DBMS engine can contain number of databases - hundreds databases tor hundreds websites for example.

Each database aka scheme can contain lot of tables, stored routines, events, views for some project.

Saying literally you can store all your data for pile of projects not only within the single database but even within the single table. But in practice it is way more useful to separate projects from each other at least by separate databases. But when some project exhaust the host's resources you have to separate projects by hosts.

Certain strategy depends on the projects' resource consumption and number of indirect factors.

Kondybas
  • 4,800
  • 19
  • 16