Skip to main content

Connection Pooling

note

Connection Pooling is currently supported for MySQL, MariaDB, MS SQL. Support for PostgreSQL is coming soon.

Overview​

PolyScale provides inbuilt connection pooling which offers the following benefits:

  • Support for very large numbers of concurrent, ephemeral TCP connections, far exceeding the origin database connection limit
  • Lowering TCP connection latency
  • Reducing CPU and memory footprint on the origin database caused by large numbers of concurrent connections

Use Cases​

Connection pooling is ideally suited to workloads that create many, short-lived TCP connections such as those seen by serverless functions. In such scenarios, a function may be called via an HTTP request, make a new TCP connection to a database, execute a query, close the connection and finally return an HTTP response. This process is repeated upon each request resulting in potentially thousands of concurrent requests per second in larger environments.

In these situations, connection pooling is an ideal fit as if the data is cached by PolyScale and the pool initiated (a minimum of 1 connection has been initiated to the origin database from PolyScale), the origin database will not service either the new incoming TCP connection or the request query.

PolyScale Database Connection Pooling

How Does It Work?​

PolyScale follows a strict policy whereby any connection to the origin database is initiated by a database client. PolyScale cannot initiate database connections independently; these are always triggered by a client application. There is a 1:1 relationship of client connection to the origin database. In this scenario, each TCP connection from a client first connects to PolyScale and is then routed to the origin database; the authentication handshake completed and then the new connection is ready to service database queries. PolyScale will maintain those connections until such times as the client disconnects. Even if the requested data is cached by PolyScale, a new connection is always created to the origin database.

For a cache with Connection Pooling enabled, the first connection will follow the same process as described above whereby the connection passes through Polyscale to the origin database. Once the client finishes the request or disconnects however, that upstream connection is maintained between PolyScale and the database and the connection is added to the pool for reuse.

The next connection that is initiated will only connect as far as PolyScale and if the requested data is within the cache (cache hit), the request will complete at the PolyScale PoP. This has the following benefits:

  • Lower connection latency - PolyScale PoP is geographically closer to the client
  • Lower connection latency - the auth handshake process is cached by PolyScale
  • Low query execution time - PolyScale will serve a cache hit in <= 1ms
  • Very high numbers of connections can be established without impacting the origin database
Sequence diagram cache hit

If the data is not cached (cache miss), then the request will be serviced by the already established upstream connection. This provides the following benefit:

  • Lower connection latency - The PolyScale PoP is geographically closer to the client. Since the upstream connection between PolyScale and the origin database is already established, the new connection only needs to connect as far as PolyScale (which has already cached the auth handshake).
Sequence diagram cache miss

If another connection is then made whilst the single upstream connection is in use, another connection is initiated from PolyScale to the origin and once again returned to the pool after servicing the request.

The process repeats to service the required concurrency levels. As noted, if the data is within the Polyscale cache, very large numbers of connections can be serviced without ever impacting the origin database.

When Not to Use Connection Pooling​

As a quick rule of guidance, use pooling if the TCP connections are short lived due to the ephemeral nature of a serverless/FaaS platform. PolyScale’s connection pooling is ideal for environments servicing large numbers of short lived TCP connections.

If the application employs long lived TCP connections, such as those seen by more monolithic applications, pooling will likely not be advantageous. Many ORM’s and database client libraries utilize client side TCP pooling. These will establish and maintain long lived connections from the application, via PolyScale to the origin database. In these scenarios, the client side pooling will offer the best efficies after the initial connections have been established as the constant connections and disconnects seen by serverless functions are not present.

FAQ​

  • How long are upstream connections kept open by PolyScale?
    • While pooling is enabled, connections from PolyScale to the source database are kept open until the database decides to close them. Generally if the connection is inactive the database server triggers a close request after a preconfigured period of time.
  • How many upstream connections can be initiated between PolyScale and the origin database?
    • PolyScale does not limit the number of upstream connections so this is currently limited by the source database itself. We plan to make this configurable in future releases.
  • Are prepared statements supported?
    • Yes, prepared statements are fully supported as part of PolyScale's Connection Pooling.