Re: Informix OnLine and Striping

> From: Jack Parker <jparker@hpbs3645.boi.hp.com> > > } In reviewing system performance, we found no significant bottlenecks. > } However, a consultant has suggested we stripe our raw data. I am not > } convinced that a significant performance improvement may be gained, > } as the database is quite large (52GB) and VERY dynamic, with rapid > } growth. (most frequently accessed data, towards the end). > } > } We currently run on a Sequent /700, OnLine v. 5.0, but anticipate > } migrating to v.6 and v.7 within 12-14 months. Once on v.7, we plan > } to use fragmentation, hence we would be row or key level striping. > } > } The question of the day is....Is anyone using striping with Informix, > } what are the pro's and con's, and would they do it again? > } > > Couple of years back I tried this on a DataGeneral box. It actually > made things slower. Your mileage WILL vary. First let me note the irony of this and other threads going about in this topic. Recently we had the famous "What affects performance" post, to wit we mostly replied everything from "it depends" to "fairy god mothers and pixie dust", yet here we are discussing the details of performance that we, theoretically, didn't want to get into. Strikes me kinda funny. WRT disk striping: We also striped a clients DG, and noticed an almost immediate gain in performance. Their system had basically one drive dedicated to the informix data, and another drive for the logs. We added in another drive to match the data drive, and we striped them together. When we initially reloaded the databases from a tbload, it took about 1.5 hours. After we striped, it went down to 1 hour. For a system with one controller, several drives, and some CPU power to burn, I believe that striping the two drives together gave the system a wider pipe to move data through. Assuming the SCSI bus isn't saturated, random bursts of data should get to their resting space twice as fast. This sytem had an I/O bottleneck, and striping those two drives seemed to help. However, someone else (I lost the post) mentioned that striping basically invalidates what OnLine tries to do to get the flushed to the disks because it effectively eliminates a page cleaner. And if you look down to the heart of it all, the way page cleaners are being used now (one per disk) is similiar to what the new OnLine is planning on doing: one process per disk and one processor per process. (Yes, it's a little abstract, and DSA does a lot more than just that, but anyway). When we looked at the striping, we looked at it this way: If we break the performance game down into it's atomic pieces, the disk is the slowest piece. Algortithms etc are important as well, but they are typically slow because they move to much data, which inevitably comes from the disk drives. So, to increase performance of the system we can increase the performance of the disk, or we can reduce the accesses to the disk. One way to reduce disk access is by reducing the volume of data being read off of the disk. This can be done by both removing data and by making sure the optimiser is processing our queries appropiately. Another way is by cacheing more of the data in memory, but that doesn't help much when the process calls for scanning a million rows. Queries like that can just flush your caches down the tubes. Anything that would be worth keeping around ends up getting washed away as the cache keeps up with the table scan. It would be nice if there was some sort of limit as to how much of the cache a massive table scan can obiliterate. If we have reduced to a minimum the amount of data we need to move to/from the disks, then the next step is to speed up the disks themselves. Since applying more voltage to the disk motors doesn't seem to work, and their aren't any options for adding more wires to the SCSI bus to make it wider, and we assume that the computer can feed the disks as fast as they can take the data (i.e. most of the time the computer is waiting on the disk drives, and not the other way around), we need to have more drives to handle the load. When the crowds come in at lunch, the bank adds more tellers. Striping helps achieve that, I think, because it can help widen the path that the data travels. If I write a 2K block of data, and split that block into 2 pieces of 1K each, and give each block to a differenet drive (each drive having the same response time), then I've performed that write twice as fast as I could have with one drive. Clearly, this transaction isn't free because the CPU must make the decision of how to place that data on those drives and break up the request accordingly. But, if there is enough free CPU to make that decision, and that decision can be made faster than the repsonse of the one drive, then we theoretically have a net gain. For example, if I have two disks drives that can make seek and store 1K of data in 1 second (yeah, I know, throw them away and by real drives, but this is an example). Then it should take 2 seconds to write a 2K block. But, if I have two of these drives, and the computer can make the decision to break that 2K block in 1/2 second, then the net time would be 1 1/2 seconds (assuming that the disks are writing asynchronously and simultaneously), so we'd have a 1/2 second gain. Now I know, that compared to the Alice in Wonderland scenario above, that reality tends to be a bit different and the issues are more complex and less cut and dried. But, seems to me, that it's close. Some folks would just move heavy tables to their own drives and do the partitioning manually, but, IMHO, that doesn't help when you do a lot of processing for that large table (and typically the large tables are the ones that get most of the processing). With CPU to burn on that issue, then every table gets the benefit of the partitioning/striping becuase the computer splits it up for us. I'm pretty confident that in our case the CPU had the surplus to handle the extra load that the striping software put on to the system. We seemed to have gained some performance in the process. Now, however, the client is finally upgrading the entire system by effectively doubleing everything: memory, cpus, and drives. I get to figure out how best to reconfigure their new drives. One of the nice things that they are doing, I think, is that they are getting several physical drives rather than getting the same number of twice as large drives. Several systems seemed to be sold today with the large monolithic drives. "We've got 4G of storage!!" Yeah, but it's only two drives! I'm a big fan of putting logs on their own drives, but it's pretty hard to justify when you've only got two, and they're both huge. Right now, with this new system, I'm looking at two volumes of two striped drives, and dividing our heaviest tables between the two volumes. Also, I'll probably put the logs on a third, less busy drive so the log purges don't fight with the data writes. Obviously people are mixed w