MySQL Cluster (Master / Slave) and Hibernate

Question

One database is used during development, 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, it is likely that MySQL clustering or Master / Slave replication will be used. Any idea on changing the code / configuration for this?

Also a quick question for everyone. How many transactions / sec can one mysql server instance running on a dedicated server handle?

Answer 1

I worked on a similar issue, although Spring was not involved. We developed an indication of a local MySQL instance (each developer had one in his own field), and the production system was a Master / Slave replication ring. We actually went further and co-hosted the application server with the database server, so at the application and database level there was a four-node.

Our application runs in JBoss. It was pretty simple, but there was quite a lot of clustering on the cluster server platform. At the same time, we were able to receive more than 300 requests per second, and sometimes up to 1000 requests per second (depending on application processing). The database was never a bottleneck - even at those rates, we rarely saw a connection pool above 5 connections.

In both cases (mine and yours), since the JDBC URL points to the local server, the functional difference does not exist from the point of view of the application. I had to play some interesting games with automatic increment settings so that data would not be typed; if you have only one deployment point (and therefore one access point to the MySQL instance), you do not need to worry about that.

Thus, no code is changed and configuration changes are not changed. It will just work. Under standard conditions, the fact that you support a couple of Master / Slave instances or a cluster will not make any difference, since you will most likely point to either the Master instance or the MySQL instance facing the cluster (in MySQL, the cluster is behind one or more database servers, replicating data).

What kind of load are you going to handle? In the past, MySQL was compared with> 1000 TPS, but the results usually vary depending on the main characteristics of the system (memory, processor cores, processor speed), responsiveness of your application, and parallel threads.

Answer 2

The replication included in MySQL does not affect your Hibernate configuration at all if the data source used by Hibernate is connected to the wizard, which is typical. If you also need to connect to the slave (for the query), then this is a different configuration.

mlschechter is right, MySQL can process thousands of transactions per second on properly configured hardware. In addition, Hibernate can be used to reduce the load on the database (session frames in the view, second level cache), so you can scale it further on the application server side before getting a new database.