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.
Google Cloud supports MySQL in a variety of ways, including hosting MySQL on GCE instances to self-manage the database and CloudSQL fully managed offering. 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) for Google Cloud. It also explains the benefits of running MySQL databases on CVS and Google Cloud.
MySQL Storage Solution for Cloud
With Cloud Volumes Service for Google Cloud, 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 Google Cloud provides four 9s of data availability.
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 Snapshot Copies
You can easily create a snapshot copy of a MySQL database using NetApp SnapshotTM 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); rapid revert is only available from the API.
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.
Supported Protocols
Cloud Volumes Service for Google Cloud delivers fully managed file services for NFS, SMB, or dual-protocol use cases. Cloud Volumes Service for Google Cloud supports NFS v4.1 and the MySQL official documentation supports the use of NFSv4 or later. Please refer the official MySQL documentation.
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 Google Cloud allows you to quickly 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.
Average Cost Savings of About 70%
When you use Cloud Volumes Service for Google Cloud, 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 gibibyte per month. It enables throughput up to 16MB/s for each tebibyte 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 gibibyte per month, it offers 4x the performance of the Standard level, with 64MB/s for each tebibyte 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 gibibytes per month, it enables up to 128MB/s for each tebibyte 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 Google Cloud is the capability to change performance on-the-fly. If the requirement is to have the Extreme performance tier for 2 hours a day and Standard performance for the rest, Cloud Volumes Service for Google Cloud 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 volume and log volumes are configured on Cloud Volumes Service. With the combination of snapshot copies, and right-sized throughput, you can easily host your high-performance database in the cloud with maximum data protection and eight 9s of data durability (soon to be nine 9s).
In the diagram above, you can see:
- The MySQL database is configured on an GCE Compute 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.
- Cloud Volumes Service is a regional offering by design, and it can support instances and instance groups, spanning multiple zones in a region
The key components of the solution include:
- MySQL database engine
- Google compute instances
- NetApp Cloud Volumes Service for Google Cloud (storage)
- NetApp Snapshot Technology
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 remarkable.
Environment:
- Instance type: n1-highmem-32
- 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
MySQL Workload – Latency Relative to Throughput
The metrics in the graph, which maps out benchmark data for MySQL, are taken from nfsiostat on the database server, and, as such represent the perspective of the NFS client. We observed maximum throughput of 500MiB/s.
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 Cloud Volumes Service for Google Cloud; other cloud storage solutions recommend that you configure performance and capacity to meet peak requirements, which boils down to additional cost. 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 Google Cloud, 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%.
Learn more about cost savings with Cloud Volumes Service for Google Cloud today!