Boost C++ Libraries

Connection pooling is a technique where several long-lived connections are re-used for independent logical operations. When compared to establishing individual connections, it has the following benefits:

This is how you can create a pool of connections:

// pool_params contains configuration for the pool.
// You must specify enough information to establish a connection,
// including the server address and credentials.
// You can configure a lot of other things, like pool limits
boost::mysql::pool_params params;
params.server_address.emplace_host_and_port(server_hostname);
params.username = mysql_username;
params.password = mysql_password;
params.database = "boost_mysql_examples";

// The I/O context, required by all I/O operations
boost::asio::io_context ctx;

// Construct a pool of connections. The context will be used internally
// to create the connections and other I/O objects
boost::mysql::connection_pool pool(ctx, std::move(params));

// You need to call async_run on the pool before doing anything useful with it.
// async_run creates connections and keeps them healthy. It must be called
// only once per pool.
// The detached completion token means that we don't want to be notified when
// the operation ends. It's similar to a no-op callback.
pool.async_run(boost::asio::detached);

connection_pool is an I/O object that manages connections. It can be constructed from an executor or execution context (like all I/O objects) and a pool_params object.

connection_pool::async_run must be called exactly once per pool. This function takes care of actually keeping connections healthy.

We're now ready to obtain connections using connection_pool::async_get_connection. We will use C++20 coroutines to make async code simpler:

// Use connection pools for functions that will be called
// repeatedly during the application lifetime.
// An HTTP server handler function is a good candidate.
boost::asio::awaitable<std::int64_t> get_num_employees(boost::mysql::connection_pool& pool)
{
    // Get a fresh connection from the pool.
    // pooled_connection is a proxy to an any_connection object.
    boost::mysql::pooled_connection conn = co_await pool.async_get_connection(boost::asio::use_awaitable);

    // Use pooled_connection::operator-> to access the underlying any_connection.
    // Let's use the connection
    results result;
    co_await conn->async_execute("SELECT COUNT(*) FROM employee", result, boost::asio::use_awaitable);
    co_return result.rows().at(0).at(0).as_int64();

    // When conn is destroyed, the connection is returned to the pool
}

By default, connection_pool::async_run will run forever. When your application exits, you will want to stop it using connection_pool::cancel. This is typical in signal handlers, to guarantee a clean shutdown.

Note that pooling works only with any_connection.

Pool size

Pools start with a fixed initial size, and will be dynamically resized up to an upper limit if required. You can configure these sizes using pool_params::initial_size and pool_params::max_size.

The resizing algorithm works like this:

By default, pool_params::max_size is 151, which is MySQL's default value for the max_connections system variable, controlling the maximum number of concurrent connections allowed by the server.

This is how you configure pool sizes:

boost::mysql::pool_params params;

// Set the usual params
params.server_address.emplace_host_and_port(server_hostname);
params.username = mysql_username;
params.password = mysql_password;
params.database = "boost_mysql_examples";

// Create 10 connections at startup, and allow up to 1000 connections
params.initial_size = 10;
params.max_size = 1000;

boost::mysql::connection_pool pool(ctx, std::move(params));

Session state

MySQL connections hold state. You change session state when you prepare statements, create temporary tables, start transactions, or set session variables. When using pooled connections, session state can be problematic: if not reset properly, state from a previous operation may affect subsequent ones.

After you return a connection to the pool, the equivalent of any_connection::async_reset_connection and async_set_character_set are used to wipe session state before the connection can be obtained again. This will deallocate prepared statements, rollback uncommitted transactions, clear variables and restore the connection's character set to utf8mb4. In particular, you don't need to call any_connection::async_close_statement to deallocate statements.

Resetting a connection is cheap but entails a cost (a roundtrip to the server). If you've used a connection and you know that you didn't mutate session state, you can use pooled_connection::return_without_reset to skip resetting. For instance:

// Get a connection from the pool
boost::mysql::pooled_connection conn = co_await pool.async_get_connection(boost::asio::use_awaitable);

// Use the connection in a way that doesn't mutate session state.
// We're not setting variables, preparing statements or starting transactions,
// so it's safe to skip reset
boost::mysql::results result;
co_await conn->async_execute("SELECT COUNT(*) FROM employee", result, boost::asio::use_awaitable);

// Explicitly return the connection to the pool, skipping reset
conn.return_without_reset();

Connection reset happens in the background, after the connection has been returned, so it does not affect latency. If you're not sure if an operation affects state or not, assume it does.

Character set

Pooled connections always use utf8mb4 as its character set. When connections are reset, the equivalent of any_connection::async_set_character_set is used to restore the character set to utf8mb4 (recall that raw async_reset_connection will wipe character set data).

Pooled connections always know the character set they're using. This means that any_connection::format_opts and current_character_set always succeed.

