MySQL Cluster (Master/Slave) and Hibernate

Question

The application when developed is using a single database and the spring configuration is as follows.

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/test" />
    <property name="username" value="root" />
    <property name="password" value="" />
</bean>


<bean id="hibernateProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
    <property name="properties">
    <props>
        <prop key="hibernate.hbm2ddl.auto">update</prop>
        <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
        ...
        </props>
    </property>
</bean>

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource">
        <ref local="dataSource" />
    </property>
    <property name="hibernateProperties">
    <ref bean="hibernateProperties" />
    </property>
    <property name="mappingResources">
    <list>
        <value>...</value>
    </list>
    </property>
</bean>

<bean id="txManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="dataSource" ref="dataSource" />
    <property name="sessionFactory" ref="sessionFactory" />
</bean>

However for production there is a chance that either MySQL clustering or Master/Slave replication will be used. Any idea about the code/configuration changes for this ?

Also a quick question to all - How much transactions/sec a single mysql server instance running on a dedicated server can handle ?

Answer 1

I've worked on a similar problem, although Spring was not involved. We developed pointing against a local instance of MySQL (each developer had one on their box), and the production system was a Master/Slave replication ring. We actually went a step further and co-located the application server with the database server, so there was a four-node cluster at both the application and database level.

Our application ran in JBoss. It was fairly light, but did have some serious clustering at the application server level. With all that, we were able to get upwards of 300 requests/second, and sometimes close to 1000 requests/second (depending on the application processing). The database was never a bottleneck - even at those rates, we rarely saw the connection pool go above 5 connections.

In both cases (mine and yours), since the JDBC URL points to the local server, there isn't really any functional difference from the perspective of the application. I had to play some interesting configuration games with the auto-increment setup to keep data from getting stepped on; if you only have a single point of deployment (and therefore one point of access to the MySQL instance(s)), you shouldn't need to worry about any of that.

So, no code changes, and no configuration changes. It'll just work. Under standard conditions, the fact that you're backed by a Master/Slave instance pair or a cluster won't make a difference, as you'll most likely be pointing at either the Master or the MySQL instance fronting the cluster (in MySQL, the cluster is behind one or more database servers, replicating data).

What kinds of load are you looking to handle? MySQL has been benchmarked in the past at >1000 TPS, but the results tend to vary based on the underlying system characteristics (memory, CPU cores, processor speed), your application's responsiveness and concurrent threads.