• 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
  • Locations
  • Blogs
    • Blog
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum

    Greenplum

    $1,500.00
    Read More
    Have any question?
    (+91)8838953252
    ITsupport@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
    • Locations
    • Blogs
      • Blog
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum

      Greenplum

      $1,500.00
      Read More

      Blog

      • Home
      • Blog
      • Blog
      • Postgres Database Performance Tuning

      Postgres Database Performance Tuning

      • Posted by Nijamutheen J
      • Categories Blog
      • Date February 13, 2019
      • Comments 0 comment
      Following steps will be helpful to improve the performance of Postgres database :
      First  you have to issue the top command then see the process which one process is taking more CPU utilization then note that PID, if that  is postgres process means use this script you can find out query and their status and timing else you can use following query to finding current running query

      SELECT now() - query_start as "runtime",state,query from pg_stat_activity WHERE pid= 32696;
          runtime     | state  |                                                                       query
      ----------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------
       00:00:01.93486 | active | select   timestatus::date from mhrornad_akl.edit_mut_new_audit WHERE ccode='270100010000760000' and report_status='Generated' and edit_mut_no=214

      Postgresql query tuning Type:

      1. For modification Query (DML,DDL)
      2. For Select Query (DRL)
      1.For modification Query:
      Check the query whether DRL (select) type or modified(DML -insert,update,delete) type ,if modified (DML) query means check the table whether fragmented or not and don’t kill the DML and DDL query if you killed this type of query means you loss your transaction.

      To checking​ long running queries and find out table which one query is running more than  else use script for long running query

      postgres=# select datname,pid,query_start,state,query from pg_stat_activity ;
      postgres=# select datname,pid,query_start,state,query from pg_stat_activity where query like '%copy%';                          
      
      2. For Select Query:
      if that query is DRL (select) means check the query status if status is active wait for some time if that query is running more than threshold period means take the explain plan for that query ,from this explain plan you can check the index used or not
      If sequential scans:
      check table having any index or not If index is not suggest to create index for that table,you can use \dt table_nameor Following query to finding table having index or not

      postgres=# select relname,indexrelname,indexrelid from pg_stat_all_indexes where indexrelid=24700;
       relname | indexrelname | indexrelid 
      ---------+--------------+------------
       k       | production   |      24700

      If table having index but not using index means check the index status valid  nor not, you can use following query to finding the index status

      postgres=# select indexrelid,indislive,indisvalid from pg_index where indexrelid=24700;
       indexrelid | indislive | indisvalid 
      ------------+-----------+------------
            24700 | t         | t
      (1 row)

      Here index status is true so index is valid  if query not using index means you have to use HINTS like

      SET enable_nestloop = off;
      select * from emp_table where id=15045;

      Otherwise index is not valid and index status is false means you have  rebuilt the index using  rebuild command or schedule script for vacuum , analyze and reindexdb

      REINDEX INDEX myindex

      If index scans:
      query is taking index scan but it is running more than threshold period means check table dead tubles as well as last analyze using  following query you can check the table dead tubles and last analyze of the table.

      postgres=# select relname,last_vacuum,n_dead_tup,last_analyze from pg_stat_all_tables where relname='k';
       relname | last_vacuum | n_dead_tup | last_analyze 
      ---------+-------------+------------+--------------
       k       |             |       8192 |

      Note: Here dead tuble is 8192  and you did not analyze the table from the table creation so you have to clear this dead tubles, for clearing dead tuble you can use vacuum

      Analyze and vacuum the table:
      it will helpful for update the current status to optimizer

      postgres=# analyze  k;     
      ANALYZE
      postgres=# vacuum k ;
      VACUUM

      Killing Query: 
      if that query is running more than threshold period and taking more CPU utilization as well as server is getting slow means  kill that query using following query or use script for killing long running query 

      SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid=32696;

      Note: if query is taking more CPU utilization you can check the CPU utilization using top command Before killing query, Before killing  you have to get approval from your organization if query status is active, if query status is idle means you can directly kill without permission.
      Before  killing long running query  you have to understand the query status 

      Script For Postgres Server Tuning:
      Then Implement the killing idle connection script if your server getting more idle connection Then implement the drop Cache script if you have root access its helpful for clearing cache memory(RAM Buffer)

      Monitoring Script For Postgres Server:
      you can check your postgresql server status using monitoring script this script will be helpful for monitoring disk and ram usage as well as how many table having more than 5000 dead tubles in a postgres database.

      Basic Linux server monitoring commands

      • 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 Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in Postgres Database
      February 13, 2019

      Next post

      Postgres Copy Command Tutorial
      February 15, 2019

      Leave A Reply

      You must be logged in to post a comment.

      Connect with



      Search

      ADVERTISEMENT

      Latest Posts

      Tips to Choose the Best Serviced Office for Your Business
      24May2022
      What key considerations do you need to keep in mind when choosing a new serviced office to house your business?
      24May2022
      The Benefits of Coworking
      24May2022
      The Long-Term Impact of Coworking
      24May2022
      Are you spending more money than you earn? Outsource with Ease and Move When You’re Ready
      24May2022
      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 Twitter Login with Linkedin Login with Windowslive Login with Yahoo

      Login with your site account

      Connect with



      Lost your password?