Create and manage read pools

This page describes how to create and manage read pools for your large read workloads.

Before you begin

  1. Read pools contain copies of a primary instance. If you haven't done so already, create a Cloud SQL Enterprise Plus edition primary instance. While read pools support public IP connectivity, for the purpose of this guide, create a primary instance with private IP (PSA) connectivity. For more information about primary instances and replication, see About replication in Cloud SQL.
  2. After the primary instance is created, choose a password for the root user and run the following command to set the password on the primary instance. Save this password to use later when connecting to the read pool.
    3.     gcloud --project=PROJECT \
    sql users set-password root --host=% \
    --instance=PRIMARY_INSTANCE_NAME --prompt-for-password

    Make the following replacements:

    • PROJECT: the name of the project where you want the primary instance and read pool to reside.
    • PRIMARY_INSTANCE_NAME: the name of the primary instance.

Create a read pool

gcloud

For information about installing and getting started with the gcloud CLI, see Install the gcloud CLI. For information about starting Cloud Shell, see Use Cloud Shell.

Use the following gcloud beta sql instances create command to create a read pool with multiple read pool nodes:

      gcloud beta sql instances create READ_POOL_NAME \
      --tier=TIER --edition=ENTERPRISE_PLUS \
      --instance-type=READ_POOL_INSTANCE --node-count=NODE_COUNT \
      --master-instance-name=PRIMARY_INSTANCE_NAME

Make the following replacements:

  • READ_POOL_NAME: the name you want to use for the read pool.
  • TIER: the machine type you want to use for each read pool node in the read pool, such as db-perf-optimized-N-4.
  • NODE_COUNT: the number of read pool nodes you want in the read pool. Choose any number from 1 to 20.
  • PRIMARY_INSTANCE_NAME: the name of the primary instance associated with the read pool, such as my-primary-instance.

REST v1

Use the insert method of the instances resource to create a read pool with multiple read pool nodes. The databaseVersion property must be the same as the primary.

Before using any of the request data, make the following replacements:

  • PROJECT: the name of the project where you want the primary instance and read pool to reside.
  • REGION: the region for the read pool, such as us-east1. The region must be the same as the primary instance.
  • TIER: the machine type you want to use for each read pool node in the read pool, such as db-perf-optimized-N-4.
  • PRIMARY_INSTANCE_NAME: the name of the primary instance.
  • READ_POOL_NAME: the name you want to use for the read pool, such as my-read-pool.
  • DATABASE_VERSION: the database version you want to use. For example, MYSQL_8_0_37.
  • NODE_COUNT: the number of read pool nodes you want in the read pool. Choose any number from 1 to 20.
  • FULL_NETWORK_NAME: the full network path where you want the read pool to reside, such as projects/vpc-host-project/global/networks/my-network-name.

HTTP method and URL: 

POST https://sqladmin.googleapis.com/v1/projects/PROJECT/instances

Request JSON body: 

