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
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
- For modification Query (DML,DDL)
- 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
Here index status is true so index is valid if query not using index means you have to use HINTS like
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.
Analyze and vacuum the table:
it will helpful for update the current status to optimizer
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
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
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 | 24700If 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
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 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
Comments
Post a Comment