Answered Before-Image - variable extent

[MarkoLachance]

Hi !

I have a database using the following before-image setting (.st file)

b /dc03db12/db/acct/can/acct.b1 f 204800
b /dc03db12/db/acct/can/acct.b2

If i understand well, the first extent is fixed to a maximum size of around 200 meg and the second one is variable .

/dc03db12/ has a maximum of 5 Gig of allowed space.

My question is what will happen with the variable extent , what will be the maximum size ? Will it grow up to 5 Gig ?


Thanks !

Progress version : 10.2B07, on AIX

 

[Cringer]

It depends on whether you have large files enabled on the DB. "proutil <db> -C describe" will tell you that:

Database Features

   ID   Feature                            Active  Details
  ----  ---------------------------------  ------  -------
     5  Large Files                        Yes
     9  64 Bit DBKEYS                      Yes
    10  Large Keys                         Yes
    11  64 Bit Sequences                   Yes

If large files are enabled then it will use all the space available and then the DB will crash. If not, it will fill up to the max value of an extent without large files (~2GB) and then the DB will crash (unless you have BI Stall on, in which case it will be a bit more graceful AFAIK).

 

[Rob Fitzpatrick]

If your BI file needs to grow, it will grow (logically) within the 200 MB fixed extent. If it needs to grow beyond that then it will grow physically, extending the variable extent as needed until (a) it no longer needs to grow, or (b) the extent reaches its maximum size, or (c) the database stalls (if -bistall and -bithold are in use).

The maximum size of the variable extent depends on whether the database is enabled for large files (check this with proutil describe). If it is not, the maximum extent size is 2 GB.

If large files are enabled and the BI continues to grow, when it reaches 5 GB the DB will be unable to write BI notes and the database will shut down. This is Very Bad. It is bad because when you attempt to restart the database it will go through crash recovery where it will attempt to write more BI notes and will immediately crash again. You will be unable to start it successfully until you move the BI to a file system with more available space.

5 GB is a very small partition for the BI unless your DB is trivially small (like a few MB). It would be helpful to know more about your DB size and number of users.

As I mentioned you can use the -bithold and -bistall parameters to prevent the BI from growing endlessly until it fills its file system. If you do, be conservative. Set the threshold to about 1/4 of the available space. When you crash, more BI notes have to be written to recover from that crash, and if you crash during that crash recovery, still more notes must be written, etc. etc.

 

[Rob Fitzpatrick]

You should have a process in place to monitor the size of your BI file and alert you when it grows significantly.

 

[MarkoLachance]

Thanks for your quick replies

Here is the result of the proutil :

Database Features

ID Feature Active Details
---- --------------------------------- ------ -------
9 64 Bit DBKEYS Yes
10 Large Keys Yes
11 64 Bit Sequences Yes

So the maximum will be 2 Gig, and the database size is around 30 Gig. So I guess that we should enable large file and add more space ?
Of course, I will check to add a process to monitor the BI file

 

[MarkoLachance]

Should I change increased the fixed extent also ?

 

[Cringer]

what you do depends on a lot of things. How big is the variable extent at the moment? You can't resize the fixed extent easily, but you could add another one. Enabling large files is simple to do and definitely worthwhile.

 

[MarkoLachance]

At this moment, the size of the variable extent is only 8 k, so it is not use at this moment. I will suggest the option to enable the large file. Thanks again for you help guys !

 

[Rob Fitzpatrick]

If you have Enterprise RDBMS, there is no valid reason not to enable large files.