Fusion-io Tweaks Flash To Speed Up MySQL Databases 

Server flash memory maker Fusion-io has tuned up its ioMemory PCI-Express flash cards with two new features that will significantly speed up the performance of MySQL databases. The new features, called Atomic Writes and NVM Compression, have been developed in conjunction with the MySQL community.

The atomic in the first new feature refers to the first of the ACID properties of transactional databases, atomicity, and it means that for a transaction to be completed and a database to be updated, all parts of the transaction have to complete. A transaction is therefore an indivisible unit, all or nothing, like the atom before quantum mechanics was discovered. (The other database properties are consistency, isolation, and durability.) In MySQL databases, atomicity is guaranteed through a double write buffer – meaning that all writes have to happen twice. As we all know, flash memory is blazingly fast on the reads, which is why companies are embedding it in their applications, replacing disk drives, to accelerate them. But it is less zippy on the writes, which creates something of an imbalance in the system.

With the Atomic Writes feature for ioMemory, Fusion-io is moving the write guarantee down into the Non-Volatile Memory File System layer of its flash devices, which means that the InnoDB engine at the heart of the MySQL database doesn't have to do double writes to its buffers any more. It does not mean that the flash device is doing double writes either, but rather that the sophisticated write mechanisms in the NVMFS layer will not let a database transaction fully commit until the data is written to the flash and known to be good. Up until now, flash memory was treated just like disk storage as far as the InnoDB engine was concerned, so it took two writes to complete a transaction. Now, it only takes one. Here is what Atomic Writes look like, conceptually:

Gary Orenstein, senior vice president of products at Fusion-io, tells EnterpriseTech that on new order transaction processing workloads, where there is a fairly balanced amount of reading and writing to the MySQL database, the Atomic Writes feature can enable a system to process somewhere between 50 and 70 percent more transactions compared to using the default InnoDB setup. This not only speeds up performance, but doing half the number of writes also extends the life of the flash memory on the ioMemory cards. Flash memory cells wear out over time with each write and get removed from the memory pool; eventually, a flash chip is useless. These days, thanks to spare capacity and very sophisticated wear-leveling algorithms, flash has a duty cycle that compared to disk drives, which also eventually wear out mechanically in the field. (Lest people forget this.) Finally, because the flash is not being written to twice as it if were a disk drive, the latency on writes is improved. On a benchmark test, the performance of writes was reduced by a factor of 2X for the 95th percentile of writes. This makes flash performance more consistent and predictable – something enterprises like as much as reliability, particularly for latency-sensitive workloads.

The Atomic Writes feature was developed in conjunction with Fusion-io and the ANSI T1O technical committee on SCSI storage interfaces.

Its companion feature, NVM Compression, was developed in conjunction with the MySQL community, which includes Oracle, MySQL (maker of MariaDB), Percona (maker of Percona Server), and large companies such as Facebook, which rely heavily on flash-enhanced MySQL. (As EnterpriseTechhas previously reported, Facebook has what is probably the largest MySQL cluster in the world.)

Data compression is not a new feature of the MySQL database, but rather has been tweaked to be flash-ware and specifically optimized for ioMemory flash cards. The NVM Compression feature hooks into the MySQL engine and compresses the data, and this has a number of different positive effects. First, datasets can be compressed by as much as a factor of 2X, according to Orenstein, and that cuts down on the amount of flash customers need for a particular database. Moreover, the compressed data also cuts down on write amplification, a side effect of wear-leveling and garbage collection on flash devices that causes more writes than you think.

The net effect is that the endurance of the ioMemory flash card can be extended by a factor of 4X through the combination of Atomic Writes and NVM Compression. Early testing by MariaDB shows performance improvements from NVM Compression compared to uncompressed MySQL databases.

The Atomic Writes feature has been woven into the MySQL 5.7.4 development release and NVM Compression is available as an early access feature for MySQL. Both features are also available for early access testing for MariaDB 10 and Percona 5.6. Orenstein says both features will be available for production use after a few months of testing.

Both new features have applicability beyond MySQL databases, of course. Orenstein did not want to talk about the possibilities of tweaking ioMemory to provide similar atomicity and compression features for Microsoft's SQL Server, Oracle's 12c, or IBM's DB2 databases. IBM and Oracle have their own flash storage and will no doubt look at what Fusion-io has done and perhaps mimic it for their own machines, and they may even work with Fusion-io to do the same. Microsoft doesn't have a system or flash business of its own, so it is no doubt interested in anything that might accelerate database performance. But then again, the new SQL Server 2014 database has its own columnar data store and in-memory processing, and how these technologies might be interleaved with flash memory is not clear.