• Home
  • Services
    • HR Services
      • HR Advisory Services
      • Contract Staffing
      • HR Outsourcing Services
      • Manpower Supply Services
      • Payroll Processing
      • Permanent Placement
      • Recruitment and Placement Services
      • Recruitment Process Outsourcing
      • Staffing Agency Services
    • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
    • Website Maintenance
    • Company Registration Services
    • Virtual Office Space Address
  • Company
    • FAQs
    • About Us
    • Contact
  • Office Space
    • Virtual Space
    • Co-working Space
    • Private Space
    • Meeting Room
    • Locations
    • Add Listing
    • Dashboard
  • Blogs
  • Careers
    • Jobseeker
    • Employer
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum

    Greenplum

    $1,500.00
    Read More
    Have any question?
    (+91)8148383856
    info@rayafeel.com
    Login
    RayaFeeL
    • Home
    • Services
      • HR Services
        • HR Advisory Services
        • Contract Staffing
        • HR Outsourcing Services
        • Manpower Supply Services
        • Payroll Processing
        • Permanent Placement
        • Recruitment and Placement Services
        • Recruitment Process Outsourcing
        • Staffing Agency Services
      • DBA Support
        • DBA Consultancy Services
        • PostgreSQL Support
      • Website Maintenance
      • Company Registration Services
      • Virtual Office Space Address
    • Company
      • FAQs
      • About Us
      • Contact
    • Office Space
      • Virtual Space
      • Co-working Space
      • Private Space
      • Meeting Room
      • Locations
      • Add Listing
      • Dashboard
    • Blogs
    • Careers
      • Jobseeker
      • Employer
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum

      Greenplum

      $1,500.00
      Read More

      Blog

      • Home
      • Blog
      • Blog
      • monitoring PostgreSQL master and slave server

      monitoring PostgreSQL master and slave server

      • Posted by Nijamutheen J
      • Categories Blog
      • Date February 13, 2019
      • Comments 0 comment

      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;

      • Share:
      Nijamutheen J
      Nijamutheen J

      Nijamutheen J 7+ years of experience in PostgreSQL, Linux admin , web hosting - apache server , Oracle ,mySQL, Mriadb, vertica DB & Server security administrator

      My updated resume is https://www.tutorialdba.com/p/hi-i-have-4.html

      Previous post

      How to Compare both table values in postgres ?
      February 13, 2019

      Next post

      How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in Postgres Database
      February 13, 2019

      Leave A Reply

      You must be logged in to post a comment.

      Connect with



      Search

      ADVERTISEMENT

      Latest Posts

      HOW VIRTUAL OFFICE IS GOING TO CHANGE YOUR BUSINESS STRATEGIES
      21Jun2022
      Take the best advantage of your Virtual Office
      21Jun2022
      Let’s discuss how corporates can improve their productivity through virtual office spaces?
      21Jun2022
      Virtual Office Space & Address Services
      21Jun2022
      HOW VIRTUAL OFFICES ARE TRANSFORMING BUSINESSES
      21Jun2022
      PostgreSQL

      PostgreSQL

      $800.00 $500.00
      Greenplum

      Greenplum

      $1,500.00
      Oracle Database

      Oracle Database

      $350.00
      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Our Team

      COURSES

      • List Of Course
      • Become An Instructor

      Support

      • DBA Support
      • Consultancy Services

      Recommend

      • Login
      • Contact Us

      IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

      • Privacy
      • Terms

      Become An Instructor?

      Join thousand of instructors and earn money hassle free!

      Get Started Now

      Connect with

      Login with Google

      Login with your site account

      Connect with



      Lost your password?