DEV Community

Cover image for MySQL HeatWave: Read Replicas for Scaling Read-Heavy Workloads
Ryan Giggs
Ryan Giggs

Posted on

MySQL HeatWave: Read Replicas for Scaling Read-Heavy Workloads

#mysqlheatwave #readreplicas #highavailability #databasescaling

Read replicas are a powerful feature of MySQL HeatWave that enable you to scale read-heavy workloads by distributing queries across multiple database instances. As read-only copies of your source DB system, read replicas handle SELECT queries, analytics, and reporting workloads while the primary instance handles write operations. This architecture improves application performance, enhances availability, and supports disaster recovery strategies.

This guide covers read replica concepts, creation, connectivity, use cases, maintenance, and limitations.

What Are Read Replicas?

Read replicas are read-only copies of a MySQL HeatWave DB system that are automatically updated using asynchronous replication from the source DB system.

Key Characteristics:

  • Read-only: Cannot execute write operations (INSERT, UPDATE, DELETE)
  • Asynchronous replication: Data replicated from source with minimal lag
  • Full data copy: Contains complete copy of source DB system data
  • Independent endpoints: Each read replica has its own connection endpoint
  • Automatic lifecycle: Started, stopped, and deleted with source DB system

Benefits of Read Replicas

1. Redirecting Excess Traffic

Distribute read traffic across multiple instances to prevent primary instance overload.

Scenario: E-commerce application during peak shopping season

  • Primary handles transactions (orders, payments)
  • Read replicas serve product catalogs, inventory checks, search queries
  • Reduces primary instance load by 60-80%

2. Business Reporting and Analytics

Offload resource-intensive reporting queries to read replicas.

Scenario: Daily business intelligence reports

  • Complex JOIN queries with aggregations
  • Historical data analysis
  • Real-time dashboards
  • No impact on transactional performance

3. Geographic Distribution

Place read replicas closer to users in different regions.

Scenario: Global application

  • Primary in US-East
  • Read replica in EU-West for European users
  • Read replica in AP-South for Asian users
  • Reduced latency for read operations

4. Disaster Recovery

Read replicas serve as standby databases for failover scenarios.

Scenario: Primary DB system failure

  • Promote read replica to standalone DB system
  • Minimal data loss (replication lag only)
  • Faster recovery than backup restoration
  • Business continuity maintained

5. Development and Testing

Create isolated environments without impacting production.

Scenario: Testing new features

  • Read replica mirrors production data
  • Safe environment for experiments
  • No risk to production workload
  • Fresh data for realistic testing

Creating Read Replicas

Prerequisites

Before creating read replicas, ensure the following requirements are met:

1. OCI User Accounts with Required Privileges

Your OCI user must have permissions to:

  • Create and manage read replicas
  • Access the source DB system
  • Manage network resources

Required IAM Policy:

Allow group DatabaseAdmins to manage mysql-family in compartment ProductionDatabases
Enter fullscreen mode Exit fullscreen mode

2. Operational DB System

An active MySQL HeatWave DB system must exist and be in ACTIVE state. You can create read replicas on standalone or high availability DB systems.

3. Compatible Shape

Read replicas are not supported on shapes that have less than 8 ECPUs or less than 4 OCPUs.

Supported Shapes:

  • ECPU: MySQL.8 (8 ECPUs), MySQL.16, MySQL.32, MySQL.64, MySQL.128, MySQL.256
  • OCPU (deprecated): MySQL.VM.Standard.E3.1.8GB (4 OCPUs) and higher

Note: OCPU shapes are deprecated for new users after September 5, 2024, and will be unavailable after March 13, 2026.

4. Network Configuration

Read replicas inherit the VCN and subnet from the source DB system by default, but you can specify a different subnet in the same VCN or different VCN.

Maximum Read Replicas

You can create a maximum of 18 read replicas per DB system. Each read replica has its own endpoint for connections.

