• Home
  • Services
    • DBA Support
    • DBA Consultancy Services
    • PostgreSQL Support
    • Website Maintenance
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum Database

    Greenplum Database

    $2,000.00 $1,500.00
    Read More
  • Company
    • FAQs
    • About Us
    • Contact
  • Events
  • Portfolio
  • Blogs
    • Blog – RayaFeeL
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
    • Blog – Medical Coding
      • Cart

        0

    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    RegisterLogin
    RayaFeeL
    • Home
    • Services
      • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
      • Website Maintenance
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00
      Read More
    • Company
      • FAQs
      • About Us
      • Contact
    • Events
    • Portfolio
    • Blogs
      • Blog – RayaFeeL
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
      • Blog – Medical Coding
        • Cart

          0

      Blog

      • Home
      • Blog
      • Blog
      • What is the easiest way to do postgres performance tuning ?

      What is the easiest way to do postgres performance tuning ?

      • Posted by 2ndnijam
      • Categories Blog
      • Date February 19, 2019
      • Comments 0 comment
      Below steps will be helpful to improve your postgres database performance so faster than earlier.
      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:
      2ndnijam

      Previous post

      how to copy data in one table to another table in postgres
      February 19, 2019

      Next post

      WHY NEED TO REFRESH POSTGRES MATERIALIZED VIEW ?
      February 20, 2019

      Leave A Reply Cancel reply

      You must be logged in to post a comment.

      Login with:

      Login with Google Login with Twitter Login with LinkedIn Login with Microsoft


      Search

      ADVERTISEMENT

      Latest Posts

      PostgreSQL Patching version 9, 10,11
      10Oct2019
      Tools for PostgreSQL
      16Sep2019
      Postgres user creation and restrict DDL & database access
      13Sep2019
      PostgreSQL SSL Setup
      07Sep2019
      How to DELETE current XLOG / WAL LOG in postgresql database ?
      19Aug2019

      Latest Courses

      PostgreSQL Database

      PostgreSQL Database

      $600.00 $500.00
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00

      Preview Course

      Free

      Recent Forum Topics

      • Are you going to take your first ste
      • How to start working on an application?
      • please let me know pre requirements to increase work_mem
      • how to copy some data in one table to another table in postgres
      • postgres script for finding queries more than 1 hours ?

      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Contact
      • Our Team
      • Blog

      COURSES

      • List Of Course
      • Become An Instructor
      • Events
      • Postgres Support Blog

      Support

      • DBA Support
      • Consultancy Services
      • Postgres Migration Blogs
      • Forum

      Recommend

      • Groups
      • Login
      • FAQs
      • SignUp

      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

      Login with:

      Login with Google Login with Twitter Login with LinkedIn Login with Microsoft

      Login with your site account

      Lost your password?

      Not a member yet? Register now

      Register a new account

      Are you a member? Login now