Separate logs are fast logs

By Paul Murphy, author of The Unix Guide to Defenestration

Every major database management product uses multiple logs. Unfortunately one of these, the transaction log, can often bottleneck your entire system. This is particularly true of older Informix implementations under HP-UX where the transaction log pretty much has to be a DDS tape drive, but it is generally true of all the major Unix/RDBMS combinations.

A nice trick I've used several times to alleviate this problem is to add an external disk array on an independent controller and dedicate it to recording the transaction log. Typically the more botched the main disk storage strategy is, the easier it is to get this approved and the more effective it gets.

Where storage is really messed up, migrating temp space for things like DBsorts to swap defined on a dedicated device on this array can also offer dramatic performance improvement but think through crash recovery first. If a failure on this device will bring the DB down, don't do it.

Costs are relatively low but depend on the hardware in place. Figure 10K for about 140GB on a US3 controller and you'll be in the ballpark for new gear -less, of course, for used.

Of course most places will still want to write those logs to tape too. In many cases you can accomodate this by writing them out during off hours or by using something running at a high nice level to haul things out to tape when the controller's not busy writing. In one case, however, the client wanted maximum 24 x 7 throughput so I used a small RAID array with dual ports and a new V100 with two DDS3 tape drives that it fills up in sequence and then pops out so someone can change it - allowing everything to run continously with only a write load on the controller in the DB server.