Databases: Have your in-memory performance, and recoverability too
Posted: 5 November 2012 | Steven Graves, CEO of McObject | No comments yet
Transaction logging provides an added durability benefit to in-memory database systems, but file I/O on a conventional disk can carry a performance penalty. Performance improves dramatically (15.33x) when state-of-the-art flash storage is used in place of the HDD, says Steven Graves.
Steven Graves, CEO of McObject
In financial computing, low latency and data durability (that is, freedom from the risk of data loss) can seem like opposing goals. For example, financial systems such as automated trading increasingly rely on in-memory database systems (IMDSs), which deliver breakthrough speed by working with records in main memory. Usually, volatility is the first objection raised to this approach. If someone pulls the plug, doesn’t the data evaporate into thin air?
To address this, IMDSs offer transaction logging, which keeps a record of changes to the database, enabling recovery in the event of system failure. This often prompts a second objection: that an IMDS with transaction logging isn’t really an IMDS. After all, the log is written to persistent storage (otherwise, why bother keeping it?) which entails file I/O – precisely the operation that IMDSs are supposed to eliminate in the name of blazing speed.
There is a small grain of truth to this argument. Transaction logging carries a performance cost. But that should not overshadow the vast increase in responsiveness made possible by moving to an IMDS with transaction logging (IMDS+TL) from a conventional on-disk DBMS.
My company recently tested the tradeoffs involved, pitting on-disk DBMS against IMDS+TL using a variety of storage devices. For the DBMS operations carrying the greatest potential to introduce latency, the IMDS+TL storing its transaction log on hard disk drive (HDD) outperformed the conventional DBMS using HDD storage several times over (even with the disk-based DBMS employing caching to minimize I/O).
Upgrading the storage device for the transaction log boosted the IMDS+TL’s speed advantage over traditional “on-disk” DBMS technology. Gains improved when a solid state drive (SSD) was used in place of the HDD. And the increase in application performance became truly dramatic when the test system was upgraded to use an ioDrive device from Fusion-io for transaction log storage. The ioDrive is installed as a PCIExpress board within the test server. It differs from SSD storage in that it presents flash to the host system as a new memory tier, integrating flash close to the host CPU and, as Fusion-io explains it, eliminating hardware and software “layers” that introduce latency by standing between CPU and SSD storage devices.
For the DBMS operations carrying the greatest potential to introduce latency, the IMDS+TL solution that stored its transaction log on hard disk drive (HDD) outperformed the conventional DBMS using HDD storage several times over (even with the disk-based DBMS employing caching to minimize I/O). The gains were even more dramatic when we swapped the hard disk for a flash memory device, i.e. a new-generation memory-tier storage device (we used the Fusion ioDrive from Fusion-io, a PCI Express-deployed flash memory device).
Let’s talk results
Let’s talk results first, then look at the reasons behind them. When inserting records into a database, moving from an on-disk DBMS to an IMDS with transaction logging (but still storing the transaction log on HDD) delivered a 3.2x performance gain. In other words, you can have your data durability, and triple your speed (and then some) inserting records into the database, even when using garden variety HDD storage.
It gets even better, though, using today’s faster storage options to hold the IMDS’s transaction log. For example, flash memory-based SSD technologies are becoming fairly common, and their performance advantage over HDD technology is well known. Storing the transaction log on SSDs boosted IMDS+TL insert performance 9.69x over the DBMS writing to hard disk.
Next, we swapped in the Fusion ioDrive to store the transaction log, and racked up a 15.33x performance gain over the DBMS+HDD combination. That’s right, inserting records into the database became 1,533 percent faster, while retaining transaction logging’s compelling data durability benefit (compelling, that is, to anyone who has lost critical data in a system failure).
Why is an in-memory database system with transaction logging so much faster than a disk-based DBMS? First, on-disk DBMSs cache large amounts of data in memory to avoid (postpone, really) performance-degrading disk writes. As it turns out, the algorithms required to manage this cache are a drain on speed. In-memory database architecture, in contrast, eliminates the entire caching sub-system. This streamlined design is faster.
Second, widely-used b-tree indexes vastly improve random lookups and retrieval of database content in sorted order, but they are also expensive for an on-disk DBMS to maintain during insert/update/delete operations, and become more expensive as the database grows. Third, the writes imposed by transaction logging are sequential, i.e. they append to the end of the log file, adjacent to the previous write. DBMSs write to a transaction log file sequentially, too, but when pages must be flushed from a DBMS cache, they are written to random locations on disk. Thus the disk head potentially travels farther between write locations, adding mechanical overhead during these flushing operations.
So far, I’ve only addressed on-disk DBMS vs. IMDS+TL performance inserting records into a database. Real-time financial systems such as trading and risk management will use the entire “portfolio” of database operations, including index searches, inserts, table traversals, updates and deletes. We tested all of these operations in the manner described above. As expected, inserts and deletes – the I/O-intensive categories of database updates – gained most from moving from DBMS to IMDS+TL, and from upgrading from hard disk to SSD or, better yet, to the Fusion ioDrive. These database operations often impose much of an application’s data management overhead, so the techniques described here are worth considering for time-critical financial systems.