Home / server / Questions / 182016
In Process
lindelof
Asked: 2010-09-17 13:34:00 +0800 CST

Should I have one MySQL instance for all our apps, or one instance for each?

  • 772

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?

mysql database

8 Answers

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

    • 7

  2. 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

  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.

    • 3

  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

  5. 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

  6. 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

  7. 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

  8. 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.

    • 1