Storage
Mobility
Protection
Analysis and control
TOPICShare
MySQL is the most widely adopted open source relational database. It is the primary relational data store for many popular websites, applications, and commercial products. With more than 20 years of community-backed development and support, MySQL is a reliable, stable, and secure SQL-based database management system. The MySQL database is suitable for a wide variety of use cases, including mission critical apps, dynamic websites, and as an embedded database for software, hardware, and appliances.
AWS supports MySQL in a variety of ways, including hosting MySQL on Amazon EC2 and self-manage the database. MySQL workloads are mission-critical for most enterprises and feature prominently in discussions over an enterprise cloud migration. This blog provides a brief overview of MySQL databases, and gives a reference architecture for deploying MySQL on Cloud Volumes Service (CVS) and AWS. It also explains the benefits of running MySQL databases on CVS and AWS.
MySQL Storage Solution for Cloud
With Cloud Volumes Service for AWS, you can run a high-performance database with maximum data protection. Underlying that security is NetApp® SnapshotTM technology, which offers a crucial option for rapid, efficient database backup and restoration. By design, CVS for AWS provides nine 9s of data durability.
With consistently high performance of over 460k IOPS, Cloud Volumes Service provides shared persistent storage with high throughput and low latency. It easily meets the demands of large MySQL databases, with SLAs that guarantee performance.
Increase the Resilience of MySQL Databases with Snapshots
You can easily create a snapshot copy of a MySQL database using NetApp Snapshot™ technology. Snapshot copies act as logical backups. They’re point-in-time representations of your data, with a rapid revert function that allows you to restore your database. You create snapshot copies manually or schedule the creation of snapshot copies using the Cloud Volumes Service API or graphical user interface (GUI).
Snapshot copies are fast, plentiful, and nondisruptive. A NetApp snapshot copy simply manipulates block pointers, creating a “frozen” read-only view of a volume that enables your applications to access older versions of files and directory hierarchies without special programming. Snapshot copy creation takes only a few seconds (typically less than 1 second) regardless of the size of the volume or the level of activity within the environment. Since they are read-only, incremental copies, you only pay for the space consumed by new data written.
Speed Up Time to Market With Instant Copy
Most organizations need multiple copies of data for testing and development. MySQL landscapes are littered with system copies for variety of uses; creating and refreshing those copies are cumbersome. Typically, creating copies of MySQL landscapes is a time-consuming and tedious process. Cloud Volumes Service for AWS allows you to instantly copy and backup database files, drastically improving the process of copying, backing up, and reverting. The process takes almost no time, which ultimately leads to lower costs by way of a quicker time to market.
Supported Protocols
Cloud Volumes Service for AWS delivers fully managed file services for NFS, SMB, and dual-protocol use cases. Cloud Volumes Service for AWS supports NFS v4.1 and the MySQL official documentation supports the use of NFSv4 or later. Please refer the official MySQL documentation.
Average Cost Savings of Around 70%
When you use CVS for AWS, you control your cloud performance by dynamically adjusting service levels. If you need to increase performance, you can increase the allocation (for example, 10TB provides 160MB/s) and/or choose a higher service level.
- The Standard service level offers very economical cloud storage, at just $0.10 per gigabyte per month. It enables throughput up to 16MB/s for each terabyte allocated. This level is ideal as a low-cost solution for infrequently accessed data.
- The Premium service level delivers a good mix of cost and performance. At a cost of $0.20 per gigabyte per month, it offers 4x the performance of the Standard level, with 64MB/s for each terabyte allocated. This is a good fit for many applications where data capacity and performance needs are balanced.
- The Extreme service level provides the best performance. At a cost of $0.30 per gigbytes per month, it enables up to 128MB/s for each terabyte allocated, and cloud volumes can scale to deliver several GB/s for reads and writes. Extreme is the best fit for high-performance workloads.
One of the unique features of NetApp Cloud Volumes Service for AWS is the capability to change performance on-the-fly. If the requirement is to have the Extreme performance tier for two hours a day and Standard performance for the rest, Cloud Volumes Service for AWS can use API calls or a scheduler in Linux to facilitate that process.
Detailed Architecture Design
Self-managed MySQL instance using CVS storage
In the architecture diagram, you can see that the MySQL database database volume and log volumes are configured on Cloud Volumes Service for AWS. With the combination of backups, snapshot copies, and right-sized throughput, you can easily host your high-performance database in the cloud with maximum data protection and nine 9s of data durability.
In the diagram above, you can see:
- The MySQL database is configured on an Amazon EC2 instance.
- The database volume is provisioned using the Extreme service class because that class provides the highest throughput at a manageable cost.
- An additional volume is dedicated to logs. A second volume is provisioned using the standard service class.
The key components of the solution include:
- MySQL database engine
- Amazon EC2 instances
- NetApp Cloud Volumes Service for AWS (storage)
- NetApp Snapshot Technology
- NetApp Cloud Backup Service
Read the full architecture paper.
My SQL Database Performance on Cloud Volumes Service
Proven performance
The graph below illustrates the performance of a MySQL database on Cloud Volumes Service. We ran the benchmark with various workload mixtures and volume counts. The results were stunning.
Environment
- Instance type: C5.9xlarge
- MySQL Version: 10.3.2
- Linux Version: Redhat Enterprise Linux 7.6
- Workload Distribution to storage: 70/30 read/write with 4KiB operation database page size*
- Volume Count: database volume (8TiB Extreme), 1 log volume (1TiB Standard)
- Allocated Storage Bandwidth: database volume 1024MiB/s, log volume 16MiB/s
- Database Size: 1.25TiB
- A c5.9xlarge instance can drive up to 40,000 disk IOPS, and the c5.18xlarge supports up to 80,000 disk IOPS.
MySQL Workload – Latency Relative to Throughput
The 450MiB/s throughput observed in this benchmark test of MySQL on Cloud Volumes Service for AWS is sidling up the 5Gbps per network flow limit imposed by AWS.
The metrics in the following graph–450MiB/s maximum throughput–are taken from nfsiostat on the database server and, as such, represent the perspective of the NFS client.
The Economic Benefits of Cloud Volumes Service
Cloud Volumes Service is the lowest cost, highest quality solution to database hosting in the cloud. You can save a significant amount of time and money by changing performance levels on demand in CVS for AWS; other cloud storage solutions recommend that you configure performance and capacity to meet peak requirements, which means peak prices. Database performance requirements are rarely consistent—they require a system that’s adaptable, agile. But other cloud solutions offer up monolithic structures without swift-footed switching between performance levels, recommending static throughput of the highest level for databases. You can use the NetApp API to change performance on the fly, or interface the service with a scheduler such as cron in Linux. That saves a lot of money.
For example, let’s say that you’re using Cloud Volumes Service and configured a volume at the Standard service level ($0.10/GB), which is suitable for a MySQL database when you’re looking for capacity over performance. If you realize that you need more performance, you can update the volume with an API call or scheduler and the change happens in seconds—it’s nondisruptive to clients. It’s just as easy to revert to the lower performance tier. So instead of continually paying for peak performance, you only incur added costs for the time you used the higher performance tier.
Think about it like this: If you have a performance intensive workload at certain times (such as peak times), you may need a volume to perform at the Extreme level for 30TB at $.030/GB, but only during those peak periods. If you were to run at this level all the time it would cost $9000/month. But with Cloud Volumes Service for AWS, when the intensive task finishes, you can quickly drop down to the Standard performance level for 160MB/s (16MB/s x 10TB) and meet the I/O needs for off-peak loads at a significantly lower cost. This performance level costs $1,000 per month (10TB at $0.10/GB). The cost savings vary, but if you run the processing intensive workload for 20% of the time and adjust the Service level, you can usually save about $6,400 each month.
Note that the formula we used to calculate savings is: $9000 – (($9000*0.2) +($1000*0.8)) = $6,400, which equals savings of more than 70%.
Want to Learn More About MySQL on CVS for AWS?
Read the full reference architecture or signup for a personalized demo.