How to

• Limit the maximum connections in YCQL and YSQL from the Client and Server sides.

Solution

Connection limits can be configured both at an application(client) side and Server side. Increasing the max connections helps to execute more concurrent queries. Setting the maximum connection also guardrail the cluster and avoid large number of inflight queries.  However, increasing the maximum connections to a very high value can cause more harm than good. This is because having a large number of concurrent connections to a T-server node can cause resource contention and can cause potential stability issues to the cluster. The optimal value is determined by considering the size of the cluster, the number of queries to be executed in parallel, and the load on the cluster. 

YCQL

Client-Side

The configuration name is dependent on the driver used. Below are widely used drivers and respective configurations:

C/C++

Sets the maximum number of connections made to each server in each IO thread.

// default value is 2
cass_cluster_set_max_connections_per_host(cluster,10)

Go

The maximum number of connections is calculated based on the total number of hosts. Per hosts, by default, the maximum client connection is 2. This means if there are n hosts. 2n connections are possible.

This is controlled by the configuration:

// number of connections per host (default: 2)
cluster.NumConns = 2

C#

The maximum number of connections per host can be set using the "SetMaxConnectionsPerHost()" method of the connectionPool class. 

//default is 2
poolingOptions.SetMaxConnectionsPerHost(5);

Java/Scala

Yugabyte JDBC Driver can be used in Java, Scala, or any other JVM-based language. 

PoolingOptions poolingOptions = new PoolingOptions();
// customize options...

poolingOptions
    .setCoreConnectionsPerHost(HostDistance.LOCAL,  4)
    .setMaxConnectionsPerHost( HostDistance.LOCAL, 10)
    .setCoreConnectionsPerHost(HostDistance.REMOTE, 2)
    .setMaxConnectionsPerHost( HostDistance.REMOTE, 4);

Cluster cluster = Cluster.builder() .withContactPoints("127.0.0.1") .withPoolingOptions(poolingOptions) .build();

The maximum number of concurrent YCQL connections that can be established to the T-Server is not configurable.

YSQL

Server Side

The maximum number of concurrent YSQL connections that can be established to the T-Server is controlled by the Tserver gflag configuration.

 --ysql_max_connections

The default value of the configuration is 300.

There are other gflag configurations to control the number of underlying connections to each Tserver from a PostgreSQL backend process. . The relevant Tserver flag is 

 --pggate_num_connections_to_server

The default value is 1.

The total number of connections to a Tserver is controlled by the Tserver glfag 

num_connections_to_server

The default value is 8. 

Please note setting `pggate_num_connections_to_server` overrides the value of `num_connections_to_server`

Please follow the documentation to set Tserver gflags. 

Client-Side

YSQL client side API is compatible with Postgres. The connection pool feature in Postgres is available in Yugabyte as well. For example, the Yugabyte JDBC driver can be configured with popular pooling solutions such as Hikari and Tomcat.  More details on configuring the connection pools is available in the Yugabyte JBDC driver documentation