To find the number of transactions per second for the given PostgreSQL DB instance run the following script.
#!/bin/sh
###############################################
#
# Check transactions per second
# for the given PostgreSQL DB instance
#
# This script connects
# to the given PostgreSQL DB instance
# and retrieves information -
# the number of transactions per second
#
# Date: 10-Aug-2020
#
# Author: Dmitry
#
###############################################
helpFunction()
{
echo " "
echo "Usage: ${0} -h db_hostname -p port -U db_user -d db_name"
echo "\t-h Postgres hostname"
echo "\t-p Postgers port"
echo "\t-U Postgres db user"
echo "\t-d Postgres db name"
echo " "
echo "Example how to run: ${0} -h my_db_host -p 5432 -U my_db_user -d my_db_name "
echo " "
exit 1 # Exit script after printing help
}
trim() {
local var="$*"
# remove leading whitespace characters
var="${var#"${var%%[![:space:]]*}"}"
# remove trailing whitespace characters
var="${var%"${var##*[![:space:]]}"}"
printf '%s' "$var"
}
while getopts "h:p:U:d:" opt
do
case "$opt" in
h ) inpHost="$OPTARG" ;;
p ) inpPort="$OPTARG" ;;
U ) inpDBUser="$OPTARG" ;;
d ) inpDBName="$OPTARG" ;;
? ) helpFunction ;; # Print helpFunction in case parameter is non-existent
esac
done
# Print helpFunction in case parameters are empty
if [ -z "$inpHost" ] || [ -z "$inpPort" ] || [ -z "$inpDBUser" ] || [ -z "$inpDBName" ]
then
echo "Some or all of the parameters are empty";
helpFunction
fi
a=$(psql -h $inpHost -p $inpPort -U $inpDBUser -d $inpDBName -t -c "select clock_timestamp() t, sum(xact_commit+xact_rollback) n, round(extract(epoch from now())) tt from pg_stat_database")
a=`trim $a`
tps_a_t=`echo $a | awk ' { printf("%s %s", $1, $2) } '`
tps_a=`echo $a | awk ' { print $4 } '`
tps_a_tt=`echo $a | awk ' { print $6 } '`
sleep 1
b=$(psql -h $inpHost -p $inpPort -U $inpDBUser -d $inpDBName -t -c "select clock_timestamp() t, sum(xact_commit+xact_rollback) n, round(extract(epoch from now())) tt from pg_stat_database")
b=`trim $b`
tps_b_t=`echo $b | awk ' { printf("%s %s", $1, $2) } '`
tps_b=`echo $b | awk ' { print $4 } '`
tps_b_tt=`echo $b | awk ' { print $6 } '`
# echo "($tps_a_t, $tps_a_tt, $tps_a)"
# echo "($tps_b_t, $tps_b_tt, $tps_b)"
x=$((tps_b-tps_a))
y=$((tps_b_tt-tps_a_tt))
# echo "x: $x"
# echo "y: $y"
tps=$((x/y))
echo "tps: $tps"
Top comments (0)