How many messages can the DB/Engine take?

[David Gordon]

We have a vendor that has hooked on to the idea of using Cloverleaf to do a bulk data load from one system to another.  Not such a big deal, but they want to dump three million messages on the engine all at once.

Of course, I snickered.

But, in the interest of being fair and investigating the idea, can anyone tell me how many messages the Recovery database can contain before CL freaks out and detonates the server, raining fiery shards of silicon upon the unwitting operations staff, leaving them rent and torn until the fire alarm triggers, dousing the room in Inergon and slowly suffocating the unconscious operators?

Thanks!

Topic

[Dee Davis]

I actually performed a data conversion on the engine with more records than that. I spent quite a bit of time optimizing so we did not overload the database.  There were different stages of the conversion in different processes. Most were fileset-local connections; some were looking at one huge file where records had to be peeled off; others picked up individual files that had been sent out by another stage.

In optimizing inbound fileset-local connections, I looked at: the read interval, max messages and scan interval in the connection set up as well as translation process throttling.  The goal was to have as few messages as possible in the database by the next read but not to leave time where no processing was happening. Also, so that if we had to shut down a process, there was actually some read time for the command thread. Each inbound thread (in different processes) ended up having different “optimal” settings depending on what was happening in the process (parsing the initial extract file, detailed translation, script only, etc).  Examples (read interval, max msg, 3600 scan interval) were 180, 76, 3600; 75, 1000, 3600; 400, 200, 7200.

Bottom line — it can be done without database issues. You just have to work at it.

[Richard Hart]

David.

We completed a data load of about 25 million messages into our clinical manager application last year as part of a consolidation  process.  

We have stored messages in SMAT files for years and were replaying these.

We used one site to read messages from a thread and send to our CM system on the OB thread.  Our CM application was half-duplex and we added counters for message out and ACKs in.

We placed some ‘smarts’ in some TCL code in the local-fileset, which In ‘simple’ terms accomplished this is:

Use the thread local ‘read’ mode to read a list of files in a directory.

If the first file was a zipped SMAT file, split this into files of 1000 records and return ‘no files found’.

If this was a file (of possible 1000 records) return ‘this one file’ (which will be read at a determined rate)

If the number of records sent was a ‘threshold’ greater that the number of records received return ‘no files found’.

If the ‘stop thread’ file existed return ‘no files found’

This meant that Cloverleaf could self regulate and not queue too many on the CM side.

We sent the message in blocks and stopped for backups etc. at regular intervals.

We had were vendor issues as their software required an hourly bounce for memory leaks when receiving 30,000 per hour.

There were also data issues as tests had changed over the years etc.

There were a few mistakes on our side with files in the wrong order as this was a manual process.

We tested 000’s of message with the CM application in an attempt to find the problems and had a fix ready for the real load, but still had issues.

IMHO it was a good technical project, but would have been simpler to load the data into a database (the vendor had provided an extract process) and web enable this!

[Author]

Replies