Hi
Could someone please clarify a question I have about how the space in the BI is used.
KB S1274 states:
"As Bi notes are written serially to the Bi file then clusters can only be re-used serially. This means it only takes one active transaction to remain open for Progress to keep adding new clusters as other users open and close transactions."
I read this to mean that while a transaction is open the BI cannot reuse existing clusters and must add new ones to the end, but I think I may be wrong here...
I created a new sports database, proserved it and ran two client connections. The first held a lock on a record in a transaction while the second performed sizable DB updates inside one large transaction. I ran the update process repeatedly and although the BI grew to cover the total transaction size it didn't continue to grow for every run (i.e. it must have been reusing existing BI clusters once the BI had reached a certain size.)
So am I wrong that a single open transaction always forces creation of new BI clusters?
The clusters of the BI file are arranged logically in a ring, i.e. c0 -> c1 -> c2 -> c3 -> c0.
When you open the database there will be at least four clusters in the ring; there will be more if you ran proutil bigrow. BI notes will be written sequentially to c0 until it is full, and then to c1, and so on. When c3, the last cluster in the ring, is filled a decision point is reached: can the space in c0 be reused or do we have to format and insert a new cluster c4 into the ring and write new notes to it?
If (a) the next cluster (c0) does not contain BI notes for an active transaction and (b) all buffers modified by notes in c0 have been written to disk and (c) c0 is at least -G seconds old then c0 can be marked as empty and reused. The BI file does not grow. If those conditions are not met then a cluster c4 is inserted in the ring between c3 and c0 and notes are written there. The BI file has grown logically (and perhaps physically) by one cluster.
Your question was whether a single active transaction always forces the creation of new clusters. I would answer "not necessarily". If the BI file current contains 20 clusters and the active BI cluster is c5 and your active transaction's begin note was written to c4, we still have 19 clusters' worth of space to write to before we have to decide whether to grow the BI file. If that same transaction is still active once we get back to c3 and fill it then yes, the BI file will grow because we can't reuse c4.
you are partly right. not /always/.
transactions that don't actually do anything are optimized away in both the 4GL runtime and in the database itself. that makes it harder to see what is happening.
when there is an active transaction that has written a transaction begin to the bi file, the bi space /from that point forward/ cannot be reused until the transaction end. this is becuase the transaction might have to be rolled back and if we overwrote any of its notes, that would be impossible. the trnasaction manager does not know which bi clusters might have been used by a given transaction so we assume all after the transaction begin.
clusters earlier than the earliest active transaction begin can be reused.
> On Nov 25, 2015, at 8:30 AM, Grant Holman wrote:
>
> Update from Progress Community [https://community.progress.com/]
>
> Grant Holman [https://community.progress.com/members/gpholman]
>
> Hi
>
> Could someone please clarify a question I have about how the space in the BI is used.
>
> KB S1274 states:
>
> "As Bi notes are written serially to the Bi file then clusters can only be re-used serially. This means it only takes one active transaction to remain open for Progress to keep adding new clusters as other users open and close transactions."
>
> I read this to mean that while a transaction is open the BI cannot reuse existing clusters and must add new ones to the end, but I think I may be wrong here...
>
> I created a new sports database, proserved it and ran two client connections. The first held a lock on a record in a transaction while the second performed sizable DB updates inside one large transaction. I ran the update process repeatedly and although the BI grew to cover the total transaction size it didn't continue to grow for every run (i.e. it must have been reusing existing BI clusters once the BI had reached a certain size.)
>
> So am I wrong that a single open transaction always forces creation of new BI clusters?
>
> View online [https://community.progress.com/community_groups/openedge_rdbms/f/18/t/21608]
>
> You received this notification because you subscribed to the forum. To stop receiving updates from only this thread, go here [https://community.progress.com/community_groups/openedge_rdbms/f/18/t/21608/mute].
>
> Flag [https://community.progress.com/community_groups/openedge_rdbms/f/18/t/21608?AbuseContentId=7dde597f-304f-43dc-8dbb-d90920d01163&AbuseContentTypeId=46448885-d0e6-4133-bbfb-f0cd7b0fd6f7&AbuseFlag=true] this post as spam/abuse.
Hello Dapeng, thanks for your reply. I can confirm I was updating a record and checked a transaction was active in promon.
Hi Rob,
Great answer, that really cleared it up for me.
Many Thanks!
Hello Gus, thanks for the extra clarification.
One more question if I may:
The Database Admin guide recommends increasing the 'BI buffer size' to reduce 'Total BI writes per second'. I know the -bibufs param increases the NUMBER of buffers but I can't see a parameter to increase the SIZE. Would this be BI block size?
Yes. Increase it, with the DB offline:
proutil dbname -C truncate bi -biblocksize 16
This would make BI block size 16 KB (the maximum).
Great, thanks again Rob.