Re: MORE RAM - BIG CONTROLLER CACHE / DB ON RAW - DB ON FS

I have outlined our experience below. MOPSOMER@raychem.com wrote: > I recently had a discussion with a consultant. The following is a > replay of this discussion. I just wanted to see your opinions on it. > > I : Why do you only sell storage arrays with no cache or a small one ? > X : Disk subsystems with GBs of NVRAM come from the mainframe world, > where memory prices are high and memory capacities quite low. > In the open systems world, RAM is not expensive, so it is better > to spend your money on extra GBs of RAM as this is much faster > and the OS, DB and application can use that RAM more intelligent > than that the disk subsystem controller can do with its big cache. I believe this to be true. The controller cache will cache the disk reads and writes. In a database environment, especially OLTP, there is not much use for this. Databases have their cache levels low because we do not read from the same physical disk pages all the time like an application server would do. If you want to spend money, spend it on RAM, or even better a Solid State virtual disk (RAM disk) for the logical logs and rootdbs. Are you having disk I/O bottlenecks? What is the problem you are trying to solve? > > I : Okay, but our BUFFERS are already set at the maximum (768.000) > and shared memory in Solaris 2.5.1 is limited to 3.75 GB anyway. > X : The biggest benefit from controller caches comes from enabling > fast-writes and colescing writes. The effect of the controller > cache on reads is very limited, as your database is over 300 GB. > For the fast-writes, you do not need a cache which is several GB. BUFFERS are for RAM. The onconfig variable here would be the RA* variables. You want RA_PAGES to match your I/O buffer cache. This is more important for DSS than OLTP. > > I : Okay, but what about the RAM that I can't use for the BUFFERS ? > In Informix 7.30, the maximum number of BUFFERS is still the same. > And even if I could set BUFFERS much higher, my checkpoints would > probably become much too long for an OLTP environment. I already > have checkpoints which take between 10 and 15 seconds nowadays. > X : You could also put your db in filesystems instead of using raw. We use Veritas to create mirrored logical Volume groups of 2GB of raw disk space for Informix. This is much better than using unix filesystems and safer for you (no one will delete the files). You get ~ 10~15% better performance. Please do not use raw. Oracle has to use raw except for Oracle Parallel Server. Checkpoints are another issue. If checkpoints are too long, try adding LRUS and Cleaners and decreasing LRU_max and LRU_min. We have for our OLTP, 100000 BUFFERS, 12 or more LRUS and min and max at 5 and 3 or 2 and 1, depending on the system. I have never seen a system that required that big of a buffer, especially for OLTP. You might want to review the tuning suggestions for BUFFERS. > > I : This is new to me. I thought filesystems were much slower ! > X : The difference is not that big anymore. With the direct I/O > improvements in Solaris 2.6 and the usage of Veritas VxFS instead > of UFS, you will get performance comparable to raw disk, but the > investment in additional RAM can be used as filesystem cache, so > you can indirectly increase your Informix BUFFERS, avoiding a lot > of expensive disk I/O or I/Os satisfied by the controller cache. > I/Os satisfied by RAM are much faster than the controller cache. This may take some bench marking to determine if this is correct. I prefer to stick to raw. There are other devices and procedures that can be utilized if you are truly getting an I/O bottleneck. > > I : [End of discussion; surprised by the statements on raw versus fs] > > Any ideas or comments on this ? > Did somebody already convert their Informix databases from raw disk to > UFS or VxFS (for performance reasons) ? Is it indeed a better idea to > invest in RAM than going for a big controller cache (4-16 GB) ? Where > does that 768,000 limit for the ONCONFIG parameter BUFFERS come from ? > Would it indeed make no sense (no performance improvements) to have 4 > GB of BUFFERS instead of the 1.5 GB which is the maximum in Informix ? > > Mario Opsomer -- R/ David S. Bruce Manager - Database Support IT&S - DHL Airways Inc.