Best practices

This page provides best practices to follow to get the best performance, durability, and availability from Cloud SQL.

If you are experiencing issues with your Cloud SQL instance, see Diagnosing issues for help with troubleshooting and Known issues for a list of known issues with Cloud SQL.

Instance configuration and administration

Best practice More information
Read and follow the operational guidelines to ensure that your instances are covered by the Cloud SQL SLA.
Configure a maintenance window for your primary instance to control when disruptive updates can occur. See Maintenance window.
For read-heavy workloads, add read replicas to offload traffic from the primary instance. Optionally, you can use a load balancer such as HAProxy to manage traffic to the replicas.
If you delete and recreate instances regularly, use a timestamp in the instance ID to increase the likelihood that new instance IDs are usable. You cannot reuse the ID of a deleted instance for a few days after the instance is deleted.
Don't start an administrative operation before the previous operation has completed.

Cloud SQL instances do not accept a new operation request until they have completed the previous operation. If you attempt to start a new operation prematurely, the operation request fails. This includes instance restarts.

Note that the instance status in the GCP Console does not reflect whether an operation is running. The green checkmark denotes only that the instance is in the RUNNABLE state. To see whether an operation is running, go to the Operations tab and check the status of the most recent operation.

Flush system tables after updates MySQL system tables (for example, mysql.user or mysql.db) use the MyISAM storage engine. These tables are vulnerable to unclean shutdowns; you should issue the FLUSH CHANGES command after making a change to these tables, such as adding or updating a user using the mysql client. If MyISAM corruption does occur, and the instance is able to restart, you can use the CHECK TABLE and REPAIR TABLE commands to attempt to address the corruption, but the contents of the table might not be correct.

Data architecture

Best practice More information
Shard your instances where possible. When possible, using many smaller Cloud SQL instances is better than one large instance. Managing a large, monolithic instance presents challenges not posed by a larger number of smaller instances.
Don't use too many database tables.

Too many database tables can impact instance response time. More than 10,000 tables will affect your SLA coverage. See Operational guidelines for more information.

Make sure your tables have a primary or unique key. Cloud SQL uses row-based replication, which works best on tables with primary or unique keys.

Application implementation

Best practice More information
Use good connection management practices, such as connection pooling and exponential backoff. Using these techniques will improve your application's use of resources and help you stay within Cloud SQL connection limits. For more information and code samples, see Managing database connections.
Test your application's response to maintenance updates, which can happen at any time during the maintenance window. Changing the machine type of an instance is the closest approximation of a maintenance update, except that during a maintenance update, the failover replica is updated (and goes offline) first, then the primary instance is updated (and goes offline) after the replica's update is complete. For machine type changes, the primary instance goes offline before the replica does. The application should attempt to reconnect to the database, preferably using exponential backoff, for at least 10 minutes to ensure the application will resume operation after a maintenance event. For more information, see Managing database connections.
Test your application's response to failovers, which can happen at any time. You can manually initiate a failover using the GCP Console, the gcloud command line tool, or the API. See Initiating failover.
Avoid very large transactions. Keep transactions small and short. If a large database update is needed, do it in several smaller transactions rather than one large transaction.
If you are using the Cloud SQL Proxy, make sure you are using the most up-to-date version. See Keeping the Cloud SQL Proxy up to date.

Data import and export

Best practice More information
Speed up imports for small instance sizes. For small instances, you can temporarily increase the tier of an instance to improve performance when importing large data sets.
If you are exporting data for import into Cloud SQL, be sure to use the proper procedure. See Exporting data for import into Cloud SQL.
Speed up imports by using the appropriate mysqldump command when you export. If you are using the mysqldump utility to export data for import into Cloud SQL, consider the following options:
  • Disable autocommit and wrap the file or table in a single transaction using the --single-transaction option.
  • Include many rows in each INSERT statement using the --extended-insert option (on by default).
For First Generation instances, use asynchronous file system replication.
For an initial import into a database, use asynchronous mode for file system replication. After the import completes, return to synchronous mode for production use. You can edit an existing instance to switch between the two modes.
Protect your data with the appropriate Cloud SQL functionality.

Backups and exports are two ways to provide data redundancy and protection. They each protect against different scenarios and complement each other in a robust data protection strategy.

Backups are lightweight and quick to create; they provide a way to restore the data on your instance to its state at the time you took the backup. However, backups have some limitations. If you delete the instance, the backups are also deleted. You can't back up a single database or table. And if the region where the instance is located is unavailable, you cannot restore the instance from that backup, even in an available region.

Exports take longer to create, because an external file is created in Cloud Storage that can be used to recreate your data. Exports are unaffected if you delete the instance. In addition, you can export only a single database or even table, depending on the export format you choose.

หน้านี้มีประโยชน์ไหม โปรดแสดงความคิดเห็น