Moving from a single database to a sharded database has some implications when it comes to configuring application server thread pools and connection pools. This article explains how to determine the ideal configuration for a sharded deployment and is applicable to any sharding solution.

Single Database

Let’s start by looking at the configuration of a web application with four application servers and a single database. The following diagram shows a high level view of the configuration of an application where the database is configured with max_connections of 1000. To ensure the database is not overloaded we choose to allow 800 concurrent requests at any time, so we configure each of the four application servers with a connection pool that allows a maximum of 200 connections. We configure each application server to support at least 200 threads to process incoming HTTP request.

Sharded Database

Now let’s assume we go to a sharded database with 4 physical shards (4 separate MySQL instances each on its own dedicated server). Now we have four databases, each configured with max_connections of 1000. Again, we want to limit each database to 800 concurrent transactions. Assuming that the sharding scheme results in equal distribution of queries across all four shards this means that each application server will on average will now be able to handle four times the number of concurrent requests and will typically have 200 connections to each shard.

The above example assumes a perfectly even distribution of load across all shards; but in reality we should expect greater load on some individual shards at certain times so it is important to build some leniency into the configuration. For example, it would be reasonable to expect load on an individual shard to temporarily spike by up to 50% to 300 concurrent connections, so the per-shard connection pool should be set to 300 rather than 200. If the application does try and execute too many transactions against a single shard then some requests will fail because there is no connection available. The only way to guarantee this cannot happen would be to limit the application server to only process 200 requests at any time as in the original setup before sharding but that would almost defeat the point of sharding and would require a larger number of application servers which are not being fully utilized. If load against a single shard is regular spiking more than 50% then it is a sign that an incorrect sharding scheme is being used or that data needs to be redistributed between shards (perhaps there is one high volume user in a single shard for example).

dbShards Configuration

The dbShards JDBC driver uses the <client> section of the dbshards-config.xml to determine connection pool sizes. There are two key properties that need to be configured – “per.shard.conn.pool.max” and “stmt.send.threads”. Here is an example configuration:

  <client>
	<property name="per.shard.conn.pool.max" value="200" />
	<property name="stmt.send.threads" value="800" />
  </client>

The “stmt.send.threads” setting must be set to at least the same value as the application connection pool maxActive setting. For each dbShards connection there could be a thread for sending data to the dbShards replication agents.

The “per.shard.conn.pool.max” setting sets the maximum number of connections that can be established to each shard.