ClickHouse DB::Exception: User session limit exceeded

The "DB::Exception: User session limit exceeded" error in ClickHouse is raised when a user attempts to open a new connection but has already reached the maximum number of concurrent sessions allowed by their profile. The USER_SESSION_LIMIT_EXCEEDED error code enforces a per-user cap on simultaneous connections.

Impact

New connections from the affected user are rejected until existing sessions are closed. Active sessions continue to work, but no additional queries can begin. This can severely impact applications that open many parallel connections, and it may cause connection pool failures in client applications.

Common Causes

1. The user's max_sessions_for_user setting is configured too low for the workload
2. A connection pool is opening more connections than the user's session limit allows
3. Application bugs or connection leaks leaving sessions open without closing them
4. Multiple services or instances sharing the same ClickHouse user account
5. Long-running queries keeping sessions active, reducing the pool of available session slots
6. Retry logic that opens new connections without closing failed ones

Troubleshooting and Resolution Steps

1. Check the current session limit for the user:

   SHOW CREATE USER my_user;
   

   Look for MAX_SESSIONS_FOR_USER or the related profile settings.

2. View active sessions:

   SELECT user, count() AS active_sessions
   FROM system.processes
   WHERE user = 'my_user'
   GROUP BY user;
   

3. Increase the session limit if it is too restrictive:

   ALTER USER my_user SETTINGS max_sessions_for_user = 50;
   

   Or adjust it in the user's settings profile.

4. Identify and kill idle or stuck sessions:

   SELECT query_id, elapsed, query
   FROM system.processes
   WHERE user = 'my_user'
   ORDER BY elapsed DESC;
   

   Kill specific queries to free sessions:

   KILL QUERY WHERE query_id = 'stuck_query_id';
   

5. Fix connection leaks in the application:

   • Ensure every connection is properly closed after use, including in error handling paths.
   • Review connection pool settings (min/max connections, idle timeout) to align with the ClickHouse session limit.

6. Distribute load across multiple user accounts:

   • If several applications share one account, create dedicated users for each with appropriate session limits.

Best Practices

• Set max_sessions_for_user based on the expected concurrency of each user or service account.
• Use connection pooling in client applications and configure the pool's max connections to stay within the ClickHouse session limit.
• Implement proper connection lifecycle management: acquire, use, and release connections promptly.
• Monitor system.processes to track session utilization trends and detect connection leaks.
• Use separate user accounts for different applications to isolate session limits and simplify troubleshooting.
• Set idle session timeouts to automatically clean up abandoned connections.

Frequently Asked Questions

Q: Where is the session limit configured?
A: The limit is controlled by the max_sessions_for_user setting, which can be set per user (ALTER USER ... SETTINGS max_sessions_for_user = N) or in a settings profile applied to the user.

Q: Does this limit apply to HTTP and native protocol connections separately?
A: No. The limit is per user across all protocols. An HTTP connection and a native protocol connection from the same user both count toward the same limit.

Q: How do I find out which connections are consuming my session slots?
A: Query system.processes filtered by the user name. Each row represents an active query or session. You can also check system.session_log for historical session data.

Q: Can I set different session limits for different users?
A: Yes. Each user can have their own max_sessions_for_user value, or you can use settings profiles to group users with similar limits.