This document in the Google Cloud Architecture Framework provides recommendations to help you optimize the cost of your databases and analytics workloads in Google Cloud.
The guidance in this section is intended for architects, developers, and administrators responsible for provisioning and managing databases and analytics workloads in the cloud.
This section includes cost-optimization recommendations for the following products:
Cloud SQL
Cloud SQL is a fully managed relational database for MySQL, PostgreSQL, and SQL Server.
Monitor usage
Review the metrics on the monitoring dashboard, and validate that your deployment meets the requirements of your workload.
Optimize resources
The following are recommendations to help you optimize your Cloud SQL resources:
- Design a high availability and disaster recovery strategy that aligns with
your recovery time objective (RTO) and recovery point objective (RPO).
Depending on your workload, we recommend the following:
- For workloads that need a short RTO and RPO, use the high-availability configuration and replicas for regional failover.
- For workloads that can withstand a longer RTO and RPO, use automated and on-demand backups, which can take a little longer to restore after a failure.
- Provision the database with the minimum required storage capacity.
- To scale storage capacity automatically as your data grows, enable the automatic storage increase feature.
- Choose a storage type, solid-state drives (SSD) or hard disk drives (HDD), that's appropriate for your use case. SSD is the most efficient and cost-effective choice for most use cases. HDD might be appropriate for large datasets (>10 TB) that aren't latency-sensitive or are accessed infrequently.
Optimize rates
Consider purchasing committed use discounts for workloads with predictable resource needs. You can save 25% of on-demand pricing for a 1-year commitment and 52% for a 3-year commitment.
Spanner
Spanner is a cloud-native, unlimited-scale, strong-consistency database that offers up to 99.999% availability.
Monitor usage
The following are recommendations to help you track the usage of your Spanner resources:
- Monitor your deployment, and configure the node count based on CPU recommendations.
- Set alerts on your deployments to optimize storage resources. To determine the appropriate configuration, refer to the recommended limits per node.
Optimize resources
The following are recommendations to help you optimize your Spanner resources:
- Run smaller workloads on Spanner at much lower cost by provisioning resources with Processing Units (PUs) versus nodes; one Spanner node is equal to 1,000 PUs.
- Improve query execution performance by using the query optimizer.
- Construct SQL statements using best practices for building efficient execution plans.
- Manage the usage and performance of Spanner deployments by using the Autoscaler tool. The tool monitors instances, adds or removes nodes automatically, and helps you ensure that the instances remain within the recommended CPU and storage limits.
- Protect against accidental deletion or writes by using point-in-time recovery (PITR). Databases with longer version retention periods (particularly databases that overwrite data frequently) use more system resources and need more nodes.
- Review your backup strategy, and choose between the following options:
- Backup and restore
- Export and import
Optimize rates
When deciding the location of your Spanner nodes, consider the
cost differences between Google Cloud regions. For example, a node that's
deployed in the us-central1 region costs considerably less per hour than a
node in the southamerica-east1 region.
Bigtable
Bigtable is a cloud-native, wide-column NoSQL store for large scale, low-latency workloads.
Monitor usage
The following are recommendations to help you track the usage of your Bigtable resources:
- Analyze usage metrics to identify opportunities for resource optimization.
- Identify hotspots and hotkeys in your Bigtable cluster by using the Key Visualizer diagnostic tool.
Optimize resources
The following are recommendations to help you optimize your Bigtable resources:
- To help you ensure CPU and disk usage that provides a balance between latency and storage capacity, evaluate and adjust the node count and size of your Bigtable cluster.
- Maintain performance at the lowest cost possible by programmatically scaling your Bigtable cluster to adjust the node count automatically.
Evaluate the most cost-effective storage type (HDD or SSD) for your use case, based on the following considerations:
- HDD storage costs less than SSD, but has lower performance.
- SSD storage costs more than HDD, but provides faster and predictable performance.
The cost savings from HDD are minimal, relative to the cost of the nodes in your Bigtable cluster, unless you store large amounts of data. HDD storage is sometimes appropriate for large datasets (>10 TB) that are not latency-sensitive or are accessed infrequently.
Remove expired and obsolete data using garbage collection.
To avoid hotspots, apply best practices for row key design.
Design a cost-effective backup plan that aligns with your RPO.
To lower the cluster usage and reduce the node count, consider adding a capacity cache for cacheable queries by using Memorystore.
Additional reading
- Blog: A primer on Bigtable cost optimization
- Blog: Best practices for Bigtable performance and cost optimization
BigQuery
BigQuery is a serverless, highly scalable, and cost-effective multicloud data warehouse designed for business agility.
Monitor usage
The following are recommendations to help you track the usage of your BigQuery resources:
- Visualize your BigQuery costs segmented by projects and users. Identify the most expensive queries and optimize them.
- Analyze
slot utilization
across projects, jobs, and reservations by using
INFORMATION_SCHEMAmetadata tables.
Optimize resources
The following are recommendations to help you optimize your BigQuery resources:
- Set up dataset-level, table-level, or partition-level expirations for data, based on your compliance strategy.
- Limit query costs by restricting the number of bytes billed per query. To prevent accidental human errors, enable cost control at the user level and project level.
- Query only the data that you need. Avoid full query scans. To explore and understand data semantics, use the no-charge data preview options.
- To reduce the processing cost and improve performance, partition and cluster your tables when possible.
- Filter your query as early and as often as you can.
- When processing data from multiple sources (like Bigtable, Cloud Storage, Google Drive, and Cloud SQL), avoid duplicating data, by using a federated access data model and querying data directly from the sources.
- Take advantage of BigQuery's backup instead of duplicating data. See Disaster recovery scenarios for data.
Optimize rates
The following are recommendations to help you reduce the billing rates for your BigQuery resources:
- Evaluate how you edit data, and take advantage of lower long-term storage prices.
- Review the differences between flat-rate and on-demand pricing, and choose an option that suits your requirements.
- Assess whether you can use batch-loading instead of streaming inserts for your data workflows. Use streaming inserts if the data loaded to BigQuery is consumed immediately.
- To increase performance and reduce the cost of retrieving data, use cached query results.
Additional reading
- Controlling BigQuery costs
- Cost optimization best practices for BigQuery
- Understanding the principles of cost optimization (PDF)
Dataflow
Dataflow is a fast and cost-effective serverless service for unified stream and batch data processing.
Monitor usage
The following are recommendations to help you track the usage of your Dataflow resources:
- Predict the cost of Dataflow jobs by running small load experiments, finding your job's optimal performance, and extrapolating the throughput factor.
- Gain increased visibility into throughput and CPU usage by using observability dashboards.
- Observe performance, execution, and health-related pipeline metrics using the Dataflow monitoring interface.
Optimize resources
The following are recommendations to help you optimize your Dataflow resources:
- Consider Dataflow Prime for processing big data efficiently.
- Reduce batch-processing costs by using Flexible Resource Scheduling (FlexRS) for autoscaled batched pipelines. FlexRS uses advanced scheduling, Dataflow shuffle, and a combination of preemptible and regular VMs to reduce the cost for batch pipelines.
- Improve performance by using the in-memory shuffle service instead of Persistent Disk and worker nodes.
- For more responsive autoscaling, and to reduce resource consumption, use Streaming Engine, which moves pipeline execution out of the worker VMs and into the Dataflow service backend.
- If the pipeline doesn't need access to and from the internet and other Google Cloud networks, disable public IP addresses. Disabling internet access helps you reduce network costs and improve pipeline security.
- Follow the best practices for efficient pipelining with Dataflow.
Dataproc
Dataproc is a managed Apache Spark and Apache Hadoop service for batch processing, querying, streaming, and machine learning.
The following are recommendations to help you optimize the cost of your Dataproc resources:
- Choose machine types that are appropriate for your workload.
- Scale automatically to match demand by using autoscaling clusters, and pay for only the resources that you need.
- If a cluster can be deleted when the job is completed, consider provisioning an ephemeral cluster using a managed-cluster workflow template.
- To avoid charges for an inactive cluster, use scheduled deletion, which lets you delete a cluster after a specified idle period, at a specified time, or after a specified period.
- Follow the best practices for building long-running clusters on Dataproc.
- Purchase committed use discounts for always-on workloads.
What's next
- Optimize cost for compute services, storage, networking, and operations:
- Explore the other categories of the Google Cloud Architecture Framework