Overview

This article provides the PostgreSQL commands to improve the performance of the database.

Pre-req

Is your DB optimized? If not, see KC.

Steps

Switch the user to Postgres

 su postgres

Connect to PostgreSQL Instance

psql

This will prompt to PostgreSQL connect database

\c intiguadb

Run the command to list the frequently used and large tables

select schemaname as table_schema, relname as table_name, pg_size_pretty(pg_total_relation_size(relid)) as total_size, pg_size_pretty(pg_relation_size(relid)) as data_size, pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as external_size from pg_catalog.pg_statio_user_tables order by pg_total_relation_size(relid) desc, pg_relation_size(relid) desc limit 10;

 

VACUUM

The VACUUM command will reclaim space still used by data that had been updated. In PostgreSQL, updated key-value tuples are not removed from the tables when rows are changed, so the VACUUM command should be run Periodically

 

Before performing VACUUM stop the tomcat service in JetPatch application server

systemctl stop tomcat

 

Now go back to database server and run VACUUM command

 VACUUM FULL <TABLENAME>

 

Once completed run the command and you can see the size consumed will be reduced

select schemaname as table_schema, relname as table_name, pg_size_pretty(pg_total_relation_size(relid)) as total_size, pg_size_pretty(pg_relation_size(relid)) as data_size, pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as external_size from pg_catalog.pg_statio_user_tables order by pg_total_relation_size(relid) desc, pg_relation_size(relid) desc limit 10;

 

Start the tomcat in JetPatch application server:

systemctl start tomcat

 

REINDEX

REINDEX rebuilds an index using the data stored in the index's table, replacing the old copy of the index. 

It provides a way to reduce the space consumption of the index by writing a new version of the index without the dead pages.

 

From the list of tables in the Select statement run REINDEX for all the tables

REINDEX TABLE <TABLENAME>

\

 

ADDITIONAL STEPS 

If the Manager Console's performance is still sub-optimal after verifying system resources and optimizing the database, we can then look into Tomcat to verify that there are no looping or failing failing processes.

After stopping Tomcat, we can run the following query

update automation_task_activity set output_processed = true where id in ( 3 select ata.id from 
automation_task_activity ata 4 inner join agent_task_item ati on ati.id=ata.agent_task_item_id inner join 
agent_task at2 on at2.id=ati.task_id 5 inner join pg_workflow_execution_state pwes on 
pwes.activity_id=at2.activity_id inner join pg_rp_execution_state pres on pres.id = pwes.rp_execution_id 6 
inner join pg_remediation_plan prp on prp.id=pres.remediation_plan_id 7 where prp.status = 'CANCELLED' 
and pres.result_status = 'CANCELLED' and pwes.result_status = 'CANCELLED' 8 and pwes.execution_type in 
('APPLY_UPDATES', 'APPLY_UPDATES_PART') 9 and ata.exit_status = 'SUCCESS' and ata.output is not null 
and ata.output != '' and ata.output_processed = false 10 );

On restart of the Tomcat service, all performance issues should be resolved. 

If there is still an issue, please add the following property to intigua.properties and restart tomcat

spring.transaction.timeout.sec=10000