Daily monitoring PostgreSQL master and slave server and schedule vacuum and change postgreSQL parametrer

Here i expalined that""How to increase the performance  postgresql server and i written script for vacuum database and changing PostgreSQL performance  parameter

cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.0 (Maipo)
bash-4.2$

Checking the master and slave IP and issues the top command any process is taken more cpu utilization:

 master 192.168.1.1 and slave 192.168.2.5:

MASTER TOP:

bash-4.2$ top -o %CPU
top - 15:37:26 up 8 days,  2:03,  1 user,  load average: 11.43, 23.29, 19.44
Tasks: 449 total,  16 running, 433 sleeping,   0 stopped,   0 zombie
%Cpu(s): 21.6 us,  3.6 sy,  0.0 ni, 44.6 id, 27.9 wa,  0.0 hi,  0.1 si,  2.3 st
KiB Mem:  49458536 total, 49169124 used,   289412 free,    29352 buffers
KiB Swap: 16777212 total,   147776 used, 16629436 free. 39245096 cached Mem

SLAVE TOP:

bash-4.2$ top -o %CPU
top - 15:54:06 up 8 days,  2:20,  1 user,  load average: 0.28, 0.30, 0.36
Tasks: 549 total,   2 running, 547 sleeping,   0 stopped,   0 zombie
%Cpu(s):  1.5 us,  0.4 sy,  0.0 ni, 97.8 id,  0.3 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem:  32943544 total, 32493288 used,   450256 free,   160384 buffers
KiB Swap: 16777212 total,    53420 used, 16723792 free. 19230732 cached Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
38296 postgres  20   0 12.508g 287932 283072 S  12.9  0.9   0:05.58 postgres
20492 postgres  20   0 12.507g 3.015g 3.011g S   5.0  9.6   1:35.30 postgres
20504 postgres  20   0 12.510g   3120   1864 S   2.3  0.0   1:51.05 postgres
39588 postgres  20   0 12.509g  13636  10708 S   0.7  0.0   0:00.13 postgres

1.CHECK THE RAM USAGE:

MASTER:
bash-4.2$ free -h
             total       used       free     shared    buffers     cached
Mem:           47G        46G       279M       8.2G        27M        37G
-/+ buffers/cache:       9.5G        37G
Swap:          15G       151M        15G


SLAVE:
bash-4.2$ free -h
             total       used       free     shared    buffers     cached
Mem:           31G        30G       433M        10G       156M        18G
-/+ buffers/cache:        12G        18G
Swap:          15G        52M        15G

2.CHECK THE DISK SIZE FOR AVODING DATA FULL OF PARTITION:

MASTER:

bash-4.2$ df -h
Filesystem                    Size  Used Avail Use% Mounted on
/dev/vda1                      20G  1.8G   17G  10% /
devtmpfs                       24G     0   24G   0% /dev
tmpfs                          24G  4.0K   24G   1% /dev/shm
tmpfs                          24G   41M   24G   1% /run
tmpfs                          24G     0   24G   0% /sys/fs/cgroup
/dev/mapper/vgzero-lvxlog     197G   61M  187G   1% /pg_xlog
/dev/mapper/vgzero-lvhome      99G  195M   94G   1% /home
/dev/mapper/vgzero-lvarchive  197G  8.2G  179G   5% /archive
/dev/mapper/vgzero-lvbackup   296G  180G  101G  65% /backup
/dev/mapper/vgzero-lvdata     2.2T  801G  1.3T  39% /data

SLAVE:

bash-4.2$ df -h
Filesystem                    Size  Used Avail Use% Mounted on
/dev/vda1                      20G  1.8G   17G  10% /
devtmpfs                       16G     0   16G   0% /dev
tmpfs                          16G  4.0K   16G   1% /dev/shm
tmpfs                          16G   65M   16G   1% /run
tmpfs                          16G     0   16G   0% /sys/fs/cgroup
/dev/mapper/vgzero-lvhome      99G  189M   94G   1% /home
/dev/mapper/vgzero-lvarchive  197G  829M  186G   1% /archive
/dev/mapper/vgzero-lvxlog     197G   61M  187G   1% /pg_xlog
/dev/mapper/vgzero-lvbackup   296G  193M  280G   1% /backup
/dev/mapper/vgzero-lvdata     1.2T  787G  335G  71% /data

3.CHECK THE ALL DATABASES SIZES.

MASTER: DATABASE

SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname)/1024/1024/1024 AS size_in_GB FROM pg_database ORDER by size_in_GB DESC;

SLAVE: DATABASE

SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname)/1024/1024/1024 AS size_in_GB FROM pg_database ORDER by size_in_GB DESC;

PostgreSQL Table size,database size,script for all database size

4.FINDING SLOW QUERY ABOVE 2 MINUTES:

\pset format wrapped
SELECT pid, now() - query_start as "runtime", usename, datname,wait_event_type,wait_event, state, query
  FROM  pg_stat_activity
  WHERE now() - query_start > '2 minutes'::interval
 ORDER BY runtime DESC;

5.CHECK VACCUM COUNT FOR ALL DATABASE WHAT ARE THE TABLES ABOVE  5000 DEAD TUBLES.

select schemaname,relname,n_dead_tup,last_vacuum,last_autovacuum, last_analyze, last_autoanalyze from pg_stat_all_tables where n_dead_tup > 1000;

hyderabad=# select count(*) from pg_stat_all_tables where n_dead_tup > 5000;
 count
-------
   114
(1 row)

mumbai=# select count(*) from pg_stat_all_tables where n_dead_tup > 5000;
 count
-------
    78
(1 row)


pune=# select count(*) from pg_stat_all_tables where n_dead_tup > 5000;
 count
-------
     1
(1 row)

delhi=# select count(*) from pg_stat_all_tables where n_dead_tup > 5000;
 count
-------
   114
(1 row)

6.CHECK WALL LOGS both master and slave:

ps -ef|grep sender
ps -ef|grep receiver

---------------------------------VACUUM ANALYZE THE DATABASE-----------------------------
Before vaccuming process count the dead tubles using following query

select schemaname,relname,n_dead_tup,last_vacuum,last_autovacuum, last_analyze, last_autoanalyze from pg_stat_all_tables;

script for vaccum:

befere scheduling crontjob check the current date.
$ date

crontab -e
0 22 * * * sh  /home/postgres/vaccumdb1.sh

 vi /home/postgres/vaccumdb1.sh

#!/bin/sh
# The script sets environment variables helpful for PostgreSQL

export PATH=/opt/PostgreSQL/9.6/bin:$PATH
export PGDATA=/data/emut_96/
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5432
export PGLOCALEDIR=/opt/PostgreSQL/9.6/share/locale
export MANPATH=$MANPATH:/opt/PostgreSQL/9.6/share/man


vacuumdb --analyze hyderabad
vacuumdb --analyze mumbai
vacuumdb --analyze delhi
vacuumdb --analyze pune

---------------------------------POSTREQUEST-----------------------------
Then check the database size master as well as slave after the vacuum process finished

SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname)/1024/1024/1024 AS size_in_GB FROM pg_database ORDER by size_in_GB DESC;

CHECK THE QUERY TIMING AGAIN:

SELECT pid, now() - query_start as "runtime", usename, datname, state, query
  FROM  pg_stat_activity
  WHERE now() - query_start > '1 minutes'::interval and state = 'active'
 ORDER BY runtime DESC;