Unable To Connect To Vertica Database

glenfilips

Hello Vertica team,
I am facing the below error every time When I try to connect Vertica database using a console like
vsql -U dbadmin -w
vsql: FATAL 4060: New session rejected due to limit, already 5 sessions active
Vertica admintools and management console continues to report database is up but can't allow a new connection. Please help me to solve it. learn more
Thanks & Regards
Filips

mosheg

By default Vertica allows 50 client sessions and an additional 5 administrator sessions.
The system returns a message if the database exceeds the limit.
To solve your issue try to stop one of the dbadmin (DB administrator) sessions and
modify the MaxClientSessions parameter.

For example, to increase the number of MaxClientSessions to 100, issue the following:
• To determine the original value for the MaxClientSessions parameter:
SELECT CURRENT_VALUE FROM CONFIGURATION_PARAMETERS WHERE parameter_name='MaxClientSessions';

• To increase the MaxClientSessions parameter to 100.
SELECT SET_CONFIG_PARAMETER('MaxClientSessions', 100);

joliveira

Greetings to everyone!
Taking advantage of the theme, I also received the message:

[Vertica]VJDBC FATAL: New session rejected due to limit, already 1000 sessions active

I'm already using the connections limit, according to the documentation:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/ManageWorkloads/ManagingSessions.htm

I need to find out which application/user caused this, is there a table that stores the access history?

Bryan_H

The system table "dc_session_starts" records a history of new sessions. You should be able to use some count or aggregate function there to determine who is creating many sessions.

joliveira

Thanks for the feedback, with this information I started the investigation, based on the query below, I got some information.

SELECT dri.node_name, drc.user_name, count(dri.transaction_id)     FROM dc_requests_issued dri     JOIN dc_requests_completed drc     USING (node_name, session_id, request_id)     WHERE dri.time between 'May 6,2023 14:30:00'::timestamptz and 'May 6,2023 15:00:00'::timestamptz    group by dri.node_name, drc.user_name order by node_name;

However, it did not return data in the production database, only in the non-production environment, which parameter is used in this data storage in the data_collector table?

SergeB

@joliveira This could be due to the Data Collector retention policies needing to be expanded in your production environment.

Default retention policy for RequestsCompleted and RequestsIssued

select get_data_collector_policy('RequestsCompleted');

get_data_collector_policy

2000KB kept in memory, 256000KB kept on disk. Time based retention disabled.
(1 row)

select get_data_collector_policy('RequestsIssued');

get_data_collector_policy

2000KB kept in memory, 256000KB kept on disk. Time based retention disabled.

You might want to to up the amount of data kept in memory and/or on disk

see: https://docs.vertica.com/12.0.x/en/sql-reference/functions/management-functions/data-collector-functions/set-data-collector-policy/

joliveira

@SergeB and @Bryan_H,
Thanks for the support and quick response.