Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Tuning

Following steps will help you to improve the Postgresql database server performance:
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 process 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 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_name or 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 vacuum with reindex

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;     
postgres=# vacuum k ;

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 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 Implementing killing script you have to understand the query status

Script Implementing 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 For Postgres Server tuning:
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 postgresql cluster.

Basic Linux server monitoring commands

No comments:

Post a Comment