Documentation Home
X DevAPI User Guide
Download this Manual

X DevAPI User Guide  /  ...  /  Connecting to a Single MySQL Server Using Connection Pooling

2.2.2 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 data dictionary that contains a single attribute named pooling. The value for this attribute is another data dictionary containing any combination of the attributes described in the following table:

Table 2.1 Options for Configuring a Connection Pool

Option Meaning Default
enabled Connection pooling enabled 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: 'root', 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=root: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 = {
    'maxSize': '10',
    'maxIdleTime': 30000,
}
client = mysqlx.GetClient(string connection_string, 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

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.