As highlighted in Distributed PostgreSQL on a Google Spanner Architecture – Query Layer, YSQL reuses the open source PostgreSQL query layer (written in C) as much as possible and as a result is wire-compatible with PostgreSQL dialect and client drivers.
In Postgres you can get a count of sessions in your database by querying the PG_STAT_ACTIVITY system table.
Example:
postgres=# SELECT COUNT(*) FROM pg_stat_activity WHERE state IN ('active', 'idle') AND pid <> pg_backend_pid();
count
-------
3
(1 row)
YugabyteDB is a distributed database typically composed of more than one node.
The data available in PG_STAT_ACTIVITY is limited to the locale node, so to get a session count in YugabyteDB, we have to connect to each of the nodes in the Universe, get a session count from each.
Example:
[yugabyte@ip-172-161-52-255 ~]$ YB_IP=`ip addr show ens5 | grep -Po 'inet \K[\d.]+'`
[yugabyte@ip-172-161-52-255 ~]$ echo $YB_IP
172.161.52.255
[yugabyte@ip-172-161-52-255 ~]$ 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
172.150.43.0 6
172.152.101.162 1
172.161.52.255 0
Below is a bash shell script that encapulates the session count logic for ease of use.
#!/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='/home/yugabyte/yb-software/yugabyte-2.15.3.2-b1-linux-x86_64/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
Example:
[yugabyte@ip-172-161-52-255 ~]$ ./host_session_count.sh 172.161.52.255 yugabyte
HOST SESSIONS
172.150.43.0 6
172.152.101.162 1
172.161.52.255 0
Have Fun!