3

We're a small startup and I need to setup several applications, almost all of which require a database.

I face the choice of setting up one instance of MySQL, with one database for each of our applications, or of setting up a separate instance of MySQL for each application.

What is the generally accepted best practice in this situation? One database to serve them all, or divide and conquer?

8 Answers 8

7

Knowing nothing else, simpler is always better. One instance.

2
  • 1
    Is that really simpler? Generally all setup instructions seem to be written from the perspective that the mysql server will only be hosting that application. Setup documents don't seem to do a very good job about telling you how to setup permissions correctly to separate various applications. If you are only looking at this from the perspective of simplicity I don't think you can make a strong case for a single database server, there are pros and cons both ways on this point.
    – Zoredache
    Sep 16, 2010 at 21:44
  • 1
    BTW, I am not saying I disagree with the single instance recommendation, I am just saying that it may not be as simple as it looks/sounds.
    – Zoredache
    Sep 16, 2010 at 21:45
4

I'd like to share the opposite view of what most answers here recommends.

Running a single instance per database gives you the chance to allocate system resources to specific databases. One example could be to give one database a much larger innodb buffer pool than other databases.

For backups, if you use xtrabackup, it's very easy to make backups of individual databases if you run a single instance per database. You can always mysqldump individual databases, but doing a recover from a mysqldump is not nearly as quick as restoring from a backup done with xtrabackup.

3

Agree with others - one instance of mysql, but:

  • seperate databases for each app
  • seperate user/password for each app

This will make it much simpler to migrate later. However do use innodb rather than c-isam as your storage engine - the latter (IIRC) still only process one request at time.

When you do need to upgrade to multiple servers, then I'd recommend using master-master replication so that each node still serves all the databases rather than splitting it, until you get to about 4 nodes, then start partitioning based on database.

3

One instance on a high spec machine, fast disk array (SSD's maybe, LACP network) and create separate DB's and login's for each application.

This is a typical deployment for most database systems, especially SQL. You must consider availability however when centralising your database server. Therefore in most production environments you will see an SQL cluster consisting of a pair of servers replicating each other or sharing some SAN storage.

1

One instance of MySQL, multiple databases is best practice.

I can't think of many scenarios where the ideal situation would be to run multiple instances of MySQL with one database per instance. It seems silly.

1

Once instance is better because:

  • you can use all available resources (ram and disk space)
  • mysql can assign the resources if needed
  • caching will be able to use the max. amount of memory (no stale caches)
  • single place for managment
  • easier to maintain
1

You always have to consider when you put multiple databases in one instance of MySQL, they can also corrupt each other if they share the same user.

Additionally you can only use one mysqld version. Maybe one of your databases need a different version.

1

I would like to go with more than one mysql instance, As the projects grow, different db's might require different configurations. Of course you can migrate into new instances at any time, But having more than one instance can have the following upsides

  1. It is easier and faster to backup/restore
  2. Replication works on the instance so it would be easier to replicate or cluster the databases with more read/write
  3. If you ever required containerization on the databases (using docker or so) it is a lot simpler to put each db in a different container.

Still I agree with the people who believe using one instance would be more efficient. Of course for small projects it would be the best solution but as the project grows, each of it's databases would begin to show different characteristics which will lead into into using different mysql instances.

2
  • I agree that one instance per db works around xtrabackup limitations, i.e. it's the only way to have per-db incremental backups. My only gripe would be that most of the RAM goes to waste this way and it won't be enough for the rare heavy query that wants to eat it all because now RAM is preallocated between instances.
    – capr
    Nov 18, 2021 at 23:33
  • @capr of course you can change the default configurations and make mysql use a lot less ram per instance, the main usage of ram in mysql is for the database so for an small db it would not take much of ram Feb 13, 2022 at 12:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .