Size, Number, and Location of Logical Logs

When either IDS or OnLine is first installed, all logfiles are in the root dbspace. The OnLine engine defaults to three logfiles, so unless changes are made in the tb/onconfig file, you will start out with three logfiles in your root dbspace. The IDS engine creates six logfiles by default. Version 7.30 creates 10. Informix does not allow you to change the size of the logfiles, although it does allow you to change the number and location of the logfiles. In OnLine systems all logfiles will be the same size.

IDS systems give the DBA the option of specifying the size of each logfile at the time of creation. In these systems, the logfiles can be of different sizes. The big advantage to this LOGSIZE parameter in IDS is to allow the DBA to increase or decrease the size of logical logs without having to completely rebuild the instance. Another advantage may be when the DBA desperately needs more logfiles and has to scrounge around finding bits and pieces of disk real estate to use for the logfiles. Here, the different logfile sizes may allow the DBA to extract the most logfile space from his available space.

In the initial configuration, the engine will calculate the total size requested for the logfiles (number times size of each logfile) and will assume that all of these logfiles go into the root dbspace. If this space is larger than the size of the root dbspace, the engine will not initialize. This is a common cause of problems during installation because the DBA will often specify his maximum number of desired logfiles in the tbmonitor process, and the initialization program will try to actually allocate space for all the requested logfiles in the root dbspace.

One of the major questions in the original setup of the OnLine system concerns the anticipated size and number of logfiles. Generally, you need to have sufficient logfiles to allow logging of a transaction in your largest table. If you do not have adequate logs for this, you will get long transaction errors and will not be able to complete your maximum size transaction.

Adding additional logfiles is a simple procedure. Changing the size of logfiles is a complex undertaking in OnLine and a simple one in IDS. To increase the size of logfiles the entire OnLine system must be reinitialized and your data must be rebuilt from an export or from other flatfiles. Changing the logfile size, reinitializing, and then restoring from an archive does not work. The RESTORE command in tb/onmonitor performs an initialization itself. It initializes the system to the parameters that were in effect at the time of the ARCHIVE. If you try to use the RESTORE in this manner, you will end up with the same size logfiles that you started with.

The Informix manual recommends a total logfile allocation of approximately 20 percent of total database space. Depending upon the nature of your transactions, this may or may not be enough. Note that a maximum size transaction by definition takes quite a bit of time to complete. If other loggable transactions are occurring at the same time, the logging requirements must take into account all of the logging load simultaneously, not just the largest. Since both the number and the location of the logfiles can be changed, only the size represents an unalterable (without reinitializing the disk) decision. There is only one constraint to consider, and that is the maximum number of logfiles allowed. An OnLine system using a 2-kilobyte page size can have about 60 logical logfiles. IDS removes the limit on the number of logfiles. Size the logfiles such that a total of 60 logfiles will take up about 20 to 25 percent of the maximum anticipated database size. If your database grows to such a size that you reach the maximum number of logfiles, you'll probably be reinstalling on bigger hardware before then anyway. Don't worry too much about trying to predict years into the future. Get the system tuned for now with a little bit of room to expand.

One factor to consider in deciding on logfile size is the speed of your tape backup device. Keep the logfile size down if you have a slow backup device. The real solution is, as usual, hardware. Get a faster tape device. As a system gets larger, the capacity of the archive tape device and the logging tape device often become the limiting factor in how large the database can grow. When your archive devices are running constantly in an attempt to archive your system, you are reaching the limit of manageable size. IDS systems have more flexibility in this regard, as the onbar/onarchive programs can allow parallel logging and archiving to multiple tape devices. Taking advantage of these capabilities requires that the DBA use onbar rather than ontape.

New logical logs do not come into use until after an archive is done. The level of the archive does not matter. If the DBA is just adding or moving logfiles, the trick here is to do the archive to /dev/null.

The location of logical logs is also important. Logfiles usually have a relatively heavy write activity. This usually consists of efficient sequential writes. Still, Informix recommends that logfiles be optimized for writing. If you have a preferred disk location that allows more efficient writes, this is a good place to place your logs. An example would be in a UNIX-mirrored rather than a RAID 5 disk partition. RAID 5 is optimized for reading. Depending upon your implementation, writing to a RAID 5 partition can be significantly slower that writing to a mirrored partition. The DBA should also look at placing the logfiles on a separate disk to minimize disk contention.

One thing to consider is moving the initial three logfiles out of the root dbspace. If there is a lot of temporary table creation, and if the temp tables are created in the root dbspace (the default) relocating the logfiles is often a good idea. Since OnLine requires a minimum of three logfiles at all times, moving the initial three is more complicated than simply dropping the old ones and adding the new ones. You must always have three logfiles available at all times.

To move the logfiles, you need to be logged in as user Informix or root. The Informix login is preferred because all activities as root need to be done through the command line rather than through tb/onmonitor. First, you have to create at least three more logfiles in another dbspace. To activate them, do an ARCHIVE to /dev/null. The /dev/null archive occurs very quickly, as OnLine handles archiving to /dev/null differently from the way it handles archiving to a real device. This dummy archive convinces OnLine that a real archive has been done and activates the new logfiles for use.

Now, using tb/onstat -1, see which logfile is currently in use. You can use the tb/onmode -1 command from the command line to cause the current log to advance to one of the new, non-rootdbs logfiles. You may need to execute the tb/onmode command several times until the current logfile is one of the newly created ones. You then need to do a backup of any of the logfiles you want to drop. Even if you skipped over them with the tb/onmode command, they need to be backed up to a real tape device, not to /dev/null. After the logs are backed up, go to quiescent mode and drop the three logfiles that are in the root dbspace. If you are using tb/onstat -c to force continuous backup of logfiles, these logfile backups will be happening automatically and you won't have to do them manually. Finally, do an actual Level 0 archive to tape to establish a baseline for future recoveries.

If you already have an Informix system running, it is very informative to watch your disk I/O statistics to see exactly how your application reads and writes to disk. This simple c-shell script called watch_hot will do the job:

   #!/bin/csh
   while ( 1 == 1 )
   tbstat -D
   tbstat -z
   sleep 5
   end

Since this script reinitializes the tbstat statistics every five seconds, make sure that it does not interfere with any long-term tbstat statistics collection that you or someone else may be doing. The script will output numbers representing the incremental reads and writes to database chunks every five seconds.

The above script will work with both engines. You can accomplish the same function by running queries against the SMI database tables on IDS systems. We'll go into more detail in the chapter on SMI system tables

One thing that you'll find with this script is that your highest rate of writing is probably to your physical logs. This is where you need to watch your disk placement to optimize for writing speed. The physical log can be a bottleneck for the entire system, so place it carefully.

Another thing that this script can show you is where you are in completing an archive. If you run it while an archive is running, you'll see a running bulge of heavy read activity in your chunks as the archive program walks through your database structure