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-releaseRed 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 %CPUtop - 15:37:26 up 8 days,  2:03,  1 user,  load average: 11.43, 23.29, 19.44Tasks: 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 stKiB Mem:  49458536 total, 49169124 used, 289412 free,29352 buffersKiB Swap: 16777212 total, 147776 used, 16629436 free. 39245096 cached Mem

SLAVE TOP:

bash-4.2$ top -o %CPUtop - 15:54:06 up 8 days,  2:20,  1 user,  load average: 0.28, 0.30, 0.36Tasks: 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 stKiB Mem:  32943544 total, 32493288 used, 450256 free, 160384 buffersKiB Swap: 16777212 total,53420 used, 16723792 free. 19230732 cached Mem  PID USER  PR  NIVIRTRESSHR S  %CPU %MEM TIME+ COMMAND38296 postgres  20 0 12.508g 287932 283072 S  12.9  0.9 0:05.58 postgres20492 postgres  20 0 12.507g 3.015g 3.011g S 5.0  9.6 1:35.30 postgres20504 postgres  20 0 12.510g 3120 1864 S 2.3  0.0 1:51.05 postgres39588 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 sharedbuffers cachedMem: 47G46G 279M 8.2G27M37G-/+ buffers/cache: 9.5G37GSwap:  15G 151M15GSLAVE:bash-4.2$ free -h total used free sharedbuffers cachedMem: 31G30G 433M10G 156M18G-/+ buffers/cache:12G18GSwap:  15G52M15G

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

MASTER:

bash-4.2$ df -hFilesystemSize  Used Avail Use% Mounted on/dev/vda1  20G  1.8G 17G  10% /devtmpfs 24G 0 24G 0% /devtmpfs  24G  4.0K 24G 1% /dev/shmtmpfs  24G 41M 24G 1% /runtmpfs  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 -hFilesystemSize  Used Avail Use% Mounted on/dev/vda1  20G  1.8G 17G  10% /devtmpfs 16G 0 16G 0% /devtmpfs  16G  4.0K 16G 1% /dev/shmtmpfs  16G 65M 16G 1% /runtmpfs  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 wrappedSELECT 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 senderps -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.$ datecrontab -e0 22 * * * sh  /home/postgres/vaccumdb1.sh vi /home/postgres/vaccumdb1.sh#!/bin/sh# The script sets environment variables helpful for PostgreSQLexport PATH=/opt/PostgreSQL/9.6/bin:$PATHexport PGDATA=/data/emut_96/export PGDATABASE=postgresexport PGUSER=postgresexport PGPORT=5432export PGLOCALEDIR=/opt/PostgreSQL/9.6/share/localeexport MANPATH=$MANPATH:/opt/PostgreSQL/9.6/share/manvacuumdb --analyze hyderabadvacuumdb --analyze mumbaivacuumdb --analyze delhivacuumdb --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;