BI file growth

Posted by atrimbw on 26-Feb-2014 13:27

We have seen situations where the BI is growing rapidly and there are several active transactions working in the database..

Is there a good methodology or utility or collection of VST calls (so that we could write our own utilit)y that would help us find the user/code/input values that are being used so that we can either fix the code or educate the user as to how to use the existing code so as to not grow the bi file.

All Replies

Posted by Rob Fitzpatrick on 26-Feb-2014 22:22

It would be helpful to know your Progress version and platform(s) as that has some impact on possible solutions or approaches.

Looking at the transaction table, with promon, ProTop, or your own code, you can see your active transactions.  The BI file, from a logical perspective, is a doubly-linked list of BI clusters (or a ring, if you like).  During normal forward processing with short-lived transactions, by the time the last cluster in the ring is filled, the first cluster will not contain any notes that describe active transactions.  In this case, provided also that the blocks modified by the BI notes have been flushed to disk and the BI cluster has been aged sufficiently (as determined by -G value), the cluster will be reused and the BI file will not grow. 

However if the rules above are not met then the cluster cannot be reused so a new cluster is formatted and inserted into the ring.  New BI notes are written to that cluster and obviously in this case, the physical size of the BI file has grown by one cluster.  So all it takes to cause BI growth is for one user to have a long-lasting transaction that prevents reuse of a BI cluster.  And that user isn't necessarily behaving badly or contributing all the notes that are causing the BI growth.  But the code they are running may have transaction scoping issues, e.g. a blocking statement like an update within a transaction.  If they (unknowingly) start a transaction and then go for lunch (or vacation...) then every other user's updates would eventually cause BI growth.

So first find the user session(s) associated with the old transaction(s).  The next step is to determine what code is being run on the client side.  Some options:

  • use the 4GLTrace log entry type on the client to write information to the client log about run statements, internal procedures, pub/sub, UDFs, class events, etc. 
  • enable database request statement caching (10.1C+) for that client and read the data about their call stack either in promon or ProTop or with your own code (looking at _Connect)
  • run proGetStack <client PID> on the client machine to produce a protrace.<PID> file.  It contains the ABL call stack, persistent procedures, classes, etc.  On *nix this command does a kill -SIGUSR1 <PID> so you must have sufficient permission, i.e. you must be that user or root.
  • If this is a reproducible issue, use the profiler handle or startup parameter on the client to capture profiling data from the client.  This will give you the call tree.  This would be helpful for post-mortem analysis if, for example, the BI growth is caused by an endless undo/redo loop within a transaction (I've seen that happen a couple of times).
  • Ask the user what they did. Not sophisticated but it works on every version of Progress :)

Once you find the offending code, or at least some likely candidates, it's up to the programmer to take it from there.  In the meantime, make sure you have so monitoring and alerting in place for the size of the BI file.

Posted by James Palmer on 27-Feb-2014 02:32

That's a fabulous post from Rob and answers everything you need. I'd just add that we have had these issues. As a result we have a piece of code that runs every half hour or so. It sends out alarms to the relevant people if transactions are found that are older than a certain amount. That way we are getting pretty good at catching transactions as they happen so that we can track down what users are doing.

The biggest culprits we have found are batch processes that message to screen (yeah I know!), and dialogues that ask the user for input within a transaction. You may well also find badly scoped transactions.

If you need any help with working out strategies for hunting down bad transactions once you have found the offending pieces of code then post here as I'm sure folks have various hints and tips! :)

Posted by Frank Meulblok on 03-Mar-2014 08:36

Indeed good detailed post by Rob.

I do have one addition: If you're on 10.1B or newer release, you'll want to use the 4GLTrans log entry type  instead of/in addition to the 4GLTrace.

That'll tell you the exact transaction scoping in your code at runtime, making it much easier to identify which parts of the code are suspect.

Posted by atrimbw on 03-Mar-2014 10:49

Thanks to all who posted here - you have given me a lot to work with in your posts.

This thread is closed