topic Re: Req advice re using fast-delete on indexed files in Operating System - OpenVMS

Req advice re using fast-delete on indexed files

John McL — Thu, 10 Mar 2011 03:48:13 GMT

What exactly does the fast-delete option (RAB$V_FDL) do on records in indexed files? The Guide to File Operations is a bit vague on the residual work that is done the next time the record is accessed on any key other than the primary. It looks like the internal pointers for non-primary keys are reset when the record is accessed, but is this for all keys or only the current key of access?

I get the impression that the guide is hinting that it's not a good idea to use fast-delete, so I'd be interested in any recommendations too. (A ballpark figure for the files we're considering this for is about 2 million blocks and let's say 1024 bytes per record and 4 keys.)

Re: Req advice re using fast-delete on indexed files

Hein van den Heuvel — Thu, 10 Mar 2011 05:32:20 GMT

Hello again John!

I think that fast delete is a great option in general.
It speeds things up when one often needs it, and it barely slows down at cleanup time.

What happens is
- a program goes looking for a record by a specific alternate key
- finds a SIDR pointer (RRV) to a potential data record
- then finds that record marked as deleted, or simply not there.
- Now it goes back to the SIDR bucket and removes the stale pointer.

The goodness is that
- 100% overhead avoided if the pointer is never used, or the file is converted.
- The fixup is a relatively small cost
--- fruitless excursion to a data bucket
--- extra re-lock and write on sidr bucket which is already in memory with NL lock
- If one considers 2 or 3 locks and IOs to get to the SIDR bucket in the first place, then that's not much incremental cost.

The badness might be
- the cost moved from being predictable resources at a predictable time to a small but unknown resources at an unpredictable time.
- read programs may unexpectedly write to the file

Using fast delete might be the only way to make a bulk delete job tolerable in the permissible batch window.

IF the deleted records are logged, then for extreme case you could at a 'slow' time and perhaps at a slow pace 'touch' the alternate keys.

If there are 4 keys, then not using fast delete typically makes each delete 4x slower.
Probably more due to increased randomness in the access pattern and buffer cache usage.

An other important heuristic would be the percentage of records to be deleted. If it is more then 5% - 10% of the file, then an extract of the records to be retained + convert becomes desirable and me be faster.

Let's do some experiments together at some point!

Hint... for an experiment just use 10 or 100 record but be sure to have RMS STATS enabled on the file, and use my RMS_STATS tool, or ANALYZE/SYSTEM ... SHOW PROC/RMS=FSB to get a clean count on LOCK and IO resources used per iteration.

Cheers,
Hein

Re: Req advice re using fast-delete on indexed files

Shriniketan Bhagwat — Thu, 10 Mar 2011 05:50:27 GMT

Hi John,

There is a utility by name FDEL (Fast File Delete) in the VMS freeware web page. Though may be helpful. Please refer the USER_DOC.TXT file available in the zip file from the below link for more details on the utility.
Below is the link to VMS freeware.

http://h71000.www7.hp.com/openvms/freeware/freeware_index.html#OSF

Regards,
Ketan

Re: Req advice re using fast-delete on indexed files

Duncan Morris — Thu, 10 Mar 2011 11:33:45 GMT

Ketan,

the poster was inquiring about the RMS fast delete record option within indexed files.

Your reply refers to a utility used to delete files (not records) from a large directory.

FDEL (Fast File Delete) performs a fast delete for directories
that have large numbers of files.

Duncan

Re: Req advice re using fast-delete on indexed files

Hein van den Heuvel — Thu, 10 Mar 2011 15:11:39 GMT

Now please be aware, about a little known fact is that Fast Delete is only effective for alternate keys which allow duplicates.

So if, for example, you have alternate keys defined with the primary key in the final segment to force a predictable sort order making them unique in the process and enforcing that, then RAB$V_FDL does nothing for that key.

Now one could probably just flip the duplicates allowed to YES on the next convert (or flip the bit on the fly?!) that is unlikely to change the application functionality.

The reason duplicates have to be allowed it that RMS does not want to refuse a fresh insert when it sees that a key value already exists when the underlying record is perhaps deleted.
Now what they _should_ have done is to just follow the pointer, like they would for a GET, and do the clean up if need be.
IMHO that would be a reasonable 'overhead' when you are about to return a dupliate key error. So it is NOT happening for normal inserts/updates.

The RMS REF manual entry for RAB$V_FDL is also not entirely correct IMHO. I think there will be a normal record-not-found returned when deleted, which would be the expected thing no? Maybe more on that an other time.

Hein

Re: Req advice re using fast-delete on indexed files

John McL — Sun, 20 Mar 2011 20:14:41 GMT

Thanks Hein. That's useful info.