In using databases as a key component of internet infrastructure, IT departments are finding unexpected problems in particular when using DBaaS (Database-as-a-Service). One of these challenges is in connection management. There are three areas where connection management can be a problem:
The Heimdall Proxy was designed for any SQL database including Azure Database for MySQL and Azure SQL Data Warehouse (SQL DW) for connection pooling such as:
Figure 1: Heimdall Proxy Architecture Diagram
The Heimdall Proxy provides better control over database resources, providing more efficient and consistent behavior. As a result, users will reduce their database instance size and/or support higher customer density on the same database. In this blog, we explain how these functions work and are configured.
A basic connection pooler opens a number of connections upfront (the pool), then as an application needs a connection, instead of opening a new connection, it simply borrows a connection from the pool and returns it as soon as it is not needed. For most pools to be effective:
For a typical application server environment (e.g. J2EE), basic pooling is supported. In other environments, where pooling was not part of the initial design, simply inserting a connection pooler can cause more overhead than expected:
Figure 2: Basic Connection Pooling
For basic connection pooling, an active (green) front-side connection is paired with a back-side connection as shown in Figure 2 above. Additionally, you may have idle (red), unassigned connections in the backend for new connections. As such, you are NOT reducing the total number of connections, but are reducing the thrashing that occurs as the connections are opened and closed. The main benefit of basic pooling is lower CPU load.
To configure connection pooling on Heimdall Central Console, select the Data Source tab. Click the checkbox to turn on Connection Pooling showed below:
Beyond basic pooling, there is connection multiplexing, which does not associate a client connection with a fixed database connection. Instead, active queries or transactions are assigned to an existing idle database connection, or else a new connection will be established. If a client connection is idle, no resources are used on the database, reducing connection load and freeing memory. Shown in Figure 3 below, only active connections (green) are connected to the database via the connection pool while the idle connections (red) are ignored.
Figure 3: Connection Multiplexing
Multiplexing is a much more complicated technology than basic pooling. Therefore, many factors need to be accounted for. In the following situations, multiplexing will halt, and a connection will remain pinned, including:
To configure multiplexing on the Heimdall Central Console, go to the VirtualDB tab. And under Proxy Configuration, just click Multiplex option shown below:
In the event that special queries break multiplexing logic, and multiplexing needs to be disabled on the connection, go to the Rules tab for more granular control (along with other pool behaviors). For example, you can add the below rules to:
The third use-case helps ensure SLAs by enforcing per-tenant limits on connections and when combined with multiplexing, total active queries. This prevents one user from saturating the database, ensuring fairness of resources for others. A second tier of pool management is activated, that of “user pools”.
In the Data Sources tab, the pool can be managed at two tiers: the user level and the database. You can limit each user to a number of total connections and idle connections. Use the icon to add limits as shown below:
Shown above, the total connections allowed to the database across all users is 1000, but each user is only allowed 100, and of those, only 10 can be idle. Excess idle connections will be disposed of. Each time a connection is returned from the pool, there is a chance the connection will be closed: A value of 1000 means that there is a 1/1000 chance that the connection will be closed. This behavior is different from most connection poolers that set an absolute connection age which for large deployments can result in a stampede of many connections closing and reopening at once.
Figure 4: Multi-tenancy with Pooling, Multiplexing and Per-tenant connection limits
Figure 4 shows two tenants (with unique usernames or catalogs), allowing only active connections (green) to the database when multiplexing is enabled. If Tenant A attempts to perform a third query (blue) while two are active, it will be queued until one of the current active queries completes.
The net result of the combination of 1) Pooling and 2) Multiplexing, and 3) Per-tenant limits is that no single tenant can saturate database capacity, resulting in the SLAs of other customers failing. Further, as beyond a certain point, adding execution threads to the database will result in negative performance. This control can improve overall performance in many cases, allowing more capacity during peak load.
Magento is an e-commerce package written in PHP. Since PHP does not support efficient connection pooling due to its processing model, each page-view opens a connection to the database, requests all the data it needs, then closes the connection. For every page-view, it results in a very high amount of connection thrashing against the database and can consume a large percentage of the database CPU. With the Heimdall proxy, basic connection pooling alone can reduce the load on the database by up to 15% percent.
Slatwall is an eCommerce platform written in Java, and is natively designed to use pooling. Although, under heavy load, it can result in the saturation of the allowed connections on MySQL (at most 7000). In order to support larger user-loads, the Heimdall proxy can reduce the connection load by an order of magnitude, resolving connection limits on the database, and allowing the CPU load to be the limiting factor in larger deployments. Per the developer of Slatwall, connection offload with multiplexing and pooling resulted in a 10x reduction in connections to the database.
While Heimdall proxy provides connection management for databases, there are other features provided that further improve SQL scale, performance and security:
Deployment of our proxy requires no application changes, saving months of deployment and maintenance. To get started, you can download a free trial on the Azure Marketplace, or contact Heimdall Data at firstname.lastname@example.org.
Heimdall Data, a Microsoft technology partner, offers a database proxy that intelligently manages connections for SQL databases. With the Heimdall’s connection pooling and multiplexing feature, users can get optimal scale and performance from their database without application changes.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.