Hi There, I have -bithold value set as 15 GB, for the DB size of approx 197GB. I am trying to understand the factors to calculate the -bithold size. can I increase it to any value (let say 30 GB - I have available system memory) . what are the lower and upper limit for this value? I am using HP-UX .
If you can show me example of calculations that would be great.
Completely agree with Rob. Monitoring bi is a must. One day it will save you from trouble,
Here is "how to"
The below program will show sizes of all areas, including Primary Recovery Area = bi area
And here is how to disconnect users with long transactions
It sounds like you don't fully understand the purpose of -bithold. I suggest you read the relevant documentation and KB articles about the BI area and BI-related parameters before making changes.
The -bithold value is the limit of how large you want the BI area to grow before forward processing stops. It isn't limited by or related to system memory and it isn't necessarily related to database size. For example, you could have a 10 GB database with only 5 users and still experience significant BI growth. If one user starts a long-running transaction and the others are updating the database then once the newest BI cluster has filled, the BI area will grow. It will continue to grow until that old transaction ends, the other users' transaction activity ends, or forward processing stops e.g. due to the BI size hitting the BI threshold, or the database shutting down.
The tendency of your BI area to grow is dependent on how your application code is written and, perhaps, how your users behave. If you observe its size over time there is probably some size range that it will reach that is normal for that application, but it isn't something you can pre-calculate per se. It is just a limit that you think is appropriate for your situation.
What you want to avoid is a situation where the BI area grows very quickly to the point where it consumes much or all of the disk space in its partition. The BI area is both read and written to during crash recovery, meaning that it may need to grow during this phase. If your database crashes when the BI partition is full or almost full, you will not be able to complete crash recovery and open your database. The -bithold parameter lets you prevent this scenario by preventing runaway BI growth from consuming your disk space.
You can also choose what happens when the database hits the threshold. If you use -bistall, the database will stall (forward processing stops) but remain up. If you don't, the database will shut down. As a rough guideline, try setting -bithold no higher than 20% of the free space in the BI partition. This assumes that the free space in that partition will be relatively constant and is segregated from other files that may grow very quickly, e.g. application or system logs.
Don't rely on just -bithold to manage BI growth. It is a stopgap of last resort; you don't want your BI size to hit the threshold. You should also monitor your BI size on an ongoing basis and alert if it starts growing significantly. It is also a good idea to monitor for and alert on long-running transactions, which are an underlying cause of BI growth.
Thanks for the links Dmitri. It appears the logic for BI physical size calculation (dbuse) is from v8.x. On a v11 database with a single-extent 10 MB BI file, this code gives me:
Physical BI size: 84,934,656
The actual size is 10,616,832, or the number above divided by 8 (the BI block size in KB).
According to Dan Foreman's VST book, the definition of _filelist-size has changed over the years.
In v8.3B, file size = _filelist-size * _filelist-blksize.
In v10+, file size = _filelist-size * 1024.
Agree with Rob..
as you already discuss, seems like your bi size 15 GB, for the DB size of approx 197GB.
Database bi size growth is depend on long running transaction and frequent changes in records...
So, I have a suggestion that why not you are going for OE feature ( large file enable on database instead
of -bithold ) .
If there is a particular time on which long running trans increase bi file I think large file enable is
one of the best approach.
Regardless of db size, user count, machine resources etc, etc, etc...
If you are setting -bithold to something larger than 100MB you have a problem with your application and you should be focused on fixing the code.
Having said that -- there is lots of crappy code out there that needs fixing. So even though 100MB is a canary in the coal mine I'd probably give a pass up to 500MB and I might even pretend that I didn't see 1GB if the customer is actually taking steps to improve the situation.
But beyond that you're just encouraging dreadful coding and a complacent attitude towards bugs. Plus you're playing with fire.
As can be seen in the other thread currently running where someone has -bithold set to 19GB.
> I have a suggestion that why not you are going for OE feature ( large file enable on database instead of -bithold )
Large file support just means that extents can grow beyond 2 GB, up to 1 TB. That doesn't prevent the problem that -bithold is intended to address, which is uncontrolled growth of the BI file. If the BI fills its partition then the database crashes and you can't restart it, unless you add another BI extent in a different partition with sufficient space to go through crash recovery.
You should also be aggressively eliminating long-running transactions. Start with anything older than 2 hours. Once you have those under control go after anything older than one hour. Then 30 minutes. Rinse and repeat.
You **might** identify a few "special cases" that actually do something important where a long transaction is arguable. But 99.44% of the long transactions that you find are going to be people sitting on a screen doing nothing. Situations where a TRANSACTION is not appropriate or desired.
Fixing the code is a much more important task than setting -bithold higher.
Thank you Rob. I removed version 8 calculations from my page.
I guess there are no version 8 users and if there are ... too bad.
Aggressively eliminating long-running transactions -- is a must, especially in case of crappy code. I have 30 minutes rule. YMMV.
As to -bithold setting it depends. And there is no one rule that works for everyone. Mine -bithold value is more then anything mentioned in this or other threads.
Since it s a fact that we established there is "crappy code out there" and nobody is going to rewrite Progress application here written in 1980es, then the size of -bithold is a matter of volume of business rather than best practice. For some folks 500MB is enough, for some 19GB is not enough. I -bigrow after each bi truncate to 2 GB just to start with. And on average day that is enough. When bi reaches 5 GB I receive a text on my phone using the code mentioned above and handle that case by case. If bi reaches -bithold I will probably be out of work, because business loss of 10 minutes of downtime exceeds average yearly salary of DBA by the factor of 3 or 5.
Same is true about Lock Table overflow (-L). For some 50,000 is too much, for us anything less than 1/2 million means we need to close our business.
There are exceptions to every rule. Our original poster wanted a formulaic approach to setting -bithold and has rejected reasonable attempts to explain why this isn't appropriate. If one needs a formula for -bithold I happen to think that my formula is excellent.
Formula or not -- if you think that you need a large -bithold then your real problem is crappy code.
If you cannot fix the crappy code and you cannot address the long running transactions you still need to be aware that large -bithold values are NOT normal or ok and should not be viewed as something to set and then wash ones hands of.
Dmitri's example of being alerted well before actually reaching your company's -bithold is a good example of taking it seriously and treating the problem as the serious threat to system stability that it is.
IMHO anyone who wants to set -bithold greater than 100MB really needs to explain WHY, in detail, and then go on to explain what else they are doing to resolve the threat to their data. (If that explanation does not include fixing the crappy code then it is incomplete and seriously flawed.)