2.2.3 Connecting to a Single MySQL Server Using Connection Pooling

X DevAPI supports connection pooling, which can reduce overhead for applications that open many connections to a MySQL Server. Connections are managed as a pool by a Client object. When opening a new Session with a Client, before a new network connection is opened, an attempt is made to retrieve from the pool an existing and currently unused connection, which is then reset and reused.

A connection pool is configured using a single key-value pair (see Connecting Using Key-Value Pairs) that contains a single key named pooling. The value for the pooling key is another set of key-value pairs containing any combination of the keys described in the following table:

Table 2.1 Options for Configuring a Connection Pool

Option	Meaning	Default
enabled	Connection pooling enabled. When the option is set to false, a regular, non-pooled connection is returned, and the other connection pool options listed below are ignored.	true
maxSize	The maximum number of connections allowed in the pool	25
maxIdleTime	The maximum number of milliseconds a connection is allowed to idle in the queue before being closed. A zero value means infinite.	0
queueTimeout	The maximum number of milliseconds a request is allowed to wait for a connection to become available. A zero value means infinite	0

Closing the Session marks the underlying connection as unused and returns it to the Client object's connection pool.

Closing the Client object closes all connections it manages, invalidates all Sessions the Client has created, and destroys the managed pool.

Note

Connection pooling is not supported by MySQL Shell.

Node.js JavaScript Code

var mysqlx = require('@mysql/xdevapi');
var client = mysqlx.getClient(
  { user: 'user', host: 'localhost', port: 33060 },
  { pooling: { enabled: true, maxIdleTime: 30000, maxSize: 25, queueTimeout: 10000 } }
);
client.getSession()
  .then(session => {
    console.log(session.inspect())
    return session.close() // the connection becomes idle in the client pool
  })
  .then(() => {
    return client.getSession()
  })
  .then(session => {
    console.log(session.inspect())
    return client.close() // closes all connections and destroys the pool
  })

C# Code

using (Client client = MySQLX.GetClient("server=localhost;user=user:port=33060;",
  new { pooling = new { Enabled = true, MaxSize = 100, MaxIdleTime=30000, QueueTimeout = 10000 } }))
   {
      using (Session session = client.GetSession())
      {
         foreach (Collection coll in session.Schema.GetCollections())
         {
            Console.WriteLine(coll.Name);
         }
      } // session.Dispose() is called and the session becomes idle in the pool
   } // client.Dispose() is called then all sessions are closed and pool is destroyed

Python Code

connection_string = {
    'host': 'localhost',
    'port': 37210,
    'user': 'user',
    'password': 'password'
}
client_options = {
    'pooling': {
        "max_size": 10,
        "max_idle_time": 30000
    }
}
client = mysqlx.get_client(connection_string, client_options)
session1 = client.get_session()
session2 = client.get_session()

# closing all the sessions
client.close()

Java Code

//Obtain new ClientFactory
ClientFactory cf = new ClientFactory(); 

//Obtain Client from ClientFactory
Client cli = cf.getClient(this.baseUrl, "{\"pooling\":{\"enabled\":true, \"maxSize\":8, 
  \"maxIdleTime\":30000, \"queueTimeout\":10000} }");
Session sess = cli.getSession();

//Use Session as usual

//Close Client after use
cli.close();

C++ Code

using namespace mysqlx;

Client cli("user:password@host_name/db_name", ClientOption::POOL_MAX_SIZE, 7);
Session sess = cli.getSession();

// use Session sess as usual

cli.close();  // close all Sessions

Connector/C++ Code using X DevAPI for C

char error_buf[255];
int  error_code;

mysqlx_client_t *cli
 = mysqlx_get_client_from_url(
     "user:password@host_name/db_name", "{ \"maxSize\": 7 }", error_buf, &error_code
   );
mysqlx_session_t *sess = mysqlx_get_session_from_client(cli);

// use sess as before

mysqlx_close_client(cli);  // close session sess