Simple & Best Postgres Free Monitoring Script
Following steps will help you to improve the Postgresql database server performance:
First you have to issue the top command then see the process which one process is taking more CPU utilization then note that PID, if that process is postgres process means use this script you can find out query and their status and timing else you can use following query to finding query
[root@tutorialdba.com]# cat monitor.sh
echo " "
echo " "
HOSTNAME="101.0.1.123"
PSQL="/opt/edb/as9.6/bin/psql"
PORT=5444
DB="edb"
USER="enterprisedb"
export PGPASSWORD="tcs@12345"
echo "ENTER THE TIME IN MINUTES ?"
echo "For example if you give 10 means it will display "
echo "queries which is running more than 10 minutes"
echo "0 means will display all queries:"
echo ""
read time
echo " "
echo " "
echo "WHAT ARE THE QUERY IS RUNING MORE THAN $time MINUTES"
$PSQL -d $DB -U $USER -p $PORT -h $HOSTNAME <<EOF
\pset format wrapped
SELECT pid, now() - query_start as "runtime", usename, datname, state, query
FROM pg_stat_activity
WHERE now() - query_start > '$time minutes'::interval
ORDER BY runtime DESC;
EOF
echo "*********************COMPLETED 1****************************** "
echo " "
echo " "
echo " "
echo "CHECKING dISK SPACE:"
df -h
echo "*********************COMPLETED 2****************************** "
echo " "
echo " "
echo " "
echo "CHECKING RAM USAGE:"
free -h
echo "*********************COMPLETED 3****************************** "
echo " "
echo " "
echo " "
echo "ENTER THE BLOATED COUNT ? "
echo "For example if you give 1000 means it will display the table count"
echo "how many table having more than 1000 dead tubles that database:"
echo ""
read count
echo " "
echo " "
echo "HOW MANY TABLES HAVING MORE THAN $count BLOATED TABLE :"
$PSQL -d $DB -U $USER -p $PORT -h $HOSTNAME <<EOF
\c LC
select count(*) from pg_stat_all_tables where n_dead_tup > $count;
EOF
echo " "
$PSQL -d $DB -U $USER -p $PORT -h $HOSTNAME <<EOF
\c DC
select count(*) from pg_stat_all_tables where n_dead_tup >$count;
EOF
echo "*********************COMPLETED 4****************************** "
echo " "
echo " "
echo " "
echo "ENTER THE BLOATED COUNT AGAING ? "
echo "it will give schema,table name and dead tuble count including "
echo "with vacuum & analyze script you can copy and run it to the "
echo "SQL prompt what are the table having more Bloated table "
echo ""
read count1
echo ""
echo ""
echo "WHAT ARE THE TABLE HAVING MORE THAN $count1 BLOATED TABLE : "
$PSQL -d $DB -U $USER -p $PORT -h $HOSTNAME <<EOF
\c LC
select 'vacuum ANALYZE '||schemaname||'.'||relname||';' from pg_stat_all_tables where n_dead_tup >$count1;
select schemaname,relname,n_dead_tup from pg_stat_all_tables where n_dead_tup >$count1;
EOF
echo " "
$PSQL -d $DB -U $USER -p $PORT -h $HOSTNAME <<EOF
\c DC
select 'vacuum ANALYZE '||schemaname||'.'||relname||';' from pg_stat_all_tables where n_dead_tup >$count1;
select schemaname,relname,n_dead_tup from pg_stat_all_tables where n_dead_tup >$count1;
EOF
echo "*********************COMPLETED 5****************************** "
echo " "
echo " "
echo " "
echo " "
SAMPLE OUTPUT:
[root@ip-10-0-1-109 ec2-user]# sh monitor.sh
ENTER THE TIME IN MINUTES ?
For example if you give 10 means it will display
queries which is running more than 10 minutes
0 means will display all queries:
4
WHAT ARE THE QUERY IS RUNING MORE THAN 4 MINUTES
Output format is wrapped.
pid | runtime | usename | datname | state | query
-------+-----------------+----------+----------------+-------+------------
15832 | 00:05:57.796746 | hr | LC | idle | unlisten *
15465 | 00:05:35.23556 | ehis | LC | idle | unlisten *
15113 | 00:05:34.880537 | billing | LC | idle | unlisten *
15918 | 00:04:50.816381 | security | LC | idle | unlisten *
15930 | 00:04:48.148488 | billing | DC | idle | unlisten *
...
..
(10 rows)
*********************COMPLETED 1******************************
CHECKING dISK SPACE:
Filesystem Size Used Avail Use% Mounted on
/dev/xvda2 250G 2.0G 249G 1% /
devtmpfs 16G 0 16G 0% /dev
tmpfs 16G 8.0K 16G 1% /dev/shm
tmpfs 16G 177M 16G 2% /run
tmpfs 16G 0 16G 0% /sys/fs/cgroup
/dev/xvdb 200G 33G 168G 17% /opt
tmpfs 3.2G 0 3.2G 0% /run/user/1000
*********************COMPLETED 2******************************
CHECKING RAM USAGE:
total used free shared buff/cache available
Mem: 31G 717M 27G 1.3G 2.7G 28G
Swap: 0B 0B 0B
*********************COMPLETED 3******************************
ENTER THE BLOATED COUNT ?
For example if you give 1000 means it will display the table count
how many table having more than 1000 dead tubles that database:
1500
HOW MANY TABLES HAVING MORE THAN 1500 BLOATED TABLE :
You are now connected to database "LC" as user "enterprisedb".
count
-------
3
(1 row)
You are now connected to database "DC" as user "enterprisedb".
count
-------
1
(1 row)
*********************COMPLETED 4******************************
ENTER THE BLOATED COUNT AGAING ?
it will give schema,table name and dead tuble count including
with vacuum & analyze script you can copy and run it to the
SQL prompt what are the table having more Bloated table
2000
WHAT ARE THE TABLE HAVING MORE THAN 2000 BLOATED TABLE :
You are now connected to database "LC" as user "enterprisedb".
?column?
-----------------------------------------
vacuum ANALYZE pg_catalog.pg_attribute;
vacuum ANALYZE pg_catalog.pg_depend;
vacuum ANALYZE pg_catalog.pg_shdepend;
(3 rows)
schemaname | relname | n_dead_tup
------------+--------------+------------
pg_catalog | pg_attribute | 21218
pg_catalog | pg_depend | 9869
pg_catalog | pg_shdepend | 2730
(3 rows)
You are now connected to database "DC" as user "enterprisedb".
?column?
----------------------------------------
vacuum ANALYZE pg_catalog.pg_shdepend;
(1 row)
schemaname | relname | n_dead_tup
------------+-------------+------------
pg_catalog | pg_shdepend | 2730
(1 row)
*********************COMPLETED 5******************************
vacuum and analyze the catalog table which is having more dead tuble, Here pg_attribute havig more dead tuble that is what am chose it and vacuum and analyze that.
LC=# vacuum ANALYZE pg_catalog.pg_attribute;
VACUUM