How does sys_auto_flush works ?

guillaume92 · ‎02-14-2017

Hello Community,

I'm trying to make a purge on some tables, i thought using the sys_auto_flush was a good idea, but i don't understand how it works.

I tried to create this entry (for testing) :

I want to delete all records on the table u_savxdsl that have been created 10 seconds ago where the ID contrat is 182736459826.

So i created a record with this conditions, wait 10 seconds (almost 2 minutes in fact ), but my record still exists.

I think i miss something about this functionnality, but i don't know what. Someone has a clue ? The Wiki isn't very explicit

Thks,

Guillaume

Robbie · ‎02-14-2017

Hi Guillaume,

Whilst you've set the 'Age in seconds' field under certain presumptions, this field doesn't actually correlate to when the Auto flush runs. This field is used as part of the query or match for what data to delete. (Along with the 'Matchfield' field)

The actual scheduled job that invokes the Table Cleaner (sys_auto_flush) runs every hour. Check your table now as its been longer than an hour and the record(s) should be deleted.

Please can you mark this answer as correct and helpful by clicking on the buttons via your post link (How does sys_auto_flush works ? ) to help close out open questions and aid others with the same/similar questions.

Thanks,

Robbie

View solution in original post

Janel · ‎07-28-2023

@Naveen Thadur1 wrote:
Hi Can anyone help me understand what exactly the "force optimize" field do on auto flush from?

Thanks

As of Utah, it does nothing. I opened a case to ask the same thing and this was their reply:

"The support for FORCE_OPTIMIZE has been discontinued in TableCleaner post-San Diego. The optimization and compaction code has been completely removed from the Table Cleaner process and is now handled by the "DB Compaction" job.

Within the CompactionJob class, the compact process begins by "requalifying" whether the table in question needs compaction. This is done by executing a query against the information_schema to retrieve the RECLAIM_SIZE_ESTIMATE and TABLE_SIZE_BEFORE values.

We are very sorry for any confusion this may have caused you. The OOTB 'DB compaction' plugin with id 'com.glide.db_compaction' is enabled by default on upgrade to Utah release.

This creates a new scheduled job called 'DB Compaction' which runs daily. Its purpose is to determine which tables qualify for compaction based upon various requirements and thresholds. Details of identified tables are then written to a new table 'sys_compaction_run' for subsequent processing.

All your instances are on 'UTAH' except for {instance}sandbox. But to answer your original ask we would not recommend selecting 'FORCE_OPTIMIZE', we have seen issues that by selecting the option you can run into issues in which you can lock the table and can result in table contention."

And of course, we don't have the ability to do anything with this DB Compaction thing.