Relationship between query_metrics and sessions system tables?

Rupendra

We were trying to monitor number of active sessions in Vertica cluster, and we thought of about using query_metrics. However, there is a confusion on interpreting the data in this table as following queries do not give the same answer-

SELECT node_name, SUM(ACTIVE_USER_SESSION_COUNT) FROM query_metrics GROUP BY node_name;    SELECT node_name, COUNT(1) FROM sessions GROUP BY node_name;

Am I missing something?

Thanks for your help,
Rupendra

Navin_C

Hi Rupendra,

I believe both tables show different data.
sessions - SESSIONS Monitors external sessions.
Query_Metrics - Monitors the sessions and queries running on each node.


So Query_metrics table actually shows the sessions opened by Vertica itself to distribute the queries and run on different nodes.

So lets say you execute a query on node 1 (initiator) , Vertica will distribute this query to node 2 and node 3 , so you should see three sessions in total one on each node in query metrics table.

But sessions table records the user sessions
try checking the Client type column in sessions table.
it can JDBC / ODBC / vsql. So it is actually Client connections(sessions) to Vertica.

In short if you want to check the total ACTIVE_SESSION_COUNT , you can check it from  

SELECT node_name, COUNT(distinct(session_id)) FROM sessions GROUP BY node_name;

Hope this helps.

Rupendra

Thanks Navin for the reply. This is helpful.

I was reluctant to use sessions table was because of the fact that the monitoring user needs to be in dbadmin or superuser roles to see other users' session information in this table.