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.
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.
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.
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.
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.
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.
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.
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 key components of the solution include:
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:
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.
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!