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)
- 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 PerformanceTo 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.
NVMe Responds Faster, More ConsistentlyFor 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
Table 1: Mean Response Times
Table 2: 90th Percentile Response Times
Tip: For high-capacity deployments that need lower, more consistent responses, consider a 4x NVMe configuration.
NVMe Is More Power-EfficientIn 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 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 LineSSDs 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 TestedTo 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
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 ConfigurationThis 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).
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
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: Hardware configuration