Getting to know Cloud SQL.
Building and operationalizing storage systems.
Cloud SQL is a relational database service that is fully managed and works with MySQL, PostgreSQL, and SQL Server databases.
A managed database requires less administration and operational resources than an unmanaged database since Google Cloud handles essential operational activities like High availability and failover, Upkeep and upgrades, Network authorization, Generating backups, Export and import, tracking and disk resizing at scale.
Cloud SQL is a regional database with up to 64 TB of storage available for a dedicated core and up to 3 TB for a shared core. It can automatically expand storage space as required. In addition, users may leverage Cloud Spanner to seek greater data storage capacity or multi-regional connectivity.
Setting Up Cloud SQL
The initial process in setting up a Cloud SQL Database server is to select one of three relational database management systems (RDBMSs). The different RDBMS all go through a typical configuration procedure.
The number of virtual CPUs (vCPUs) and RAM that are accessible vary based on the machine type you select when using Cloud SQL. For example, users may select up to 96 CPUs and 624 GB of memory.
A public IP address is allocated by default, but customers may choose to use a private or public IP address for the Cloud SQL instance. When utilising a public IP, you must designate one or more external networks that interface to the database instance. Additionally, you may use Cloud SQL Proxy to communicate with the database. Cloud SQL Proxy assists with managing authentication and performs data encryption automatically.
A Cloud SQL instance is, by default, with a single instance in a single zone. Therefore, you should select the High Availability option to ensure that your application can continue accessing data even if one instance is unavailable for any reason, which is advised for systems used in production.
By doing so, Cloud SQL will create a new instance in a different zone and will simultaneously duplicate data from the primary instance to the standby instance. Cloud SQL will fail over or shift to utilising the standby instance if the original instance stops functioning.
The maintenance of smaller databases is more straightforward. Furthermore, if a single instance is affected, the effects are restricted to data on that particular instance. There should be at most 50,000 tables in a Cloud SQL database instance. All tables need a primary key to allow for row-based replication and avoid lag.
Enhancing Read Performance by leveraging Read Replicas
Read replicas can help boost the speed of cloud SQL databases. The read replica is a carbon copy of the original instance. The read replica receives data and other inputs from the primary instance in near real-time.
Applications may read exclusively from the read replica while the original instance executes write functions.
Consider the following on reading replicas:
Read replicas are read-only; they do not accept write operations.
When using Cloud SQL High Availability, place read replicas in a separate zone from the primary instance.
To support read replicas, automated backups and binary logging must be on.
Read replicas do not adhere to maintenance windows and may receive maintenance updates anytime, which can distort the operation.
Promoting a read replica stops replication and turns the instance into a full-blown database.
Best practices for data import and export
Cloud SQL databases allow for the import and export of data. Therefore, each database server comes with its import and export package.
SQL dump files and CSV files for MySQL export and import.
SQL dump files, CSV files, pg_dump and pg_restore for PostgreSQL export and import.
SQL dump files and BAK files for SQL Server export and import.
Here are some things to take note of when importing and exporting data in Cloud SQL:
Utilise BAk files rather than SQL files and operate with smaller data batches to speed up lengthy import and export operations.
Avoid using a Cloud Storage bucket with requester pays.
To cut costs, import from and export to compressed files.
Final Thoughts
Cloud SQL is Google Cloud's fully managed solution for local databases such as MySQL, PostgreSQL, and SQL Server. In addition, it has an all-around feature to focus fewer resources on maintaining and administering your database and more time utilizing it.
Cloud SQL is ideal for regional-based applications that don't require more than 64 TB of data storage per instance. However, if you desire a more robust and global database, use Cloud Spanner.