Re: PHISICAL LOGS configuration

The inconsistency that you are pointing out is because it's really impossible to exactly determine how much log space you really need. The determining factor is really how much your typical transaction is going to update. For performance issues, the total logical log size is not really going to matter that much. The physical log size has some influence on this, but in a rather indirect way. The main purpose of the physical log file is to save a copy of a page that is about to be updated, if it is the first update of that page since the last checkpoint. Because of that, when the physical log file is about to get full, we issue a checkpoint, even though the 'checkpoint interval' has not been reached. Therefor, by making the physical log file too small, you will see an increase in the frequency of checkpoints. My personal preference is to keep the checkpoints about 5 to 10 minutes apart and size the physical log so that the physical log filling up does not cause the checkpoints to get started before the checkpoint interval is reached. However, by sizing your system so that the physical log size is really large has a disadvantage as well, it means that recovery might take a tad bit longer. This is somthing that you have to decide for yourself. As far as decision as to number/size of the log files, my preference has been to go with more smaller ones as opposed to fewer larger ones. But the reason for this has nothing to do with performance, but recoverability. Since the log file will not be copied to an external media until it has been closed, I have always prefered to try to get it to that external media as quickly as possible. The only way to do this is to minimize the size as much as I could. However, since you are on a 5.x version, you have to be careful because there is a limit on the number of log files that you can have. Now for the performance issues. Use buffered logging if at all possible. If you can use buffered logging, set your log buffers to at least 32 pages. Check tbstat -l to see what the log buffer io rate is - it should be near the size of the log buffer itself. If your application is OLTP, set your LRUmin/max down pretty low to minimize checkpoint length. Try to get your read cache hit is above 98%. This is most easily done by increasing the number of buffers in your instance. Also, and probably most important, know your applications. Most of the performance issues are really brought on by application problems, be they locking problems or joins being done without indexes to support them. This is more easily done in 7.x because of the sysmaster database, but you really need to insure that your data has indexes built on the main ways that the data is accessed. Hope this helps. Madison Pruet