logo-micron

Add Bookmark(s)


To:

Email


Bookmark(s) shared successfully!

Please provide at least one email address.

Open-Source OLTP Never Looked So Good

NVMe™ SSDs Deliver Amazing Results With MySQL® and OLTP
 

Overview

Open-source relational database management systems (RDBMS) like MySQL® are broadly deployed1 and very flexible with extensive community support. MySQL powers some of the largest high-growth websites while remaining free.2

MySQL’s licensing model shifts the way we think about storage. Compared to traditional closed-source RDBMSs with heavy licensing costs, MySQL can give us a different focus. In this brief, we discuss the configuration needed to achieve MySQL system responsiveness (average time for the database to respond to an inquiry), response consistency and power efficiency (database operations per watt of system-level power consumed). The goal is to generate more performance3 and more value for MySQL.

We also explore supporting more users and throughput when the system is heavily loaded4, which is an important consideration for I/O-intensive applications using scripting languages like PHP with a database backend — a common use for MySQL.

In our testing, we used standardized OLTP performance metrics and a data set that exceeds available system memory (to test storage system I/O) to compare performance, average response time, response time consistency and power efficiency for three storage configurations used with MySQL Community Edition 5.7:
  • NVMe 1: 2x 3.8TB SSDs, RAID 1 (LVM)
  • NVMe 2: 4x 3.8TB SSDs, RAID 10 (LVM)
  • Legacy: 8x 300GB 15K RPM HDDs, hardware RAID 10 (baseline configuration for comparison)
We found that NVM Express® (NVMe) SSD configurations demonstrated far higher performance, with quicker response times and more consistency, bringing more value to OLTP workloads on MySQL compared to our HDD configuration.
  1. Deployment data from https://www.mysql.com/why-mysql/
  2. MySQL GPL licensing: https://www.mysql.com/about/legal/
  3. Throughout this paper “performance” means new orders per minute (NOPM)
  4. Heavily loaded is defined as maximum user count without reaching one or more stop conditions (see the How We Tested section)

Fast Facts

  • More orders, more fulfillments, more value to your bottom line with NVMe
  • Data can’t wait — NVMe enables amazing results for MySQL
  • Release your data’s potential like never before:
    • 2x NVMe SSDs: >103,000 new orders per minute
    • 8x 15K HDDs: ~1,800 new orders per minute
  • Applications are highly responsive and more consistent with NVMe
9200_PCIe_U.2_isometric_right

NVMe Provides Much Higher Performance

To test platform performance, we measured new orders per minute (NOPM) for all three configurations across all supported user counts and averaged the results. (See the How We Tested section for details on the range of user counts for each configuration.) This gives a good view of each configuration’s performance.

Figure 1: Average NOPM

As shown in Figure 1, both NVMe configurations showed very high performance compared to the legacy HDD configuration. The 2x NVMe configuration supported slightly higher performance (just over 100,000 NOPM) than the 4x NVMe configuration (99,000 NOPM). This was somewhat unexpected and may be due to MySQL performance not scaling well for very high-performance storage. As a result, a 4x NVMe configuration is a good option for high-performance, high-capacity platforms.
Tip: The 4x NVMe configuration is a great choice for high-performance, high-capacity MySQL platforms. 4x NVMe doubles storage capacity with excellent performance.

NVMe Responds Faster, More Consistently

For many OLTP deployments, performance is the design driver, translating to more revenue and greater benefit. However, this is not always the case.

For some, database responsiveness (mean response time) drives configuration choice. For still others, database response time consistency (90% transaction response time is a good measure of latency consistency) may be a primary configuration driver (this may be common in high performance OLTP systems that can’t tolerate an occasional outlier during which the database response takes much longer than its average).

As shown in Figures 2a and 2b, we compared mean and 90% transaction response time for all three configurations. We found that the NVMe configurations responded very quickly and with excellent consistency.

Figure 2a: Mean response time

Figure 2b: 90th percentile response time

Figures 2a and 2b show that the NVMe configurations have dramatically lower mean response times and are more consistent than the legacy reference configuration (this result is not surprising). The legacy configuration shows high mean transaction response time, indicating its overall response times are high. It also shows a high 90% latency response time, indicating poor response time consistency.

Table 1: Mean Response Times


Table 2: 90th Percentile Response Times

Table 2: 90th Percentile Response Times

 


Tip: For high-capacity deployments that need lower, more consistent responses, consider a 4x NVMe configuration.
While both NVMe configurations showed low and consistent response times, with double-digit improvements over the basline configuration, the 4x NVMe configuration showed lower average response time with more consistency than the 2x configuration.

NVMe Is More Power-Efficient

In densely packed, high-I/O deployments, power efficiency may be more important than raw performance or than faster, more consistent response time. To gauge power efficiency, we divided each configuration’s performance (in NOPM) by the system power draw at that load to calculate performance per watt, as shown in Figure 3. Note that we used the system power draw (in watts) at the NOPM values shown in Figure 1 for this calculation.

