Load balanced database connection pools
You might have used Resin’s built-in database connection pooling, but did you know that Resin can also load balance across multiple database servers in the backend transparently? It’s as simple as adding more database drivers to your database pool. What’s more, Resin supports database server failover and even failover pools!
For example, say you’ve got two database servers that are mirrored in the backend. You can have Resin use round-robin load balancing to switch new connections between the two servers using the following:
<web-app xmlns="http://caucho.com/ns/resin">
<database jndi-name="jdbc/test">
<!-- primary server -->
<driver type="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource">
<url>jdbc:mysql://192.168.1.34.5:3306/test</url>
<user>test_user</user>
<password>test_password</password>
</driver>
<!-- secondary server -->
<driver type="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource">
<url>jdbc:mysql://192.168.1.34.6:3306/test</url>
<user>test_user</user>
<password>test_password</password>
</driver>
</database>
</web-app>
Now imagine that you’ve got a primary server and a backup that you don’t want to use until the primary fails. You can use the <backup-driver> tag to specify that:
<web-app xmlns="http://caucho.com/ns/resin">
<database jndi-name="jdbc/test">
<!-- primary server -->
<driver type="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource">
<url>jdbc:mysql://192.168.1.34.5:3306/test</url>
<user>test_user</user>
<password>test_password</password>
</driver>
<!-- backup server -->
<backup-driver type="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource">
<url>jdbc:mysql://192.168.1.34.6:3306/test</url>
<user>test_user</user>
<password>test_password</password>
</driver>
</database>
</web-app>
If you’ve got multiple <drivers> and a single <backup-driver>, the drivers will be load balanced in a round robin fashion until they all fail, then the backup will be used. If you have multiple backups, they are also balanced using round robin once all the primary drivers have failed. The order that they appear in the configuration determines the order in which they are load balanced. You can find the in depth documentation of the <driver> and <backup-driver> tags here: Database tag configuration.
If you have a fancy database driver that does load balancing, you probably don’t want to use it in conjunction with this feature. Check you driver documentation for details.