💾 Cloud SQL Setup - Advanced

Introduction

Advanced Cloud SQL patterns enable building resilient, high-performance databases with automatic failover, read replicas, and sophisticated optimization techniques.

Key Learning Outcomes

By the end of this lesson, you'll understand:

• High availability and failover configuration

• Read replicas and distributed queries

• Connection pooling optimization

• Query performance tuning

• Backup and recovery strategies

• Multi-region deployments

• Custom metrics and monitoring

High Availability Setup

Create HA-Enabled Instance

gcloud sql instances create ha-instance \
  --database-version=MYSQL_8_0 \
  --region=us-central1 \
  --tier=db-n1-standard-2 \
  --availability-type=REGIONAL \
  --backup-start-time=03:00 \
  --enable-bin-log \
  --retained-backups-count=30

Automatic Failover Configuration

# Enable automated backups and binary logging
gcloud sql instances patch ha-instance \
  --backup-start-time=03:00 \
  --backup-location=us-multi-region \
  --transaction-log-retention-days=7

Read Replicas

Create Read Replica

# Create read replica in different region
gcloud sql instances create ha-instance-replica \
  --master-instance-name=ha-instance \
  --region=us-east1 \
  --replica-type=READ \
  --tier=db-n1-standard-1

Application layer connection routing:

class DatabaseRouter {
  constructor() {
    this.primary = {
      host: 'primary-instance-ip',
      port: 3306
    };
    this.replicas = [
      {host: 'replica1-ip', port: 3306},
      {host: 'replica2-ip', port: 3306}
    ];
  }

  // Route read-heavy queries to replicas
  async executeQuery(query, isWrite = false) {
    if (isWrite) {
      return this.executeOnPrimary(query);
    }

    // Round-robin replica selection
    const replica = this.replicas[Math.floor(Math.random() * this.replicas.length)];
    return this.executeOnReplica(query, replica);
  }

  async executeOnPrimary(query) {
    const connection = await primaryPool.getConnection();
    try {
      return await connection.query(query);
    } finally {
      connection.release();
    }
  }

  async executeOnReplica(query, replica) {
    const replicaPool = mysql.createPool({
      host: replica.host,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME
    });

    const connection = await replicaPool.getConnection();
    try {
      return await connection.query(query);
    } finally {
      connection.release();
    }
  }
}

Connection Pooling Optimization

const mysql = require('mysql2/promise');

class OptimizedPoolManager {
  constructor() {
    this.pools = new Map();
  }

  getPool(name, config) {
    if (!this.pools.has(name)) {
      const pool = mysql.createPool({
        ...config,
        connectionLimit: 20,
        waitForConnections: true,
        queueLimit: 5,
        enableKeepAlive: true,
        keepAliveInitialDelayMs: 30000,
        multipleStatements: false,
        trace: false,
        decimalNumbers: true
      });

      pool.on('error', (err) => {
        console.error(`Pool ${name} error:`, err);
      });

      this.pools.set(name, pool);
    }

    return this.pools.get(name);
  }

  async executeWithRetry(poolName, query, params, maxRetries = 3) {
    const pool = this.getPool(poolName);
    let lastError;

    for (let i = 0; i < maxRetries; i++) {
      try {
        const connection = await pool.getConnection();
        try {
          return await connection.execute(query, params);
        } finally {
          connection.release();
        }
      } catch (error) {
        lastError = error;

        if (error.code === 'PROTOCOL_CONNECTION_LOST') {
          console.log(`Retry ${i + 1}/${maxRetries}...`);
          await new Promise(resolve => setTimeout(resolve, 1000 * (i + 1)));
        } else {
          throw error;
        }
      }
    }

    throw lastError;
  }
}

Query Optimization

Index Strategy

-- Analyze slow queries
SHOW SLOW QUERIES;

-- Create optimal indexes
CREATE INDEX idx_user_status ON users(status);
CREATE INDEX idx_post_author_date ON posts(author_id, created_at DESC);
CREATE FULLTEXT INDEX ft_title_content ON posts(title, content);

-- Monitor index usage
SELECT * FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = 'mydb';

Query Plan Analysis

async function analyzeQuery(query) {
  const connection = await pool.getConnection();
  try {
    const [plan] = await connection.query(`EXPLAIN FORMAT=JSON ${query}`);
    console.log(JSON.stringify(plan, null, 2));
    return plan;
  } finally {
    connection.release();
  }
}

// Usage
const [rows] = await analyzeQuery(
  'SELECT * FROM users WHERE status = "active" ORDER BY created_at DESC LIMIT 100'
);

Multi-Region Deployment

# Primary instance in us-central1
gcloud sql instances create primary \
  --region=us-central1 \
  --tier=db-n1-standard-2 \
  --availability-type=REGIONAL

# Read replica in europe-west1
gcloud sql instances create eu-replica \
  --master-instance-name=primary \
  --region=europe-west1 \
  --replica-type=READ

# Read replica in asia-southeast1
gcloud sql instances create asia-replica \
  --master-instance-name=primary \
  --region=asia-southeast1 \
  --replica-type=READ

Backup and Recovery

Advanced Backup Strategy

# On-demand backup before migrations
gcloud sql backups create \
  --instance=primary \
  --description="Pre-migration backup"

# Automated backup retention
gcloud sql instances patch primary \
  --retained-backups-count=30 \
  --backup-start-time=03:00

# Point-in-time recovery
gcloud sql backups restore BACKUP_ID \
  --backup-instance=primary \
  --backup-configuration=default

Point-in-time recovery script:

#!/bin/bash

# Create clone from point-in-time
gcloud sql instances clone primary recovery-instance \
  --point-in-time=2024-01-15T14:30:00Z

# Verify recovery
gcloud sql connect recovery-instance --user=root

# If successful, promote recovery-instance to replace primary

Performance Metrics

class CloudSQLMonitor {
  async getMetrics(instanceName) {
    const client = new monitoring.MetricServiceClient();
    const projectName = client.projectPath(projectId);

    const request = {
      name: projectName,
      filter: `
        resource.type = "cloudsql_database" AND
        resource.labels.database_id = "${projectId}:${instanceName}" AND
        metric.type = "cloudsql.googleapis.com/database/cpu/utilization"
      `,
      interval: {
        endTime: {seconds: Math.floor(Date.now() / 1000)},
        startTime: {seconds: Math.floor((Date.now() - 3600000) / 1000)}
      }
    };

    const [result] = await client.listTimeSeries(request);
    return result;
  }
}

Key Takeaways

• **High availability** ensures automatic failover

• **Read replicas** scale read capacity across regions

• **Connection pooling** optimizes resource usage

• **Index strategy** dramatically improves query performance

• **Multi-region** provides disaster recovery

• **Automated backups** enable point-in-time recovery

Next Steps

Explore migration strategies from on-premises databases, or learn about Cloud SQL Auth for secure connections.