Figure 3: NOPM per watt

The 2x NVMe configuration showed the highest NOPM per watt, indicating greater power efficiency compared to the 4x NVMe configuration. This is because the 4x NVMe configuration has slightly lower NOPM and slightly higher power draw compared to the 2x NVMe configuration.

The legacy configuration NOPM is very low while its power draw is close to both NVMe configurations. This drives its calculated power efficiency much lower than both NVMe configurations.

Tip:
The 2x NVMe supports slightly higher performance than the 4x NVMe configuration and draws slightly less power.

The Bottom Line

SSDs with NVMe bring amazing results to MySQL and OLTP. We used a standardized OLTP performance metric and a data set that exceeded available system memory to compare two NVMe configurations to one HDD-based legacy baseline configuration.

We’ve shown how SSDs with NVMe can be a great fit for MySQL. They show very high performance with fast and consistent responses and excellent power efficiency per operation. A legacy HDD configuration simply could not keep up, even with many more drives.

Micron offers a broad portfolio of SSDs with NVMe. You can estimate the benefits of an all-flash data center using our Move2SSD TCO Tool.

 

How We Tested

To ensure a fair assessment of each configuration’s capabilities, we took a configuration-specific approach. We wanted to measure each configuration’s performance over a broad range of user counts, as opposed to an arbitrary fixed number of users.


We started testing with 32 users and increased the user count until the platform was saturated. Prior to testing, we established stop conditions, as shown in Table 1. During testing, we increased the user count until reaching a stop condition.

Table 1: Stop conditions5,6

We set the 90th percentile transaction response time to the values in Table 2, which reflect common tolerance limits. When we reached a stop condition, we described the user count as the platform “saturation point.”

Table 2: Threshold limits

  1. We set the stop condition for CPU utilization at 80%. Many IT organizations plan for platform upgrade when CPU utilization reaches 50% and implement that plan when it reaches 80%.
  2. We sized the data set to ensure it was large enough to exercise storage I/O (data set size about 2X the memory size) but did not occupy more than 80% storage capacity.

 

Determining User Count by Configuration

This section shows the test condition(s) that established the maximum user count for each configuration.

Legacy Configuration Stop Condition: Delivery Latency
Figure 4 shows the legacy configuration’s average latency for delivery and the 90% delivery latency 5-second threshold (dashed line). When the user count exceeds 32, the 90% latency exceeded 5 seconds (the preset stop condition). As a result, we used 32 users for all legacy configuration data. Note, Figure 4 shows results beyond 32 users to provide additional detail. This data is shaded in gray to indicate that these values were not used in our comparisons).

2x NVMe Configuration Stop Condition: NOPM Plateau
Figure 5 shows the 2x NVMe configuration performance by user count. Performance plateaus at 96 users. As a result, we used 96 users for all 2x NVMe configuration data. Note, Figure 5 shows results beyond 96 users to provide additional detail. This data is shaded in gray to indicate that these values were not used in our comparisons.

Figure 5: 2x NVMe stop condition

4x NVMe Configuration Stop Condition: NOPM Plateau
Figure 6 shows the 4x NVMe configuration performance by user count. Performance plateaus at 64 users. As a result, we used 64 users for all 4x NVMe configuration data. Note, Figure 6 shows results beyond 64 users to provide additional detail. This data is shaded in gray to indicate that these values were not used in our comparisons.

Figure 6: 4x NVMe stop condition

Table 3 shows the hardware platform configuration used for all testing. We used MySQL Community Edition 5.7. on CentOS 7.4 for all tests.

Table 3: Hardware configuration

To download a pdf version of this Technical Brief, click here.

Products

7100 PCIe SSD

9200 SSD with NVMe™

Build a foundation of agility and efficiency for your data center with Micron 9200 SSDs with NVMe — the largest-capacity high-performance Micron enterprise NVMe SSDs to-date.

View 9200 Products

Resources

Blog

OpenStack Summit 2018 is quickly closing in, and Micron has a new reference architecture to support OpenStack™ cloud storage and content distribution.

Brief/Flyer

Micron has combined its storage engineering expertise along with Excelero's world-class SDS solution to build a bulletproof, scalable storage solution for your data center needs.

Blog

In light of NVMe, why is Micron releasing new SATA drives? Because the SATA SSD segment still has a lot of life and gives us the unique opportunity to differentiate and deliver a winning, best-in-class drive.

Blog

It is somewhat difficult to relate how changes in memory technology might impact you in everyday life. In this blog post I want to take the opportunity to shed some light on how the most recent advancements in NAND technology from Micron help satisfy the human element of...

Sign up for updates

Get Updates From Micron Storage

Subscribe