Oozie database, wf_action big index, purgeservice

[Morgrim Murdargent <mo...@gmail.com>]

Hello !

I send you this mail because I have a problem with my oozie database.
I'm using postgres as the database for oozie.
And I have a lot of coordinators launching multiple workflows.

My colleagues from production don't use the purge service of oozie, they
execute the following command to directly perform some space in the oozie
database :
###

#!/bin/ksh

d=$(date --date '7 days ago'  "+%Y-%m-%d")

psql -p 5434 oozie <<!
delete from COORD_ACTIONS where job_id in (select id from COORD_JOBS where
created_time < '${d}');
commit;
delete from WF_JOBS where created_time < '${d}';
commit;
delete from WF_ACTIONS where end_time < '${d}';
commit;
vacuum full verbose;
###

Thing is, we can see that we have a huuuge index for the table wf_action.
So we will rebuild this index with the REINDEX command.
REINDEX TABLE my_table;

If I send you this mail it is to know if the purgeservice takes care of the
indexes of the tables or not ?

Best regards.

Morgrim.

Re: Oozie database, wf_action big index, purgeservice

[Abhishek Bafna <ba...@gmail.com>]

Hi,

The PurgeService will remove the data from database as per the
configuration. Once the data is removed, the indexes on the database should
be updated by database.

Also note that, PurgeService for long running coord action is turned off by
default. You can enable that by setting the property
"oozie.service.PurgeService.purge.old.coord.action" to "true".

Thanks,
Abhishek

On Thu, Apr 21, 2016 at 3:06 PM, Morgrim Murdargent <morgrim.oozie@gmail.com
> wrote:

> Hello !
>
> I send you this mail because I have a problem with my oozie database.
> I'm using postgres as the database for oozie.
> And I have a lot of coordinators launching multiple workflows.
>
> My colleagues from production don't use the purge service of oozie, they
> execute the following command to directly perform some space in the oozie
> database :
> ###
>
> #!/bin/ksh
>
> d=$(date --date '7 days ago'  "+%Y-%m-%d")
>
> psql -p 5434 oozie <<!
> delete from COORD_ACTIONS where job_id in (select id from COORD_JOBS where
> created_time < '${d}');
> commit;
> delete from WF_JOBS where created_time < '${d}';
> commit;
> delete from WF_ACTIONS where end_time < '${d}';
> commit;
> vacuum full verbose;
> ###
>
> Thing is, we can see that we have a huuuge index for the table wf_action.
> So we will rebuild this index with the REINDEX command.
> REINDEX TABLE my_table;
>
> If I send you this mail it is to know if the purgeservice takes care of the
> indexes of the tables or not ?
>
> Best regards.
>
> Morgrim.
>