Running Informix on Solid-State Drives—Speed Up Database Access

I have been successfully running my primary Informix development database server on a solid-state drive (SSD) for over a year now. My development server has 12 CPU cores, 24 GB of RAM, 5 TB of disk space, and one 512 GB SSD. I use the server for developing Informix data warehouse projects and benchmarking OLTP versions. I started experimenting with the SSD when I got the server last year. I moved my main Informix database spaces from a pair of mirrored disk drives to the SSD. I was testing two Informix servers: one was release 11.50 and the other was release 11.70. Both versions were much faster on the SSD. Since I used links to the database space, I could copy the space back and forth between regular disk drives and the SSDs, reset the links, and start the server to test performance. The SSD performance was so much better, I did not want to go back to the old disk drives. Stability has been great and I would recommend SSDs for a critical production system.

Disk drives have been the bottleneck in database performance for years. Disks have gotten bigger, and people have been putting more data on them, but they have not really gotten faster. And with more data on a disk, throughput has become a concern. Typically, the best way to speed up disk I/O is to spread your data across more disk drives, putting less data on each disk. SSDs are a game changer, and I think disk drives will go the way of tape drives in the future. Everyone will run on SSDs and disk drives will be used for backup media. SSDs are already showing up more often in laptops, desktops, and server arrays.

One interesting development is hybrid disk systems, which combine SSDs with traditional hard disk drives. Several vendors make arrays with SSDs as intelligent caches for a bunch of traditional disk drives. The idea is that all data pages will be on traditional disk drives and the most-used data pages will be cached on the SSDs. One of my customers is using one of these arrays with great success and experiencing fast performance. The other hybrid trend is pairing an SSD directly with a large disk drive as a disk cache. Seagate and OCZ make disk drives or disk cards that are hybrids. While hybrids do improve performance, they are not as fast as pure SSDs. I think they are just temporary measures until the cost of SSDs comes down and the price-to-performance ratio is improved. As production of SSDs increases and costs decrease, hybrids will not be needed.

Setting up the benchmark

For this article, I decided to start over again and do a benchmark to measure the performance improvement that SSDs can deliver. I wanted to stress and measure the performance of sequential reads, bulk sequential writes, and random reads and writes. Using the latest Informix 11.70 FC4 release, I started with a clean install and scripts to create my Informix environment from scratch. The same ONCONFIG file was used for each test, and I switched between the traditional disk drives and SSDs by changing the links of the Informix disk space. The traditional disk drives were a fast mirrored pair as found in most production systems today. The results are shown in the table in Figure 1.

The following is a description of each Benchmark Test task.

1. Configure Dbspaces and Logs
   The first test measures the time to create all the dbspaces and all the logical logs, and move the physical log to a separate dbspace to re-create my benchmark environment. Informix was set up to do direct I/O and use cooked files in both tests. In the past, I simply copied dbspaces between the SSD and traditional disk drives; this test re-created the whole environment on both systems.
2. Import Database
   The next task was to perform a dbimport of each database used in each configuration. This task stress-tested the write performance of the drives.
3. Benchmark 2: Batch Billing Job
   This is a benchmark performed in 2009 at the IIUG Informix User Conference for the Fastest DBA contest—I have written about it here in the past. Since the goal of the test was disk I/O, I did not optimize the SQL to reduce the number of reads and writes.
4. Benchmark 4: OLTP with 1,000 Users
   This is the benchmark used at the 2011 IIUG Informix User Conference for the Fastest Informix DBA contest. Advanced DataTools sponsors the contest, which finds and honors the fastest Informix DBAs. This contest has been one of the more fun events at the last three Informix conferences.
   The test uses the Open Source BenchmarkSQL Java program to generate 1,000 sessions doing inserts, updates, and deletes against an Informix database. The Open Source BenchmarkSQL is a JDBC benchmark that closely resembles the TPC-C standard for OLTP. In the contest, contestants were challenged to take an Informix server running 1,000 OLTP users and optimize it in one hour to see who could get the most transactions per minute. Since this is a random I/O test doing reads and writes, it stress-tests the random I/O capabilities of the disk drive. The SSD did best in this test, performing 2,216 percent more transactions per minute then the traditional disk drives.
5. Data Warehouse Queries Benchmark
   This is a new benchmark I developed to test data warehouse performance. Since the SSD capacity was limited to 512 GB, I used a smaller database to run 18 complex data warehouse queries. Most of the queries require a sequential scan of a fact table to get results, so this stress-tested the read access of the drives.

Key considerations

In moving the Informix dbspaces to SSD, you also need to consider where else you may have disk I/O bottlenecks. When I first moved to SSDs in the data warehouse benchmark, I did not get the performance gain I expected. Two other factors that were affected by disk I/O performance were temporary space for sorts and output spaces for the query reports. Once I moved the temp space and the output space of my reports to the SSD, the performance gain was clear. In fact, moving your Informix temp dbspace to an SSD may be a fast way to take advantage of this new technology without reconfiguring your whole server.

I discussed SSD technology with other system administrators who manage systems for large imaging processing systems (like moviemaking). These systems need near-real-time access to large image files. These administrators had been successful building RAID-10 arrays of six to eight SSDs. They report that RAID-10 mirroring and striping of data adds safety and improves the already fast performance of SSDs. I would really like to try this with an Informix database.

One concern with SSDs is their reliability as new technology. I have had only one SSD that has lasted me all year without a problem. In the same time period, I have had three regular hard disk drives fail. All media will fail at some time, so the best plan is a backup solution. Regular backups are required for all disk drives. If I were designing a new production Informix database server using SSDs, I would create an Informix High Availability Remote Secondary Server (RSS) server on regular disk drives as a fallback system. But this is a best practice for any production database server.

Conclusion

SSDs were faster for Informix database tasks in all the tests. In sequential scans, random I/O, and reads and writes, the SSDs all came out ahead. SSDs greatly improved the performance of the OLTP random disk I/O benchmark. The numbers in Figure 1 speak for themselves. But suffice it to say, an easy way to speed up your Informix database server is to move your dbspaces to SSDs. Informix runs great on SSDs.