This document in the Google Cloud Architecture Framework provides recommendations to help you optimize the performance of your databases in Google Cloud.
Cloud SQL
The following recommendations help you to optimize the performance of your Cloud SQL instances running SQL Server, MySQL, and PostgreSQL databases.
- For SQL Server databases, Google recommends that you modify certain parameters and retain the default values for some parameters.
- When you choose the storage type for MySQL or PostgreSQL databases, consider the cost-performance tradeoff between SSD and HDD storage.
- To identify and analyze performance issues with PostgreSQL databases, use the Cloud SQL Insights dashboard.
- To diagnose poor performance when running SQL queries, use the
EXPLAINstatement.
For more information, see the following documentation:
Bigtable
This section provides recommendations to help you optimize the performance of your Bigtable instances.
Plan capacity based on performance requirements
You can use Bigtable in a broad spectrum of applications, each with a different optimization goal. For example, for batch data-processing jobs, throughput might be more important than latency. For an online service that serves user requests, you might need to prioritize lower latency over throughput. When you plan capacity for your Bigtable clusters, consider the tradeoffs between throughput and latency. For more information, see Plan your Bigtable capacity.
Follow schema-design best practices
Your tables can scale to billions of rows and thousands of columns, enabling you to store petabytes of data. When you design the schema for your Bigtable tables, consider the schema design best practices.
Monitor performance and make adjustments
Monitor the CPU and disk usage for your instances, analyze the performance of each cluster, and review the sizing recommendations that are shown in the monitoring charts.
Spanner
This section provides recommendations to help you optimize the performance of your Spanner instances.
Choose a primary key that prevents a hotspot
A hotspot is a single server that is forced to handle many requests. When you choose the primary key for your database, follow the schema design best practices to prevent a hotspot.
Follow best practices for SQL coding
The SQL compiler in Spanner converts each declarative SQL statement that you write into an imperative query execution plan. Spanner uses the execution plan to run the SQL statement. When you construct SQL statements, follow SQL best practices to make sure that Spanner uses execution plans that yield optimal performance.
Use query options to manage the SQL query optimizer
Spanner uses a SQL query optimizer to transform SQL statements into efficient query execution plans. The query execution plan that the optimizer produces might change slightly when the query optimizer itself evolves, or when the database statistics are updated. You can minimize the potential for performance regression when the query optimizer or the database statistics change by using query options.
Visualize and tune the structure of query execution plans
To analyze query performance issues, you can visualize and tune the structure of the query execution plans by using the query plan visualizer.
Use operations APIs to manage long-running operations
For certain method calls, Spanner creates long-running operations, which might take a substantial amount of time to complete. For example, when you restore a database, Spanner creates a long-running operation to track restore progress. To help you monitor and manage long-running operations, Spanner provides operations APIs. For more information, see Managing long-running operations.
Follow best practices for bulk loading
Spanner supports several options for loading large amounts of data in bulk. The performance of a bulk-load operation depends on factors such as partitioning, the number of write requests, and the size of each request. To load large amounts of data efficiently, follow bulk-loading best practices.
Monitor and control CPU utilization
The CPU utilization of your Spanner instance can affect request latencies. An overloaded backend server can cause higher request latencies. Spanner provides CPU utilization metrics to help you investigate high CPU utilization. For performance-sensitive applications, you might need to reduce CPU utilization by increasing the compute capacity.
Analyze and solve latency issues
When a client makes a remote procedure call to Spanner, the API request is first prepared by the client libraries. The request then passes through the Google Front End and the Cloud Spanner API frontend before it reaches the Spanner database. To analyze and solve latency issues, you must measure and analyze the latency for each segment of the path that the API request traverses. For more information, see Spanner end-to-end latency guide.
Launch applications after the database reaches the warm state
As your Spanner database grows, it divides the key space of your data into splits. Each split is a range of rows that contains a subset of your table. To balance the overall load on the database, Spanner dynamically moves individual splits independently and assigns them to different servers. When the splits are distributed across multiple servers, the database is considered to be in a warm state. A database that's warm can maximize parallelism and deliver improved performance. Before you launch your applications, we recommend that you warm up your database with test data loads.
What's next
Review the best practices for optimizing the performance of your compute, storage, networking, and analytics resources:
- Optimize compute performance.
- Optimize storage performance.
- Optimize networking performance.
- Optimize analytics performance.