OnLine performance using RAID

Thanks to Louis for the information on RAID and its technical merits. RAID works great for our requirements. We have many systems here running Informix Online in various configurations and have successfully tuned each for optimal performance. We will go into greater detail on our RAID implementation, but must also mention that we have deployed Informix systems with and without mirroring and with and without RAID. Each implementation was influenced by application, database and operational requirements. Each performs well when properly tuned. Each can perform poorly if you don't know what your are doing. This was our first experience with RAID and it was fun to make it work. Our users are very happy with reliability and performance. Now to the good part... Operational Requirements 100 users. 24 by 7 Operations. Customers on the phone wanting answers. The Application A Reservation System that is highly interactive, update intensive, periodic full text searches (how is your company spelled?) and batch reports (run at 2:00 PM without impacting on-line performance). The Database 4.5 GB and growing. A large conference table with over 100 other tables. The problem Poor Interactive performance. Checkpoints take 15 seconds. Batch reports take hours. Quarterly dramatic increases in business activity. We put together a plan to measure, analyze, propose and modify our system incrementally in 3 phases. We also involved the developers, users and management in our plans. You can't tune in a vacuum... First we'll describe what the system started as, second what we discovered and fixed, and finally what the system looks like now... The Hardware Sun SS1000 4 CPU's 128 MB RAM Four .5 GB Drives Two 4mm Tape drives (one for archive, one for continous logs) RAID Fast-wide SCSI Controller 5 2GB Drives The Software Solaris 2.3 (billions and billions of patches) Informix On-Line 5.03.UC1 In-House Developed Applications 4GL P-code The Layout Complete Database on RAID tower System and User Data on Sun Disks Discoveries When vendor upgraded RAID Proms, they set the RAID to Level 3 (arg...) RAID 5 is what you want for an interactive database. Our developers suggested that the Conference table should be seperated from the other tables to minimize contention. We added a second RAID tower. Logs were moved to the Sun Disks to minimize RAID disk contention. Logs should be on a separate drive from the database. Think of each RAID as a inseparable group of disks. Root DBS was moved to the Sun Disks. We used Informix mirroring for reliability. Note that temp tables are in rootdbs, a source of contention with the database. The Informix shared memory buffers is 16MB of RAM. We set LRU_MAX_DIRTY to 1 and LRU_MIN_DIRTY to 0. This fixed checkpoint duration. Calculation of the amount of time it takes to flush the buffers to disk using the Informix default cache values is left to the reader. Take your time. Added cache memory to RAID towers. Our RAID towers featured a write-back-cache. Memory in the RAID is battery-backed up. Writes to the tower are acknowledged as soon as they are in the towers RAM and queued for write to disk (i.e. assuming the cache isn't full, writes are nearly instantaneous). RAID Striping was set to 42 sectors (the meaning of life). Don't even ask where we got this number. When Informix gets in trouble, it performs 16KB I/O's (in other words, 8 times page size). Read-ahead/behind cache hits in the RAID memory benefited. Exported and imported the database. We now know exactly where extents are. Rewrote batch program. We told the developers we had CPU to burn. They used it. What the system looks like now Sun SS1000 4 CPU's 128 MB RAM 4 0.5GB disks 2 4mm Tapes 2 Ea. RAID`s Fast-wide SCSI Controller 5 2GB Drives 32 MB battery backed cache Conference Table on RAID tower 1 Other tables on RAID tower 2 Logs on Sun Disks RootDBS and mirror on Sun Disks Results Sub 3-second response on interactive transactions 24 seconds on Conference table text search (non-indexed) RAID tower avg service time < 10 msec Informix cache hit (read 97%, write 93%) RAID cache hit (80% avg) Disk crash on one RAID tower. Tower alarm very loud. Drive replaced and rebuild took 1 hour. Users totally unaware, system kept running (try this with mirroring). Do's Define an entire RAID tower as One Chunk Let Informix Optimize the I/O and don't try to second guess it. Talk with developers They know how their apps really work. Talk to and watch users Watching what they typed was sometimes different than what they claimed (surprise, surprise). Use performance analysis tools tbstat, iostat, mpstat, perfmeter, etc. Read all manuals Don't believe all you read. Test and understand the hardware. Tuning parameters of the RAID has dramatic effects on performance (stripes size, cache size, etc.) Measure, analyze, modify Understand what you are changing before you change it. Apply the currect architecture to solve application, data and operational requirements RAID 3 is probably not right for Informix Online 5.0 Cache, cache, cache RAM is cheap... Don'ts Change too many things at once Tuning parameters can be highly interactive. Believe the system is like you think it is Get the facts. Ready, fire, aim Planning is instrumental in success. Our system is working quite well with RAID technology. We are kicking around things to change Compiled vs. P-code Save CPU but put more pressure on I/O subsystems. Additional RAID Currently mirroring Rootdbs and Solaris (a risk) Lastly, Your mileage may vary. Thanks to all the developers, systems people, and users.... -----------------------------------------------------------------+------------ _______ _______ _______ | /\\ ____\\ /\\__ __\\ /\\__ __\\ David Buchholz | \\ \\ \\___/ \\/_/\\ \\_/ \\/_/\\ \\_/ Unix Systems Administrator | Standard \\ \\ \\____ \\ \\ \\ \\_\\ \\__ ConferTech International Inc. | Disclaimers \\ \\______\\ \\ \\_\\ /\\______\\ Westerminster, CO. | Apply \\/______/ \\/_/ \\/______/ daveb@cfer.com/(303)633-3089 | | -----------------------------------------------------------------+------------