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®
) 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.
- Deployment data from https://www.mysql.com/why-mysql/
- MySQL GPL licensing: https://www.mysql.com/about/legal/
- Throughout this paper “performance” means new orders per minute (NOPM)
- Heavily loaded is defined as maximum user count without reaching one or more stop conditions (see the How We Tested section)
- 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
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.
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
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.
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
- 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%.
- 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.
4x NVMe Configuration Stop Condition: NOPM Plateau
Figure 5: 2x NVMe stop condition
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.