Re: Overcoming 7.12 vs. 5.05 performance gap

Without knowing the nature of the application, I really can't make the best suggestions, but I do see some things that I question a bit. Also, I don't really know how close you were to your limits on 5.x. Since performance bottlenecks have an inverse porportional curve, you can be very close to your limits with very good response, but a small increase in work can actually cause a major degredation. Ok -- here's my best shot. 1) BUFFERS 2000 --- try increasing to arround 40000, if you have the memory for it. 2) LRU_MIN/MAX_DIRTY 50/60 --- try decreasing to no more than 5/10. 50% of 2000 pages is 1000. That's 1000 physical IO's that might have to be done during the checkpoint. At 40000, that could yield 20000 pages -- That's going to take quit a bit of time. 3) NUMAIOVPS 2 --- unless your platform supports KAIO, it might be better to set this no lower than 6. 4) PHYSFILE 2000 --- probably too small. This is going to cause a checkpoint if it gets 75% full. That means that after 1500 pages have been altered, you will encounter a checkpoint. The best way to monitor this is to see how often the checkpoint is actually occuring. If the checkpoints are often occuring before CHKPNT interval is exceeded, you need to increase the PHYSFILE. 5) MULTIPROCESSOR 1 --- This can be turned off, even with multiple cpuvps. What this parameter really does is to turn on spin locking. This causes the cpuvp to go into a loop when a resource in memory is unavailable. The theory is that since these resources are locked for only a vew nanosecs, it is better to spin than to give up the processor. This tends to work best on systems with more than 8 physical processors. With more limited environments, it might be more efficient to give up the processor. 6) SHMVIRTSIZE 8000 --- Probably way too small. Check in your online log file. Are you dynamically adding cpuvps? If so figure out how much memory you are actually using and set SHMVIRTSIZE to that value. While it is ok on most hardware to occasionally add a virtural memory segment, it is somewhat of a drag on overall performance. 7) CKPTINTVL 300 --- Try setting to 600. The main thing that checkpoints are really used for is to set the spot where recovery is to begin. Since this cost is only encountered when the engine is started, a checkpoint every 5 minutes might be too much overhead. This is expecially true if your checkpoints are taking more than 2-3 seconds to complete. However, remember to monitor the physical log file size to make sure that it's filling up doesn't trigger premature checkpoints. 8) Read ahead--- --- Try setting RA_PAGES/RA_THRESHOLD to 32/30. Finally -- if at all possible, try to get on a release past 7.12. The sysmaster database has a lot of tables that make it very easy to analyze where problems and bottlenecks are. But, there are some problems with queries made against some of those tables in 7.12 that can result in an online abort. Madison Pruet