Creating Read Replicas via OCI Console

  1. Navigate to DB System

    • Open OCI Console
    • Go to Databases → HeatWave MySQL → DB Systems
    • Select your source DB system

  2. Access Read Replicas Section

    • Under Resources, click Read replicas
    • Click Create read replica

  3. Configure Read Replica

    • Display name: Descriptive name (e.g., "prod-analytics-replica")
    • Description: Purpose description
    • Shape: Inherit from source or override with different shape
    • Configuration: Inherit from source or select custom configuration
    • MySQL version: Inherit or override (must be same or higher)
    • Networking: Select VCN and subnet (can differ from source)
    • Delete protection: Enable to prevent accidental deletion

  4. Review and Create

    • Click Create read replica
    • Read replica enters CREATING state
    • Typically available in 15-30 minutes

Creating Read Replicas via OCI CLI

Generate configuration template:

oci mysql replica create --generate-full-command-json-input > replica-config.json
Enter fullscreen mode Exit fullscreen mode

Edit configuration:

{
  "dbSystemId": "ocid1.mysqldbsystem.oc1...",
  "displayName": "analytics-read-replica",
  "description": "Read replica for BI workloads",
  "shapeName": "MySQL.16",
  "isDeleteProtected": true,
  "waitForState": ["SUCCEEDED"]
}
Enter fullscreen mode Exit fullscreen mode

Create replica:

oci mysql replica create --from-json file://replica-config.json
Enter fullscreen mode Exit fullscreen mode

Read Replica Properties

Inherited vs. Overridden Properties

By default, a read replica inherits shape, configuration, and version from the DB system. You can override these properties.

When Inherited:

  • Changing property on DB system also changes it on read replica
  • Replica follows source configuration automatically

When Overridden:

  • Changing property on DB system does NOT affect read replica
  • Replica maintains independent configuration

Overridable Properties:

  • Shape (e.g., use larger shape for heavy analytics)
  • Configuration (e.g., tune for read-heavy workload)
  • MySQL version (must be same or higher than source)

Automatic Read Replica Load Balancer

When you create the first read replica of a DB system, a read replica load balancer is created automatically, which distributes read traffic among the read replicas.

Load Balancer Features:

  • Distributes queries across all read replicas
  • Health checks ensure traffic goes to ACTIVE replicas
  • Automatic failover if replica becomes unavailable
  • No manual configuration required

Connecting to Read Replicas

Client applications cannot directly access read replicas from the internet. Connection methods mirror those for DB systems.

Connection Methods

1. Load Balancer (Recommended)

Use the automatically created read replica load balancer for balanced traffic distribution.

Load Balancer Endpoint:

<replica-load-balancer-ip>:3306
Enter fullscreen mode Exit fullscreen mode

Benefits:

  • Automatic distribution across replicas
  • Health checks
  • Simplified application configuration
  • High availability

Important: When connecting via load balancer, user accounts require username only (no hostname restriction like 'user'@'%').

2. Bastion Session

Provides restricted and time-limited secure access.

Use Case: Temporary administrative access, troubleshooting

Setup:

  1. Create bastion session in OCI Console
  2. Session provides temporary SSH access
  3. Connect to read replica from bastion
  4. Session expires after defined period (30 minutes to 3 hours)

Example:

# Create bastion session
oci bastion session create-managed-ssh ...

# Connect through bastion
ssh -i <private_key> -o ProxyCommand="ssh -W %h:%p ..." replica_ip
mysql -h <REPLICA_IP> -u admin -p
Enter fullscreen mode Exit fullscreen mode

3. Compute Instance

For long-term, secure access.

Setup:

  1. Create compute instance in same VCN
  2. Configure security lists to allow MySQL ports
  3. Install MySQL client on compute instance
  4. Connect to read replica endpoint

Example:

mysql -h <REPLICA_IP> -P 3306 -u admin -p
Enter fullscreen mode Exit fullscreen mode

