Editorials

Real World SQL Proxies

Today I wanted to share an implementation of a proxy pattern for SQL databases. This story comes from a dot net implementation from a very large scale, with multiple web services all hitting the database server concurrently. The problem is that when enough services are operating, especially when they are clustered or use load balancing technology, the SQL database server can actually run out of ports for connections. Even if you forcefully close a database connection, in dot net it can take up to a minute to release the port for re-allocation.

Even using connection pooling, we would sometimes run short on ports for SQL Connections. Each individual service had its own pool of ports which were never shared with any other service. To help alleviate this issue we created a proxy service for accessing databases. This proxy service would have a limited number of ports, just like the database server. However, the connections it would hold could be used for any service needing to access the database. We created multiple proxy services and load balanced them. Having the proxy service simply as a gateway to the database server made it possible to reduce the number of connections to the database server, and make them available to multiple services at the same time. In short, any service wanting to connect to the same database on a SQL server, could utilize the same thread that was created by some other service, and still in the pool.

Another implementation I worked on had a different problem altogether. They had thousands of databases. In order to handle thousands of databases you would have to close databases not in use, and even that doesn’t necessarily let your database server manage them all. I found, through testing, that once you had mounted over a thousand databases on a SQL Server the service began to degrade. You could probably mount more today, but there is a limit. In this case, it was easier to put the databases on multiple servers. Now comes the proxy pattern.

For this solution, one of our genius developers came up with a database broker using a proxy pattern. The database broker knew where every database was hosted. When a client requested access to the database it would route the traffic to the appropriate database server. Again, the proxy is only acting as a pipeline as described above, but solving a different problem.

Another scenario we are probably all familiar with is the use of a proxy to allow database sharding. In this case, a database that grows too large for good performance is broken up into multiple pieces, and the data is actually hosted by more than one database engine. The heavy lifting is performed by multiple servers. Then a proxy is placed in front of all database calls so the queries performing the actual work are routed to the correct instance(s) of the database to get the work done. You’ll see this done many times using MySql to get it to scale on a massive level.

So, just because you are an SQL Head, and like working with SQL engines doesn’t mean you can’t get your hands dirty and play with a proxy.

Cheers,

Ben

Facebooktwittergoogle_plusredditpinterestlinkedinmail