Overview
As time passed data usage increased significantly, parallel the requirement for managing it also grew. The immense growth of databases become an integral part of every business, regardless of its size and structure. So, to address those database businesses, use MySQL, the most popular open-source RDBMS (Relational Database Management System) to store, retrieve, modify and administer a database using SQL (Structured Query Language). Now, when thereโs data you also need to take care of its security, backup, replicas, etc.
To make it simple and easy to use we have Cloud SQL by Google Cloud Platform a one-stop, hassle-free solution. Cloud SQL is a fully-managed relational database service where Google Cloud takes care of the administrative tasks required to run a database. In this article, we will deep dive into Cloud SQL and learn how to use it efficiently and optimize its costs to be within budget.
In Cloud SQL, you can manage each type, from in-memory to relational, to non-relational, as well as what each service is good for. It has Cloud-native database services where you can use MySQL or PostgreSQL for ease of migration and compatibility, but it also does support traditional databases such as SQL Server.
It lets you hand off boring, time-consuming tasks, like patches, updates, backups, and replicas, so you can focus on building your app. It is a full suite of database services available to meet your different requirements. It integrates with BigQuery for analytics or connects as a backend to your App Engine, Compute Engine, or Kubernetes Engine. And finally, it also works with some popular integrations, such as MongoDB, Confluent, and many more, to provide you with their integrated support.
Use Cases and Examples of Cloud SQL
Now let’s explore some of the use cases involving Cloud SQL. As you can see over here, Cloud SQL works across many use cases, from gaming to inventory management, and many more.
Source: GCP
Next, let’s zoom into one example, a sample fictitious gaming backend database architecture. Here, we can see in the picture all of the different databases are in action.
First, we have Cloud Firestore, a NoSQL document database, which can be used for authentication as well as the player database. Cloud Memorystore is then used for storing regional, in-memory caches, which speeds up access to frequently viewed data. Cloud SQL then helps to do the hosting of the game data. You can also log events to Cloud Bigtable, Google Cloud’s NoSQL columnar database. And finally, you can also connect to BigQuery for data warehousing and gameplay analytics.
Source: GCP
How to use Cloud SQL
Setting up Cloud SQL is quick and seamless. In a snap, you can create in an instant with the Cloud console by choosing the type of database version and location or using the gcloud CLI (command line). Then you’re ready to import your data and connect your applications. Cloud SQL also has a built-in high availability option, which helps you to reduce downtime when a zone or instance becomes unavailable. With high availability, your data will still continue to be available to client applications.
Cloud SQL instance overview
The database is stored on a scalable, durable network storage device called a persistent disk that attaches to the VM. A static IP address is always available in front of each VM to ensure that the IP address of an application connects to persists.
Cloud SQL Read Replica
To go further, with Cloud SQL, you can easily create read replicas and backups, and there is also automatic failover, ensuring that your database will always be available when you need it. You will get full encryption at rest and in transit and keeps your private data safe and compliant. You can use Cloud SQL to manage everything right from a single WordPress blog to a global aggregation of wheat crops and rainfall.
How Cloud SQL is Billed – Pricing
Google Cloud Billing consists of services you are using every service comes up with different pricing and it varies with your configurations, settings, and region and also depends upon:
- Storage provisioned for Cloud SQL, in GiB per month
- CPUs selected for Cloud SQL instance
- Memory selected for Cloud SQL instance
- The location where data is being hosted
- Amount of network traffic leaves your instance
- Number of IP addresses assigned and used
These are some of the general ways to get charged but it varies by instance type, itโs similar for MySQL and PostgreSQL but it’s different if youโre using SQL Server.
MySQL & PostgreSQL Server Pricing
Here the Cloud SQL pricing consists of the following charges such as:
- CPU & Memory Pricing
Pricing for CPUs and memory depends on the region where your instance is located & the year of commitment.
- Storage & Networking Pricing
Pricing for Storage and networking depends on the region where the instance is located.
- Instance Pricing
Instance pricing also depends on the region where your instance is located and it is charged for every second that the instance is running. This means that each second of usage counts toward a chargeable minute.
Cloud SQL Server Pricing
Cloud SQL for SQL Server consists of the following charges such as:
- CPU & Memory Pricing
- Storage & Networking Pricing
- Licensing
Here two are the same as MySQL & PostgreSQL Pricing but for the third one instead of Instance Pricing, we have Licensing. Where SQL Server instances are charged a 10-minute minimum for licenses. After 10 minutes, SQL Server licenses are charged in every 1-minute increments.
MySQL, Cloud SQL High Availability Pricing Examples
| Use case | Configuration details | Monthly cost |
|---|---|---|
| Test instance | – 1 CPU – 614 MB memory – 10 GB storage – No backup storage – No commitment term – Not highly available - us-central1 region | $9.37 |
| Highly available production database | – 4 CPUs – 24 GB memory – 60 GB storage – 80 GB backup storage – 3-year commitment term – Highly available – us-central1 region | $273.55 |
| Higher performance, highly available production database | – 32 CPUs – 208 GB memory – 10,230 GB storage – 1,000 GB backup storage – 3-year commitment term – Highly available – us-central1 region | $5,532.26 |
Here, you can see MySQL Pricing Examples with different Configurations. Same as this you can create & configure your own Cloud SQL instance according to your requirements. You can also use the Google Cloud Pricing Calculator to get your pricing estimates, even before creating your instance in real.
Now that you know how to configure your Cloud SQL database and estimate your pricing, let’s see some of the main sources of waste in Cloud Databases & best practices for its optimization. Here are some of the pro tips to optimize, you can follow:
Cloud SQL Cost Optimization Methods
Idle Instances
Cloud SQL makes it very simple for developers to create new instances to build prototypes or run some test environments. As a result, idle resources are known to be one of the main reasons to waste in cloud spending, ranging from entire projects to individual Cloud SQL instances that tend to be forgotten about.
Here, the Cloud SQL idle instance recommender also known as Active Assist helps to detect instances that might be idle. Active Assist then provides you with recommendations and insights on how you can reduce costs. So, by using Active Assist you can simply stop idle instances.
Scheduling your development server to start every morning when your work starts and stop each evening when you’re done with your development work. Configuring your instances to start and stop each workday using Cloud Functions, Cloud Pub/Sub, and Cloud Scheduler can save you up to 75% of the cost to run an instance per week versus having it continuously run.
Overprovisioned Instances
When developers are comfortable and provision unnecessarily large instances, it can lead to unnecessary spending. Itโs also common for database administrators who are used to provisioning larger instances, where it can be non-trivial to quickly increase instance size, to carry this practice over into the cloud environment, where it’s not as critical due to its elasticity.
The Cloud SQL over-provisioned instance recommender, Active Assist helps to detect instances that are unnecessarily large for a given workload. It then provides you with some recommendations on how you can resize these instances. It uses machine learning and Googleโs Cloud SQL telemetry data to identify instances that have low peak utilization for CPU and memory, to ensure that they can be rightsized with minimal risk and have enough capacity to still handle their peak workloads after they are right-sized.
Incur Network Egress
Sometimes you may experience strange charges for network egress from Google Cloud Functions, where the function and the cloud SQL instance may both be in the same region and connects to the SQL instance via the provided Unix socket. Also, the SQL instance does have a public IP address.
To fix this add a private IP address for the SQL instance, a Serverless VPC Connector, and change the database connection code to use the private IP address explicitly.
Note if the instance has both public and private IP addresses, then connecting via the Unix socket /cloudsql/<instance_connection_name> will still incur egress charges. And if the instance only has a private IP address, the Unix socket method uses the private network and does not incur charges. Therefore to retain a public IP and not incur egress charges from cloud functions, you’ll need to use the private IP instead of the Unix socket.
Long-time Commitments
Lastly, do leverage commited use discounts, which are ideal for predictable workloads on a one-year or three-year basis, which can give you significant cost savings. Here, you can save 25% of on-demand pricing for a one-year commitment and 52% for a three-year commitment.
Conclusion
Using Cloud SQL without proper planning can be very expensive. These tips and practices can help you cut down the costs of Cloud SQL. The best long-term strategy is to establish a finops practise within your company. We are Economize. We are committed to the idea of making your cloud spend simpler and noise-free to help engineering teams like yours to understand and optimize it. Get started today with a personalised demo for your organization.