We recommend to always stick to utf8mb4. If you really need to use any other character set, use async_set_character_set on your connection after it's been retrieved from the pool.

Connection lifecycle

The behavior already explained can be summarized using a state model like the following:

In short:

Thread-safety and executors

By default, connection_pool is NOT thread-safe, but it can be easily made thread-safe by using:

// The I/O context, required by all I/O operations
boost::asio::io_context ctx;

// The usual pool configuration params
boost::mysql::pool_params params;
params.server_address.emplace_host_and_port(server_hostname);
params.username = mysql_username;
params.password = mysql_password;
params.database = "boost_mysql_examples";

// By passing pool_executor_params::thread_safe to connection_pool,
// we make all its member functions thread-safe.
// This works by creating a strand.
boost::mysql::connection_pool pool(
    boost::mysql::pool_executor_params::thread_safe(ctx.get_executor()),
    std::move(params)
);

// We can now pass a reference to pool to other threads,
// and call async_get_connection concurrently without problem.
// Individual connections are still not thread-safe.

This works by using strands. Recall that a boost::asio::strand is Asio's method to enable concurrency without explicit locking. A strand is an executor that wraps another executor. All handlers dispatched through a strand will be serialized: no two handlers will be run in parallel, which avoids data races.

We're passing a pool_executor_params instance to the pool's constructor, which contains two executors:

Transport types and TLS

You can use the same set of transports as when working with any_connection: plaintext TCP, TLS over TCP or UNIX sockets. You can configure them using pool_params::server_address and pool_params::ssl. By default, TLS over TCP will be used if the server supports it, falling back to plaintext TCP if it does not.

You can use pool_params::ssl_ctx to configure TLS options for connections created by the pool. If no context is provided, one will be created for you internally.

Implementing sync functions

connection_pool is internally implemented in terms of any_connection async functions because:

You can build a sync connection pool on top of connection_pool using code like this:

// Wraps a connection_pool and offers a sync interface.
// sync_pool is thread-safe
class sync_pool
{
    // A thread pool with a single thread. This is used to
    // run the connection pool. The thread is automatically
    // joined when sync_pool is destroyed.
    boost::asio::thread_pool thread_pool_{1};

    // The async connection pool
    boost::mysql::connection_pool conn_pool_;

public:
    // Constructor: constructs the connection_pool object from
    // the single-thread pool and calls async_run.
    // The pool has a single thread, which creates an implicit strand.
    // There is no need to use pool_executor_params::thread_safe
    sync_pool(boost::mysql::pool_params params) : conn_pool_(thread_pool_, std::move(params))
    {
        // Run the pool in the background (this is performed by the thread_pool thread).
        // When sync_pool is destroyed, this task will be stopped and joined automatically.
        conn_pool_.async_run(boost::asio::detached);
    }

    // Retrieves a connection from the pool (error code version)
    boost::mysql::pooled_connection get_connection(
        boost::mysql::error_code& ec,
        boost::mysql::diagnostics& diag,
        std::chrono::steady_clock::duration timeout = std::chrono::seconds(30)
    )
    {
        // The completion token to use for the async initiation function.
        // use_future will make the async function return a std::future object, which will
        // become ready when the operation completes.
        // as_tuple prevents the future from throwing on error, and packages the result as a tuple.
        // The returned future will be std::future<std::tuple<error_code, pooled_connection>>.
        constexpr auto completion_token = boost::asio::as_tuple(boost::asio::use_future);

        // We will use std::tie to decompose the tuple into its components.
        // We need to declare the connection before using std::tie
        boost::mysql::pooled_connection res;

        // async_get_connection returns a future. Calling std::future::get will
        // wait for the future to become ready
        std::tie(ec, res) = conn_pool_.async_get_connection(timeout, diag, completion_token).get();

        // Done!
        return res;
    }

    // Retrieves a connection from the pool (exception version)
    boost::mysql::pooled_connection get_connection(
        std::chrono::steady_clock::duration timeout = std::chrono::seconds(30)
    )
    {
        // Call the error code version
        boost::mysql::error_code ec;
        boost::mysql::diagnostics diag;
        auto res = get_connection(ec, diag, timeout);

        // This will throw boost::mysql::error_with_diagnostics on error
        boost::mysql::throw_on_error(ec, diag);

        // Done
        return res;
    }
};

Benchmarks

A throughput benchmark has been conducted to assess the performance gain provided by connection_pool. Benchmark code is under bench/connection_pool.cpp. The test goes as follows:

We can see that pooling significantly increases throughput. This is specially true when communication with the server is expensive (as is the case when using TLS over TCP). The performance gain is likely to increase over high-latency networks, and to decrease for heavyweight queries, since the connection establishment has less overall weight.