Environment

• Yugabyte Platform - 2.2
• Yugabyte Platform - 2.4
• Yugabyte Platform - 2.6
• Yugabyte Platform - 2.8

Issue

The tasks page in the Yugabyte admin console https://yugaware-ip/tasks fails to load tasks and eventually times out. This page shows the status, time, and other details of old and new tasks executed from the admin console.

Resolution

1. Create indexes

Copy the following commands to a created_index.sql file

begin;
CREATE INDEX customer_task_task_uuid_idx ON public.customer_task USING btree (task_uuid);
CREATE INDEX task_info_parent_uuid_idx ON public.task_info USING btree (parent_uuid);
commit;

Execute the create index SQL using docker exec

cat create_index.sql | docker exec -i postgres sh "psql -U postgres -d yugaware" > index_output.txt

2.  Clean up old tasks

Take yugaware database backup to a SQL file

docker exec -i postgres pg_dump yugaware -U postgres |gzip > yugaware_dump_$(date +"%Y-%m-%d").sql.gz

Copy the following command to a clean_tasks SQL file

begin; 
select task_uuid from customer_task where task_uuid not in (select uuid from task_info);
select uuid, parent_uuid from task_info where uuid not in (select task_uuid from customer_task) and parent_uuid not in (select task_uuid from customer_task); delete from customer_task where task_uuid not in (select uuid from task_info);
delete from task_info where update_time <= 'YYYY-MM-DD';
delete from task_info where uuid not in (select task_uuid from customer_task) and parent_uuid not in (select task_uuid from customer_task);
commit;

Execute the clean tasks SQL using docker exec

cat clean_tasks.sql | docker exec -i postgres sh "psql -U postgres -d yugaware" > output_$(date +"%Y-%m-%d").txt

Overview

This issue is present in the lower version of the Yugabyte platform and impacts the queries when the size of the table increases with tasks. There are a couple of additional optimizations added in the later versions such as adding Indexes on necessary columns and Task Garbage Collector which runs a check at a certain interval and automatically deletes all completed tasks outside of specified retention duration