All the distributers of MySQL (MariaDB, Oracle and Pecona), have announced support for flash-aware interfaces, including NVM (non volatile memory) compression and Atomic writes. These Server SAN functions give three important benefits to MySQL applications:
- Increased operation throughput performance - improves performance by about 3 times when applied to systems running Linux compression and Linux DoubleWrites.
- Improved flash longevity - 2-4 times improvement in the endurance of flash cards, using half the amount of flash storage
- Reduced operation latency and latency variance - 2-4 times latency improvement
Assume that the starting point is a MySQL application using compression to half the size of a flash storage database and using Ext4 DoubleWrites. Assume performance is an issue. Previously the DBA would have two quick ways of avoiding poor MySQL performance: turn off compression and turn off the Ext4 DoubleWrites.
- Turning off compression improves throughput by about 64%, but:
- Doubles the storage space required;
- Doubles the bandwidth required to read/write data to storage.
- Turning off DoubleWrites improves throughput by about 70%, but:
- With only a single write, the ACID properties of MySQL are compromised;
- Atomicity and data integrity is sacrificed.
Enter the ability to make applications flash-aware with an open source component called NVM Extensions for MySQL. With NVM extensions are two elements, Linux Atomic writes and NVM compression for MySQL. NVM compression was announced April 2014 at the Percona LIVE conference. All three open source MySQL distributions (MariaDB MySQL, Oracle MySQL and Percona MySQL) fully support NVM extensions.
These extensions reduce the total path-length and the distance between the data, the application and the end-user. Non-volatile (flash) memory (NVM) is now used in all performance critical databases, including those of the leading users of MySQL such as Facebook and Apple. The keys to improving MySQL performance are:
- Bringing the data as close as possible to the processor (e.g., using the PCIe bus with PCIe attached flash modules, or battery backed DIMM DRAM memory);
- Removing legacy code where possible (e.g., avoiding traditional SCSI commands between the processor and disk drives by using PCIe flash cards);
- Removing the storage network (SAN) overheads (e.g., communicating directly to NVM storage on the processor bus);
- Introducing new ways of achieving flash-aware applications, (e.g., Atomic writes and NVM compression).
Figure 1 below shows the impact of NVM extension components.
- The base line is an MySQL system running normal protected SQL (DoubleWrites) and compression. The base line is set to 100 operations per unit of time.
- The NVM extensions to MySQL will produce about 10% additional throughput for any flash device. The is represented by the second column of 110 operations per unit of time.
- The third column represents the benefit of Atomic writes. Figure 4 in the Footnotes shows a TPC-C benchmark running XtraDB, an enhanced version of the InnoDB storage engine. The performance of the top line (using Atomic writes) is about 70% faster than the performance of the bottom line (using DoubleWrites, giving the same level of protection as the Atomic writes). The performance of the Atomic writes is shown in the third bar as 170 operations per unit of time.
- The right-hand column in Figure 1 shows the additional benefits of adding NVM compression. Figure 3 in the Footnotes shows the performance of the same card running NVM compression is the same as the card running no compression (36,000 operations per second). When compression is run without NVM compression, the performance is 22,000 operations per second. The NVM compression is 64% faster than the base. The performance of Atomic writes and NVM compression is 1.7 × 36 ÷ 22 = 2.8.
Improved Flash Longevity
An additional benefit from using the NVM extensions is improved flash endurance, a major cost factor for flash storage. The Atomic writes half the number of writes to the flash, and therefore double the longevity of the flash storage. If the base was a non-compressed MySQL workload, the benefit would be a 1.7 performance improvement (no overhead for using compression), the amount of flash storage would be halved, and the longevity of the flash storage would be 4 x longer (half the number of write with Atomic writes, and half the number of compressed writes).
Improved Operation Latency
Wikibon has consistently pointed out the importance of latency and latency variance in database performance. Traditional benchmarks of all types are run to optimize the number of transactions. Every vendor runs TPC-C benchmarks with the maximum of threads to maximize throughput, and the benchmarks are extremely artificial, very unstable and very difficult to perform. In the real world consistent response time and consistent performance in very varied environments are the norm. To provide consistency, measuring and lowering average operation & IO latency and latency variance become essential to understanding and improving performance for the consumer of the MySQL applications.
At the simplest level, lower operation latency results in better response times, and better end-user experience. Lower latency reduced IO wait times in the processor, which allows the processor to do more work and increase throughput. However, real world MySQL applications are not simple, and operations and the IOs that support them are not independent. Occasional very long IO times in particular have a dramatic impact the stability of performance. One very slow IO within one operation can have a ripple effect across the system as a whole. Vadim Tkachenko leads Percona's development group, points out that response time is way more important metric than throughput.
The Y (vertical) axis in Figure 2 uses the metric 99% operation latency, the latency that includes 99% of the operations in a probe every 10 seconds. The x (horizontal) axis shows the latency every 10 seconds. The latency of a system using DoubleWrites has a high average latency, and a very variable distribution. The highest latency per operation in an hour is 185 milliseconds, and there are many over 100 milliseconds. Using the Atomic write NVM interface, the average latency is 2-4 times lower (better), and the variance much, much lower. Apart from one set-up reading, all the operations using Atomic write are completed in less than 15 milliseconds.
Figure 5 in the Footnotes shows the latency of specific operations using either no compression, NVM compression, or traditional Linux compression. It shows that the latency of NVM compression and no compression are very similar, and both are 2-4 times better than traditional compression. The conclusion is that using NVM compression will have neither a performance impact or a latency impact on systems, and should be used to reduce the amount of flash used by 50%, as well as increasing the longevity of the flash. No-brainer!
Flash has been delivered in three main forms:
- SSDs – the flash media is delivered within a traditional disk environment, so that traditional hard disk (HDD) interfaces and software can be used. This allows easy adoption and lower initial cost but has much higher performance overhead. Because it uses traditional SCSI commands and moves storage physically further from the processors, it does not take full advantage of the performance improvement of NVM extensions.
- DIMM attached Flash cards are attached as SATA storage drives, and use the normal SCSI protocols. They do not support the NVM extensions. In 2015 there may be better support for this type of approach when next generation cards with different protocols are slated for production.
- PCIe attached Flash – the flash media is attached on the PCIe bus, and is the only NAND flash device that supports the NVM extensions. All PCIe cards will receive the 10% additional performance as shown in the second bar of Figure 1. To achieve the full optimized benefits of Figure 1, the PCIe cards need to provide full support for the NVM extensions (e.g., Fusion-io's ioDrive cards).
The NVM benefits of increased performance, decreased cost of flash storage and increased longevity of flash storage can only be achieved when the NVM flash storage is very close to the processors. This is an example of high performance Server SAN architecture, and why over the next decade Server SAN will replace traditional SAN/NAS architectures. This architecture is easier to support for DBAs and system administrators. Server SAN architecture can produce very large improvements in performance, and enables modern applications to be written to process 10 to 100 times more data. This capability will enable much greater consumer and enterprise productivity and business value.
The overall conclusion is that MySQL systems will either be 3 x faster with twice the flash storage longevity, or 1.7 x faster with half the flash storage cost and 4 x the flash storage longevity. Either decision is an absolute no-brainer.
Action Item: The business case for using PCIe cards and the MySQL NVM interfaces is a "no-brainer" that reducing overall hardware cost, reducing DBA time and improving performance of MySQL databases. In principle, databases should be supported by flash storage on the bus as close as possible to the server in a Server SAN performance configuration, rather than using traditional SSD SCSI protocols.Footnotes: