Set up YSQL Connection Manager

Start YSQL Connection Manager

To start a YugabyteDB cluster with YSQL Connection Manager, set the yb-tserver flag enable_ysql_conn_mgr to true.

When enable_ysql_conn_mgr is set, each YB-TServer starts the YSQL Connection Manager process along with the PostgreSQL process. You should see one YSQL Connection Manager process per YB-TServer.

Because enable_ysql_conn_mgr is a preview flag, to use it, add the flag to the allowed_preview_flags_csv list (that is, allowed_preview_flags_csv=enable_ysql_conn_mgr).

For example, to create a single-node cluster with YSQL Connection Manager using yugabyted, use the following command:

./bin/yugabyted start --tserver_flags "enable_ysql_conn_mgr=true,allowed_preview_flags_csv={enable_ysql_conn_mgr}" --ui false

To create a large number of client connections, ensure that "SHMMNI" (the maximum number of concurrent shared memory segments an OS allows) as well as ulimit is set correctly as follows:

1. Open the file /etc/sysctl.conf.
2. Add kernel.shmmni = 32768 (support for 30000 clients) at the end of the file.
3. To refresh the settings, use sudo sysctl -p.

Configure

By default, when YSQL Connection Manager is enabled, it uses the port 5433, and the backend database is assigned a random free port.

To explicitly set a port for YSQL, you should specify ports for the flags ysql_conn_mgr_port and ysql_port.

The following table describes YB-TServer flags related to YSQL Connection Manager:

Authentication methods

The following table outlines the various authentication methods supported by YugabyteDB and their compatibility with the YSQL Connection Manager when a connection matches an HBA (Host-Based Authentication) record.

Sticky connections

YSQL Connection Manager enables a larger number of client connections to efficiently share a smaller pool of backend processes using a many-to-one multiplexing model. However, in certain cases, a backend process may enter a state that prevents connection multiplexing between transactions. When this occurs, the backend process remains dedicated to a single client connection (hence the term "sticky connection") for the entire session rather than just a single transaction. This behavior deviates from the typical use case, where backend processes are reassigned after each transaction.

Currently, once formed, sticky connections remain sticky until the end of the session. At the end of the session, the backend process corresponding to a sticky connection is destroyed along with the connection, and the connection does not return to the pool.

When using YSQL Connection Manager, sticky connections can form in the following circumstances:

• Creating TEMP tables.
• Declaring a CURSOR using the WITH HOLD attribute.
• Using a PREPARE query (not to be confused with protocol-level preparation of statements).
• Superuser connections; if you want superuser connections to not be sticky, set the ysql_conn_mgr_superuser_sticky flag to false.
• Using a SEQUENCE with ysql_conn_mgr_sequence_support_mode set to session. (Other values for this flag provide lesser support without stickiness.)
• Replication connections.
• Setting the following configuration parameters during the session:
  • session_authorization
  • role
  • default_tablespace
  • temp_tablespaces
  • Any string-type variables of extensions
  • yb_read_after_commit_visibility

Limitations

• Changes to configuration parameters for a user or database that are set using ALTER ROLE SET or ALTER DATABASE SET queries may reflect in other pre-existing active sessions.
• YSQL Connection Manager can route up to 10,000 connection pools. This includes pools corresponding to dropped users and databases.
• Prepared statements may be visible to other sessions in the same connection pool. {{<issue 24652>}}
• Attempting to use DEALLOCATE/DEALLOCATE ALL queries can result in unexpected behavior. {{<issue 24653>}}
• Currently, you can't apply custom configurations to individual pools. The YSQL Connection Manager configuration applies to all pools.
• When YSQL Connection Manager is enabled, the backend PID stored using JDBC drivers may not be accurate. This does not affect backend-specific functionalities (for example, cancel queries), but this PID should not be used to identify the backend process.
• By default, currval and lastval functions do not work when YSQL Connection Manager is enabled. They can be supported with the help of the ysql_conn_mgr_sequence_support_mode flag.
• YSQL Connection Manager does not yet support IPv6 connections. {{<issue 24765>}}
• Currently, auth-method cert is not supported for host-based authentication. {{<issue 20658>}}
• Although the use of auth-backends (ysql_conn_mgr_use_auth_backend=true) to authenticate client connections can result in higher connection acquisition latencies, using auth-passthrough (ysql_conn_mgr_use_auth_backend=false) may not be suitable depending on your workload. Contact {{% support-general %}} before setting ysql_conn_mgr_use_auth_backend to false. {{<issue 25313>}}
• Salted Challenge Response Authentication Mechanism (SCRAM) is not supported with YSQL Connection Manager. {{<issue 25870>}}
• Unix socket connections to YSQL Connection Manager are not supported. {{<issue 20048>}}
• Connection manager is not supported or included in the MacOS YugabyteDB releases, and there are currently no plans to support it.