monitoring PostgreSQL master and slave server
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;
