Re: Maximum number of simultaneious Submits

On Dec 29, 9:46 pm, "Michael D. Ober" <obermd.@.alum.mit.edu.nospam.>
wrote:
Frank - you are correct in assuming this is a year end database purge.  The
database itself is in a series of ISAM RMS files with hundreds of support
files keyed to the primary key of the database.  This was the first year I
ran the entire process.  After watching the problems in the previous years,
I decided to change the basic search and purge algorithms.  For this year I
split the database purge apart from the file deletion - it was previously
done serially in a singled thread application - by writing the DCL commands
for the file deletion to COM files and then submitting the COM files to one
of our batch queues.  Previouslyt the DCL was executed directly.  The submit
process is easy.  Combining this split with a smarter algorithm for finding
the accounts to purge halved the time it takes to run the year end purge.
What I ran into, however, is that the COM files were taking too long to
process once they got an execution slot in the batch queue.  I did some
timing analysis and realized that each COM file must contain the DCL to
delete the support files for no more than 500 records, or the simple
execution of individual COM files would take too long and interfere with our
near real-time processing.  Basically, I have a window each night in which I
must complete the previous day's transactions, backup the data RMS files,
and start the several hour long process required to get the system ready for
tomorrow's interactive operations.  If the final step starts too late, our
employees are twiddling their thumbs when they come in.

Based on that description, here are some recommendations (some already
mentioned by others):

1) Create a new queue manager and put the queue and journal files for
it on a less-busy, less-full disk drive (if possible). Check the docs
for $START/QUE/MANAGER/ADD/NAME_OF_MANAGER=. If this is a clustered
environment then run this new queue manager on a node other than the
system default queue manager.

2) Create a new batch queue that will be dedicated to running the
command procedures that are being generated by the database purge
process. It should be assigned to the new queue manager. This new
queue should be set as /JOB_LIMIT=1 /BASE_PRIORITY=<either 1, 2, or 3;
anything lower than the priority at which the near-real-time queues
are set>. The base priority may not get you much since these sound
like I/O-bound file deletes, but at least they won't take up an entry
in the batch queue used for the production processes. Check the docs
for $INIT/QUE/BATCH/NAME_OF_MANAGER=.

3) Instead of generating a file-deletion command procedure every 500
records, generate one for each record and submit it to the dedicated
queue created in (2) above. Ideally, the file deletion will have
completed for a single record before the database search has
identified the next candidate to be deleted. If not then it's no big
deal. The queue manager won't care about 100000 jobs pending in the
queue. You just have to manage the disk space for the journal file,
which is why it should go on a spare disk if possible.

4) Another guess is that the files being deleted are all in the same
directory, and the directory contains thousands of files. That would
explain why file deletion takes such a long time. Consider changing
the application to distribute the support files to different
directories and/or disk drives. However, you don't want to go from
thousands of files in one directory to thousands of subdirectories in
one directory. You want to devise a directory tree structure that
spreads the support files around while limiting the number of files/
directories contained within any given directory in the tree.

5) Do your database purges more frequently. Let's say you're deleting
records in December that are more than 3 years old. Rather than
waiting until the end of the year, consider a rolling purge where
every month you delete records that are more than three years old (or
whatever age limit you're using). In a balanced world that means for
all the records you'd normally purge in December you'd now be deleting
1/12th of the records and files in Jan, another 1/12th in Feb, and so
on. You could switch to biweekly or weekly purges and further reduce
the impact on the production processes.

6) Hundreds of support files for a given master record sounds
unusual. We can't rewrite the application on Usenet groups but I'd
say there's some work that could be done to improve the application
design. Depending on how those files are structured it might be as
simple as using a single file with variable-length records to replace
the hundreds of files for any given record.
.

Relevant Pages

  • Re: Maximum number of simultaneious Submits
    ... files keyed to the primary key of the database. ... I decided to change the basic search and purge algorithms. ... process once they got an execution slot in the batch queue. ... that number of jobs in the queue. ...
    (comp.os.vms)
  • Re: byte inversion in ciphertext
    ... controversial landscape were becoming in support of the tall ... queue. ... If the subjective humours can strike shortly, ...
    (sci.crypt)
  • Re: CLOS and databases
    ... Updating any slot ought to add that object to a queue of objects to be ... the queue gets flushed to the database. ... Supporting this for the add/drop of a slot in one class is trivial. ...
    (comp.lang.lisp)
  • Re: Parallel Programming
    ... database and pass them in as a parameter (looking a the Parallel.For ... I run my worker objects in a seperate thread als code is encapsulated in a ... worker class that implements my queue interface it can comunicate with my ... I need to grab the data from the database and pass it to a web service ...
    (microsoft.public.dotnet.languages.vb)
  • Re: CLOS and databases
    ... Once the queue reaches x objects, or every y seconds, or upon ... the queue gets flushed to the database. ... > ought to be very minimal. ... I'm not sure what you mean by "moving a slot from one class to another." ...
    (comp.lang.lisp)