Spring framework, Hibernate and MySQL Replication

Laterly we have our site grown to the stage that a single mysql server will not able to do the job anymore. That's something we realy like to see. The CPU usage had reached more then 90% at peak traffic time. MySQL even crashed a couple time when we were running an early version. 5.0.x. Don't remember what it was exactly. But when we upgrade to 5.0.24, it stopped crashing. It was also unacceptablely slow under the heavy loads. We need some heavy duty database solution.

I have read a couple articals about mysql cluster. So my first thought is cluster. But when we ask our hosting company to set up a MySQL cluster, they suggested replication instead. Sometimes you just could not judge things by their name. Have run into the word "replication" a couple times in the mysql ref book. I miss judged the replication function. My sql does not like some other databases, this replication is real-time replication.

To set up a master/slave replication system up is really a straight forward thing to do. I installed FC6 on my desktop. Enjoyed the new tool FC6 provided for xen vertiual machine. As others said, it's like using VMWare. A big improvement from FC5. Followed the mysql reference book, made it work after a couple tries. Xen is such a good thing to have. Doesn't have to be big, Xen is a perfect fit for this low cost testing environment.

So far, we had the database ready for testing. We had one more thing now quite figured out by the time. How does the app use this master/slave system. With the question, did a little search on google. Found the ReplicationDriver that is provided by MySQL Connecter/J is what we need.

Our application is a Sping+Hibernate J2ee web application. App is doing declarative transaction by using "org.springframework.transaction.interceptor.TransactionProxyFactoryBean". The connection pool we used is Apache DBCP. Rock solid connection pool. I like to have all things controlled and configured in one place. So had the driver changed in our sping applicationContext config, Ant build, deploy, and start my local environment. Everything was fine. I had my both mysql instances general logging on. Look at the log files, everything was what we expected. Reads and searchs were on slave. Updates were on master. We had a little concern about Spring. The mysql ref book's sample code explained the setReadOnly(). We were not sure what Spring is doing. Once we saw it's working. We had no more concerns.

The second day, it only took me a couple minutes to have the replication set up. The firewall gave us a small supprise. Did not take long to relise the port is not open. Had the app configuration change to the new master/slave servers. Restart our app, master mysql's cpu drop to 10%. Slaves are about 20%.

Although it's working, we had to do a trick for a small problem. Our connection pool seems only use the first slave server. So I fooled the app by giving it different ips for the same hostname. A group of our app servers are using one slave server, the other group is using the second. The ReplicationDriver is probably picking the first slave by default. Because it was said, the slave connection is sticky. It probably will not use other slaves unlease something bad happen. I am going to check it out when I have a chance.