{
  "name": "READ_POOL_NAME",
  "masterInstanceName": "PRIMARY_INSTANCE_NAME",
  "project": "PROJECT",
  "databaseVersion": "DATABASE_VERSION",
  "region": "REGION",
  "instanceType": "READ_POOL_INSTANCE",
  "nodeCount": NODE_COUNT,
  "settings": {
    "tier": "TIER",
    "edition": "ENTERPRISE_PLUS",
    "ipConfiguration": {
      "ipv4Enabled": false,
      "privateNetwork": "FULL_NETWORK_NAME"
    }
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

REST v1beta4

Use the insert method of the instances resource to create a read pool with multiple read pool nodes. The databaseVersion property must be the same as the primary.

Before using any of the request data, make the following replacements:

  • PROJECT: the name of the project where you want the primary instance and read pool to reside.
  • REGION: the region for the read pool, such as us-east1. The region must be the same as the primary instance.
  • TIER: the machine type you want to use for each read pool node in the read pool, such as db-perf-optimized-N-4.
  • PRIMARY_INSTANCE_NAME: the name of the primary instance.
  • READ_POOL_NAME: the name you want to use for the read pool, such as my-read-pool.
  • DATABASE_VERSION: the database version you want to use. For example, MYSQL_8_0_37.
  • NODE_COUNT: the number of read pool nodes you want in the read pool. Choose any number from 1 to 20.
  • FULL_NETWORK_NAME: the full network path where you want the read pool to reside, such as projects/vpc-host-project/global/networks/my-network-name.

HTTP method and URL: 

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT/instances

Request JSON body: 

{
  "name": "READ_POOL_NAME",
  "masterInstanceName": "PRIMARY_INSTANCE_NAME",
  "project": "PROJECT",
  "databaseVersion": "DATABASE_VERSION",
  "region": "REGION",
  "instanceType": "READ_POOL_INSTANCE",
  "nodeCount": NODE_COUNT,
  "settings": {
    "tier": "TIER",
    "edition": "ENTERPRISE_PLUS",
    "ipConfiguration": {
      "ipv4Enabled": false,
      "privateNetwork": "FULL_NETWORK_NAME"
    }
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

Convert a read replica to a read pool

You can convert an existing Cloud SQL Enterprise Plus edition read replica into a read pool by specifying the number of nodes in the pool. During this conversion process, the replica IP will become the read pool IP (the read endpoint), so existing clients can connect to the pool without reconfiguration.

The newly created read pool nodes will have the same machine type and configuration as the original read replica. Changing this machine type or configuration requires a separate operation. This operation is only supported for zonal read replicas. To convert a highly available (HA) read replica to a read pool, you must first convert it to a zonal read replica.

For more information, see Edit read pool configuration.

gcloud

For information about installing and getting started with the gcloud CLI, see Install the gcloud CLI. For information about starting Cloud Shell, see Use Cloud Shell.

Use the following gcloud beta sql instances patch command to convert a read replica for use with a read pool:

      gcloud beta sql instances patch READ_REPLICA_NAME \
      --instance-type=READ_POOL_INSTANCE --node-count=NODE_COUNT

Make the following replacements:

  • READ_REPLICA_NAME: the name of the read replica you want to convert.
  • NODE_COUNT: the number of read pool nodes you want in the read pool. Choose any number from 1 to 20.

REST v1

Use the patch method of the instances resource to convert a read replica to a read pool.

Before using any of the request data, make the following replacements:

  • READ_REPLICA_NAME: the name of the read replica you wish to convert, such as my-read-replica.
  • NODE_COUNT: the number of read pool nodes you want in the read pool. Choose any number from 1 to 20.

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/v1/projects/PROJECT/instances/READ_REPLICA_NAME

Request JSON body: 

{
  "instanceType": "READ_POOL_INSTANCE",
  "nodeCount": NODE_COUNT
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

REST v1beta4

Use the patch method of the instances resource to convert a read replica to a read pool.

Before using any of the request data, make the following replacements:

  • READ_REPLICA_NAME: the name of the read replica you wish to convert, such as my-read-replica.
  • NODE_COUNT: the number of read pool nodes you want in the read pool. Choose any number from 1 to 20.

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT/instances/READ_REPLICA_NAME

Request JSON body: 

{
  "instanceType": "READ_POOL_INSTANCE",
  "nodeCount": NODE_COUNT
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

Convert a read pool to a read replica

gcloud

For information about installing and getting started with the gcloud CLI, see Install the gcloud CLI. For information about starting Cloud Shell, see Use Cloud Shell.

Use the following gcloud beta sql instances patch command to convert a read replica for use with a read pool:

      gcloud beta sql instances patch READ_POOL_NAME \
      --instance-type=READ_REPLICA_INSTANCE

Make the following replacements:

  • READ_POOL_NAME: the name of the read pool you want to convert.

REST v1

Use the patch method of the instances resource to convert a read pool to a read replica.

Before using any of the request data, make the following replacements:

  • READ_POOL_NAME: the name of the read pool, such as my-read-pool.

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/v1/projects/PROJECT/instances/READ_POOL_NAME

Request JSON body: 

{
  "instanceType": "READ_REPLICA_INSTANCE"
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

REST v1beta4

Use the patch method of the instances resource to convert a read pool to a read replica.

Before using any of the request data, make the following replacements:

  • READ_POOL_NAME: the name of the read pool, such as my-read-pool.

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT/instances/READ_POOL_NAME

Request JSON body: 

{
  "instanceType": "READ_REPLICA_INSTANCE"
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

View read pool information

gcloud

For information about installing and getting started with the gcloud CLI, see Install the gcloud CLI. For information about starting Cloud Shell, see Use Cloud Shell.

Use the following gcloud beta sql instances describe command to describe the read pool:

      gcloud beta sql instances describe READ_POOL_NAME

Make the following replacements:

  • READ_POOL_NAME: the name of the read pool you want to describe.

An example response with IP address and node information, might look similar to the following:

      ...
      connectionName: my-project:us-central1:read-pool
      ipAddresses:
      - ipAddress: 10.3.0.108
        type: PRIVATE
      nodeCount: 2
      nodes:
      - dnsName: c5bdacb09ffc.j10o8yqc7pve.us-central1.sql.goog.
        gceZone: us-central1-f
        ipAddresses:
        - ipAddress: 10.3.0.112
          type: PRIVATE
        name: read-pool-node-01
        state: RUNNABLE
      - dnsName: 8f77c454d6b2.j10o8yqc7pve.us-central1.sql.goog.
        gceZone: us-central1-c
        ipAddresses:
        - ipAddress: 10.3.0.113
          type: PRIVATE
        name: read-pool-node-02
        state: RUNNABLE

REST v1

Use the get method to view read pool details.

Before using any of the request data, make the following replacements:

  • PROJECT: the name of the project where the read pool resides.
  • READ_POOL_NAME: the name of the read pool, such as my-read-pool.

HTTP method and URL: 

GET https://sqladmin.googleapis.com/v1/projects/PROJECT/instances/READ_POOL_NAME

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

REST v1beta4

Use the get method to view read pool details.

Before using any of the request data, make the following replacements:

  • PROJECT: the name of the project where the read pool resides.
  • READ_POOL_NAME: the name of the read pool, such as my-read-pool.

HTTP method and URL: 

GET https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT/instances/READ_POOL_NAME

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

{
  [...],
  "connectionName": "my-project:us-central1:read-pool",
  "ipAddresses": [
    {
      "type": "PRIVATE",
      "ipAddress": "10.3.0.108"
    }
  ],
  "nodeCount": 2,
  "nodes": [
    {
      "ipAddresses": [
        {
          "type": "PRIVATE",
          "ipAddress": "10.3.0.112"
        }
      ],                                                                                                                 
      "name": "read-pool-node-01",
      "gceZone": "us-central1-f",
      "dnsName": "c5bdacb09ffc.j10o8yqc7pve.us-central1.sql.goog.",
      "state": "RUNNABLE"
    },
    {
      "ipAddresses": [
        {
          "type": "PRIVATE",
          "ipAddress": "10.3.0.113"
        }
      ],
      "name": "read-pool-node-02",
      "gceZone": "us-central1-c",
      "dnsName": "8f77c454d6b2.j10o8yqc7pve.us-central1.sql.goog.",
      "state": "RUNNABLE"
    }
  ]
}

Add or remove read pool nodes

The following steps scale a read pool in or out by modifying the number of read pool nodes in the read pool. Some operation limitations apply. For more information, see Read pool limitations.

gcloud

For information about installing and getting started with the gcloud CLI, see Install the gcloud CLI. For information about starting Cloud Shell, see Use Cloud Shell.

Use the following gcloud beta sql instances patch command to scale the read pool:

      gcloud beta sql instances patch READ_POOL_NAME \
      --node-count=NODE_COUNT

Make the following replacements:

  • READ_POOL_NAME: the name of the read pool.
  • NODE_COUNT: the number of read pool nodes you want in the read pool. Choose any number from 1 to 20.

REST v1

Use the patch method to scale a read pool in or out by modifying the number of read pool nodes in the read pool.

Before using any of the request data, make the following replacements:

  • PROJECT: the name of the project where the read pool resides.
  • NODE_COUNT: the number of read pool nodes you want in the read pool. Choose any number from 1 to 20.

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/v1/projects/PROJECT/instances/READ_POOL_NAME

Request JSON body: 

{
  "nodeCount": NODE_COUNT
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

REST v1beta4

Use the patch method to scale a read pool in or out by modifying the number of read pool nodes in the read pool.

Before using any of the request data, make the following replacements:

  • PROJECT: the name of the project where the read pool resides.
  • NODE_COUNT: the number of read pool nodes you want in the read pool. Choose any number from 1 to 20.

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT/instances/READ_POOL_NAME

Request JSON body: 

{
  "nodeCount": NODE_COUNT
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

Edit read pool configuration

The following steps show you how to edit read pool configuration. For more detailed information, see About instance settings and Edit instances.

gcloud

For information about installing and getting started with the gcloud CLI, see Install the gcloud CLI. For information about starting Cloud Shell, see Use Cloud Shell.

Use the following gcloud beta sql instances patch command to vertically scale the read pool, for example, by modifying the machine type:

      gcloud beta sql instances patch READ_POOL_NAME \
      --tier=TIER

Make the following replacements:

  • READ_POOL_NAME: the name of the read pool.
  • TIER: the machine type you want to apply to each read pool node in the read pool, such as db-perf-optimized-N-8.

REST v1

Use the patch method to modify read pool node configuration. Settings are uniformly applied across all read pool nodes in the read pool.

Before using any of the request data, make the following replacements:

  • PROJECT: the name of the project where the read pool resides.
  • TIER: the machine type you want to use for each read pool node in the read pool, such as db-perf-optimized-N-4.
  • READ_POOL_NAME: the name of the read pool, such as my-read-pool.

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/v1/projects/PROJECT/instances/READ_POOL_NAME

Request JSON body: 

{
  "settings": {
    "tier": "TIER"
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

REST v1beta4

Use the patch method to modify read pool node configuration. Settings are uniformly applied across all read pool nodes in the read pool.

Before using any of the request data, make the following replacements:

  • PROJECT: the name of the project where the read pool resides.
  • TIER: the machine type you want to use for each read pool node in the read pool, such as db-perf-optimized-N-4.
  • READ_POOL_NAME: the name of the read pool, such as my-read-pool.

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT/instances/READ_POOL_NAME

Request JSON body: 

{
  "settings": {
    "tier": "TIER"
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

Connect to a read pool

There are many ways to connect to a read pool. The following steps show one way, namely, connecting to a read pool with a private IP address by creating a VM in the same VPC network to serve as the connection source.

For more information about other ways you can configure connectivity to a Cloud SQL instance, see About Cloud SQL connections. The connection methods usually require you to first obtain the IP address or connection name of the instance, as described in View read pool information. Read pools support most of the connection methods available for other Cloud SQL instances, with some limitations.

If connecting using the Cloud SQL Auth Proxy or the Cloud SQL Connectors, make sure to update to the latest version. For read pool support, the minimum required versions include the following:

  • Cloud SQL Auth Proxy: v2.15.2
  • Cloud SQL Python Connector: v1.18.0
  • Cloud SQL Go Connector: v1.16.0
  • Cloud SQL Node Connector: v1.7.0
  • Cloud SQL Java Connector: v1.24.0

Console

To connect to a read pool, complete the following steps:

  1. In the Google Cloud console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

    You're taken to the instance Overview page. Click into the new read pool to view the details including its private IP address. In the Connect to this instance section, copy and save the instance's Connection name. The connection name is in the format projectID:region:instanceID. You'll use this connection name later when starting the Cloud SQL Auth Proxy.
  2. Create a Compute Engine VM.
  3. Open two SSH connections to the Compute Engine VM. These are used in subsequent steps to run the Cloud SQL Auth Proxy and run the database client.
  4. Install the client.
  5. Install the Cloud SQL Auth Proxy.
  6. Start the Cloud SQL Auth Proxy.
  7. Connect to your Cloud SQL instance.

For more information, see Connect to a Cloud SQL instance with private IP.

Delete a read pool

gcloud

For information about installing and getting started with the gcloud CLI, see Install the gcloud CLI. For information about starting Cloud Shell, see Use Cloud Shell.

Use the following gcloud sql instances delete command to delete a read pool:

      gcloud sql instances delete READ_POOL_NAME

Make the following replacements:

  • READ_POOL_NAME: the name of the read pool you want to delete.

REST v1

Use the delete method to delete a read pool.

Before using any of the request data, make the following replacements:

  • PROJECT: the name of the project where the read pool resides.
  • READ_POOL_NAME: the name of the read pool, such as my-read-pool.

HTTP method and URL: 

DELETE https://sqladmin.googleapis.com/v1/projects/PROJECT/instances/READ_POOL_NAME

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

REST v1beta4

Use the delete method to delete a read pool.

Before using any of the request data, make the following replacements:

  • PROJECT: the name of the project where the read pool resides.
  • READ_POOL_NAME: the name of the read pool, such as my-read-pool.

HTTP method and URL: 

DELETE https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT/instances/READ_POOL_NAME

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

What's next