Managing Client Connections

You can limit the number of active concurrent sessions a user can open to the server. Limiting user sessions lets you better manage sessions and prevents excessive server communication.

You can define connection limits at three levels: user level, for an individual node or entire cluster.

You can use the following parameters to close a session that has been idle for a period of time:

IDLESESSIONTIMEOUT: Sets how much time can elapse before a session times out.
DEFAULTIDLESESSIONTIMEOUT— A configuration parameter that sets the default timeout value for users who do not have idlesessiontimeout set. See Security Parameters.

The IDLESESSIONTIMEOUT parameter applies only to sessions that are idle, not to sessions where query execution is in progress. An idle session is one that has no queries running. If a client is slow or unresponsive during query execution, that time does not apply to timeout. For example, if you are doing a streaming batch insert, the time that it takes to perform this operation is not counted towards timeout.

Use Cases

A user executes a query, and for some reason the query takes an unusually long time to finish (for example, because of server traffic or query complexity). In this case, the user may mistakenly think the query failed and opens another session to run the same query. Now, two sessions are running the same query, using an extra connection.

To prevent this situation, limit the number of sessions a user can run by creating or modifying a user and setting MAXCONNECTIONS. For example:

=> CREATE USER u1 MAXCONNECTIONS 10 ON NODE;

Another issue setting client connections prevents is when a user connects to the server many times. Too many user connections exhausts the number of allowable connections set with the MaxClientSessions parameter (see Managing Sessions).

Note: No user can have a MAXCONNECTIONS limit greater than the MaxClientSessions setting.

Manage Client Sessions

After setting a user's connection limits, you can manage the client sessions by monitoring changes to the user's connection parameters. Also be aware that behavior changes can occur with client connection limits when a node:

Is removed
Is added
Goes down
Comes up

Client Connection Changes

When you modify a user's client connection parameters, be aware that:

Changing a user's MAXCONNECTIONS setting only affects new connections.
Changing a user's connection mode from DATABASE to NODE does not affect current sessions. All new sessions are reserved on the invoking node rather than the entire cluster.
Changing a user's connection mode from NODE to DATABASE does not affect current sessions. New connection requests are reserved on the entire cluster.

Node Going Up or Down

In terms of honoring connection limits, no significant impact exists when nodes go down or come up in between connection requests. No special actions are needed to handle this. However, note the following:

If a node goes down, its active session exits and other nodes in the cluster also drop their sessions. This frees up connections. The query may hang in which case the blocked sessions are reasonable and as expected.

User-Defined Session Timeout

As the DBADMIN, you can set a user's idle session timeout with IDLESESSIONTIMEOUT. A user can edit the session timeout value as follows:

=> SET SESSION IDLESESSIONTIMEOUT 'interval';

In the user role, you can only lower the IDLESESSIONTIMEOUT value. If you attempt to increase the value, the following error message appears:

ERROR 0:  New idlesessiontimeout 00:12 would exceed user limit of 00:10

Run the following query to see the current IDLESESSIONTIMEOUT value:

=> SHOW IDLESESSTIONTIMEOUT;
name  |  idlesessiontimeout
------+---------------------
user1 |  00:08

Set the DEFAULTIDLESESSIONTIMEOUT configuration parameter as the default session timeout value:

=> ALTER DATABASE <dbname> SET DEFAULTIDLESESSIONTIMEOUT = interval;

This value applies to users who do not have an IDLESESSIONTIMEOUT value set.

In the user role, you can lower the idle timeout for a specific session as follows:

=> SET SESSION IDLESESSIONTIMEOUT 'interval';

Close a Session for a User

If necessary, you can manually close a user session with CLOSE_USER_SESSIONS:

=> SELECT CLOSE_USER_SESSIONS ('user-name');
                  close_user_sessions
--------------------------------------------
Session close command sent. Check v_monitor.session for progress