Re: Newbee with questions about physical and logical log allocations.

The method I use for calculating Physical Log sizes is to estimate the number of transactions in 5 minutes (CKPTINVL) and then calculate how many rows these will affect. That number of rows is the number of pages changed in 5 minutes. Multiply that nunber by 1.33 to get the physical log size. If you do that it should result in the physical log being flushed every 5 minutes, or when it gets to 75% full, which we've just calculated. It's only rule of thumb and needs tuning from that point. As to the logical logs they are a bit more tricky. This time you need to decide how much data you could afford to lose if a head crash wiped out your system. In those cases the un-backed up logs are lost. If they are mirrores you have some protection but it wouldn't be impossible to have simultaneous head crashes. I've seen them - caused by Power Glitches! That said, your log capacity should be sufficient to allow for the maximum planned outage of the log tape backup device, or for the longest transaction. But how big is a transaction. Here I normally calculate the size in the log of a transaction by adding the row lengths of all rows affected by a transaction, multiplying this by 2 (before and after images) and adding 20 bytes per record changed(this figure can be made more accurate by reference to the manuals!). This figure for small transactions multiplied by the number of transactions performed during a tape outage should be about 90% of the logical log capacity. For long transactions I normally calculate a background transaction rate, multiply this by the potential length of the long transaction, add this to the long transaction size, and then make this figure equal to LTXLWM percent of the total log size. After all that it is then a matter of WAIT AND SEE what happens in practice. You can use onlog to dump out all changes for a tranaction to determine precisely how much is changed by a transaction and I recommend doing this for all known transaction types before the system goes live. It is good documentation of the transaction and can be revisited later in live running comparisons. As to the number of logical logs it could be preferable to have more small logs if you are worried about non-mirrored logs. They don't all need to be in root dbspace and a common practice is to set them up in rootdbs and then move them to a separate logs dbspace, adding more as required. I would always recommend setting the shared memory number of logs 2 or 3 bigger than the number specified in root dbs specification for future flexibility. Well, that covers quite a bit of an online course. If you want to know more you know where I am. > Does anybody have a good rule-of-thumb calculating the size of the > physical and logical log sizes. > > Informix said the total physical and logical logs size should be 20% > of the total dbspace dedicated to online. > Informix also indicates that the ratio of logical to physical logs > size should be 3 to 1. > > What informix does tell you is how to arrive at the number of LOGFILES > to be managed by informix. > > I know informix needs at least 3 logicals logs to perform log > rotation, so if LOGFILES is set to 10 does informix attempt to > allocate 10 logfiles of X LOGSIZE in side the root dbspace or does > it allocate them dynamically when needed. > > I need this info to get a handle on how much raw disk space to > dedicate to the root dbspace. > > > > > > > Malcolm Weallans Online Database Consultancy Phone 01628-72154 Fax 01628-37463 CIX - onlinedbc