We learned in the tip LIST THE SERVERS IN YOUR CLUSTER that in YSQL we can list the nodes (hosts) in our cluster via the yb_servers() function.
Example:
yugabyte=# SELECT host, num_connections FROM yb_servers() ORDER BY 1;
host | num_connections
-----------------+-----------------
127.0.0.1 | 0
127.0.0.2 | 0
127.0.0.3 | 0
(3 rows)
yugabyte=# SELECT COUNT(*) FROM pg_stat_activity;
count
-------
2
(1 row)
Note the num_connections column is always zero, even though there are active connections as can been seen in the PG_STAT_ACTIVITY system table.
yugabyte=# SELECT COUNT(*) FROM pg_stat_activity;
count
-------
2
(1 row)
This is because the num_connections column is currently not used as mentioned in the Smart JDBC driver for YSQL documention.
But here is a tip to list the connections for each node.
First, note that the PG_STAT_ACTIVITY system table only has data for the local node, so we’ll have to connect to each node to get the number of connections on each.
Here is an example of doing just that:
[root@localhost yb2]# YB_IP=127.0.0.1
t@localhost yb2]# ysqlsh -h $YB_IP -Atc "SELECT '\! ysqlsh -h ' || host || ' -Atc \"SELECT ''' || host || ''' host, (SELECT COUNT(*)' || CASE WHEN host = '$YB_IP' THEN ' - 1' ELSE '' END || ' FROM pg_stat_activity WHERE state IN (''active'', ''idle'') AND pid <> pg_backend_pid()) active_sessions;\"' FROM yb_servers() ORDER BY host;" | ysqlsh -Ath $YB_IP | awk -F"|" 'NR==1 {print "HOST","SESSIONS"} {gsub(/"/,""); print $1,$2}' | column -t
HOST SESSIONS
127.0.0.1 2
127.0.0.2 2
127.0.0.3 5
Here’s a shell script that’ll do the same as our command above. You can use and modify it as needed.
#!/bin/bash
shopt -s expand_aliases
# exit when any command fails
set -e
# Script: host_session_count.sh
# Description: This script will connect to each node of a YB cluster to get the session count
# Parameters accepted:
# 1. IP Address
# 2. Database to authenticate against
# 3. Database to check for sessions (Optional)
# It is recommended to use a .pgpass file to store the database password for parameter #2
# Created: 11/22/2022
# Last update: 11/23/2022
# Set up
# Check if IP address is provided
if [ -z "$1" ]
then
echo "Please provide an IP address for one host in the cluster."
echo "Usage: host_session_count.sh <<IP Address>> <<Database>>"
exit 1
else
YB_IP=$1;
fi
# Check if database provided
if [ -z "$2" ]
then
echo "Please provide a database name."
echo "Usage: host_session_count.sh <<IP Address>> <<Database>>"
exit 1
else
YB_DATABASE=$2;
fi
# Set up the YSQLSH alias below per your environment
YB_PATH='/root/yugabyte-2.20.1.3/bin/ysqlsh'
YB_USER='yugabyte'
alias y1="$YB_PATH -h $YB_IP -U $YB_USER -d $YB_DATABASE"
alias y2="$YB_PATH -U $YB_USER -d $YB_DATABASE"
# If parameter 3 is provided, verify that it is a valid database
if [ -n "$3" ]
then
tc=`y1 -Atc "SELECT COUNT(*) FROM pg_database WHERE datname = '$3';" | awk '{ print $1'}`
if [ $tc -eq 0 ]
then
echo "The database $3 does not exist"
exit 1
fi
fi
# Get a comma separated list of nodes in the cluster
YB_HOSTS=`y1 -Atc "SELECT string_agg(host, ',' ORDER BY host) FROM yb_servers();"`
# Print a header
echo -e 'HOST|SESSIONS' | awk -F"|" '{printf "%-17s%s\n", $1,$2}'
# Loop through the hosts and print count of sessions (either active or idle)
for host in ${YB_HOSTS//,/ }
do
if [ -z "$3" ]
then
y2 -h $host -Atc "SELECT '$host', COUNT(*) FROM pg_stat_activity WHERE state IN ('active', 'idle') AND pid <> pg_backend_pid();" | awk -F"|" '{printf "%-17s%s\n", $1,$2}'
else
y2 -h $host -Atc "SELECT '$host', COUNT(*) FROM pg_stat_activity WHERE state IN ('active', 'idle') AND pid <> pg_backend_pid() AND datname = '$3';" | awk -F"|" '{printf "%-17s%s\n", $1,$2}'
fi
done
# All done
exit 0
If we run the script, we get the same result…
[root@localhost yb2]# ./host_session_count.sh 127.0.0.1 yugabyte
HOST SESSIONS
127.0.0.1 2
127.0.0.2 2
127.0.0.3 5
Have Fun!
