Splitting MySQL Database into separate databases

Question

I have a requirement that the MySQL database being used in my application is scaling very aggressively. I am in no state currently to migrate to a NoSQL Database.

I have figured out the following areas where I can try splitting the current database into multiple databases:

1. There are some tables which have static content, i.e. it changes barely.
2. There are user tables which store the user data upon interaction which changes drastically.

Now, if i split the database into two different databases, how will I handle the transaction? How will I write the Data Access Layer, will i have connections to both the databases? The application currently uses Spring & Hibernate for Back End. There are calls which join the user tables and the content tables in the current schema.

The architecture follows the current structure: Controller -> Service -> DAO Layer.

So, if i am willing to refactor the DAO layer which communicates with the database, what approach should i follow? I know only about Hibernate ORM but i would be willing to letting it go if there is something better than Hibernate.

Answer 1

Multiple databases on the same server? That approach will probably not improve performance on its own. RAM, fast disks, optimization, partitioning, and correct indexing will have a far greater payback.

If you have multiple databases on one server you can connect to them with a single connection, and simply use the database names with the table names in your SQL. Transactions work fine within a single connection.

Transactions across multiple connections and multiple servers are harder. There's a feature in MySQL called XA transactions to help handle this. But it has plenty of overhead, and is therefore most useful for high-value transactions as in banking.

In the jargon of the trade, adding servers is called "scale-out." The alternative is "scale-up," in which you add more RAM, faster direct-access storage, optimization, and other stuff to a single server to get it to do more.

There are several approaches you can take to the scale-out problem. The classic one is to use MySQL to set up a single primary server with multiple load-balanced replica servers.. That's probably the path that's most often taken, so you can do it without reinventing a lot of wheels. In this solution you do all your writing to a single instance. Queries that look up data can use multiple read-only load-balanced instances.

http://dev.mysql.com/doc/refman/5.5/en/replication-solutions-scaleout.html

This is a very popular approach where you have a mix of long-running reporting queries and short-running interactive queries. The reporting can be run on dedicated slave servers.

Another approach is multiple-primary-server replication using MySQL Cluster. https://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-replication-multi-master.html

Another approach, if you have money to spend, is to go with a supported MySQL Cluster. Oracle, MariaDB, and Percona have such products on offer.

Scale-out is a big job no matter how you approach it. There's some documented experience from other people who have done it. For example, https://www.facebook.com/note.php?note_id=23844338919