4. VPN Connection

For site-to-site connectivity from on-premises.

Options:

  • Site-to-Site VPN: IPSec VPN over internet
  • FastConnect: Dedicated private connection
  • OpenVPN: Client VPN access

5. Direct Application Connection

Configure application to use read replica endpoints.

Application Configuration:

# Primary for writes
primary_connection = mysql.connector.connect(
    host='primary_db_ip',
    user='app_user',
    password='password',
    database='production'
)

# Read replica for reads
replica_connection = mysql.connector.connect(
    host='replica_ip',
    user='app_user',
    password='password',
    database='production'
)

# Write operation
primary_connection.cursor().execute("INSERT INTO orders ...")

# Read operation
replica_connection.cursor().execute("SELECT * FROM products")
Enter fullscreen mode Exit fullscreen mode

Monitoring Read Replicas

Read Replica Lag

Read replica lag measures how far behind the replica is from the source, measured by the read replica lag metric.

Factors Affecting Lag:

  • Workload on each read replica
  • CPU and memory resources
  • Network latency
  • Volume of writes on primary

Monitoring Lag:

-- On read replica
SHOW REPLICA STATUS\G
Enter fullscreen mode Exit fullscreen mode

Check Seconds_Behind_Source value.

Creating Alarms

Set up OCI alarms to monitor read replica health:

  1. Navigate to Monitoring service
  2. Create alarm for ReplicaLag metric
  3. Set threshold (e.g., > 60 seconds)
  4. Configure notification topic
  5. Add email or webhook destination

Alarm Query:

ReplicaLag[1m]{resourceDisplayName = "analytics-replica"}.mean() > 60
Enter fullscreen mode Exit fullscreen mode

Automatic Recovery

MySQL HeatWave Service performs automatic recovery on any read replica that has failed.

Recovery Process:

  1. Failure detected
  2. Read replica set to UPDATING state
  3. Cannot accept client connections during recovery
  4. After success, set to ACTIVE state
  5. Becomes operational again

Failed Beyond Recovery:
If replica cannot replicate from DB system anymore, it's set to NEEDS_ATTENTION state. Manual intervention required.

Read Replica Maintenance and Upgrades

Standalone DB System Upgrades

Read replicas are upgraded in parallel with source DB system. Both the source and all read replicas go offline for an update and upgrade at the same time.

Process:

  1. Maintenance window starts
  2. Source and all replicas enter UPDATING state
  3. Upgrades applied simultaneously
  4. All return to ACTIVE state together

Downtime: Brief period (typically 5-15 minutes depending on upgrade type)

High Availability DB System Upgrades

For HA DB systems, read replicas are upgraded one by one using rolling maintenance process. Only one read replica goes offline at a time.

Rolling Upgrade Process:

  1. Replica 1 enters UPDATING state
  2. Replica 1 upgraded, returns to ACTIVE
  3. Replica 2 enters UPDATING state
  4. Replica 2 upgraded, returns to ACTIVE
  5. Continue for all replicas

Benefits:

  • Minimal impact on read capacity
  • At least N-1 replicas always available
  • Reduced performance impact

Duration: Longer total time but better availability

Read Replica Limitations

1. Shape Requirements

Read replicas are not supported on shapes that have less than 8 ECPUs or less than 4 OCPUs.

Unsupported:

  • MySQL.2 (2 ECPUs)
  • MySQL.4 (4 ECPUs)
  • MySQL.VM.Standard.E3.1.8GB (1 OCPU)
  • MySQL.VM.Standard.E3.1.16GB (2 OCPUs)

2. Load Balancer Account Name Restrictions

When connecting via load balancer, user accounts require username only ('user'@'%'), not hostname-specific restrictions.

Won't Work:

CREATE USER 'app_user'@'10.0.1.%' ...
Enter fullscreen mode Exit fullscreen mode

