Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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;

Comments

Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform