MySQL Database with Multiple Servers

Question

Basically, I'm working on a REST API for my application. I was researching for server performance, and I couldn't find information on splitting a database. Would it be better to have a single file system shared through multiple servers that use MySQL (if it's possible to store it on a shared file system), or should I just upgrade my current server or store different information on different databases (for example users a-n information on database 1 and o-z on another) if it's slowing it down? If anyone has more information on increasing database performance, or something I can read through it would be very much appreciated. Thanks in advance.

Answer 1

"Sharding" is where you put some of the rows of the main table on one physical server, some on other server(s). Only one system in a thousand needs this type of scaling.

"Partitioning" is where you split a single table into multiple "subtables" that act like a single table. There are very few use cases (I count only 4) where this provides any benefit. All the subtables live in the same server.

Having multiple MySQL instances on the same physical server adds complexity and may add some performance, but it is unlikely.

Having multiple MySQL instances on different physical server, but sharing the same data -- Do not attempt; MySQL does not know how to share its own data this way.

Using Replication lets you do arbitrary read scaling. This is slightly complex. It is a common approach. But it only handles apps that are more 'read' than 'write'.

Galera Clustering gives you some write scaling.

A single instance can handle lots of incoming requests (from a web server or other type of client). These clients could be scattered across multiple servers. That is scaling of clients, even without scaling MySQL, may be useful to you.

With a load balancer / proxy server / etc, you can also have multiple clients talking to multiple MySQL servers (read Slaves / Galera nodes / sharded servers / etc).

Many of the above techniques can be combined in the same system.

Bottom line: It smells like you don't yet know if you will need any scaling. When you get to that point, please provide more info on what the app is like so we can discuss the various options with less 'hand waving'.

Answer 2

I'm 99.999% certain all those words are just saying "premature optimization". Unless you have over 20GB of data (or 50GB or 100GB.. basically, a lot), use a single database and once it starts slowing down look at different options (sharding, etc).

And don't worry, you'll have plenty of other things to keep you busy without introducing advanced db tactics :)