Must Use:

CREATE USER 'app_user'@'%' ...
Enter fullscreen mode Exit fullscreen mode

3. Outbound Replication Source

Read replicas cannot be configured as a source for outbound replication. Only the primary DB system can be a replication source.

4. IPv4 Subnet Support Only

Read replicas are only supported with IPv4 subnets. IPv6 subnets are not supported.

5. Bandwidth Limits

Read replicas support up to 8 Gbps bandwidth for client connections.

Implications:

  • High-throughput workloads may hit bandwidth limits
  • Monitor network metrics
  • Consider multiple replicas for very high throughput needs

6. Lifecycle Dependency

Read replicas are started when DB system is started, stopped when DB system is stopped, and deleted when DB system is deleted.

Cannot:

  • Start replica independently if source is stopped
  • Keep replica running when source is stopped
  • Preserve replica after deleting source

7. HeatWave Cluster Not Supported

Read replicas cannot have HeatWave clusters attached. If the source DB system has a HeatWave cluster, read replicas will not replicate HeatWave-specific data.

8. Configuration Changes

Prior to March 2024, configuration changes required deleting and recreating read replicas. As of March 26, 2024, you can now change shape and configuration of DB systems with read replicas without deleting replicas.

Best Practices

1. Use Multiple Read Replicas

For high availability and load distribution:

  • Minimum 2 replicas for redundancy
  • More replicas for higher read throughput
  • Maximum 18 replicas per DB system

2. Monitor Replication Lag

Set up alerts and regularly check:

SHOW REPLICA STATUS\G
Enter fullscreen mode Exit fullscreen mode

If lag exceeds 60 seconds consistently:

  • Increase replica shape
  • Reduce read load
  • Optimize slow queries

3. Size Replicas Appropriately

Match replica shape to workload:

  • Light analytics: Same shape as source
  • Heavy reporting: Larger shape than source
  • Mixed workload: Multiple replicas with different shapes

4. Implement Read/Write Splitting in Applications

Direct writes to primary, reads to replicas:

def ex# Conclusionecute_query(query):
    if query.startswith('SELECT'):
        return replica_connection.execute(query)
    else:
        return primary_connection.execute(query)
Enter fullscreen mode Exit fullscreen mode

5. Use Load Balancer for Simplicity

Automatic distribution and failover:

  • Configure application with load balancer endpoint only
  • No application changes when adding/removing replicas
  • Built-in health checks

6. Plan for Replication Lag

Design applications to tolerate eventual consistency:

  • Don't read immediately after write from replica
  • Use primary for read-after-write scenarios
  • Communicate lag tolerance to users

7. Test Failover Scenarios

Regularly test promoting replica to standalone:

  • Verify promotion procedure
  • Measure recovery time
  • Update application connection strings
  • Practice makes perfect for real disasters

8. Enable Delete Protection

For production replicas:

--is-delete-protected true
Enter fullscreen mode Exit fullscreen mode

Prevents accidental deletion.

Read replicas are essential for scaling MySQL HeatWave beyond single-instance limitations. By distributing read traffic across multiple instances, you improve application performance, support business analytics, enable geographic distribution, and enhance disaster recovery capabilities.

Key takeaways:

  • Asynchronous replication: Data replicated with minimal lag
  • 18 replicas maximum: Scale read capacity significantly
  • Shape requirement: Minimum 8 ECPUs or 4 OCPUs
  • Automatic load balancer: Simplifies traffic distribution
  • Multiple connection methods: Bastion, compute, VPN, load balancer
  • Rolling upgrades for HA: Minimal downtime during maintenance
  • Monitor lag: Critical for maintaining data freshness
  • Disaster recovery: Promote replica to standalone if needed

Whether supporting global applications, offloading analytics, or implementing disaster recovery, MySQL HeatWave read replicas provide the scalability and flexibility needed for modern database architectures.

Top comments (0)