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
master 192.168.1.1 and slave 192.168.2.5:
MASTER TOP:
MASTER:
MASTER: DATABASE
4.FINDING SLOW QUERY ABOVE 2 MINUTES:
5.CHECK VACCUM COUNT FOR ALL DATABASE WHAT ARE THE TABLES ABOVE 5000 DEAD TUBLES.
Before vaccuming process count the dead tubles using following query
Then check the database size master as well as slave after the vacuum process finished
CHECK THE QUERY TIMING AGAIN:
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: DATABASESELECT 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 size4.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;
Comments
Post a Comment